7 PostgreSQL Performance Tricks I Wish I Knew Earlier

import Post from ’../../layouts/Post.astro’; export const prerender = true;

I’ve been burned by slow PostgreSQL queries more times than I’d like to admit. Every time I thought “the database is the bottleneck,” it turned out I was just writing lazy queries and missing obvious indexes. Here are the 7 tricks that made the biggest difference in my production apps.

1. Stop Using SELECT * — You’re Reading Data You Don’t Need

This should be obvious, but I still see it everywhere. SELECT * forces PostgreSQL to read the entire row, including columns you don’t need.

-- Slow: reads all columns
SELECT * FROM orders WHERE user_id = 123;

-- Faster: reads only what you need
SELECT id, total, created_at FROM orders WHERE user_id = 123;

For wide tables with TEXT or JSONB columns, this difference is massive. One app I worked on dropped query time from 800ms to 40ms just by listing explicit columns.

2. Index the Right Columns — And Order Matters

Creating an index on user_id is obvious. What’s not obvious: the column order in composite indexes matters.

-- This index helps for filtered-by-status queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- This one helps for sorting after filtering
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

If your query filters on user_id = X AND status = 'pending', put user_id first. If you filter on status then sort by date, put status first. Match your index to your query pattern.

Also — use INCLUDE columns to make your index covering:

-- The index covers the entire query — no heap lookup needed
CREATE INDEX idx_orders_covering
ON orders(user_id)
INCLUDE (status, total, created_at);

3. EXPLAIN ANALYZE Is Your Best Friend

I know, I know — it looks intimidating. But once you learn to read it, you’ll spot performance issues instantly.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.region = 'EU'
AND o.created_at > NOW() - INTERVAL '30 days';

Look for these red flags:

• Seq Scan on large tables → needs an index
• High actual rows vs estimated rows → stale statistics, run ANALYZE
• Nested Loop on large datasets → consider a hash join
• High shared hit/read ratio → data isn’t in cache

The BUFFERS option tells you how much data came from cache vs disk. Disk reads are 100x slower. If you’re reading from disk constantly, you need more RAM or better indexes.

4. Use Connection Pooling — Or PgBouncer Will Kill You

Every PostgreSQL connection is a separate OS process. Spawning 100 connections means 100 processes fighting for CPU. With connection pooling, you reuse connections:

# docker-compose.yml with PgBouncer
services:
  api:
    environment:
      DATABASE_URL: postgresql://app:secret@pgbouncer:5432/myapp

  pgbouncer:
    image: edoburu/pgbouncer
    environment:
      DATABASE_URL: postgresql://postgres:secret@postgres:5432/myapp
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 500
      DEFAULT_POOL_SIZE: 20

Transaction-mode pooling is usually the right choice for web APIs — connections live only during transactions.

Rule of thumb: MAX_CLIENT_CONN = 10x your DEFAULT_POOL_SIZE.

5. Partial Indexes — Index Just What You Need

If you frequently query a subset of rows, index only that subset:

-- Only index pending orders — much smaller, faster
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- This query uses the partial index automatically
SELECT * FROM orders
WHERE status = 'pending'
AND created_at > NOW() - INTERVAL '7 days';

Partial indexes are dramatically smaller than full indexes. For a table with millions of rows where 2% are “pending,” your partial index might be 50x smaller and fit entirely in memory.

6. Don’t Ignore VACUUM — It’s Not Just Cleanup

PostgreSQL’s MVCC model means old rows aren’t deleted immediately — they’re marked as dead tuples. VACUUM cleans these up. In Postgres 16+, autovacuum handles most of this, but:

-- Check table bloat
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

If dead_pct > 20%, run VACUUM ANALYZE tablename. For heavily updated tables, consider tuning autovacuum:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vacuum sooner (default 0.2 = 20%)
  autovacuum_analyze_scale_factor = 0.01
);

7. Use pg_stat_statements — Find Your Actual Slow Queries

pg_stat_statements tracks query performance across all connections. Enable it first:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find your slowest queries by total time
SELECT query,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

This tells you not just “slow queries” but which queries run most often and are slowest on average. A query that takes 500ms but runs 1000 times an hour is more impactful than a 5-second query that runs once a day.

---

The Bottom Line

Most PostgreSQL performance problems aren’t the database being slow — they’re queries that don’t match your indexes, missing indexes on foreign keys, or connection overhead. Start with EXPLAIN ANALYZE and pg_stat_statements. They’ll show you exactly where time is going.

What’s your slowest query horror story? Drop it in the comments — I’ve seen some real beauties.

---