What is write amplification in database indexing and why does it accumulate over time?

Write amplification in database indexing refers to the additional write operations a database must perform to maintain index structures when rows are inserted, updated, or deleted. For each index on a table, a single INSERT requires one write to the table heap and one write to each index B-tree (or GIN, BRIN, or other structure); a table with eight indexes requires nine write operations per insert instead of one. The amplification compounds under high write rates because index pages must be updated, split when full, and eventually cleaned by autovacuum to reclaim space from dead tuples produced by updates and deletes. The accumulation problem is that indexes are added incrementally over the lifetime of a production system — one added in the performance optimization session, one added in the incident response session, one added as part of a schema migration — and each addition increases the write amplification permanently until the index is explicitly removed. A table that starts with two indexes and accumulates ten over three years of growth has five times the write amplification of its original baseline. If the write rate also grows five-fold during that period, the combined effect is twenty-five times the original index maintenance overhead on the same hardware — an effect that typically surfaces not as a sudden failure but as a gradual INSERT latency increase over months, making the connection between index accumulation and performance degradation difficult to diagnose without an index inventory that shows when each index was added and which query patterns it was intended to serve.

How does the composite index column order affect which queries benefit from the index?

In a composite B-tree index, the column order determines which query predicates can use the index for range scans and equality lookups. A composite index on (user_id, created_at) can satisfy queries that filter on user_id alone (using only the first column), queries that filter on both user_id and created_at (using both columns from the left side of the index), and queries that sort by user_id followed by created_at. It cannot satisfy queries that filter on created_at alone without also filtering on user_id, because the B-tree is sorted first by user_id and only within each user_id value by created_at — a range scan on created_at without a user_id equality predicate would require scanning the entire index. The column order decision is made at index creation time and cannot be changed without dropping and recreating the index. In practice, the column order decision is made to serve the specific query that prompted the index addition — the slow user dashboard query that filters WHERE user_id = $1 ORDER BY created_at DESC. When a new query pattern emerges that filters on created_at across all users (a time-range report, a background job, a monitoring query), the existing index does not serve it efficiently, and a new index is added on (created_at, user_id) or (created_at) without a review of whether the original (user_id, created_at) index is still actively used. The index coverage rationale documents not only why the column order was chosen but which query patterns it does and does not serve, enabling future sessions to identify whether a new query pattern requires a new index or can be served by adjusting an existing one.

What is the difference between a partial index and a full index and when is a partial index preferable?

A partial index includes only a subset of rows that satisfy a specified WHERE condition, rather than all rows in the table. For example, an index on payments(user_id) WHERE status = 'pending' indexes only pending payments rather than all payments. Partial indexes are preferable when the query pattern that requires the index filters on a selective subset of rows, the subset is substantially smaller than the full table, and the WHERE condition is a stable filter that appears in the query predicate. The benefits are proportional to the selectivity of the condition: if 2% of payments are pending at any time, the partial index is approximately 50 times smaller than the equivalent full index, the index scan is faster because fewer pages must be read, autovacuum work is proportional to index size and maintenance is reduced, and each INSERT and DELETE only updates the index if the row satisfies the partial condition — write amplification for rows outside the condition is zero. The drawbacks are that the partial index condition must match the query predicate exactly for the planner to use the index, and that the index definition embeds a domain assumption (that status = 'pending' is the meaningful filter state) that must be updated if the filtering logic changes. The index coverage rationale must document the partial condition, the selectivity that justifies the partial index over a full index, and the query predicate that it is designed to serve, so that a future schema change that adds a new status value or renames the status column can evaluate whether the partial condition is still correct without consulting the original optimization session.

How does pg_stat_user_indexes identify unused indexes that should be retired?

The pg_stat_user_indexes system view in PostgreSQL tracks the cumulative number of index scans (idx_scan), index rows read (idx_tup_read), and live rows fetched from the table heap following an index scan (idx_tup_fetch) since the statistics were last reset, which happens at server restart or when pg_stat_reset() is called explicitly. An index with idx_scan = 0 over a representative monitoring window — typically 30 to 90 days of production traffic — has not been used by the query planner for any query during that period, meaning either no query in production exercises the predicate the index was built to serve, or the query planner has determined that a sequential scan is cheaper than the index scan for the current table size and data distribution. Indexes with persistent idx_scan = 0 are retirement candidates: they occupy storage, they add write amplification to every INSERT, UPDATE, and DELETE on the indexed table, they add autovacuum work proportional to the index size, and they provide no query performance benefit during the monitoring window. Before retiring a candidate, the index must be confirmed absent from the application codebase as an explicit hint (PostgreSQL does not support index hints in the same way MySQL does, but some ORMs reference index names), the write amplification reduction must be estimated (the number of indexes on the table after retirement determines the new baseline write operations per INSERT), and the DROP INDEX CONCURRENTLY must be executed during an off-peak window to avoid blocking writes on the table during the drop. Statistics reset by a server restart can cause a recently restarted server to show idx_scan = 0 for indexes that were actively used before the restart; the monitoring window must span at least one full representative traffic cycle — typically including a week's worth of traffic that exercises background jobs, reporting queries, and scheduled maintenance — before an index is classified as a retirement candidate based on zero scan count.

The database indexing strategy decision record: why the index selection model you chose determines your query performance ceiling and your write amplification floor

Published 2026-07-17 · WhyChose

Database index decisions are made when a query runs slowly enough that someone investigates, when a schema migration adds a new table that needs foreign key indexes, or when a production incident triggers an emergency index addition during an outage window. The AI session that addresses a slow query is practically focused and reaches a working state quickly: it retrieves the query, runs EXPLAIN ANALYZE to identify a missing index or a suboptimal plan, constructs a CREATE INDEX CONCURRENTLY statement with appropriate column selection, waits for the index build to complete, and re-runs the query to confirm the plan has switched from a sequential scan to an index scan with an appropriate cost reduction. The session ships an index that fixes the immediate problem, and the team observes the slow query resolving and the dashboard loading in acceptable time.

What the AI session does not produce is the second half of the indexing decision. The session answers "which index fixes this query?" and delivers a working plan. It does not ask: what is the index coverage rationale — which query patterns will this index serve, what is the expected selectivity, why were these specific columns chosen in this specific order, is this a case where a partial index on a selective subset of rows would be smaller and faster than a full-table index, and does the index cover all SELECT columns so that an index-only scan is possible without a table heap access? What is the index maintenance model — how many indexes now exist on this table, what is the write amplification per INSERT given that number, how do UPDATE and DELETE operations interact with each indexed column, what is the expected autovacuum load at the production write rate, and what is the monitoring signal that detects when index bloat accumulation is degrading write throughput? What is the slow query detection strategy — at what latency threshold does a query trigger an investigation, how is a new sequential scan on a table that previously used an index detected before it reaches production, how is a P99 baseline established for each key query pattern so that a regression is detectable, and what is the procedure when pg_stat_statements reports a query that previously executed in 4ms now consistently executes in 340ms? What is the schema evolution contract — who proposes a new index and what evidence must accompany the proposal, what is the naming convention so team members can understand which query pattern an index was built to serve from its name alone, what are the retirement criteria for an index whose scan count has been zero for 90 days, and what documentation must be updated when an index is added or removed? Each of these questions has an answer that is not derivable from "we added a composite index on (user_id, created_at) and the dashboard query is fast now" as the team describes the optimization result. Each answer determines whether indexes added one at a time across two years of optimization sessions accumulate into 40GB of overhead on a 6GB table with seven of eleven indexes providing no measurable query benefit, whether an INSERT latency regression caused by index accumulation is diagnosed as an infrastructure problem or correctly identified as a write amplification consequence of unreviewed index additions, whether a slow sequential scan on a 200GB table is detected by a monitoring alert or by a user complaint about a report that times out, and whether a new team member adding an index for a reporting query knows to check whether the existing composite index could serve the query with a different column order rather than adding a ninth index to a table that already has eight. The answers exist in the AI sessions. They are the rationale behind the B-tree structure. They are almost never written down.

Two ways indexing decisions produce the wrong outcome in production

The index accumulation problem and the write amplification ratchet

A B2B SaaS startup builds its core feature around an events table that records every user action in the application. The events table starts with a primary key index and a foreign key index on user_id. Session 1: the founding engineer works with ChatGPT to optimize the user activity dashboard, which queries SELECT * FROM events WHERE user_id = $1 ORDER BY created_at DESC LIMIT 50. EXPLAIN ANALYZE shows a sequential scan over 800,000 rows. The session adds CREATE INDEX CONCURRENTLY idx_events_user_created ON events(user_id, created_at DESC). Dashboard query time drops from 1.4 seconds to 12ms. Session closes.

Session 2, six months later: a new product manager requests a "recent activity across all users" admin panel. The query is SELECT * FROM events WHERE created_at > NOW() - INTERVAL '7 days' ORDER BY created_at DESC. The existing composite index on (user_id, created_at) cannot satisfy this query efficiently — without a user_id equality predicate, the planner must scan the entire (user_id, created_at) index to find all rows within the time range, which is equivalent to a sequential scan on the index. EXPLAIN ANALYZE confirms: Seq Scan on events (cost=0.00..42817.00 rows=1108834 width=287). The session adds CREATE INDEX CONCURRENTLY idx_events_created ON events(created_at DESC). Admin panel query drops from 2.1 seconds to 18ms. Session closes.

Session 3, four months after Session 2: a background job processes unprocessed events for billing. Query: SELECT id, user_id, event_type FROM events WHERE processed = false ORDER BY created_at ASC LIMIT 1000. Sequential scan. Session adds CREATE INDEX CONCURRENTLY idx_events_unprocessed ON events(created_at ASC) WHERE processed = false. Background job latency drops from 800ms per batch to 6ms. Session closes. Session 4, three months later: a new engineer adds an event_type filter to the admin panel. The compound query SELECT * FROM events WHERE created_at > $1 AND event_type = $2 does not use idx_events_created efficiently when event_type is highly selective. Session adds CREATE INDEX CONCURRENTLY idx_events_created_type ON events(created_at DESC, event_type). Session 5: a reporting query on (user_id, event_type). New index. Session 6: an analytics query on (event_type, created_at) for a different time-range report. New index.

Three years after founding, the events table has grown from 800,000 rows to 280 million rows at 15,000 inserts per minute from a user base that has grown 30x. The table has eleven indexes. Total index size is 41GB. Table data size is 6GB. Write amplification per INSERT is eleven index entries plus the heap row — twelve write operations per event ingested, compared to two at founding (primary key plus user_id FK). At 15,000 inserts per minute, the database performs 180,000 index writes per minute, of which the useful work is 30,000 (the two founding indexes plus the partial index on unprocessed); the remaining 150,000 are maintaining five indexes whose query patterns have changed or whose query frequency does not justify the overhead. pg_stat_user_indexes shows four of the eleven indexes with idx_scan = 0 for the past 90 days. Two more are scanned fewer than 200 times per day — the reporting queries that prompted their creation have been moved to a read replica and the primary still carries the index overhead without receiving the read benefit. INSERT latency, which was 1.2ms at founding, is now 18ms on average and spikes to 340ms during batch event ingestion windows. The team diagnoses the problem as "database server CPU utilization" and purchases a larger instance. The INSERT latency improves to 9ms on the new instance but continues to climb as the write rate grows. No one has reviewed the index inventory since Session 1. The index accumulation problem is not visible in any dashboard — it is visible only in pg_stat_user_indexes, which no one has queried in production.

The overlapping index problem and the compliance audit discovery

A fintech startup processes payment transactions through a PostgreSQL-backed API. The payments table is created with primary key, user_id FK, and merchant_id FK indexes at founding. Over the following nine months, six separate optimization sessions — each responding to a specific slow query identified in production or staging — add six additional indexes. By the time a routine compliance audit reviews the database schema, the payments table has eleven indexes: three covering overlapping column combinations from different optimization sessions (idx_payments_user_status on (user_id, status), idx_payments_user_status_created on (user_id, status, created_at), and idx_payments_user_created on (user_id, created_at) — all three include user_id as the leading column and varying combinations of status and created_at in different orders), one partial index on (user_id) WHERE status = 'pending' that overlaps with idx_payments_user_status for the pending-status case, and seven additional indexes covering amount ranges, merchant time ranges, and a now-unused refund tracking query that was replaced by a separate refunds table six months earlier.

The compliance audit engineer runs SELECT indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE relname = 'payments' ORDER BY idx_scan ASC. The output shows: idx_scan = 0 for three indexes over the past 60 days, idx_scan between 2 and 15 per day for four more, and meaningful scan activity for only four of the eleven. Total index size for the payments table: 31GB on a 6GB table. Index size exceeds data size by more than 5x. Each payment INSERT writes to eleven indexes. The audit engineer estimates the write amplification cost: at the startup's current transaction volume of 3,200 payments per hour, the database performs 35,200 index writes per hour on the payments table, of which approximately 9,600 serve actively-used query patterns and 25,600 maintain indexes the query planner does not use for any currently active query. INSERT latency for payments has grown from a baseline of 2ms at founding to 28ms current, and peaks at 180ms during settlement batch windows that process thousands of payments concurrently. The team has attributed the latency growth to "database aging" and "transaction volume growth" rather than investigating whether eleven indexes on a single table explain why payment inserts are taking 14x longer than at founding with only a 4x increase in transaction volume. The three overlapping composite indexes that include user_id as the leading column each maintain a separate sorted structure covering data that the original idx_payments_user_status_created already covers for the combined predicate. The partial index on (user_id) WHERE status = 'pending' duplicates the work of idx_payments_user_status for the pending case while also being smaller — both indexes exist simultaneously, and the planner alternates between them depending on the query cost estimate and the current cardinality of the pending subset. The team schedules a maintenance window to DROP seven indexes, implement a review policy for future index additions, and reconfigure the billing batch process to use a read replica instead of writing new indexes to the primary to serve reporting queries.

Three structural properties that indexing decisions determine

The index coverage rationale and the query performance ceiling

The index coverage rationale is the explanation of what each index was designed to do and for which query patterns it provides benefit. Without this rationale, an index accumulation problem is invisible: team members reviewing the schema see a list of index names, cannot determine from the names which queries each index serves, and cannot evaluate whether a new query requires a new index or whether an existing index with a different column order could serve it with a plan change. The coverage rationale must document four properties for each index. First, the query pattern: the specific query text or query shape (equality on user_id, range scan on created_at, partial filter on status = 'pending') that the index was created to serve, with EXPLAIN ANALYZE output showing the pre-index and post-index query plan to confirm the improvement was realized. Second, the expected selectivity: the fraction of table rows that match the index predicate under typical production conditions — a composite index on (user_id, created_at) on an events table where a single user has 0.001% of all rows has very different selectivity than an index on (status) where status = 'active' represents 97% of rows. Third, the column ordering rationale: for composite indexes, why the leading column was chosen (highest selectivity predicate, most common filter in the WHERE clause, the column that appears in equality predicates while secondary columns appear in range predicates) and what query patterns are explicitly not served by the current order. Fourth, the index-only scan eligibility: whether the index covers all columns referenced in the SELECT list and the WHERE clause, enabling the planner to serve the query entirely from the index without touching the table heap. An index-only scan requires that the index includes every column the query references — an index on (user_id, created_at) cannot support an index-only scan for SELECT id, user_id, created_at, event_type because event_type is not in the index; including it as a non-key column (using INCLUDE in PostgreSQL 11+) enables index-only scans without affecting the B-tree sort order. The coverage rationale provides the baseline for evaluating whether a new query requires a new index or can be served by an existing index with a covering column addition — a distinction that determines whether the team adds a new index with its associated write amplification overhead or modifies an existing index to cover the new query pattern without adding a second index to the table. The database vendor decision record specifies which database engine and version is in use; the index types available and the index-only scan behavior are engine-specific — PostgreSQL's INCLUDE column support, GIN indexes for JSONB and array columns, and BRIN indexes for time-series data with natural physical ordering all depend on the engine version selected, and the indexing strategy must be evaluated against the specific engine capabilities documented in the vendor decision record.

The index maintenance model and the write amplification floor

The index maintenance model determines the minimum additional I/O overhead per write operation given the number and type of indexes on each table, and the operational cost of maintaining those indexes at the production write rate. The model has four components. The first is the per-table write amplification calculation: for each table, the number of indexes (including the primary key B-tree) determines the write amplification per INSERT (one heap write plus one index write per index), and the columns updated in the most common UPDATE patterns determine which indexes must be updated per UPDATE operation (only indexes covering the modified columns require a write). A table with nine indexes where the most common UPDATE pattern modifies a non-indexed status column has lower effective write amplification for updates than for inserts, but if a second common UPDATE pattern modifies a timestamp column that is the leading column of three indexes, those three indexes each require a write. The write amplification must be stated per operation type (INSERT, the most common UPDATE patterns, DELETE) for each high-write table — not as a theoretical maximum but as the expected amplification at production traffic patterns. The second component is the autovacuum interaction: PostgreSQL's multi-version concurrency model produces dead tuples from UPDATE and DELETE operations, and autovacuum must reclaim dead tuple space from both the table heap and every index on the table. A table with eleven indexes where 20% of rows are updated per day produces dead tuple accumulation in eleven index structures simultaneously; autovacuum must clean all eleven even if only two indexes cover the updated columns, because the B-tree pages for non-updated columns still contain pointers to dead tuple versions that must be removed. High write amplification combined with slow autovacuum creates index bloat: index pages become partially filled with dead pointers, the index grows larger than the data it covers, and sequential scans begin outperforming index scans for certain query shapes because the planner's cost estimate reflects the bloated index size rather than the live data size. The autovacuum schedule must be specified at the expected write rate — "autovacuum runs every 50 million write operations" is not a specification; "at the current 15,000 inserts per minute and 3,000 updates per minute on the events table, autovacuum triggers approximately every 28 minutes and completes in approximately 4 minutes based on the current index size of 41GB" is a specification that can be monitored and compared against production behavior. The third component is the index type maintenance overhead: GIN indexes for JSONB and full-text search have significantly higher write overhead than B-tree indexes because GIN indexes decompose each value into individual tokens and maintain an inverted posting list; a GIN index on a JSONB column with 50 keys per document costs roughly 50 index entries per INSERT, compared to one for a B-tree index on a scalar column. BRIN indexes have very low write overhead because they store only per-range summaries rather than per-row entries, but they provide accurate selectivity estimates only for columns that are naturally correlated with physical storage order — an events.created_at column that is always monotonically increasing benefits from BRIN; an events.updated_at column that is updated for arbitrary rows does not. The fourth component is the monitoring signals that detect when maintenance overhead exceeds acceptable thresholds: pg_stat_user_indexes.idx_scan = 0 for a configurable window (typically 30 to 90 days) identifies retirement candidates; pg_stat_user_tables.n_dead_tup accumulation rate identifies tables where autovacuum is falling behind; pgstattuple extension measurement of index bloat ratio (live tuples as a fraction of index size) identifies indexes where bloat has degraded scan efficiency; INSERT latency tracking in pg_stat_statements for key write operations provides the operational signal that write amplification is affecting application throughput. The database connection pooling decision record specifies the maximum concurrent write connections; high write amplification combined with inadequate connection pool sizing creates contention on index pages during concurrent insert bursts, amplifying the latency effect of each additional index beyond the single-connection write amplification calculation.

The slow query detection strategy and the schema evolution contract

The slow query detection strategy determines whether a query regression — an index that the planner stopped using after a table size crossed a threshold, a new query pattern that performs a sequential scan on a table that grew from 10GB to 200GB over six months, a background job that changed its query shape and stopped matching the partial index condition — is detected by a monitoring alert before it reaches a user, or is reported by a user experiencing a timeout. Detection has three tiers. The first is the pg_stat_statements baseline: for each key query pattern in the application (the user dashboard query, the payment insert, the merchant settlement query, the background job processing query), a P99 latency baseline is established at the time the query is deployed and an alert threshold is configured as a multiple of that baseline (commonly 3x for interactive queries, 10x for background queries). pg_stat_statements must be enabled in the database configuration (shared_preload_libraries = 'pg_stat_statements') and the monitoring system must query it on a schedule (every 5 minutes for interactive queries, every 30 minutes for batch queries) and compare current P99 values against the stored baseline. The baseline must be updated when the query is intentionally modified or when the expected latency changes because of a schema optimization — a stale baseline that no longer reflects expected performance will either suppress alerts for genuine regressions or fire alerts continuously for queries that have been intentionally changed. The second tier is sequential scan monitoring for large tables: pg_stat_user_tables.seq_scan tracks the cumulative number of sequential scans per table since the last statistics reset. A large table (over 1GB) that accumulates sequential scans at a rate faster than the rate of expected sequential-scan queries is a signal that a query is not using an available index, or that an index is being used by some queries and not others because of planner cost estimate variations. The alert threshold for sequential scan accumulation must account for legitimate sequential scan queries (ETL jobs, backup procedures, VACUUM operations that perform sequential scans internally) and should focus on unexplained increases in sequential scan rate for tables that have stable sequential scan traffic. The third tier is query plan regression detection for schema changes: any CREATE INDEX, DROP INDEX, or ALTER TABLE that modifies column definitions or constraints may change the planner's cost estimates for existing queries, causing the planner to select a different plan than before the change. Before and after each schema migration that modifies indexes, EXPLAIN ANALYZE output for the ten most critical query patterns should be captured and compared — a plan change that switches from an index scan to a sequential scan on a table over 100MB is a regression that must be investigated before the migration is promoted to production. The observability strategy decision record specifies the monitoring platform and the alerting configuration; the indexing strategy's slow query detection policy must be wired into the same alerting infrastructure so that a slow query alert and an error rate alert from the same time window are correlated into a single incident rather than investigated independently.

The schema evolution contract specifies the process by which indexes are proposed, approved, named, added to the production database, and eventually retired. The contract must address four questions. How is a new index proposed: what evidence must accompany a proposal (the specific slow query in pg_stat_statements, the EXPLAIN ANALYZE output showing the current plan and cost, the estimated plan and cost after the proposed index, the write amplification impact stated as the new index count for the table and the expected INSERT latency change at the production write rate, and the check for whether any existing index could serve the query with a covering column addition rather than a new index). What is the naming convention: a consistent naming scheme enables team members to understand the purpose of an index from its name — idx_{table}_{leading_column}_{secondary_columns}_{partial_suffix} is a common convention, where idx_payments_user_status indicates a composite index on (user_id, status) and idx_events_created_unprocessed_partial indicates a partial index on (created_at) WHERE processed = false; the convention must be specified in the contract so that all team members apply it consistently and the index inventory remains readable. What are the retirement criteria: an index with idx_scan = 0 in pg_stat_user_indexes for a configurable window (commonly 90 days for production traffic that covers all regular query patterns including month-end and quarter-end reporting cycles) is a retirement candidate; the retirement procedure requires confirming the index name does not appear in application code as a hint, calculating the write amplification reduction from removing the index, and executing DROP INDEX CONCURRENTLY during an off-peak window to avoid blocking writes on the table. What documentation must be updated: the schema evolution contract itself (removing the retired index entry), the index inventory in the decision record, and any observability baseline that included the indexed query pattern. The CI/CD pipeline decision record specifies the migration deployment process; CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY require specific handling in the migration pipeline — standard schema migration tools that acquire an exclusive table lock during migration cannot be used for index operations on high-traffic tables without causing downtime, and the pipeline must support running CONCURRENTLY operations as separate migration steps that do not hold transaction locks across the build window.

Three AI session types that embed indexing decisions without documenting them

The performance optimization session is the most common source of undocumented index decisions. The session is triggered by a specific slow query identified in production monitoring, a user complaint about a page load time, or a latency alert from a performance monitoring tool. The session is focused on diagnosing and resolving the immediate performance problem: it examines the query, runs EXPLAIN ANALYZE, identifies the missing index or the suboptimal plan, constructs the CREATE INDEX statement, waits for the concurrent build to complete, and verifies that the query plan has changed from a sequential scan to an index scan. The session succeeds when the specific slow query is fast. It does not produce the coverage rationale for the new index (which query patterns it serves, which it does not, and what the column ordering rationale is), the write amplification impact (how the new index changes the total write amplification for the table at the current production write rate), or the index inventory review (a check of whether existing indexes on the table have been used since the last statistics reset and whether any retirement candidates were made by the new index addition). The session also does not produce the slow query detection update: the P99 baseline for the newly fast query is not recorded, and the alert threshold that would detect if the query regresses to its pre-index latency is not set. The next performance optimization session on the same table starts without visibility into the cumulative write amplification of all previous sessions.

The schema migration session embeds indexing decisions as infrastructure boilerplate. When a new table is created, the migration adds indexes as part of the standard setup — a primary key, foreign key indexes for each FK column, and often a CREATE INDEX on whatever column the initial query will filter on. These decisions are made in the migration file without explicit rationale: the migration says CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id) but not "this index serves the subscription lookup query in the billing flow; the write amplification is acceptable because subscriptions have a low insert rate (one per user signup) and the query is latency-critical." The migration session also does not consider whether the new table's indexes interact with an existing table's indexes — a subscription table that joins frequently against the events table does not prompt a review of whether the events table's composite index on (user_id, created_at) serves the join condition or whether a new index on events might reduce the join cost. The database migration strategy decision record specifies the migration tooling and the rollback procedure; the indexing strategy must specify that every migration that adds or removes an index must include the coverage rationale in the migration file comments (not just the SQL) and must be reviewed against the current index inventory for the affected tables before the migration is approved for production deployment.

The incident response session adds indexes under time pressure without a retrospective. A production incident caused by a slow query — a timeout on the payment endpoint, a deadlock on the user authentication table, a background job that is processing at 10% of its expected rate — demands immediate resolution. The session identifies the missing index or the incorrect query plan, adds the index as the fastest path to restoring acceptable performance, and verifies the incident is resolved. The session closes when the metrics return to normal. What does not happen in the incident session: a review of whether other existing indexes on the table have degraded since the last review (a separate index may be the root cause of the write amplification that contributed to the incident), documentation of the coverage rationale for the emergency index, or an update to the slow query detection baseline so that the resolved query now has an alert threshold that would catch a future regression. The open-source extractor surfaces these performance optimization sessions, schema migration sessions, and incident response sessions from AI chat history, recovering the index creation decisions and the query optimization rationale made in contexts where writing down the coverage model was not the goal — the goal was the faster query, and the rationale was the implicit reasoning behind the column selection, the partial condition, and the composite ordering that the session author understood at the time and that no subsequent team member can recover from the index name alone.

The five sections of a database indexing strategy decision record

The first section documents the index inventory per table. For each table in the database that has more than the primary key index, the inventory records every index by name, type (B-tree, GIN, BRIN, partial, expression), the columns covered and their ordering, any partial index condition, the query patterns the index was created to serve (with enough specificity that a future team member can determine whether a new query is served by the existing index or requires a new one), the expected selectivity of the index predicate under production data distribution, the current pg_stat_user_indexes scan count over the standard monitoring window, and the index-only scan eligibility given the SELECT columns of the query patterns it serves. The inventory is the canonical reference that prevents duplicate index creation — before adding a new index, the engineer proposing the index must check the inventory to confirm that no existing index serves the new query pattern with a different column order or a missing covering column. The inventory must be maintained as a living document: updated when an index is added, when an index is retired, and when the query patterns an index serves change because the application query is modified. An inventory that has not been updated since the last index addition is worse than no inventory, because it creates false confidence that the team understands the current index landscape. The database read replica decision record specifies whether read queries are routed to replicas; the index inventory for a database with a read replica must distinguish between indexes that serve primary write-path queries (which require the index to be on the primary) and indexes that serve read-only reporting or analytics queries (which can be created exclusively on the replica to avoid write amplification on the primary, a capability supported by PostgreSQL's physical replication but requiring application-level routing logic to direct the queries to the replica).

The second section documents the write amplification model per high-write table. For each table with an insert rate above a defined threshold (commonly 100 inserts per minute in production), the write amplification model states the total number of indexes (each index is one additional write operation per INSERT), the write amplification per INSERT as a total count (heap write plus one per index), the write amplification per UPDATE for the most common update patterns (listing which indexes cover each updated column and must be written), the DELETE amplification (same as INSERT for most index types), the expected autovacuum trigger frequency at the current insert and update rate, the estimated autovacuum completion time given the current index sizes, and the monitoring signals that detect when autovacuum is falling behind dead tuple accumulation. The model must also state the write amplification floor — the minimum achievable amplification given the mandatory indexes (primary key, foreign key constraints) — and the overhead above that floor attributable to optional indexes. The overhead above the floor is the cost of the coverage the optional indexes provide; each optional index must justify its overhead against the query performance benefit it provides. Indexes whose coverage rationale is "no known query actively uses this index" (idx_scan = 0 for 90 days) have a write amplification cost greater than zero and a query performance benefit of zero — they are net negative by definition and must be retired. The database sharding decision record specifies whether the database is sharded; index write amplification is multiplied by the shard count for indexes that must be maintained on every shard, and global indexes that span shard boundaries require coordination writes that multiply the amplification further — the sharding strategy and the indexing strategy must be designed together to avoid write amplification that scales with both the number of indexes and the number of shards.

The third section documents the slow query detection strategy. The specification must state the pg_stat_statements configuration (which queries are tracked, the query normalization settings, the maximum number of tracked queries, and the sampling rate if pg_stat_statements.track is set to top rather than all), the P99 latency baseline for each key query pattern at the time the strategy is established, the alert threshold for each query pattern as a multiple of the baseline with the justification for the multiplier (a 3x threshold for a 4ms query means an alert at 12ms; a 3x threshold for a 400ms batch query means an alert at 1,200ms, which may or may not be operationally meaningful), the sequential scan monitoring policy for tables above a configurable size threshold (1GB is a common starting point), the query plan regression detection procedure for schema migrations (which queries are re-EXPLAIN ANALYZED before and after the migration, and what plan change constitutes a regression requiring rollback), and the escalation procedure when a slow query alert fires. The escalation procedure must specify the first-responder action (check pg_stat_user_indexes for recent scan count changes, check pg_stat_user_tables for sequential scan rate increases, check whether a recent schema migration changed the available index set) before attempting to add a new index as the resolution — because a missing index is only one of several causes of a slow query alert, and adding a new index to resolve a latency alert caused by autovacuum contention or connection pool exhaustion adds write amplification without addressing the root cause. The performance optimization decision record specifies the methodology for diagnosing and resolving production performance problems; the indexing strategy's slow query detection policy feeds into the performance optimization process as the signal layer, and the performance optimization decision record should reference the indexing strategy as the primary intervention mechanism for query-pattern-specific latency regressions.

The fourth section documents the schema evolution contract. The specification must state the index proposal requirements (the query in pg_stat_statements showing the slow query and its current P99 latency, the EXPLAIN ANALYZE output before the proposed index showing the current plan type and cost, the EXPLAIN ANALYZE output after the proposed index on a representative data sample showing the new plan type and estimated cost, the write amplification calculation showing the new index count for the table and the expected INSERT latency change at the current production write rate, and the index inventory check confirming no existing index serves the query pattern with a column order adjustment or covering column addition), the index naming convention applied consistently to all index names in the inventory, the retirement criteria (idx_scan = 0 for the standard monitoring window across a representative traffic period that includes month-end and quarter-end query patterns, confirmed against the application codebase for any reference to the index name as a query hint, with the write amplification reduction calculation and the DROP INDEX CONCURRENTLY procedure scheduled for an off-peak maintenance window), and the documentation update requirements after each index addition or retirement (inventory updated, write amplification model updated for the affected table, slow query detection baseline updated if the changed index affects a monitored query pattern, schema migration file annotated with the coverage rationale). The contract must also address the index consolidation review cadence: a periodic (quarterly or semi-annual) review of the full index inventory against the current pg_stat_user_indexes scan counts and the current application query patterns, with the explicit goal of identifying retirement candidates and redundant indexes before write amplification accumulation creates a production write performance problem. The database backup strategy decision record specifies the backup schedule and format; DROP INDEX CONCURRENTLY on a table with billions of rows can take hours, and the operation must be scheduled to avoid overlapping with backup windows — a DROP INDEX that runs concurrently with a pg_dump can increase backup duration significantly, and both operations must be accounted for in the maintenance window planning.

The fifth section documents the index type selection rationale. Not every index in the inventory requires the default B-tree structure; for some column types and query patterns, a different index type provides better query performance with lower write amplification or storage overhead. The specification must state why each index type was selected for each index in the inventory: B-tree for equality and range predicates on scalar columns (the default, appropriate for the majority of indexes); GIN (Generalized Inverted Index) for JSONB column containment queries (WHERE data @> '{"key": "value"}'), for full-text search (WHERE to_tsvector(body) @@ to_tsquery('search term')), and for array containment (WHERE tags @> '{postgresql}') — GIN indexes have significantly higher write overhead than B-tree because each GIN entry decomposes the value into tokens and maintains a posting list, so GIN is appropriate only when the query pattern requires the containment or overlap operator that B-tree cannot serve; BRIN (Block Range Index) for monotonically ordered columns on very large tables where physical storage order correlates with the column value — an events table where created_at increases monotonically and rows are never updated has a natural physical correlation that BRIN can exploit; BRIN stores one range summary per 128 blocks (the default) rather than one entry per row, so a BRIN index on a 200GB events table is under 1MB compared to a B-tree index of several GB, and write amplification is negligible because most BRIN summaries are unchanged by a new row at the end of the table; partial index for selective subset queries where a WHERE condition filters to a small fraction of the table (under 20% is a common heuristic); expression index for queries that filter on a derived value (WHERE LOWER(email) = $1 is served by CREATE INDEX ON users(LOWER(email)), avoiding full-table case-insensitive scans). The selection rationale enables future sessions to evaluate whether a new query on a JSONB column should use the existing GIN index on the JSONB column or whether the query's access pattern requires a separate expression index on a specific JSONB key path, a distinction that determines whether the new query is served at zero additional index cost or requires a new index with GIN write overhead. The new CTO onboarding problem is what happens when none of these five sections exist: the incoming technical leader finds eleven indexes on the payments table with no rationale for any of them, seven of which have not been scanned in 90 days, total index overhead exceeding table data size by 5x, INSERT latency growing at a rate that can't be explained by transaction volume alone, no monitoring alert on index scan count or write amplification, and no schema evolution contract that would have prevented the accumulation from reaching eleven indexes in the first place. Each of these properties can be recovered from the AI optimization sessions, incident response sessions, and schema migration sessions that created each index — "let's add an index on user_id and created_at for the dashboard query," "the admin panel query needs created_at without a user_id filter," "add a partial index on pending payments for the background job" — the reasoning exists in the chat history where each index was proposed, evaluated with EXPLAIN ANALYZE, and added to the schema. WhyChose's open-source extractor surfaces these performance optimization sessions, schema migration sessions, and incident response sessions as structured decision records before seven indexes with zero scans are identified only during a compliance audit that also reveals the total index overhead has exceeded the table data size, before an INSERT latency degradation caused by eleven-index write amplification is misdiagnosed as a hardware capacity problem requiring a more expensive instance instead of a maintenance window to drop six unused indexes, and before the performance optimization session that finally investigates the write amplification ratchet discovers that the coverage rationale for each of the eleven indexes exists in six separate ChatGPT conversations from three different engineers across three years of growth, none of which have been read since the session that produced them.

The performance optimization session that added a composite index on (user_id, created_at) to fix a slow dashboard query, the schema migration session that added standard FK indexes plus a (created_at) index for the admin query without reviewing the existing index inventory, and the incident response session that added a partial index on pending payments without documenting the coverage rationale or updating the write amplification model each produced indexing decisions whose long-term maintenance cost — eleven indexes on a payments table with only four used by active queries, 40GB of index overhead on a 6GB events table, INSERT latency growing to 18ms from a 1.2ms baseline as the write rate scaled and the index count accumulated — exceeds what an index coverage rationale documenting which query patterns each index serves, a write amplification model identifying the four indexes responsible for half the maintenance overhead, a slow query detection baseline catching the sequential scan regression before it reached production, and a schema evolution contract with 90-day retirement criteria would have cost to specify at the time the founding indexing decisions were made. The decisions are in the AI sessions: the composite index chosen with user_id as the leading column because the dashboard query filtered by user_id, the write amplification cost accepted implicitly because the focus was the slow query not the maintenance overhead, the retirement process never established because each optimization session was focused on adding the index that solved the current problem not reviewing whether previous sessions' indexes were still needed. WhyChose's open-source extractor surfaces these performance optimization sessions, schema migration sessions, and incident response sessions as structured decision records before an index accumulation problem accumulates across dozens of sessions into 40GB of overhead on a 6GB table, a write amplification ratchet that adds irreversible maintenance cost with each undocumented index addition, and a schema evolution gap that leaves the team without a way to determine which of the eleven indexes on the payments table can be safely retired without consulting six engineers across three years of optimization history. The decisions never written down in a database indexing deployment are the index coverage rationale with the column ordering justification and the query patterns each index serves or explicitly does not serve, the write amplification model with the maintenance overhead per high-write table at the production write rate, the slow query detection strategy with P99 baselines for key query patterns and sequential scan monitoring for large tables, and the schema evolution contract with retirement criteria and the consolidation review cadence — the four properties that together determine whether the index infrastructure grows proportionally to the query patterns that require it or accumulates unboundedly as each optimization session adds a solution without a mechanism for retiring solutions that the application has outgrown.