PostgreSQL Database Indexes: Complete Production Troubleshooting

Before we open a single terminal, here is what you are getting. Every index type PostgreSQL 18 ships, on a five million row table. Measured, not guessed. You will watch the planner refuse a perfectly good index, and you will find out why. If you have ever added an index and watched nothing get faster, this is the one that fixes it.

Four point eight seconds for one query. Sequential scan. Five million rows read, nine thousand returned. Almost all of that work was thrown away.

Here is the shape of it. Nineteen thousand blocks pulled through shared buffers. The answer lived in about a hundred and ten. Every red cell is work nobody asked for.

One index, shaped correctly. Status equality first, created at range second. Same SQL, new plan, twelve milliseconds. Three hundred and ninety nine times faster.

Now the part nobody warns you about. This table already had an index on both columns. The planner still refuses it. Same four point eight seconds. Sources: What you are getting, Query burns 4.8 seconds.

A filter that matches fifteen thousand rows out of five million, and Postgres still reads the whole table. Nearly five million rows removed by filter, eighteen thousand blocks read, one point eight seconds. There is no index on status yet, so the planner has no other option. Watch the pain first, then we build the path.

Watch the path light up. The root splits the key space into coarse decisions. One internal page narrows the band. The leaf finally holds the keys and the CTIDs that point into the heap. Equality walks one spine from root to leaf, then stops on the matching entries. A range does that same descent, then walks sibling leaves along the chain. That is why btree wins for equals and for ordered ranges in the same structure. It is also why a leaf split under insert load becomes part of your write amplification story. Hold this picture for a full beat. Every later index type is either refining this idea or rejecting it for a different access pattern. When a plan says Index Scan, this is the machine you are paying for.

One btree on status, then ANALYZE so the planner has fresh statistics. Cancelled is rare, so this index earns its rent. Watch the plan change in two ways. Index Scan replaces Seq Scan, and the predicate moves from Filter to Index Cond. That second difference matters more than the node name. Index Cond bounds what gets read. Filter throws rows away after they have already been read.

The index exists, it is valid, and the planner still refuses it. Look at why. is_active is true for about half the table, so an index scan would visit almost every heap page anyway, in random order. Sequential access wins that trade. Watch Rows Removed by Filter, and notice the cost estimate is not a bug. Selectivity decides this, not the presence of an index. A partial index on the small side of that boolean is the honest fix. Sources: Index exists. Still Seq Scan, Why did Seq Scan beat a selective btree?.

Now an index exists on created_at and status, the plan uses it, and Index Cond even mentions status. It still takes nine hundred milliseconds and eight thousand block reads. The node name says success. The buffers say otherwise. Hold on to that gap, because this is the most misread plan in Postgres.

Composite btree keys are compared left to right. Leading equality is what shrinks the scanned index range. Columns to the right can still be checked as Index Cond while the scan walks, even when they do not choose the start of the scan. That is the advanced distinction: Index Cond means checked in the index. Range reduction means fewer leaf pages visited. For status equals and a recent created_at window, put status first so equality bounds the scan, then range on created_at. With created_at first, status may still appear in Index Cond, but you often walk a wide leaf range and pay for it. PostgreSQL 18 skip scan can use some non-leading equalities. Do not teach a folklore absolute. Read Index Cond, buffers, and actual time on your version.

Same SQL on both sides, and both plans can show Index Cond. Watch which column bounds the scan. With created_at leading, the scan starts at a timestamp and walks every status inside that window. With status leading, equality pins the scan to one narrow slice of the leaf level, and the time range trims it further. Read the buffer counts, not the node names. That is range reduction.

Now the mirror image. This query filters only created_at, and a status leading index cannot start the scan anywhere useful. Columns to the right can still be checked as the scan walks, which is why Index Cond can mention them, but they do not bound the range. Measure this shape against a dedicated created_at index before you add one. On Postgres eighteen, skip scan can help some non leading cases, so measure rather than assume. Sources: The shape of the waste, Same query, new plan.

The node says Index Only Scan, which should mean the heap is never touched. Heap Fetches says eighty four thousand. That is the tax nobody mentions. Something is forcing this scan back to the table, and until we fix it the word only is a promise the plan cannot keep.

Index Only Scan is possible when the index can return every column the query needs and the access method supports it. INCLUDE lets you carry non-key payload in the leaf without making those columns search keys or changing sort order. INCLUDE does not by itself turn a plan into Index Only Scan. The visibility map decides whether Postgres must still visit the heap for visibility checks. Dirty VM means Heap Fetches climb while the node name still says Index Only Scan. Watch the leaf: key columns, included payload, ctid. Then watch Heap Fetches at zero after VACUUM. That is the real win condition.

Search columns go in the key, payload goes in INCLUDE. Columns in INCLUDE are stored in the leaf but they are not search keys and they do not affect ordering, so they cannot bound a scan. Then VACUUM, so the visibility map can mark pages all visible. Now select only covered columns and watch Heap Fetches read zero. All three parts are required. Miss any one of them and you are back to touching the heap.

The plan says Index Only Scan, so this should be fast, and it is not. Look at Heap Fetches, eighty four thousand of them. An Index Only Scan still visits the heap whenever the visibility map does not mark a page all visible, and heavy updates leave that map behind. Run VACUUM, then run the query again and watch Heap Fetches drop to zero. Same plan, same index, a completely different amount of real work. Sources: Leftmost column order.

There is a partial index on this table, it is valid, and the planner will not look at it. The query asks for the last thirty days without mentioning status, and the index only holds unpaid rows. Postgres cannot prove those two sets line up safely. That proof is the whole mechanism.

A partial index stores only the hot slice. The WHERE clause on the index is the membership test. Cancelled rows in, everything else out. The index stays small, cache friendly, and honest about what you actually query in production. The trap is implication. Your query predicate must prove the row would be in that slice. If the planner cannot see that proof, the partial index becomes invisible even though it is valid and healthy. Look at the cold mass versus the hot band on screen. Index the band you pay for, not the whole table by habit. Partial indexes are how you stop storing keys for rows your hottest path never reads. When the slice is real, size drops, cache hits rise, and the planner finally has a cheap path that matches the business rule.

The index carries created_at, but only for rows where status is unpaid. That is a small index on the part of the table this query actually cares about. Watch two things in the plan. Index Cond shows the range on created_at, and the planner accepted the index because the query predicate implies the index predicate. Fewer leaf pages, fewer heap visits, and writes only touch this index when a row is inside that slice.

Same time range, one difference. The status equals unpaid test is gone. The planner will not use a partial index unless it can prove the query only asks for rows inside the index predicate, and a bare created_at filter proves nothing about status. So we are back to a sequential scan. That is the trap with partial indexes. They are invisible to queries that do not repeat the predicate, even when the rows would have qualified. Sources: What this course proves, Equality hits Index Cond.

The index on lower of email is healthy and completely unused. This query compares raw email instead. To the planner those are two different keys, so it scans the table to answer a single row lookup. The mismatch is one function call wide. Watch how literal the matching is.

Expression indexes store the result of an expression, not the raw column. If you search lower of email, index lower of email. If the query writes a different expression, the planner will not rewrite your hope into a match. Functions, casts, and JSON extractions follow the same rule. The animation is the contract: column in, function applied, key stored, query must repeat that exact shape. Miss the shape and you get a sequential scan with a perfectly healthy unused index sitting next to it. When someone says the index is broken, first compare the expression in CREATE INDEX to the expression in WHERE. Sameness is the feature. Clever rewriting is not. Write the expression once in the index, then copy that exact shape into every query that needs the path.

An expression index stores the result of the function, not the column. Here the key is lower of email, so it only helps queries that write lower of email the same way. Watch Index Cond, it shows the expression itself. The function has to be immutable or Postgres will refuse to index it, and every insert now pays to evaluate that expression once. Confirm the plan says Index Scan before you trust it.

Same table, same index in place, and this query compares raw email instead. The planner matches expressions literally, so lower of email and email are two different keys, and no match means a sequential scan. Fix it in one of two places. Either the application always calls the same function, or you index the column the application actually filters on. Watch for the same trap with trim, upper, casts, and date_trunc. Sources: Why is this composite slow even when Index Cond shows status?.

Two predicates, each moderately selective, neither one narrow on its own. No index is used at all here, so four point seven million rows get read and thrown away. One wide composite index would only serve this exact pair of columns. Watch how Postgres combines two narrow indexes instead.

Bitmap scans are the combine play. Probe one index and build a bitmap of candidate heap blocks. Probe another and build a second bitmap. AND or OR them together. Then visit the heap in block order so scattered CTIDs become a calmer I/O pattern. This is how Postgres uses multiple single-column indexes without forcing one giant composite for every mix of filters. Watch the merge light up on screen. The win is fewer heap pages touched, not a prettier plan name. When selectivity is middling on two independent columns, bitmap is often the adult answer. If you only ever create composites, you miss this tool when the query mix will not sit still. Keep both tools. Let EXPLAIN tell you which one paid rent today.

Two separate single column indexes, and the query filters both columns. Watch what the planner builds. A Bitmap Index Scan on each index, a BitmapAnd that combines them, then one Bitmap Heap Scan that visits the heap in physical block order instead of jumping around. That block order is the whole point. It is why two narrow indexes can beat one wide index when the combinations of predicates are unpredictable.

The plan looks right and the numbers do not. Look at Rows Removed by Index Recheck, eight hundred thousand of them. When a bitmap outgrows work_mem, Postgres stores whole pages instead of individual tuples, the bitmap becomes lossy, and every row on those pages gets rechecked against the condition. Either the predicate is not selective enough to be worth an index, or work_mem is too small for this shape. Measure both before adding another index.

The GIN index fires, the operator matches, and the query still takes seven hundred and eighty milliseconds. Eighty thousand rows match containment, and twelve thousand blocks come off disk to fetch them. A correct index cannot fix a predicate that matches most of the table. Budget the heap work, not just the lookup.

GIN is built for many keys per row. Arrays, JSONB, full text. The entry tree stores each key once. The posting list stores the CTIDs that contain it. Lookups feel like inverted index work: find the key, read the postings, then fetch heap tuples that still pass the check. Writes are heavier because one row update can touch many keys. With fastupdate, inserts can land in a pending list. Searches also scan that pending list, so results are not stale waiting for merge. A large pending list hurts search latency until VACUUM or maintenance moves entries into the main structure. GIN is not a slower btree. It is a different machine. Budget write cost and pending-list latency the way you budget read latency.

GIN indexes the pieces inside a value, so a jsonb containment test can be answered from the index. Watch the plan shape. A Bitmap Index Scan on the GIN index finds candidate rows, then a Bitmap Heap Scan fetches them. GIN almost always appears under a bitmap path rather than a plain Index Scan, because one row holds many keys and one key points at many rows. Check that Index Cond names the containment operator.

The index is used, and the query is still slow. That is the case worth studying. Look past the node name and read the row counts. Containment on size M matches eighty thousand rows, so the bitmap is correct and enormous, and heap work swamps everything the index saved. GIN cannot make a broad predicate narrow. When most rows match, no access method rescues the query. Change the predicate or change the model. Sources: B-tree path to a key.

An overlap query against a range column, and the plan is a sequential scan across every reservation. A btree cannot answer overlap, and there is no GiST index here yet. The operator and the index have to be built for each other. That is what this chapter proves.

GiST is a framework for predicate trees. Bounding keys let Postgres reject whole subtrees before it touches rows. Ranges, geometry, nearest neighbor, exclusion constraints. The boxes on screen are the idea: coarse keys first, refine only where the query can still be true. Operators and opclasses matter more here than in plain btree. If the operator class does not support your predicate, the index is decoration. Think filters and lossy bounds, then confirm with EXPLAIN that the index condition actually fired. GiST wins when the question is about overlap and exclusion, not about sorting a single scalar column. Learn the operator family first. The pretty tree only helps when the predicate can use it.

GiST stores a bounding key for every value, so an overlap test can be answered without reading the row. Watch the operator in Index Cond, the double ampersand overlap test. This is the same machinery behind exclusion constraints, which is how you stop two reservations from holding the same room at the same time. If the operator is not in the opclass the index is ignored, so check what your type actually supports.

Recheck Cond on a GiST plan is normal, not a defect. The index answers with bounding keys, which can be approximate, so Postgres re-tests the real values. Watch how many rows that recheck throws away. Twelve hundred is fine. Hundreds of thousands means the bounding keys are not separating your data, and the fix is a better opclass, a tighter query window, or a different access method. Read the buffer counts next to it. Sources: Boolean btree ignored.

There is an SP-GiST index on this table, on the coordinate column, and this query filters on name with a text pattern. Nothing in that opclass answers that operator, so it is a sequential scan. Space partitioning only wins when the query asks a question about that space.

SP-GiST partitions space, not just sorted keys. Quads, prefixes, unbalanced splits. Points and some network and text patterns fit this shape when the data naturally divides into regions. It is not a drop-in btree substitute and it is not GiST with a different badge. You choose it when the access pattern matches space partitioning and the operator class exists for your type on your version. Watch one quadrant light up. That is the promise: skip regions that cannot match, descend only where the space still intersects the query. Prove it with operators and EXPLAIN, not with the name alone. If your data has no natural partition story, stay on btree or GiST and keep the tool shelf honest.

SP-GiST partitions the space instead of balancing the keys, which suits points, prefixes, and any data that clusters unevenly. Watch the plan for Order By on the index, that is a nearest neighbour search answered in index order with a LIMIT. The tree is deliberately unbalanced, so its depth follows the shape of your data. Measure with your real distribution, and compare it against GiST on the same column.

Here is the failure mode. The index is on coords, the query filters name, and nothing in that opclass supports a text pattern, so the planner ignores it. Two lessons. An index only helps the operators its opclass declares, and SP-GiST support varies by type and by major version. Read the documentation for the version you actually run, then confirm with EXPLAIN instead of assuming. That confirmation costs seconds.

This BRIN index is twelve megabytes and it is not helping. Read the correlation, near zero. The rows are physically shuffled, so every block range looks like it might hold a match and nothing gets skipped. Correlation, not size, decides whether BRIN works. Check it before you build one.

BRIN stores tiny summaries per page range: minimum, maximum, maybe more depending on the opclass. It is small and cheap to maintain when heap order correlates with the indexed column. Time-series inserts that append naturally are the classic win. If the heap is shuffled, every range summary looks wide, and BRIN stops excluding pages. Correlation first, index second. The hot range on screen is the only band you should visit. If correlation is weak, fix physical order or pick another access method before you blame BRIN. Tiny indexes are not free wins. They are conditional machines that need ordered reality. Check correlation, then build BRIN, then prove page exclusion in EXPLAIN ANALYZE.

Same column, two indexes. The btree is around nine gigabytes, the BRIN is twelve megabytes. BRIN stores only a minimum and a maximum per block range, which is why it is tiny. Size is not speed though. That summary only lets Postgres skip block ranges while the table is physically ordered by the column. Watch the size difference now, and hold on to it, because we are about to destroy the correlation.

This is what kills BRIN. Read the correlation out of pg_stats, zero point zero four. The rows are physically shuffled, so nearly every block range holds nearly every value, and no range can be skipped. The index is still tiny and it now reads almost the whole table. BRIN is a bet on physical ordering. Check correlation before you build it, and check it again after a bulk update or a repack.

A hash index on token, and this query asks for greater than. Sequential scan. Hashing destroys ordering by design, so there is nothing in the index to walk for a range. Equality is the only question a hash bucket can answer, and that limit is the whole trade.

Hash indexes send equality lookups to buckets. That is the whole product. No range scan. No ORDER BY help. No inequality. Modern Postgres WAL-logs them, so the old folklore about crash safety is outdated, but the access pattern is not. Use hash when you truly mean equals and you have measured a win over btree on your workload. Watch one bucket ignite on screen. If your query needs a between clause tomorrow, you already chose the wrong tool. Keep hash for the narrow equality case, and keep btree as the default when the predicate shape can grow. Measure both. Ship the one that wins on your keys, not the one that sounds exotic.

Hash indexes do exactly one thing. Equality. This token lookup is pure equality, so watch the plan use the hash index and finish well under a millisecond. Since Postgres ten they are crash safe and written to the WAL, which is what made them usable in production again. On long keys they can be smaller than a btree, but they cannot help ordering or ranges at all. Compare the size against a btree on the same column.

One inequality is enough to make a hash index vanish from the plan. There is no ordering inside a hash bucket, so greater than, less than, BETWEEN, ORDER BY and prefix matching all fall back to a sequential scan. That is the trade you accepted when you chose hash. If any query on that column needs a range or a sort, build a btree, and keep hash for the strict lookup path.

Containment reached the GIN index, so the operator path worked, and then the heap drowned the query. Twelve thousand blocks read, seven hundred and eighty milliseconds. The opclass was right and the selectivity was wrong. Watch the fetch tax, because that is where the time actually went.

JSONB search is operator-led. Containment with the contains operator, path ops, or extract a scalar and btree it. GIN on jsonb or jsonb_path_ops indexes the shape you probe. The tree on screen is containment thinking: attributes, keys, values, then a probe for color red. Wide GIN hits can still drown you in heap fetches, so selectivity still rules after the index says yes. Pick the operator that matches the question, build the index for that operator, and prove it with EXPLAIN. JSON is not magical. The access method and the operator class are the real product. When the document is wide and the probe is rare, GIN shines. When every row matches, you still drown.

Two GIN opclasses on the same column. jsonb_ops indexes every key and every value, so it answers existence as well as containment. jsonb_path_ops hashes the whole path down to the value, which makes it smaller and quicker for containment alone. Watch which one the planner picks here, then compare the two sizes on disk. Choose the opclass for the operators your application really issues, not the widest one available.

Here is the cost of that choice. This query asks whether the key color exists, with the question mark operator, and jsonb_path_ops does not keep keys on their own, only hashed paths. It cannot answer, so the plan falls to a sequential scan. If you need existence tests as well as containment, use jsonb_ops, or keep both indexes and pay for both on every write. Watch how the operator alone decides this.

A leading wildcard LIKE against millions of rows, and two point one seconds later Postgres has read the whole table. A btree on email cannot help, because it can only start from the beginning of a string. A percent sign at the front is the classic case for trigrams.

pg_trgm breaks text into three-character fingerprints. That is how leading wildcard LIKE and similarity search become indexable. The grid on screen is the fingerprint. Matching trigrams narrow candidates, then the row is checked for a real match. It is not full-text ranking, and it is not a free substring engine for every pattern you can invent. Extension, operator class, and a real selectivity story still matter. When users type contains-style search against names or titles, trigram is often the difference between a sequential scan apology and an indexed path you can ship. Create the extension, build the GIN or GiST trigram index, then prove the LIKE or similarity operator actually uses it.

The same LIKE pattern on both sides. On the left a btree cannot help a leading wildcard, so Postgres scans the whole table. On the right pg_trgm has broken the text into three character pieces and indexed those, so the identical pattern becomes a Bitmap Index Scan. Watch the execution times, and notice the query did not change. The index changed what that operator could reach.

Trigram indexes are not free. This one is four hundred and twenty megabytes on a single text column, because every row contributes many trigrams. Watch the size, then think about the write path. Every insert and every update of that column maintains all of those entries, and on a hot table that shows up as slower writes and more WAL. Measure the search win against the write cost before you keep it.

The full text index found fifty thousand candidate documents quickly, and the query is still expensive. Every candidate has to be ranked before ORDER BY can pick twenty. Lookup and ranking are two different costs, and only one of them is indexed. Watch which one dominates.

Full text is tokens, not raw strings. Document to tsvector, query to tsquery, match through a GIN posting list. Stemming and dictionaries change what gets stored, so the language configuration is part of the index definition. Ranking with ts_rank runs after candidates are found, so a broad query can still be expensive after a clean index hit. Watch the pipeline on screen: document, vector, postings. Index the tokens you search, then measure ranking cost separately from lookup cost. If ranking dominates, tighten the query before you add another index. Store a generated tsvector when you can, keep the config stable, and treat FTS as a pipeline, not a single magic column.

Full text search indexes a tsvector, not the raw text. websearch_to_tsquery turns ordinary user input, quotes and or and minus included, into a tsquery, and the double at operator matches it against the vector. Watch the Bitmap Index Scan on the GIN index. Keep the vector in a stored generated column so it cannot drift, and use the same text search configuration on both sides or the match quietly fails.

The index did its job and the query is still slow. Read the row count, fifty thousand candidates, then look at where the time actually goes. ts_rank has to be computed for every one of them before ORDER BY can pick twenty. Ranking is not indexed. Narrow the candidate set first with a tighter query or extra filters, and only rank what survives. That is the fix, not a bigger index.

A bloom index on three equality columns, and the query adds a time range. Bloom goes silent. Its signature answers whether a row might equal a value, and nothing about order, so a range predicate has nowhere to go. Watch which predicate breaks it. That boundary is the chapter.

Bloom is an extension fingerprint for multi-column equality. Bits turn on for the columns you care about. Lookups are cheap filters with possible false positives that still need a heap check. There are no ranges, no ordering, and it is not a core access method, so label it in your runbooks and extension inventory. Use bloom when many equality columns are probed in shifting combinations and a forest of btrees would be worse. Watch the hot bits light. If you need between on a timestamp, bloom will sit there silently while btree or bitmap does the real work. Fingerprint first, verify second, and never pretend bloom replaced your range indexes.

Bloom is an extension access method, so it starts with CREATE EXTENSION. It builds a small signature per row from several columns at once, and any combination of equality tests can be checked against that signature. Watch the plan use it as a coarse filter and then recheck the survivors. The trade is false positives, never false negatives, so results stay correct while some extra rows get read.

Add one range predicate and bloom stops helping. A signature can answer whether a row might equal a value, and nothing else. There is no ordering inside it, so created_at greater than a timestamp has to be handled somewhere else, usually a btree or a bitmap combination. Bloom earns its place with many low cardinality columns and unpredictable equality filters. It is not a general replacement.

One customer, ordered by created_at, twenty rows wanted. There is a Sort node sitting above the scan, which means Postgres read and ordered every matching row before discarding all but twenty. An index exists. Its leaf order just does not match what ORDER BY asked for.

B-tree leaves are stored in key order, and that order is usable output. When ORDER BY matches the index order, the plan can stop after LIMIT rows and skip the Sort entirely. Three things break it. A direction mismatch between ascending and descending across columns, a missing tie breaker so the order is not stable, or an ORDER BY on a column the index does not carry. For deep pages, keyset pagination beats a large OFFSET every time.

The index now leads with the equality column, customer_id, and carries created_at descending after it. That matches the query exactly. Watch the plan lose the Sort node completely and finish in under two milliseconds. Two details are worth copying. The descending direction is written into the index, so the scan reads forward, and the LIMIT stops that scan early instead of after the fact. Postgres can read a matching index backwards too, so one direction often serves both, but confirm it with EXPLAIN when columns disagree on direction.

OFFSET does not skip work, it discards it. Look at the plan. To return twenty rows this scan walks one hundred thousand and twenty rows and throws away the first hundred thousand, and it gets worse on every page. Keyset pagination is the production pattern. Remember the last id you sent, then ask for id greater than that value with the same order and the same LIMIT. Cost stays flat however deep the user goes, and the index does the skipping.

A unique index is how Postgres enforces uniqueness, and a primary key is a unique index with NOT NULL attached. By default nulls count as distinct, so many null emails can coexist. NULLS NOT DISTINCT, available from Postgres fifteen, makes nulls compare as equal so only one null is allowed. Say the version out loud when you ship this, because the behavior changed. Watch the duplicate insert fail with a unique violation, which is the constraint doing its job.

Here is a trap in index cleanup. This unique index shows almost no scans in pg_stat_user_indexes, because the application rarely searches by email. It is still doing critical work on every insert, rejecting duplicates from client retries. A low idx_scan means nobody queries it, not that nobody needs it. Never drop an index that backs a constraint, a primary key, or a uniqueness rule on that evidence alone.

The operator class decides which operators an index can answer, and it is easy to build the wrong one. GIN with jsonb_ops covers existence and containment. jsonb_path_ops covers containment only. A trigram index needs gin_trgm_ops, and without it LIKE stays a sequential scan. Text indexes carry a collation as well, so a mismatch between index and query can make it useless for comparisons and ordering. Inspect the definition, confirm the opclass, then confirm the plan.

Deleting a parent row makes Postgres check every child table that references it. On the left there is no index on the child foreign key column, so one small delete becomes a sequential scan of orders, and that is how routine cleanup turns into an outage. On the right the same delete uses an index on customer_id and finishes immediately. Postgres indexes the parent side of a foreign key for you, never the child side. That one is your job, and the same index helps nested loop joins.

Partitioned tables do not have one shared btree across all partitions. A partitioned index is a template, and the real indexes live on each partition, one per child. Two consequences follow. A unique constraint has to include the partition key, because uniqueness can only be enforced inside one child, and every new partition needs its indexes before traffic arrives. When you attach an existing table, build the matching index first, then attach, so the parent index becomes valid.

A heap only tuple update keeps the new version on the same page and touches no index, but only while every indexed column is unchanged. Compare n_tup_hot_upd against n_tup_upd. If the ratio is poor, look at which columns your updates touch, and at free space on the page. HOT needs room inside the heap page, so the setting that matters is the table fillfactor, not the index fillfactor. Lower it, then rewrite the table so existing pages actually carry the new free space. Index fillfactor is a different lesson about page splits.

Before you add an index, check whether the planner even understands the data. Compare rows estimated against rows actual in the plan. When those are far apart the plan is a reasonable decision made on wrong information. status and created_at are correlated, and single column statistics cannot see that, so CREATE STATISTICS teaches the planner about the pair. ANALYZE after any bulk load, because autovacuum gets there eventually but the lag is real. Watch the estimate move closer, and the plan follow it.

random_page_cost models non sequential page access relative to sequential access. The default is four, and that number already assumes a good share of random reads come from cache. It is not simply a spinning disk value. On SSDs, or on a workload that is mostly cached, a lower value can match reality better, and effective_cache_size is what tells the planner how much operating system cache to expect. Change these in a session first, watch the plan, and measure before you touch the server config.

A prepared statement can settle on a generic plan after a few executions, one plan for every parameter value. When the values are skewed, and cancelled is rare while shipped is common, that single plan can be wrong for both. Force custom plans in your session while you investigate, so the planner sees the actual parameter, and compare the two. Then fix the underlying cause, better statistics or a different query shape, instead of leaving the override in place.

This is the evidence query for index cleanup. idx_scan counts how often the index was chosen, idx_tup_read and idx_tup_fetch show how much it returned, and joining pg_statio_user_indexes adds block hits and reads so you can see what is cached. Read all of it next to the size on disk. A large index with zero scans since the last statistics reset is a candidate, not a verdict. Check that it does not back a constraint, check the reset is old enough to cover monthly jobs, then decide.

Two indexes doing one job cost you twice on every write. Look at the column lists here. One is status, the other is status with created_at. The narrow one is a prefix of the wide one, so the wide index can serve almost everything the narrow one served. Confirm with the plans and with idx_scan, check that neither enforces a constraint, then drop the loser. Prefix overlap is the most common form of redundant indexing.

The symptom is inserts getting slower over a week with no code change. The cause is write amplification. Every index on the table is maintained on insert, and on update whenever an indexed column changes, and that work lands in the WAL too. The fix is not tuning, it is subtraction. List the indexes on the hot table, find the ones with no scans and no constraint behind them, drop them with evidence, then measure the insert path again.

The symptom is a query that was fine at two hundred thousand rows and terrible at five million, with nothing deployed in between. The cause is a cost crossover. As the table grows the fraction of rows the filter matches changes, and at some point a sequential scan stops being cheaper, or the index that used to win now visits too many heap pages. Re EXPLAIN at production scale, not on a small copy. A plan is a decision about data volume.

The symptom is writes stalling during a deployment. The cause is a plain CREATE INDEX, which holds a lock that blocks inserts, updates and deletes for the entire build. On a five million row table that is minutes of downtime nobody planned for. Use CREATE INDEX CONCURRENTLY on a live primary. It takes longer and needs two table scans, and if it fails it leaves an invalid index behind, so always check afterwards.

The symptom is heavy write cost with two indexes covering the same ground. Compare the column lists first. If one is a prefix of the other, the wider index usually covers both jobs. Before dropping anything, confirm neither is unique and neither backs a constraint, then check idx_scan for both over a window long enough to include batch jobs. Drop one, measure the write path, and keep watching the plans you care about.

The symptom is a strange plan choice with no obvious reason. Read the numbers. Estimated eight hundred rows, actual nine hundred and twenty thousand. That is a thousand fold error, and every decision built on it will be wrong. The usual causes are stale statistics after a bulk load, or correlated columns that single column statistics cannot model. ANALYZE first, add extended statistics for the correlated pair, then look at the plan again. Fix the estimate before the index.

A plain CREATE INDEX takes a lock that lets reads continue and blocks every write for the whole build. It is the fastest way to build an index and the most dangerous thing to run on a busy primary in the middle of the afternoon. Inside a maintenance window it is the right tool, because one table scan is cheaper than two. Outside a window it is an outage with a progress bar. Know which situation you are in before you press enter.

CONCURRENTLY is the production build. Postgres does the work in several passes, waits for transactions that started before it, and lets writes continue the whole time. The price is real. It scans the table twice, takes noticeably longer, cannot run inside a transaction block, and if anything fails partway it leaves an invalid index behind. That is an acceptable trade on a live system, as long as you check the result afterwards.

This is the check people forget. Query pg_index for indisvalid false. An invalid index is the leftover of a failed concurrent build. The planner will not use it, so queries stay slow, and it is still maintained on every write, so you pay the cost without the benefit. Drop it, find out why the build failed, usually a conflicting lock or a deadlock, then build it again. Put this query in your deployment checklist.

REINDEX rebuilds an index from scratch, which is how you recover from bloat, or from a broken index after a collation change. Plain REINDEX blocks writes. REINDEX INDEX CONCURRENTLY builds a replacement alongside the old one and swaps them, so writes continue. It needs disk space for both copies at once, and like any concurrent operation it can fail and leave an invalid index. Measure the bloat before and after, so you know it was worth doing.

pgstatindex, from the pgstattuple extension, gives you real numbers instead of a guess. Look at leaf fragmentation, forty two percent here, and at how densely the leaf pages are filled. Compare that against the size on disk. A fragmented index means more pages for the same keys, which means more reads and less of it fitting in cache. Pick a threshold you trust, rebuild above it, and ask whether the workload will simply create the bloat again.

That is the full path, from how a b-tree finds a row to keeping indexes healthy in production. Run the lab on your own data, measure before and after, and drop what nothing uses. See you in the next one.