标签：#postgressql

Losing PostgreSQL Gains? Blame Inline JSONB!! PostgreSQL's jsonb is a favorite among developers for its flexibility - but it hides a dark side. When used carelessly, especially in-line within rows under 2KB, it can silently destroy performance, even if you're using indexes. Here's why. 🔍 The Hidden Cost of JSONB (Inline Storage) PostgreSQL stores table rows in 8KB pages, packing as many tuples as possible. For a typical row with 10–12 columns, and small text/integers, 40–100 rows can easily fit per page. Typically row count = Page Size(8kb) / row size + row metadata (30-50 bytes approx.) But the game changes when you add jsonb. Example CREATE TABLE events ( id serial PRIMARY KEY, user_id int, action text, metadata jsonb ); Suppose metadata which is a jsonb column contains: { "ip": "127.0.0.1", "device": "Android", "country": "IN" } This JSON might be just 100–500 bytes, so PostgreSQL stores it in-line inside the same page (no TOASTing). Result Each row size jumps from ~80 bytes → ~200–400 bytes Row count per page drops from 100 → 20–40 Index scan still needs to read each page for matching rows More pages = more I/O, slower performance 🔢 Real Benchmark Insight Performance comparisonEven with a GIN or B-tree index on the JSONB column, PostgreSQL still needs to scan all matching pages to retrieve the full tuple. 🧠 Why Index Doesn't Save You Say you index a JSONB key like: CREATE INDEX ON events ((metadata->>'ip')); And query: SELECT * FROM events WHERE metadata->>'ip' = '127.0.0.1'; PostgreSQL will: Use the index to find matching tuples Still need to fetch the row from disk Because JSONB is in-line, many pages are touched More page fetches = more IO = slower queries 🩹 What You Can Do ✅ Force TOAST: Add padding to make JSONB exceed 2KB: UPDATE events SET metadata = metadata || jsonb_build_object('padding', repeat('x', 2000)); ✅ Split into separate table: If JSONB is rarely queried ✅ Stick to well defined schema and avoid using jsonb unless absolutely necessary. 🧾 TL;DR

SokoFlow Build Log — Month 1 of 4 Last semester I set out on a new strategic plan to level up my software development skills through deliberate, project-based learning. That work produced one of the most ambitious things I've built so far: Sim-Pesa , a local-first transactional appliance that lets developers working in the M-Pesa ecosystem test and simulate STK Push workflows entirely on their own machines, without depending on the Daraja sandbox. I documented that build in 16 weekly posts, which you can find here . This semester, the focus shifts — from fintech foundations to cloud-native integration and real-world systems. The flagship project is SokoFlow , a conversational ERP for small Kenyan shopkeepers to track inventory and record sales entirely through WhatsApp chat. No app to download, no training session required — just natural language. Where Sim-Pesa lived in a controlled, predictable transactional world, SokoFlow steps into the mess of cloud-native reality: third-party API failures, webhook signature verification, the statelessness of HTTP, and container orchestration. The target audience shifts too — Kenyan SMEs operating on infrastructure that is often unreliable by design, not by exception. It's an ambitious project, but the goal was always to learn as much as possible from it. With the plan in place, I got to work. 1. The Vision of a Headless ERP The first real question I had to answer before writing a line of code: what does "headless" actually mean? Headless architecture decouples the frontend — the "head," or user interface — from the backend, the "body" that holds the data and business logic. A conventional ERP bundles both: backend plus a dashboard or UI on top. A headless ERP, by contrast, is just the engine. The brain. There's no built-in screen. So how do users interact with a system that has no interface of its own? SokoFlow doesn't actually care. It could be: WhatsApp SMS A web app A mobile app A voice assistant In this case, the "frontend