I spent this week actually benchmarking PostgreSQL queries on a 500,000-row table instead of just reading about it. Here is what I found, with real EXPLAIN ANALYZE output at every step.
Some of it confirmed what I expected. Some of it genuinely surprised me.
The Setup
I built a URL monitoring table that a scheduler queries constantly to find URLs that haven't been checked in the last hour.
CREATE TABLE urls (
id SERIAL PRIMARY KEY,
url TEXT NOT NULL,
status_code INT,
last_checked_at TIMESTAMPTZ,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO urls (url, status_code, last_checked_at, is_active)
SELECT
'https://example.com/page/' || generate_series(1, 500000),
CASE WHEN random() < 0.9 THEN 200 ELSE 500 END,
NOW() - (random() * INTERVAL '30 days'),
CASE WHEN random() < 0.85 THEN true ELSE false END
FROM generate_series(1, 500000);
The query I needed to optimize:
SELECT id, url
FROM urls
WHERE last_checked_at < NOW() - INTERVAL '1 hour'
AND is_active = true
ORDER BY last_checked_at ASC
LIMIT 100;
Full lab code and seed scripts:
Afshan738
/
postgres-scheduler-benchmark
Production-grade benchmark comparing PostgreSQL scheduler patterns: 845ms → 3ms using partial indexes, advisory locks, and SKIP LOCKED. 500k URL simulation with real production patterns.
PostgreSQL Scheduler Benchmark
Production-grade benchmark comparing PostgreSQL scheduler patterns. From 121ms to 0.141ms using indexes and LIMIT. 500k URL simulation with real production patterns.
Quick Start
git clone https://github.com/Afshan738/postgres-scheduler-benchmark
cd postgres-scheduler-benchmark
docker-compose up -d
docker-compose exec postgres psql -U benchmark -d scheduler_benchmark
\i lab/01_baseline.sql
What You Will Learn
Why sequential scans are slow on large tables
How indexes make queries faster
Why LIMIT is important for scheduler queries
How partial indexes save disk space
How PostgreSQL caching affects performance
When not to use an index
Lab Files
File What it shows
lab/01_baseline.sql Run query without index
lab/02_create_index.sql Add B-tree index
lab/03_after_index.sql See the improvement
lab/04_partial_index.sql Create smaller index
lab/05_size_comparison.sql Compare index sizes
lab/06_drop_index.sql Remove index to test
lab/07_disable_indexscan.sql Force different scan method
lab/08_final_query.sql Production ready query
Three Ways to Use This Lab
Option 1: Step by Step (Recommended)
Follow the instructions in docs/instructions.md. Run each query manually and observe the…
Finding 1: The Baseline Was Worse Than I Expected
Before adding any index, I ran EXPLAIN ANALYZE on the query. The result caught me off guard.
PostgreSQL did not just do a regular sequential scan. It launched a parallel sequential scan with 2 workers. The database was working as hard as it could without an index, splitting the table scan across multiple CPU cores.
And it still took 356.667 ms.
Two things to notice in that output. First, PostgreSQL chose to parallelize automatically because the table was large enough to warrant it. Second, even with 2 workers helping, it still had to read through the entire table and throw away 25,142 rows that did not match the filter. That is a lot of wasted work.
Finding 2: One Index, 1,524x Faster
CREATE INDEX idx_last_checked ON urls (last_checked_at);
Same query. Same table. Same 500,000 rows.
0.234 ms.
The difference in the plan is obvious. Instead of a parallel seq scan touching every row, PostgreSQL now walks the B-tree index directly to the matching timestamp range and pulls out exactly the rows it needs. It filtered out only 19 rows instead of 25,142.
That is 356.667 ms down to 0.234 ms. A 1,524x improvement from a single CREATE INDEX statement.
The crossover point where indexes start winning is roughly 10,000 to 50,000 rows. Below that, the overhead of reading the index and then jumping to specific heap pages is often slower than just scanning the whole (small) table. PostgreSQL's planner makes this decision automatically based on table statistics.
Finding 3: LIMIT Is Not Optional
This one surprised me the most. I assumed the index was doing all the work. It turns out LIMIT matters just as much.
I ran the same query without LIMIT to see all matching rows:
270.351 ms.
Even with the index. Now with LIMIT 100:
0.595 ms.
The reason is how B-tree indexes work. The data is already stored in sorted order by last_checked_at. With LIMIT 100, PostgreSQL walks the index from the oldest timestamp forward, collects 100 rows, and stops completely. It never looks at the other 424,575 matching rows.
Without LIMIT, it has to find and return all of them. Even the fastest index cannot help you there.
For any scheduler or pagination query, always use LIMIT. Pick a batch size and process in waves. There is rarely a good reason to fetch 400,000 rows at once.
Finding 4: Partial Indexes Are Underused
My scheduler query always filters on is_active = true. There is no reason to index the inactive rows. A partial index lets you index only the rows that actually matter:
CREATE INDEX idx_partial_last_checked ON urls (last_checked_at)
WHERE is_active = true;
PostgreSQL switched to the partial index on its own. No query changes needed. And the filter line is gone from the plan because the index already guarantees every row in it has is_active = true.
Now compare the sizes:
11 MB for the full index. 9,344 kB for the partial one. About 15% smaller. That matters more than it sounds because indexes need to fit in memory to be fast. A smaller index means more of it stays in cache. It also means writes to inactive rows do not touch the index at all, so inserts and updates are slightly cheaper.
Finding 5: The Cache Effect Is Real and Dramatic
I ran the optimized query twice immediately back to back.
0.521 ms the first time. 0.196 ms the second time. PostgreSQL caches frequently accessed index pages and table data in shared_buffers. The first query reads from disk. Everything after that reads from memory.
What this means for benchmarking: always measure the second run, not the first. Production systems run warm. Cold cache numbers will make your optimized queries look worse than they actually perform day to day.
Finding 6: Sometimes the Sequential Scan Is the Right Answer
After all this index work, I tested what happens when the query becomes less selective. I changed the interval to catch most of the table.
At some point the EXPLAIN ANALYZE output switched back to a Seq Scan on its own. No index. The planner decided it was cheaper to read the whole table in one sequential pass than to bounce around disk following index pointers to individual rows.
Random I/O is expensive. Sequential I/O is fast. When you are fetching more than roughly 30% of a large table, PostgreSQL knows this and ignores the index on purpose.
Do not fight it. If you force an index when the planner wanted a seq scan, you will make things worse.
The Full Picture
| Stage | Time |
|---|---|
| Baseline: parallel seq scan, no index | 356.667 ms |
| After adding index on last_checked_at | 0.234 ms |
| Without LIMIT (same index) | 270.351 ms |
| With LIMIT 100 | 0.595 ms |
| Partial index, cold cache | 0.521 ms |
| Partial index, warm cache | 0.196 ms |
Result: 356.667 ms → 0.196 ms = 1,820x faster
What Actually Made the Difference
Ranked by impact:
- Adding the index on last_checked_at, eliminated the full table scan
- Using LIMIT, stopped PostgreSQL from returning 400k rows it did not need
- Partial index, smaller, slightly faster, and cleaner query plans
- Benchmarking warm, gave accurate numbers that reflect real production performance
Key Takeaways
- Run EXPLAIN ANALYZE before and after every change. Never guess.
- Always use LIMIT on scheduler and pagination queries. The index cannot help you if you are asking for everything.
- Partial indexes are underused. If your query always filters on a condition, your index should too.
- Benchmark on the second run. That is what your users actually experience.
Trust the query planner on seq scan versus index scan decisions. It is reading statistics you cannot easily see at a glance.
This is Part 1 of a series on PostgreSQL performance.
In Part 2, I am going to cover something that catches most engineers off guard.
That 0.196 ms query you just saw? Come back to it three months later on a production system with heavy writes and it will be slow again. Not because anything changed in your code. The index is still there. The query is identical.
The reason is index bloat, dead tuples accumulating inside the index itself until PostgreSQL is reading through garbage to find your data.
I will show exactly why it happens, how to detect it before it becomes an incident, and how to fix it permanently using VACUUM, REINDEX CONCURRENTLY, and table partitioning.
Follow me here on Dev.to so you catch Part 2 when it drops.
If you want to discuss this or have questions about the lab, connect with me on
LinkedIn:
https://www.linkedin.com/in/afshan-qasim-998917300/
Full lab code and seed scripts:
Afshan738
/
postgres-scheduler-benchmark
Production-grade benchmark comparing PostgreSQL scheduler patterns: 845ms → 3ms using partial indexes, advisory locks, and SKIP LOCKED. 500k URL simulation with real production patterns.
PostgreSQL Scheduler Benchmark
Production-grade benchmark comparing PostgreSQL scheduler patterns. From 121ms to 0.141ms using indexes and LIMIT. 500k URL simulation with real production patterns.
Quick Start
git clone https://github.com/Afshan738/postgres-scheduler-benchmark
cd postgres-scheduler-benchmark
docker-compose up -d
docker-compose exec postgres psql -U benchmark -d scheduler_benchmark
\i lab/01_baseline.sql
What You Will Learn
Why sequential scans are slow on large tables
How indexes make queries faster
Why LIMIT is important for scheduler queries
How partial indexes save disk space
How PostgreSQL caching affects performance
When not to use an index
Lab Files
File What it shows
lab/01_baseline.sql Run query without index
lab/02_create_index.sql Add B-tree index
lab/03_after_index.sql See the improvement
lab/04_partial_index.sql Create smaller index
lab/05_size_comparison.sql Compare index sizes
lab/06_drop_index.sql Remove index to test
lab/07_disable_indexscan.sql Force different scan method
lab/08_final_query.sql Production ready query
Three Ways to Use This Lab
Option 1: Step by Step (Recommended)
Follow the instructions in docs/instructions.md. Run each query manually and observe the…
Top comments (0)