Indexing Strategy and Design Questions

Direct answer

An index-only scan lets the database answer a query straight from the index, skipping the table (heap) entirely, but only when two conditions both hold: every column the query needs is present in the index, AND the database can prove, for each candidate row, that its current version is visible to the query without checking the table. In Postgres, that second check is done via the visibility map, a small per-table bitmap that marks which data pages contain only rows every current transaction is allowed to see. Right after a bulk load or heavy write activity, the visibility map is not yet set for the new or changed pages, so even a genuinely covering index falls back to fetching from the heap row by row, silently, until a VACUUM catches up. Two other common causes: the query selects a column the index doesn't include (classically SELECT *), or the predicate itself isn't sargable so the planner never even considers that index.

Structured elaboration

What "covering" actually requires. A covering index must contain every column the query reads, not just the ones in the WHERE clause. In Postgres this usually means an INCLUDE clause carrying non-key payload columns alongside the indexed predicate columns.

The visibility map, in one sentence. Every heap page has a visibility-map bit that says "every row on this page is visible to every transaction, so a scan of the index alone doesn't need to double-check the heap for this page." Bulk-loaded or recently-updated pages start with that bit clear, so the planner may still CHOOSE an index-only scan (the query shape supports it), but the EXECUTOR still has to fetch from the heap for every row on a not-yet-visible page. EXPLAIN ANALYZE reports this directly as Heap Fetches: N. A healthy index-only scan shows Heap Fetches: 0.

What actually clears the visibility map. VACUUM (including autovacuum) is what sets visibility-map bits. Nothing else does automatically at any useful cadence.

Worked example

Seeded on Postgres 16: accounts(id, account_status, region, balance_cents, updated_at), 800,000 rows, account_status = 'suspended' matching about 2 percent of rows (16,002 rows), with a genuinely covering index:

CREATE INDEX idx_accounts_status_balance ON accounts (account_status) INCLUDE (balance_cents);

Right after the bulk load and index build, before any VACUUM:

EXPLAIN (ANALYZE, BUFFERS)
SELECT account_status, balance_cents FROM accounts WHERE account_status = 'suspended';
-- Index Only Scan using idx_accounts_status_balance
--   Heap Fetches: 16002        <- every single matched row still hit the heap
--   Execution Time: 6.152 ms

The planner correctly PICKED an index-only scan (the plan node says so), but Heap Fetches: 16002 proves every row still needed the table, because the visibility map was not yet set for these pages.

VACUUM accounts;

EXPLAIN (ANALYZE, BUFFERS)
SELECT account_status, balance_cents FROM accounts WHERE account_status = 'suspended';
-- Index Only Scan using idx_accounts_status_balance
--   Heap Fetches: 0
--   Execution Time: 1.054 ms

After VACUUM, Heap Fetches drops to 0 and execution time falls from 6.15 ms to 1.05 ms, roughly 6x, purely from the visibility map catching up. The plan's estimated cost fell in step too (2816.41 before, 545.30 after), because the planner's own cost model accounts for expected heap fetches.

Now the "select an uncovered column" trap, run against the SAME, already-vacuumed index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM accounts WHERE account_status = 'suspended';
-- Index Scan using idx_accounts_status_balance   <- note: plain Index Scan, not Index Only Scan
--   Execution Time: 6.402 ms

SELECT * needs id, region and updated_at, none of which are in the index, so Postgres falls back to an ordinary index scan (heap lookup for every row) regardless of how well-vacuumed the table is. This is the realistic complicating factor a candidate cannot always fix: if the query comes from a client library or an ORM (object-relational mapping) tool that always emits SELECT * and cannot be changed, no amount of index tuning recovers the index-only path; the only real fixes are changing the query (list only the needed columns) or broadening the index's INCLUDE list to cover everything that client actually touches.

Trade-offs & pitfalls

Diagnostic checklist for "why isn't my index-only scan happening":

1. Confirm the index actually covers every column the query reads (check the SELECT list, not just the WHERE clause), including columns hidden behind SELECT *.
2. Run EXPLAIN (ANALYZE, BUFFERS) and read Heap Fetches, not just the plan node name. A non-zero, especially a 100 percent, heap-fetch rate on an Index Only Scan node is the visibility-map signature.
3. Run (or wait for autovacuum to run) VACUUM on the table and re-check. If Heap Fetches drops to 0, that confirms visibility map staleness was the cause.
4. If the table is write-heavy enough that autovacuum can't keep the visibility map current between bulk changes, that's a tuning signal on its own (lower autovacuum_vacuum_scale_factor for that table, or schedule explicit VACUUMs after bulk loads).
5. If Heap Fetches is already 0 and the plan still isn't an Index Only Scan, re-check for an uncovered column, or a predicate the planner can't push into the index at all (a function-wrapped column without a matching expression index, for example).

The cost of a covering index is not free: every extra INCLUDEd column widens each index entry, so covering indexes trade some write and storage cost for the read-side win of skipping the heap. Widening an INCLUDE list to cover a SELECT * client is a real fix, but it's worth first asking whether the client actually needs every column, since a narrower, explicit column list is both cheaper to index and clearer about what the code depends on.

Direct answer

Don't assume the indexes are the cause; confirm it first with EXPLAIN (ANALYZE, BUFFERS) on the actual insert statement and a look at pg_stat_user_tables/pg_stat_user_indexes, since checkpoint I/O and disk contention produce a very similar symptom and need a completely different fix. If indexes really are the driver, mitigate immediately without permanently losing any of them: batch inserts into fewer, larger transactions (or COPY), drop any index that turns out to be genuinely unused for good, and reserve a scripted, bounded drop-then-rebuild only for indexes you still need but can tolerate briefly missing during a specific catch-up window.

Immediate diagnostic steps

1. Rule out the other usual suspect first. A burst of checkpoint-driven I/O (Postgres periodically flushing all dirty pages to disk) produces the same symptom, bulk inserts suddenly slow, but the fix is tuning checkpoint_completion_target and max_wal_size, not touching indexes at all. Check the Postgres log for checkpoint warnings and pg_stat_bgwriter before blaming indexes.
2. Count and characterize the indexes on logs. pg_stat_user_indexes gives idx_scan per index; any index with a near-zero scan count over a representative window is pure write cost with no read benefit today, a candidate for permanent removal, not just a symptom to work around.
3. Measure the insert cost directly. EXPLAIN (ANALYZE, BUFFERS) works on INSERT statements, not just SELECT, and shows real buffer activity for that specific insert.
4. Confirm the slowdown correlates with when the indexes were added, using pg_stat_user_tables growth history or deployment timestamps, before committing to an index-focused fix.

Worked example

CREATE TABLE writeamp_1idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE TABLE writeamp_5idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE INDEX ON writeamp_5idx (a); CREATE INDEX ON writeamp_5idx (b);
CREATE INDEX ON writeamp_5idx (c); CREATE INDEX ON writeamp_5idx (d); CREATE INDEX ON writeamp_5idx (e);
-- both tables pre-loaded with 1,000,000 rows each

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO writeamp_1idx (id, a, b, c, d, e)
SELECT g, g%1000, g%1000, g%1000, g%1000, g%1000 FROM generate_series(1000001, 1010000) g;

EXPLAIN (ANALYZE, BUFFERS)
INSERT INTO writeamp_5idx (id, a, b, c, d, e)
SELECT g, g%1000, g%1000, g%1000, g%1000, g%1000 FROM generate_series(1000001, 1010000) g;

Real result for the identical 10,000-row insert: against the single-index table, Buffers: shared hit=20343 read=11 dirtied=108 written=101, 10.2 ms. Against the six-index table, Buffers: shared hit=119263 read=1139 dirtied=1228 written=101, 118.6 ms: about 5.9x more buffer touches and 11.4x more dirtied pages for the same row count, from index maintenance alone. That is the concrete confirmation step: if this same test against logs shows a comparable gap once you strip the query down to a plain INSERT, the indexes are genuinely the bottleneck, not a coincidence in timing.

Mitigation, without losing any index you still need

• Batch, don't drop, first. If the application is doing one INSERT per row, switching to COPY or wrapping many rows into fewer, larger transactions reduces per-statement and per-commit overhead across the board; it does not reduce the number of index entries written, but it amortizes the fixed cost around them.
• Drop what's actually unused, permanently. An index with idx_scan = 0 over a representative period isn't "needed" today; removing it has no read-side cost and immediate write-side benefit.
• For indexes you do need for reads, but can tolerate a short gap on: DROP INDEX CONCURRENTLY right before a bounded, scripted catch-up load, CREATE INDEX CONCURRENTLY immediately after, with the rebuild step automated and monitored (alerting if it doesn't complete), so the index is briefly absent on a known schedule, never accidentally forgotten.

Trade-offs and pitfalls

Any window where a needed index is absent is a window where queries that depended on it either get slow or, for a uniqueness constraint, lose that guarantee entirely; a table serving live user-facing lookups needs either an off-peak window for the drop-rebuild approach or a different mitigation altogether. Batching into fewer, larger transactions also means a mid-batch failure loses more uncommitted work than smaller transactions would, so batch size should be chosen with a realistic failure/retry story in mind, not just for maximum throughput.

Direct answer

Treat this as a measurement problem before it is a deletion problem. Pull real usage statistics for every index on the table (scan counts, tuples read, index size), group the single-column indexes by which queries actually use them together, and only fold a group into one composite index when the leftmost-prefix rule still serves every query that used to hit the separate indexes. Roll it out on a staging replica with production-shaped data, add the new composite index first and leave the old ones in place for an observation window, then drop the old indexes one at a time (never all at once) while watching both query plans and write-side metrics.

A consolidation methodology

1. Inventory usage, not just existence. pg_stat_user_indexes (idx_scan, idx_tup_read, idx_tup_fetch) tells you which indexes are actually touched by reads; pg_relation_size tells you what each one costs to maintain. An index with idx_scan near zero over a full business cycle (including month-end, quarter-end, whatever your peak reporting window is) is a drop candidate on its own, independent of any consolidation work.
2. Cluster indexes by co-occurring predicates. Use pg_stat_statements or a slow-query log to find WHERE-clause shapes. If columns a, b, and c each have their own single-column index but every query that touches any of them actually filters on a AND b, or on a AND b AND c together, they are consolidation candidates.
3. Order the composite by the leftmost-prefix rule. A composite index on (a, b, c) still serves a query that filters on a alone, or on a and b, because a B-tree (a balanced, sorted tree structure that backs most indexes) stores entries sorted by the first column first, then the second within ties on the first, and so on. It does NOT serve a query filtering on b alone or c alone with no a predicate, because there is no way to jump into the sorted order without first fixing a. Put the column with equality filters first, range-filtered columns next, and any column only used for sorting last. Before you drop the single-column index on b, confirm nothing in production filters on b without a.
4. Prove the write-side win with a real measurement, not intuition. WAL (write-ahead log, the record every durable change is written to before the change is considered committed) volume for a fixed batch of writes is a clean, engine-level proxy for the extra maintenance cost that indexes add, and it does not depend on how warm your cache happens to be.
5. Roll out add-before-drop. Build the new composite index with CREATE INDEX CONCURRENTLY (avoids taking a lock that blocks writes), let it run alongside the old single-column indexes for an observation window, replay real query samples against it and check EXPLAIN confirms the plans you expect, then drop the redundant single-column indexes one at a time with DROP INDEX CONCURRENTLY, watching error rates and p95 latency (the 95th-percentile response time: 95% of requests finish at or below this value, a common way to watch a worsening tail without one-off outliers skewing an average) between each drop. Keep the exact CREATE INDEX statements for everything you drop in a rollback script, checked in next to the migration, not just remembered.

Worked example

I measured this on Postgres 16 rather than estimating it. First, the problem statement itself: a table with six single-column indexes (one primary key plus five independent single-column indexes) against the same table with only the primary key, inserting the same 200,000 rows into each, measuring WAL bytes generated via pg_current_wal_lsn() before and after (a page-accurate, engine-native measure of write volume, immune to caching effects that would confound a wall-clock timing comparison):

CREATE TABLE writeamp_1idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE TABLE writeamp_5idx (id bigint PRIMARY KEY, a int, b int, c int, d int, e int);
CREATE INDEX ON writeamp_5idx (a);
CREATE INDEX ON writeamp_5idx (b);
CREATE INDEX ON writeamp_5idx (c);
CREATE INDEX ON writeamp_5idx (d);
CREATE INDEX ON writeamp_5idx (e);

CHECKPOINT;
SELECT pg_current_wal_lsn() AS lsn_before \gset
INSERT INTO writeamp_1idx (id, a, b, c, d, e)
SELECT g, g%1000, g%1000, g%1000, g%1000, g%1000 FROM generate_series(1, 200000) g;
SELECT pg_current_wal_lsn() AS lsn_after \gset
SELECT pg_size_pretty(pg_wal_lsn_diff(:'lsn_after', :'lsn_before')); -- 29 MB

-- repeat CHECKPOINT + the same INSERT pattern against writeamp_5idx -- 97 MB

Real result: inserting the identical 200,000 rows generated 29 MB of WAL (30,907,856 bytes precisely) against the primary-key-only table and 97 MB (101,636,336 bytes precisely) against the six-index table. At full byte precision that is a 3.29x increase from adding five redundant single-column indexes; the rounded MB figures alone (29 and 97) divide to roughly 3.34x, so verify this ratio from the byte counts above, not from the rounded MB display. That is the concrete shape of "heavy write amplification."

Then the fix, measured the same way: three single-column indexes on (a), (b), (c) consolidated into one composite index on (a, b, c), same 200,000-row insert:

CREATE TABLE writeamp_3single (id bigint PRIMARY KEY, a int, b int, c int);
CREATE INDEX ON writeamp_3single (a);
CREATE INDEX ON writeamp_3single (b);
CREATE INDEX ON writeamp_3single (c);

CREATE TABLE writeamp_1composite (id bigint PRIMARY KEY, a int, b int, c int);
CREATE INDEX ON writeamp_1composite (a, b, c);
-- same CHECKPOINT / pg_current_wal_lsn() / INSERT 200000 rows pattern against each

Real result: 68 MB of WAL for the three-single-index table, 43 MB for the one-composite-index table, a 1.58x reduction. On-disk index size dropped from 9,832 kB (three indexes combined) to 6,264 kB (one composite), also a 1.57x reduction, consistent with the WAL number since less index structure means less to log on every insert.

Trade-offs and pitfalls

Consolidation is not free in the other direction: a composite index only helps queries that match its leftmost-prefix shape, so merging three single-column indexes into one composite silently breaks any query that filtered on the second or third column alone. That is exactly why step 2 of the methodology (cluster by real co-occurring predicates from production query logs) has to come before step 3 (pick column order), not after. CREATE INDEX CONCURRENTLY avoids blocking writes, but it can still fail partway through (a uniqueness violation encountered during the scan, for instance) and leave an INVALID index behind that consumes write overhead while being ignored for reads, so the rollout script needs a check for pg_index.indisvalid after each build, with DROP INDEX CONCURRENTLY and retry as the documented recovery path. Finally, do not chase "fewer indexes" as a goal in itself: a single wide composite index that now serves five different query shapes, several of them only through a non-leading column that used to have its own dedicated index, can become a bigger, hotter piece of structure than the sum of the indexes it replaced, with its own write and cache-locality cost. Consolidate where the leftmost-prefix rule and real usage data support it; leave the rest alone.

Direct answer

For WHERE profile->'address'->>'city' = 'Seattle', where profile is a JSONB (Postgres's binary-encoded JSON, JavaScript Object Notation, column type that supports indexing and efficient key lookups, unlike plain text JSON) column, the fastest, cheapest option is a functional (expression) B-tree index built on exactly that expression: CREATE INDEX ON users ((profile->'address'->>'city')). A GIN (Generalized Inverted iNdex, Postgres's index type for indexing every element inside a composite value like a JSONB document or an array) index on the whole profile column is more flexible (it can answer many different key-path queries, not just this one) but it only accelerates containment-style queries (profile @> '{"address":{"city":"Seattle"}}'), not the ->>'city' = form as literally written, and it costs more to write and more to store. If you only ever query this one path, the functional B-tree wins outright; if the application queries many different keys inside profile unpredictably, GIN is the one index that covers all of them, at a real cost.

Structured elaboration

Functional (expression) B-tree. Postgres lets you index the RESULT of an expression, not just a raw column. CREATE INDEX idx_users_city ON users ((profile->'address'->>'city')) builds a plain B-tree over the extracted city string. It only matches queries whose WHERE clause contains that EXACT expression (same operators, same path), it is small (roughly proportional to the number of rows, like any B-tree on a single scalar), and it supports equality and range/sort on that one derived value.

GIN. CREATE INDEX idx_users_profile_gin ON users USING GIN (profile) decomposes the whole JSONB document into its component keys and values and indexes each one, so it can accelerate @> containment queries against ANY path inside the document, including paths you didn't anticipate when you built the index, and multiple keys in one query. The cost is a bigger index (it stores an entry per key/value pair, not per row) and slower writes (every insert or update has to update potentially many index entries per document, not one).

Worked example

Seeded on Postgres 16: users(id, profile jsonb), 500,000 rows, with profile->'address'->>'city' = 'Seattle' matching 2,000 rows (0.4 percent selectivity).

No index at all:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE profile->'address'->>'city' = 'Seattle';
-- Gather (actual time=20.704..23.055, rows=2000)
--   Parallel Seq Scan on users (Rows Removed by Filter: 166000)
-- Execution Time: 23.123 ms

Functional B-tree:

CREATE INDEX idx_users_city ON users ((profile->'address'->>'city'));
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE profile->'address'->>'city' = 'Seattle';
-- Bitmap Heap Scan (actual time=0.047..0.136, rows=2000)
--   -> Bitmap Index Scan on idx_users_city
-- Execution Time: 0.197 ms

23.1 ms down to 0.2 ms, about 115x faster, for a small single-purpose index.

GIN on the whole column, queried with the matching containment form:

DROP INDEX idx_users_city;
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE profile @> '{"address":{"city":"Seattle"}}';
-- Bitmap Heap Scan (actual time=0.216..0.444, rows=2000)
--   -> Bitmap Index Scan on idx_users_profile_gin
-- Execution Time: 0.507 ms

Fast too (about 45x faster than no index), but notice what happens if the query is left in its ORIGINAL form with only the GIN index present:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM users WHERE profile->'address'->>'city' = 'Seattle';
-- Gather -> Parallel Seq Scan (Rows Removed by Filter: 166000)
-- Execution Time: 17.752 ms

Still a full scan. The GIN index was built and is healthy, but the ->>'city' = expression does not map to the @> operator GIN accelerates, so it is never even considered. That gap between "an index exists on this column" and "an index accelerates this exact query form" is the single most common JSONB indexing mistake.

Trade-offs & pitfalls

Before indexing the JSONB path at all, weigh normalizing it into its own column (or a separate table, for a repeating structure like tags or line items). If city is queried, filtered and joined on constantly, an ordinary typed column with an ordinary B-tree is simpler to reason about, cheaper to index, and lets the query planner use accurate per-column statistics instead of the coarser statistics Postgres keeps for JSONB paths. Reach for indexing inside the JSONB when the schema is genuinely variable (different documents have different shapes) or the paths queried are too numerous or too unpredictable to normalize one by one.

Once an application is already carrying an expensive, broad GIN index over a whole JSONB column, and most queries turn out to hit only two or three hot paths, migrating those hot paths to small functional B-tree indexes (and, once traffic confirms nobody queries the old paths, dropping the broad GIN) is a real, incremental way to cut both write amplification and storage without a big-bang schema rewrite.

If indexing alone stops being enough, for example a dashboard needs city, plan and a derived metric together and touches most of the table anyway, a materialized view or a denormalized reporting table refreshed on a schedule is the next escape hatch: it turns a JSONB path-extraction query into an ordinary, cheaply-indexed columnar query, at the cost of some staleness.

A GIN index also supports jsonb_path_ops, a narrower operator class that indexes only @> containment (not key-existence operators like ?), which produces a smaller, faster-to-maintain index when containment is the only query shape you need; the default jsonb_ops class is more flexible but larger.

Direct answer

For this exact query (a date filter followed by GROUP BY customer_id, SUM(amount)), a covering index on (created_at, customer_id) INCLUDE (amount) beats both a plain single-column index on created_at and a composite index on (created_at, customer_id) without amount, but only once the table has been vacuumed. I measured all three, plus the un-indexed baseline, on a real 5,000,000-row table, and the result includes a case where the "better" index made the planner pick a WORSE plan until one more condition was met, which is the real answer to "mention cases where the index won't help."

What each index shape actually buys you

Worked example

CREATE TABLE agg_orders (order_id bigint PRIMARY KEY, customer_id int NOT NULL,
                          created_at timestamp NOT NULL, amount numeric(10,2) NOT NULL);
-- seeded: 5,000,000 rows, created_at spread over 2020-01-01 .. ~2026-01-01;
-- created_at >= '2025-01-01' matches 830,666 of the 5,000,000 rows (16.6%)

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(amount) FROM agg_orders WHERE created_at >= '2025-01-01' GROUP BY customer_id;

Real results (Finalize HashAggregate total cost and actual execution time; per-worker duplicate lines trimmed):

• No index: Parallel Seq Scan, cost ..70366.45, 140 ms.
• (created_at) alone: Bitmap Heap Scan via idx_created_at, cost ..63802.60. Cost estimate improved, but actual time got WORSE (203 ms), because the 830,666 matching rows are scattered across the table and the bitmap heap scan now does random-order heap access that a sequential scan avoided entirely.
• (created_at, customer_id), no amount: cost ..67676.33, still a Bitmap Heap Scan, still fetches the heap for amount on every row. Barely different from the single-column case; the extra key column bought nothing for this query.
• (created_at, customer_id) INCLUDE (amount), right after CREATE INDEX: the planner picked Parallel Seq Scan again, cost ..70305.77, almost identical to having no index at all. This is the case worth naming explicitly: a covering index freshly built on a bulk-loaded table has no guarantee its pages are marked "all visible" yet (Postgres tracks this in a visibility map, only updated by VACUUM), so an index-only scan would still have to check the heap for row visibility on every entry, and the planner correctly judges that isn't worth it.
• Same covering index, after VACUUM agg_orders: the planner NATURALLY switches to Parallel Index Only Scan using idx_covering, Heap Fetches: 0, cost 33432.25..33680.41, actual time 103 ms. That's roughly half the planner-estimated cost of the baseline and the only plan that touches zero heap pages for the matching rows.

At much larger scale (billions of rows, not millions)

The index choices above hold, but past roughly a billion rows a few more levers matter alongside indexing. Range-partitioning the table by created_at (splitting one physical table into several smaller ones by date range) lets a date-filtered query skip entire partitions outright (partition pruning) before an index inside any single partition even comes into play, which is a coarser but often cheaper first cut than asking one giant index to do all the work alone; pairing the covering index with partitioning, rather than choosing between them, is usually the right call. If this same GROUP BY, or a Top-N variant of it (ORDER BY SUM(amount) DESC LIMIT 20), runs often enough against data that doesn't change after the fact, a materialized view that pre-aggregates the sums (refreshed on the same schedule the source data loads on) can turn a billion-row scan into a lookup against a few thousand pre-computed rows; that's a workload-shaping decision that sits alongside indexing, not a replacement for it, and it trades query speed for staleness between refreshes. Where the aggregate is an approximate top-K or a distinct count (COUNT(DISTINCT customer_id) over a huge table, for instance) rather than an exact sum, an approximate structure like HyperLogLog (a probabilistic data structure that estimates a distinct count using a small, fixed amount of memory instead of storing every distinct value) is worth considering once exactness stops being worth the scan cost it demands; Postgres doesn't ship this natively, but extensions provide it. And for a wide, low-cardinality rollup column like a daily count broken out by event type, a columnar storage layout (values from the same column stored together, so scanning one column ignores the others entirely and compresses far better, versus Postgres's default row-oriented storage where every column of a row sits together on disk) can beat any row-store index for a pure aggregate-and-report workload; that's usually a separate analytical store or extension, not a rebuild of the OLTP (online transaction processing) table itself.

Validating that any of this actually helped, on a high-write table specifically

Before and after any index or partitioning change on a table this size, the validation loop is the same one used to justify the change in the first place: capture EXPLAIN (ANALYZE, BUFFERS) for the representative query before the change, apply the change, capture it again, and compare buffers, row counts, and plan shape, not just the wall-clock number: those reflect actual work done and are far less sensitive to whatever else happens to be running on the machine at the time than a raw millisecond figure is. On a high-write table (heavy inserts alongside the reporting reads), also weigh each candidate structure's write-side maintenance cost against its read-side benefit explicitly: a composite or covering index adds a write-time cost on every insert; a materialized view adds a refresh-time cost instead; partitioning adds close to nothing per write but real operational cost in managing partition boundaries. Pick the option whose maintenance cost profile actually matches how this specific table is written to, not just how it's read.

Trade-offs and pitfalls, including where the index won't help

The covering index only pays off after VACUUM has populated the visibility map; a freshly loaded or very heavily updated table without a recent VACUUM gets none of this benefit even with the ideal index in place, so an index-only-scan strategy needs autovacuum tuned to keep up with the table's write rate, not just the right index definition. Selectivity is the other limit: at 16.6% of the table matching, this is already close to the point where a sequential scan is competitive; push the filter to match 30 to 40% of rows and no index, covering or not, will beat a sequential scan, because randomly visiting that many scattered pages costs more than reading the table straight through. And a composite index that adds columns purely for "just in case" coverage, without checking whether those columns are actually part of the query's SELECT list or a further filter, adds storage and write cost for no read benefit, which is exactly what happened here with plain (created_at, customer_id): the extra column in the key did nothing until amount was added as an INCLUDE column too.