PostgreSQL Covering Indexes for Performance

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

I made one change to a PostgreSQL index and cut query time by 7x. Here's exactly what happened: I had a simple query on a 100k-row users table: SELECT id, email FROM users WHERE country = 'US' AND status = 'active'; It was returning 6,237 rows. Seemed fine. Until I actually measured it. ▸ No index → 12.5ms (sequential scan, reads every single row) ▸ Composite index on (country, status) → 3.5ms (much better, but still hitting the heap) ▸ Covering index with INCLUDE (id, email) → 1.7ms (index-only scan, zero heap access) The key insight: a regular index tells Postgres *which* rows to fetch. A covering index also stores the *data* you need — so Postgres never has to go back to the table at all. The EXPLAIN output that made it click for me: Heap Fetches: 0 That single line means no table pages were read. The entire query was served from the index. When should you use covering indexes? ✓ Frequently-run queries on read-heavy paths ✓ When latency matters and your SELECT columns are predictable ✗ Not on every query — they cost extra storage and slow down writes The progression: Seq Scan → Bitmap Heap Scan → Index Only Scan Each step removed a category of unnecessary work. The first optimization found rows faster. The second eliminated the roundtrip to fetch the data. Small index change. Big difference. Details in this article: https://lnkd.in/eKKckpen

Our PostgreSQL queries were taking 8 seconds. I got them under 200ms. Here's exactly what changed and what didn't. Context: We were running a multi-tenant SaaS. As the user base grew, a core query started degrading badly. Users noticed. It had to be fixed. Step 1: EXPLAIN ANALYZE before touching anything -sql EXPLAIN ANALYZE SELECT * FROM bookings WHERE tenant_id = $1 AND status = 'confirmed' ORDER BY created_at DESC; Output showed: Sequential scan on 2M rows. No index on tenant_id + status. Step 2: Composite index -sql CREATE INDEX idx_bookings_tenant_status ON bookings (tenant_id, status, created_at DESC); This alone dropped the query from 8s to 900ms. Still not good enough. Step 3: Stop selecting * The query was pulling 40 columns. The UI needed 6. -sql SELECT id, title, start_time, end_time, status, attendee_id FROM bookings WHERE tenant_id = $1 AND status = 'confirmed' ORDER BY created_at DESC LIMIT 50; Down to 350ms. Step 4: N+1 was hiding in the ORM For each booking, the ORM was firing a separate query to fetch the attendee. Switched to a JOIN. -sql SELECT b.id, b.title, u.name as attendee_name FROM bookings b JOIN users u ON u.id = b.attendee_id WHERE b.tenant_id = $1 AND b.status = 'confirmed' ORDER BY b.created_at DESC LIMIT 50; Final result: 180ms. What didn't help: connection pooling tuning (already fine), adding a cache (premature fix the query first), switching databases (never needed to). 8 seconds → 180ms. No infrastructure changes. Just understanding what the database was actually doing. What's your go-to first step when a query goes slow? #PostgreSQL #SQL #DatabaseOptimization #QueryOptimization #BackendEngineering #FullStackDevelopment #SoftwareEngineering

🚀 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

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

A PostgreSQL table with 500 million rows doesn't just slow down queries. It slows down everything: VACUUM takes hours, index builds lock the table, and deleting old data generates massive WAL and leaves bloat behind. Table partitioning splits a large table into smaller physical pieces while keeping it as a single logical table. The query planner uses partition pruning to scan only the relevant partitions. Here's what matters in practice: 1. The partition key must appear in your most common WHERE clauses. If 90% of your queries filter on event_timestamp, partition by timestamp. A partition key that queries don't filter on provides zero benefit and only adds planning overhead. Always verify with EXPLAIN before committing to a scheme -- if pruning doesn't activate, the partitioning isn't helping. 2. Partition count matters more than people think. Monthly partitions for 5 years = 60 partitions (reasonable). Daily partitions for 5 years = 1,825 partitions (the planner slows down noticeably). The planner evaluates each partition during query planning. Keep it manageable, or use TimescaleDB which is specifically optimized for high partition counts. 3. The biggest operational win is instant data removal. DROP TABLE on a partition takes milliseconds and generates no WAL. Compare that to DELETE FROM events WHERE event_timestamp < '2024-02-01' on 100 million rows -- that takes an hour, generates massive WAL, and leaves dead tuples for VACUUM to clean up. The biggest gotcha: you can't convert an existing table to a partitioned table in place. You need to create a new partitioned table, migrate data in batches, and swap with a rename in a single transaction. For zero-downtime migrations, add a trigger or logical replication to capture writes during the migration. Automate partition lifecycle with pg_partman or cron. A missing future partition causes INSERT failures. A forgotten old partition wastes storage. Full guide with range, list, hash strategies, migration patterns, and pg_partman setup: https://lnkd.in/eGUj8zXC #PostgreSQL #TablePartitioning #DatabasePerformance #DataEngineering #DevOps #SRE

Part 2 of my PostgreSQL series is out — this time on indexes. You created an index, but the query still does a Seq Scan? The cost in EXPLAIN looks like a random number? Reads with an index are somehow slower than without? What's covered: → Why PostgreSQL ignores your index when a query hits 70%+ of the table → How data correlation on disk can make the same index 100x slower → EXPLAIN costs demystified — and why they're not milliseconds → Covering indexes, GiST for fuzzy search, GIN for full-text — when to use what → All demonstrated on 4 million rows with runnable SQL #PostgreSQL #Database #SQL #Backend #SoftwareEngineering