Database Indexing vs Caching: Measure First, Then Decide

A Redis cache in front of an indexed 1 ms Postgres query was slower than no cache at every hit rate up to 37%, and only tied at 80%. A cache did pay off on a 100 ms aggregate. I ran both fixes, an index and a cache, against a seeded 2-million-row table, and show the EXPLAIN output and timings I got. The post ends with a rule for choosing index, cache, or both.

Setup: Docker, versions, and limits

This is a single-node experiment: Postgres and Redis in Docker on a Windows 11 laptop (Docker Desktop, WSL2), with one Python client over published localhost ports and no concurrency. Absolute milliseconds will differ on your hardware; the plan shapes and ratios are the useful part. Because there is no real network between app and Redis, the cache numbers are a best case: a remote Redis adds a round trip to every hit and every miss.

docker run -d --name bl-pg -e POSTGRES_PASSWORD=pw -p 55432:5432 postgres:18
docker run -d --name bl-redis -p 56379:6379 redis:8
pip install "psycopg[binary]" redis

Versions, queried from the running containers on 2026-10-07:

$ docker exec bl-pg psql -U postgres -Atc "SELECT version()"
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

$ docker exec bl-redis redis-cli INFO server | grep redis_version
redis_version:8.10.2

Every plan was run with EXPLAIN (ANALYZE, BUFFERS, COSTS OFF). I ran each three times and show the third, because the first run includes cold reads (the read= counts in Buffers). I trimmed the planning lines.

The table and the query

An orders table: 2,000,000 rows, 50,000 customers, 730 days of created_at. About 1% of rows are pending, 2% refunded, the rest paid. The note column is 100 bytes of padding so rows aren't unrealistically thin. Save this as seed.sql and run it with docker exec -i bl-pg psql -U postgres -X -q < seed.sql. The setseed call makes random() repeatable, so you should get the same row counts I quote (timestamps are relative to now(), so date-dependent counts like the 30-day rollup can shift slightly):

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id int NOT NULL,
  status text NOT NULL,
  total_cents int NOT NULL,
  created_at timestamptz NOT NULL,
  note text
);
SELECT setseed(0.42);
INSERT INTO orders (customer_id,status,total_cents,created_at,note)
SELECT (random()*50000)::int,
  CASE WHEN r  Sort (actual time=167.200..167.203 rows=10.00 loops=3)
               Sort Key: created_at DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Parallel Seq Scan on orders (actual time=14.258..167.015 rows=10.33 loops=3)
                     Filter: ((customer_id = 4242) AND (status = 'paid'::text))
                     Rows Removed by Filter: 666656
                     Buffers: shared hit=13015 read=27802
 Execution Time: 189.134 ms

The primary key doesn't help, so Postgres reads the whole table (about 40,900 buffers) across three processes to find 31 matching rows. Note read=27802: even on the third run most pages came from the OS, not Postgres's cache. This is the case where indexing is the answer and caching is a bandage.

Postgres composite, covering and partial indexes: EXPLAIN ANALYZE before and after

Single-column index, then composite

CREATE INDEX orders_cust ON orders(customer_id);

Runs: 1.696, 0.429, 0.329 ms. The plan is a Bitmap Heap Scan (32 heap blocks, Buffers: shared hit=38) followed by a Sort node: 189 ms to 0.33 ms, but Postgres still fetches every row for the customer, filters on status and sorts, so the cost grows with the customer's order history, not with your LIMIT. Equality columns first, then the sort column in the query's direction, fixes that:

CREATE INDEX orders_cust_status_created
  ON orders(customer_id, status, created_at DESC);
DROP INDEX orders_cust;

Runs: 0.157, 0.169, 0.140 ms.

 Limit (actual time=0.036..0.108 rows=20.00 loops=1)
   Buffers: shared hit=23
   ->  Index Scan using orders_cust_status_created on orders (actual time=0.035..0.104 rows=20.00 loops=1)
         Index Cond: ((customer_id = 4242) AND (status = 'paid'::text))
         Index Searches: 1
         Buffers: shared hit=23
 Execution Time: 0.140 ms

No sort node, no filter, and it stops after 20 rows because the index returns them already in created_at DESC order.

Covering index: only the full column list gets an index-only scan

An INCLUDE that lists only some of the selected columns still visits the heap. The query selects id, created_at and total_cents, and the index-only scan rules require every referenced column to be in the index:

DROP INDEX orders_cust_status_created;
CREATE INDEX orders_cover ON orders(customer_id, status, created_at DESC)
  INCLUDE (id, total_cents);
VACUUM orders;

 Limit (actual time=0.091..0.122 rows=20.00 loops=1)
   Buffers: shared hit=11
   ->  Index Only Scan using orders_cover on orders (actual time=0.089..0.116 rows=20.00 loops=1)
         Index Cond: ((customer_id = 4242) AND (status = 'paid'::text))
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=11
 Execution Time: 0.171 ms

Heap Fetches: 0 and 11 buffers instead of 23. Single runs this small are noise, so I ran each of three index variants 10 times after two warm-ups (the middle row is the same CREATE INDEX orders_cover with INCLUDE (total_cents) only):

The ranges overlap heavily, and the first two run the same plan on the same 23 buffers, so the gap between them is noise. Only the index-only scan is lower, by about 0.1 ms at the median. Index-only scans depend on the visibility map, so a table with heavy updates and no recent vacuum will show nonzero heap fetches and lose the benefit. INCLUDE columns also copy table data into the index. \di+ after the partial index below shows the cost:

 Name           | Access method |  Size
----------------+---------------+--------
 orders_cover   | btree         | 112 MB
 orders_pending | btree         | 632 kB
 orders_pkey    | btree         | 43 MB

The covering index is 112 MB against a 319 MB heap, for about 0.1 ms on a query already well under a millisecond. I'd take the plain composite unless this query is hot enough that the heap visit matters.

Partial index for a small slice

An ops screen lists the oldest pending orders. Only 1% of rows qualify, and the composite above can't serve it because status isn't its leading column:

SELECT id, customer_id, created_at FROM orders
WHERE status = 'pending' ORDER BY created_at LIMIT 50;

Without a suitable index: Parallel Seq Scan, runs of 255, 203, 207 ms. Then:

CREATE INDEX orders_pending ON orders(created_at) INCLUDE (customer_id)
  WHERE status = 'pending';
VACUUM orders;

Runs: 0.828, 0.625, 0.649 ms.

 Limit (actual time=0.058..0.594 rows=50.