Tagorenath V’s Post

"next time a query gets slow, don't just ask "is the query bad?" or "is the index covered?" instead, add this to your arsenal:

This is a great breakdown of how PostgreSQL actually behaves under the hood—especially Visibility Map(VM) & Free Space Map (FSM). What we consistently see in production is this: teams don’t struggle with understanding these concepts—they struggle with observing them in real time. Questions like: Are index-only scans actually skipping heap reads? Is autovacuum keeping up or silently falling behind? How quickly is XID age increasing? By the time performance drops, VM bits are already cleared, FSM is outdated, and bloat has started accumulating. pgpulse is designed to surface these internals—VM, FSM, vacuum behavior, bloat, and more—in real-time, before they become production issues. #pgpulse #supabase #databases #postgres #database #performance

#postgres users, interesting detailing on what a VACCUM can do for the performance improvements of your databases, I liked the three important questions you need to ask whenever your DB has slow queries 𝗪𝗵𝗲𝗻 𝗱𝗶𝗱 𝗩𝗔𝗖𝗨𝗨𝗠 𝗹𝗮𝘀𝘁 𝗿𝘂𝗻 𝗼𝗻 𝘁𝗵𝗶𝘀 𝘁𝗮𝗯𝗹𝗲? 𝗛𝗼𝘄 𝗺𝗮𝗻𝘆 𝗱𝗲𝗮𝗱 𝘁𝘂𝗽𝗹𝗲𝘀 𝗮𝗿𝗲 𝗮𝗰𝗰𝘂𝗺𝘂𝗹𝗮𝘁𝗶𝗻𝗴? 𝗔𝗿𝗲 𝗼𝘂𝗿 𝗶𝗻𝗱𝗲𝘅-𝗼𝗻𝗹𝘆 𝘀𝗰𝗮𝗻𝘀 𝗮𝗰𝘁𝘂𝗮𝗹𝗹𝘆 𝘀𝗸𝗶𝗽𝗽𝗶𝗻𝗴 𝘁𝗵𝗲 𝗵𝗲𝗮𝗽? #postgres #pgpulse #supabase #neon #railway #DatabaseInternals #BackendEngineering #Performance

Every standard B-tree index lookup in PostgreSQL is a two-step process: scan the index to find row pointers, then fetch the actual row data from the heap table. That second step -- the heap fetch -- is the bottleneck. For queries returning many rows, each heap fetch is a random I/O operation scattered across the table. A covering index eliminates the heap entirely. If all the columns your query needs exist in the index, PostgreSQL reads everything from the compact, ordered index structure. No random heap access. No wasted I/O. Three things most teams miss about covering indexes: **1. INCLUDE is different from a composite index.** Before PostgreSQL 11, you had to create a composite index on all columns: `(customer_id, customer_name, customer_email)`. This sorts on all three columns even though nobody searches by customer_name. The `INCLUDE` clause adds columns to leaf pages without including them in the sort key: `CREATE INDEX ON customers (customer_id) INCLUDE (customer_name, customer_email)`. Smaller index, cleaner semantics. **2. Index-only scans depend on vacuum.** PostgreSQL can skip the heap only for pages marked "all-visible" in the visibility map. If vacuum falls behind, pages are not marked all-visible, and the planner falls back to regular index scans with heap fetches -- even with a covering index. A covering index without healthy vacuum is a wasted investment. **3. The performance gap widens dramatically with scale.** On a 100-million-row table, eliminating heap fetches can reduce query time from hundreds of milliseconds to single-digit milliseconds -- a 10-100x improvement. The larger the table and the more rows your query returns, the bigger the win. Look for `Index Scan` with high `Heap Fetches` in your EXPLAIN output. If you see `Heap Fetches: 1000` where an `Index Only Scan` with `Heap Fetches: 0` is possible, there is a covering index opportunity waiting. Practical guide with INCLUDE vs composite examples, dashboard query patterns, and vacuum considerations: https://lnkd.in/eFhhvyU6 #PostgreSQL #DatabasePerformance #Indexing #CoveringIndex #SoftwareEngineering #DevOps

If your PostgreSQL queries search inside JSONB, arrays, or text -- and you don't have GIN indexes -- every single one of those queries does a sequential scan. No exceptions. B-tree indexes handle scalar comparisons (equality, range, ordering). But they can't index values inside composite data types. A JSONB column with dozens of keys, an array of tags, a tsvector of text lexemes -- B-tree can't touch these. Without a GIN index, PostgreSQL reads every row in the table and evaluates the condition one by one. Three things most teams get wrong about GIN indexes: 1. Operator class choice matters. For JSONB, there are two options: the default jsonb_ops (supports @>, ?, ?|, ?&) and jsonb_path_ops (supports only @> but is 2-3x smaller and faster). If your queries only use containment checks (@>), jsonb_path_ops is the better choice. Create one with the wrong operator class and the index gets silently ignored. 2. fastupdate causes unpredictable latency. GIN indexes batch insertions into a pending list for efficiency. Most inserts are fast, but occasionally a query triggers a pending list flush, causing an unexpected slowdown. For consistent query latency, disable it: ALTER INDEX ... SET (fastupdate = off). The tradeoff is slower inserts. 3. LIKE '%pattern%' needs a trigram GIN index, not a regular index. B-tree indexes require a fixed prefix. Only a GIN index with pg_trgm's gin_trgm_ops operator class can accelerate leading-wildcard pattern matches and similarity searches. The rule is simple: every JSONB column queried with @> or ? needs a GIN index. Every array column queried with @> or && needs a GIN index. Every tsvector column needs a GIN index. Every text column searched with LIKE '%pattern%' needs a trigram GIN index. Build this into your schema design process. Don't wait for production complaints. Full guide with operator class comparison, full-text search setup, and performance tuning: https://lnkd.in/e4vWHuVb #PostgreSQL #GINIndex #JSONB #FullTextSearch #DatabasePerformance #DevOps

🚀 How Data is Actually Stored in PostgreSQL Let’s break it down 👇 🔍 1. Data is Stored in Pages (Blocks) PostgreSQL doesn’t store rows randomly. 👉 Everything is stored in fixed-size pages (8KB by default) • Each table = collection of pages • Pages are the smallest unit of I/O 📦 2. Inside a Page (What’s Really There) Each page contains: • Page Header → metadata • Item Pointers (Line Pointers) → offsets to rows • Actual Rows (Tuples) 👉 Rows are not stored sequentially 🧠 3. What is a Tuple? In PostgreSQL, a row = tuple Each tuple contains: • Actual column data • Transaction IDs (xmin, xmax) • Visibility info (for MVCC) 👉 This is how PostgreSQL handles concurrency 🔄 4. MVCC (Why Multiple Versions Exist) Instead of updating rows in place: • PostgreSQL creates a new version of the row • Old version remains until cleaned 👉 Enables: • No read/write blocking • High concurrency 🧹 5. Dead Tuples & VACUUM When rows are updated/deleted: • Old versions become dead tuples 👉 VACUUM process: • Cleans them • Frees space • Prevents table bloat 📂 6. Table Storage (Heap Structure) 👉 PostgreSQL uses heap storage • No guaranteed order of rows • New rows go into available space 💡 That’s why indexing is critical for fast lookup 🌳 7. Index Storage (Separate Structure) Indexes are stored separately: • Usually B-Trees • Store: value → pointer to tuple 👉 Query uses index → then fetches actual row Have you ever debugged a slow query using this knowledge? #PostgreSQL #Databases #SystemDesign #BackendEngineering #Performance #Scalability

How VACUUM can impact queries (and create vacuum lag) PostgreSQL doesn’t delete rows immediately. It marks them as dead. VACUUM later cleans these dead rows and frees space. When VACUUM can’t keep up (due to high write load, long-running transactions, or misconfigured auto-vacuum), dead tuples accumulate. This leads to vacuum lag, slower queries, bloated tables, and inefficient index scans. VACUUM isn’t just maintenance. It’s a critical part of query performance and database health. Ignoring it doesn’t fail fast & it fails quietly. #PostgreSQL #DatabaseInternals #VACUUM #SystemDesign #BackendEngineering #PerformanceEngineering #SoftwareArchitecture #Databases

🚀 What *actually* happens inside PostgreSQL when you run a query? Most developers use PostgreSQL daily… but very few understand what’s happening behind the scenes. Here’s a simple breakdown 👇 🟢 1. You run a query Example: SELECT * FROM users WHERE id = 1; 🧠 2. Query Parser kicks in PostgreSQL checks: * Is the SQL valid? * Do tables/columns exist? ⚙️ 3. Planner / Optimizer This is where the magic happens ✨ Postgres decides: * Use index? 🔍 * Or scan full table? 📦 🚀 4. Executor runs the query Now PostgreSQL actually fetches data. ⚡ 5. Memory check (super important) * If data is in cache (shared buffers) → FAST ⚡ * If not → load from disk → slower 💾 6. Disk access Data is stored in pages (8KB blocks) Postgres reads only required pages — not entire table 👌 🔁 7. For WRITE queries (INSERT / UPDATE / DELETE) Example: UPDATE users SET age = 30 WHERE id = 1; Here’s what REALLY happens: 1. Data updated in memory 2. Change written to WAL (Write-Ahead Log) 🧾 3. Success response sent ✅ 4. Actual disk write happens later 👉 This is called: “Log first, write later” 🛡️ 8. Crash Safety (WAL) If system crashes 💥 Postgres: * Replays WAL logs * Restores data No data loss 🔥 🔄 9. MVCC (Concurrency magic) Instead of overwriting: Old row → stays New row → created So: * Readers don’t block writers * Writers don’t block readers 🧹 10. VACUUM (cleanup crew) All old rows = “dead tuples” Postgres cleans them using: VACUUM / Autovacuum 🤖 🎯 Final Flow (Simple) Query → Parser → Planner → Executor → Memory → Disk → WAL → Result 💡 Takeaway: PostgreSQL is not just a database — it’s a highly optimized system with: ✔ Smart query planning ✔ Efficient caching ✔ Crash recovery (WAL) ✔ High concurrency (MVCC) If you’re preparing for backend/system design interviews… understanding this gives you a HUGE edge. #PostgreSQL #BackendEngineering #SystemDesign #Databases #SoftwareEngineering #plpgsql

Postgres doesn't know if a row is visible to your query by looking at the row. It knows by checking who wrote it and whether that transaction is still alive. Every visibility decision is a transaction ID lookup, evaluated fresh against the current snapshot. Here's how Postgres decides what you see: 1. Every transaction gets a monotonically increasing transaction ID (txid) at start. 2. At query time, Postgres builds a snapshot, capturing the current txid and the list of all in-progress transaction IDs. 3. For every tuple, Postgres evaluates two fields - xmin (who created it) and xmax (who deleted it). 4. xmin must belong to a committed transaction that was visible at snapshot time. Otherwise the tuple never existed for this query. 5. xmax must be 0 or belong to an aborted or not-yet-committed transaction, otherwise the tuple is deleted. 6. Commit status of every transaction ID is stored in pg_xact - a bitmap Postgres checks to confirm committed or aborted. This is why a rolled-back DELETE leaves the tuple fully intact. xmax is set but points to an aborted transaction. Postgres reads pg_xact, confirms the abort, and treats the tuple as live. The row isn't marked visible or invisible. Postgres works that out fresh, every time, for every tuple. #PostgreSQL #DatabaseEngineering #BackendEngineering

I shipped pg_sorted_heap v0.13.0. The main change in this release is that the narrow fact-shaped GraphRAG contract is now part of the stable surface inside PostgreSQL. In practical terms, 0.13.0 brings together: - sorted_heap as a sorted table access method - sorted_hnsw as the planner-integrated ANN path for svec and hsvec - stable fact-shaped GraphRAG entry points - a stable routed GraphRAG dispatcher for multi-shard application flows Some current anchors: Gutenberg ~104K x 2880D: sorted_hnsw (hsvec) at 1.404 ms, 100.0% Recall@10 same corpus: pgvector halfvec at 2.031 ms, 99.8% Recall@10 fact-shaped multihop GraphRAG (5K chains, 384D): 0.962 ms median on the path-aware helper A big part of this release was also lifecycle hardening: - extension upgrade - dump/restore - crash recovery - concurrent online operations - shared-cache correctness CI green on PostgreSQL 17 and 18, including pg_upgrade 17 -> 18 FlashHadamard is included in 0.13.0 but remains explicitly experimental. The stable headline of this release is GraphRAG + planner-integrated ANN inside PostgreSQL. Release: https://lnkd.in/eg2QGvkQ Repo: https://lnkd.in/e-xEx8fN #postgresql #vectorsearch #llm #rag #opensource #database #ai #postgres #hnsw #graphrag #opensource