PostgreSQL GraphRAG v0.13.0 Released

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

PostgreSQL maintains two tiny files alongside every table that affect performance: Visibility Map & Free Space Map. Visibility Map (VM): - 2 bits per page: all_visible, all_frozen - all_visible decides whether a query touches disk - all_frozen decides whether VACUUM does - If all_visible = 1, PostgreSQL skips the heap entirely and serves the query from the index (index-only scan) - If all_visible = 0, it must fetch heap page, check row visibility via xmin/xmax - If all_frozen = 1, it means all rows frozen, VACUUM skips the page entirely (saves I/O) Free Space Map (FSM): - 1 byte per page (0–255) - On INSERT or UPDATE, PostgreSQL reads that byte to find a page with room, jumps directly to that page with available space - No byte, then no shortcut. It would scan the table looking for space The catch is that both maps go stale quickly due to PostgreSQL's MVCC nature: - UPDATE = INSERT + mark old row dead - DELETE = mark row dead, space not reusable yet - In both cases: VM bit cleared, FSM not updated until space is reclaimed VACUUM fixes all of this: - Removes dead tuples - Rewrites FSM bytes to reflect real free space - Sets all_visible = 1 when the page is clean - Sets all_frozen = 1 when all rows are old enough to freeze - Prevents transaction ID wraparound (after ~2B transactions, IDs wrap and frozen rows are immune) So, next time a query gets slow, don't just ask "is the query bad?" or "is the index covered?" Ask:  - When did VACUUM last run on this table?  - How many dead tuples are accumulating?  - Are our index-only scans actually skipping the heap? #PostgreSQL #DatabaseInternals #BackendEngineering #Performance #pgpulse

"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

🚀 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

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

Ahsan Hadi: pgEdge Vectorizer and RAG Server: Bringing Semantic Search to PostgreSQL (Part 2) Just when you thought PostgreSQL couldn't get any cooler, Ahsan Hadi shows up with a double whammy bringing AI-powered search directly to your database. Ever thought semantic search had to involve some fancy-schmancy vector database to clutter your infrastructure? Think again. Ahsan's duo, the pgEdge Vectorizer and RAG Server saves you from that hassle. They create a native Retrieval-Augmented Generation pipeline that's not some Star Wars technology it's just PostgreSQL doing its thing. For those moments when your documents change as often as your coffee preferences, these tools keep your search in sync without ninja scripts or chaotic orchestration tools. But wait, there's more. The RAG Server doesn’t just spit out SQL results oh no it offers a neat HTTP API that blends vector similarity with BM25 keyword matching like a fine cocktail. Throw in an LLM and voila your questions get answered from your very own data not some random internet lore. Hats off to Ahsan for making Postgres not just powerful but AI-savvy. Give it a whirl and say goodbye to keyword-hunting forever! You can find the full article here: https://postgr.es/p/8D0

🚀 Optimizing PostgreSQL for Large-Scale Systems: Indexing Strategies That Actually Matter If your PostgreSQL database is handling millions (or billions) of rows, indexing decisions are no longer a “nice-to-have”—they directly impact query latency and user experience. Here’s a quick breakdown of what I’ve learned about indexing at scale: 🔹 B-Tree vs GIN vs BRIN ▶️ B-Tree: Great for equality and range queries on well-distributed data. The default choice for most workloads. ▶️ GIN: Optimized for full-text search or array/JSONB containment queries. Perfect when you need fast lookups on complex structures. ▶️ BRIN: Lightweight, space-efficient, and ideal for huge tables with naturally ordered data (e.g., timestamps). 🔹 Composite Indexes & Query Patterns Building a composite index isn’t just “add more columns.” The column order should reflect your query’s filtering and sorting patterns. Misaligned indexes can easily be ignored by the planner. 🔹 Indexing JSONB Fields JSONB is flexible but can be slow if queried naively. Use GIN indexes for @> containment queries. Use expression indexes if you frequently filter on a nested property. 🔹 Query Planner Insights with EXPLAIN ANALYZE Always validate your assumptions. EXPLAIN ANALYZE doesn’t lie. It shows exactly how the planner executes a query and which indexes it chooses. A slow query often tells a story about a missing or misused index. 💡 The takeaway: At scale, indexing decisions aren’t just a tweak—they can mean the difference between sub-second responses and multi-second waits. Understand your data, your queries, and let the planner guide your indexing strategy. Have you ever seen a 100ms query drop to 10ms just by rethinking indexes? Postgres magic. ✨ #PostgreSQL #DatabaseOptimization #Indexing #PerformanceTuning #DataEngineering

A function-based expression index can make a query orders of magnitude faster, without changing a single line of application code. Most engineers index columns. Postgres lets you index expressions. The difference sounds subtle but the performance gap is not. A query filtering on LOWER(email) against a plain index on email does a full sequential scan. The same query against an index on LOWER(email) does a precise index lookup. Here's why it works internally: 1. A B-Tree index stores pre-computed values at write time, whatever expression was specified at index creation. 2. When a query filters on a bare column, Postgres matches against stored values directly. 3. When a query wraps a column in a function, Postgres can't match against the raw stored values and the index becomes invisible. 4. An expression index solves this by storing the function output directly. CREATE INDEX ON users (LOWER(email)) stores the lowercased value for every row at insert time. 5. At query time, Postgres evaluates LOWER(email) on the search term, finds a match in the index, and goes straight to the heap. 6. The same principle applies to type mismatches. WHERE id = '42' on an integer column forces an implicit cast, breaking the index. This technique is most valuable for case-insensitive lookups, date truncation filters, and computed fields queried frequently. The index Postgres uses isn't always the one on the column. Sometimes it's the one on what the column becomes. #PostgreSQL #Performance #Databases #BackendEngineering