PostgreSQL Hidden Features That Cut Our Query Time by 73%: A Production Deep Dive

Last year, our team hit a wall. We'd scaled our SaaS platform to about 50 million database queries per day, and our PostgreSQL instance was starting to buckle. Average query response time had crept up to 2.1 seconds for our most critical endpoints. Our CTO, Maria, gave us two weeks to fix it before we'd need to start sharding—a complexity nightmare we wanted to avoid.

I spent those two weeks diving deep into PostgreSQL's documentation, not the getting-started guides, but the actual internals docs that most developers never read. What I found changed everything. PostgreSQL has dozens of features that can dramatically improve performance, but they're either poorly documented, hidden in advanced sections, or just not widely known.

After implementing what I learned, we cut our average query time to 580ms—a 73% improvement. We handled our traffic spike during a major product launch without adding a single database server. More importantly, I learned that PostgreSQL's "hidden" features aren't actually hidden—they're just buried under layers of conventional wisdom and cargo-cult optimization.

Here's what I discovered, complete with the benchmarks, gotchas, and production lessons that the documentation glosses over.

The Partial Index Revelation That Saved Us $40k/Month

I'll be honest: I thought I understood indexes. I'd been using B-tree indexes for years, throwing them on foreign keys and frequently queried columns like everyone else. But I was completely wrong about how to use them effectively at scale.

Our biggest performance problem was a user_events table with about 180 million rows. We tracked every user action—clicks, page views, API calls, everything. The table looked roughly like this:

CREATE TABLE user_events (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_data JSONB,
    is_processed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    processed_at TIMESTAMP
);

CREATE INDEX idx_user_events_user_id ON user_events(user_id);
CREATE INDEX idx_user_events_created_at ON user_events(created_at);

Our analytics dashboard needed to query unprocessed events constantly:

SELECT * FROM user_events 
WHERE is_processed = false 
ORDER BY created_at DESC 
LIMIT 100;

This query was taking 1.8 seconds on average. The EXPLAIN output showed it was scanning millions of rows:

Limit  (cost=1847291.23..1847291.48 rows=100 width=584) (actual time=1823.445..1823.467 rows=100 loops=1)
  ->  Sort  (cost=1847291.23..1892456.89 rows=18066264 width=584) (actual time=1823.443..1823.453 rows=100 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 95kB
        ->  Seq Scan on user_events  (cost=0.00..1345678.90 rows=18066264 width=584) (actual time=0.034..1456.789 rows=18234567 loops=1)
              Filter: (NOT is_processed)
              Rows Removed by Filter: 161765433
Planning Time: 0.234 ms
Execution Time: 1823.512 ms

See that? It was doing a sequential scan of the entire table, filtering out 161 million processed rows to find 18 million unprocessed ones. My index on is_processed wasn't being used because PostgreSQL's query planner knew that nearly 90% of rows matched the condition—making the index less efficient than a sequential scan.

Then my colleague Jake mentioned partial indexes. I'd seen them in the docs but never understood when to use them. Here's what changed everything:

CREATE INDEX idx_user_events_unprocessed ON user_events(created_at) 
WHERE is_processed = false;

This index only includes rows where is_processed is false. Instead of indexing all 180 million rows, it only indexed the 18 million unprocessed ones. The size difference was massive:

SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE tablename = 'user_events';

Results:

| indexname                        | index_size |
|----------------------------------|------------|
| idx_user_events_user_id          | 3845 MB    |
| idx_user_events_created_at       | 3821 MB    |
| idx_user_events_unprocessed      | 387 MB     |

The partial index was 10x smaller. But the real win was query performance:

Limit  (cost=0.43..12.89 rows=100 width=584) (actual time=0.034..0.156 rows=100 loops=1)
  ->  Index Scan Backward using idx_user_events_unprocessed on user_events  (cost=0.43..2267834.56 rows=18234567 width=584) (actual time=0.033..0.145 rows=100 loops=1)
Planning Time: 0.123 ms
Execution Time: 0.189 ms

From 1823ms to 0.189ms. That's a 9,647x improvement. No, that's not a typo.

But here's the gotcha that bit us in production: partial indexes only work when your query's WHERE clause exactly matches the index condition. This query still did a sequential scan:

-- This DOESN'T use the partial index
SELECT * FROM user_events 
WHERE is_processed = false AND user_id = 12345
ORDER BY created_at DESC;

Why? Because the partial index is on created_at WHERE is_processed = false, but the query adds an additional user_id filter. PostgreSQL can't use the partial index efficiently here. We needed a composite partial index:

CREATE INDEX idx_user_events_unprocessed_by_user ON user_events(user_id, created_at) 
WHERE is_processed = false;

After implementing partial indexes across our schema, we reduced our index storage from 47GB to 23GB and cut query times by an average of 68% for filtered queries. Our AWS RDS instance dropped from db.r5.4xlarge to db.r5.2xlarge, saving us about $3,400/month. Over a year, that's $40k just from understanding partial indexes.

BRIN Indexes: The Time-Series Secret Weapon Nobody Talks About

Here's something that surprised me: B-tree indexes aren't always the answer for time-series data. I learned this the hard way when our metrics table hit 500 million rows.

We were collecting system metrics every 30 seconds from about 5,000 servers:

CREATE TABLE metrics (
    id BIGSERIAL PRIMARY KEY,
    server_id INTEGER NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    metric_value NUMERIC(10,2),
    collected_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_metrics_collected_at ON metrics(collected_at);
CREATE INDEX idx_metrics_server_id ON metrics(server_id);

The idx_metrics_collected_at index was 12GB. For a table with 500 million rows, that's reasonable, but it was growing by about 400MB per month. Our queries were fast enough—usually 100-300ms for time-range queries—but the index maintenance was killing us during bulk inserts.

We batch-inserted metrics every minute (about 5,000 rows per batch). Each insert was taking 2-3 seconds, and I could see in the query logs that most of that time was index updates:

LOG:  duration: 2847.234 ms  statement: INSERT INTO metrics (server_id, metric_name, metric_value, collected_at) VALUES ...

Then I discovered BRIN indexes while reading a PostgreSQL 9.5 release note (yes, from 2016—I was late to the party). BRIN stands for Block Range Index, and it's designed for tables where values have a natural physical ordering.

Here's the key insight: in our metrics table, newer rows always had later collected_at timestamps because we only inserted new data, never updated old rows. PostgreSQL stores rows in pages (8KB blocks), and in our table, rows in page 1 had timestamps from January, page 2 from February, etc. A BRIN index takes advantage of this by storing summary information about ranges of pages instead of indexing every single row.

I replaced our B-tree index with a BRIN index:

DROP INDEX idx_metrics_collected_at;
CREATE INDEX idx_metrics_collected_at_brin ON metrics USING BRIN(collected_at) WITH (pages_per_range = 128);

The size difference was shocking:

SELECT pg_size_pretty(pg_relation_size('idx_metrics_collected_at_brin'));

Result: 24 MB

We went from a 12GB index to a 24MB index—a 500x reduction. But did queries get slower? I was worried they would. Here's what I found:

Query 1: Recent data (last 24 hours)

SELECT server_id, AVG(metric_value) 
FROM metrics 
WHERE collected_at > NOW() - INTERVAL '24 hours' 
GROUP BY server_id;

B-tree index: 287ms
BRIN index: 312ms

Slightly slower, but acceptable.

Query 2: Historical data (30 days ago)

SELECT server_id, AVG(metric_value) 
FROM metrics 
WHERE collected_at BETWEEN '2024-01-01' AND '2024-01-31' 
GROUP BY server_id;

B-tree index: 423ms
BRIN index: 389ms

Actually faster! This surprised me until I realized that BRIN indexes have less overhead to traverse.

The real win: Insert performance

-- Batch insert 5,000 rows
INSERT INTO metrics (server_id, metric_name, metric_value, collected_at)
SELECT ...

B-tree index: 2847ms
BRIN index: 234ms

A 12x improvement in insert speed. Our batch processing pipeline went from taking 3-4 minutes per batch to about 20 seconds.

But here's the critical gotcha: BRIN indexes only work well when your data has physical correlation with the indexed column. If you're inserting data out of order, BRIN indexes will perform terribly. We learned this when we tried to backfill historical data:

-- Backfilling data from 6 months ago
INSERT INTO metrics (server_id, metric_name, metric_value, collected_at)
VALUES (1234, 'cpu_usage', 45.2, '2023-08-15 10:30:00');

This insert went into a page with recent data, breaking the physical ordering. Our BRIN index started returning incomplete results until we ran:

REINDEX INDEX idx_metrics_collected_at_brin;

The reindex took 45 minutes on our 500M row table. Not fun.

My recommendation: Use BRIN indexes for:

• Append-only tables (logs, metrics, events)
• Tables with natural ordering (timestamps, auto-incrementing IDs)
• Very large tables (100M+ rows) where index size matters
• High-volume inserts where index maintenance is a bottleneck

Don't use BRIN indexes for:

• Tables with random updates
• Tables where you frequently delete old data
• Small tables (under 10M rows—the space savings don't matter)
• Queries that need exact lookups (BRIN does range scans)

After implementing BRIN indexes on our time-series tables, we reduced total index size from 89GB to 12GB and improved our insert throughput by 8x. The slight query performance trade-off (10-15% slower on some queries) was absolutely worth it.

Expression Indexes: When Your WHERE Clause Is Lying to You

I spent two days debugging a query that should have been fast but wasn't. The query looked innocent:

SELECT * FROM users 
WHERE LOWER(email) = 'john.doe@example.com' 
LIMIT 1;

We had an index on the email column:

CREATE INDEX idx_users_email ON users(email);

But the query was doing a sequential scan:

Seq Scan on users  (cost=0.00..45678.90 rows=1 width=584) (actual time=234.567..456.