Stop Adding Postgres Indexes (The Case for Brute-Force Parallel Scans on Modern NVMe)

Imagine a 500GB events table with fifteen different B-Tree indexes. Every time your application performs a simple INSERT, Postgres has to update fifteen different data structures scattered across your disk. Your write latency is climbing, your autovacuum is gasping for air, and your storage costs are ballooning. You’ve been told that "indexes make queries fast," but on modern hardware, that's a half-truth that might be slowing you down.

The Index Tax Nobody Talks About

We’ve been conditioned to think that a sequential scan (the dreaded Seq Scan in EXPLAIN ANALYZE) is a failure. In the era of spinning platters (HDDs), it was. Seeking a needle in a haystack meant moving a physical metal arm, which took forever.

But your production database probably runs on NVMe SSDs now. These drives don't care about "seeking." They care about throughput. An NVMe drive can read several gigabytes per second without breaking a sweat. When you combine that with Postgres's ability to split a single query across multiple CPU cores, the "brute-force" approach becomes surprisingly viable.

Every index you add is a tax:
1. Write Penalty: Every INSERT, UPDATE, and DELETE becomes slower.
2. Storage Bloat: I've seen databases where indexes take up 3x more space than the actual data.
3. Memory Pressure: Indexes need to live in shared_buffers or the OS cache to be effective. If you have 50 indexes, you're kicking actual data out of memory to make room for metadata.

The Power of the Parallel Sequential Scan

Postgres 9.6 introduced parallel query execution, and it has only gotten better. Instead of one CPU core slowly picking through a table, Postgres can recruit a small army of workers to scan the heap in parallel.

If you have a table that is frequently updated but also queried for broad analytics, sometimes it's better to drop the index and let the CPUs do the heavy lifting.

First, check your settings. Most default Postgres configs are way too conservative for modern high-core machines:

-- Let's see what we're working with
SHOW max_parallel_workers_per_gather; 
-- Usually 2. On a 16-core machine, this is a crime.

-- Bump these up (adjust based on your actual CPU cores)
SET max_parallel_workers_per_gather = 4;
SET max_parallel_workers = 8;
SET min_parallel_table_scan_size = '8MB';

A Real-World Comparison

Let’s look at a table with 10 million rows. We want to find the total revenue for a specific category where we only query this data once an hour for a dashboard.

The Index Approach:
You add an index on category_id.
- Index Size: ~220MB.
- Maintenance cost: Every single sale slows down by a few milliseconds.
- Query time: 5ms.

The Brute-Force Parallel Approach:
No index. We just let Postgres rip through the raw data.

EXPLAIN (ANALYZE, BUFFERS)
SELECT SUM(price) 
FROM sales 
WHERE category_id = 42;

On an NVMe-backed instance with 4 parallel workers, the output looks like this:

Finalize Aggregate  (cost=1245.00..1245.01 rows=1 width=8)
  ->  Gather  (cost=1244.00..1245.00 rows=4 workers=4)
        Workers Planned: 4
        ->  Partial Aggregate  (cost=1243.00..1243.01 rows=1 width=8)
              ->  Parallel Seq Scan on sales (cost=0.00..1100.00 rows=250000 width=4)
                    Filter: (category_id = 42)

The query takes 150ms.

Now, ask yourself: Does your dashboard care about the difference between 5ms and 150ms? Probably not. But your ingestion pipeline definitely cares about the 20% speedup it gets by not maintaining that index.

When to Go "Index-Less"

I’m not suggesting you drop your primary keys or your unique constraints. Please, keep those. But for the "maybe we'll need to filter by this" columns, consider these factors:

1. Data Volatility: If the table is hit with 1,000 writes per second, every index is a massive liability.
2. Selectivity: If your query returns more than 5-10% of the table, Postgres will likely ignore your index and perform a sequential scan anyway.
3. Modern IO: If you are on an AWS io2 block store or a local NVMe, your bottleneck is almost certainly CPU, not disk I/O. Parallel scans maximize CPU usage.

Tuning the "Brute Force"

If you decide to lean into parallel scans, you need to make sure Postgres doesn't get "lazy." You can actually hint to the optimizer that sequential scans are cheaper than it thinks on your fast storage:

-- Default is 1.0. If you have insanely fast NVMe, 
-- lowering this makes Postgres more likely to choose parallel scans.
SET seq_page_cost = 0.1;

-- Default is 4.0. This represents the cost of a random disk seek.
SET random_page_cost = 1.1; 

By bringing random_page_cost and seq_page_cost closer together, you are telling Postgres: "Hey, our disk is so fast that we don't need to be terrified of reading the whole table."

The "Gotchas"

Before you go deleting all your indexes, remember that parallel scans are heavy.
- They consume significant CPU. If your database is already at 80% CPU usage, forcing parallel scans will push it over the edge.
- They don't scale to thousands of concurrent users. If you have 500 web requests per second hitting the same unindexed column, you need an index.

But for background jobs, reporting, and internal tools? Stop hoarding indexes. Give your CPUs something to do and let your NVMe drives scream. Your write latency will thank you.