Postgres Performance Tuning for People Who Aren't DBAs

Somewhere around my third production incident caused by a "simple" query that suddenly started taking 40 seconds, I decided I needed to actually understand what Postgres was doing instead of treating the database as a black box that occasionally betrayed me. I'm not a DBA and I never want to be one — I just want my app to be fast, and it turns out you don't need a DBA's depth of knowledge to fix 90% of the performance problems you'll actually encounter. You need to understand indexes, read an execution plan without panicking, and know what a connection pool is actually for.

This is the guide I wish someone had handed me before that incident.

Reading EXPLAIN ANALYZE without your eyes glazing over

EXPLAIN ANALYZE is the single most underused tool in most app developers' toolkits, mostly because the output looks like hieroglyphics the first time you see it. Here's a real-ish example — a query looking up orders for a customer, before any tuning:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 48213;

Seq Scan on orders  (cost=0.00..48291.00 rows=12 width=142) (actual time=0.045..312.891 rows=8 loops=1)
  Filter: (customer_id = 48213)
  Rows Removed by Filter: 2499992
Planning Time: 0.112 ms
Execution Time: 312.933 ms

The two things to actually look at, in order: the node type (Seq Scan) and the gap between estimated and actual. Seq Scan means Postgres read every single row in the table — all 2.5 million of them — to find the 8 that matched. That's your smoking gun. "Rows Removed by Filter: 2499992" is Postgres basically telling you "I did a lot of unnecessary work here," and it did that work in 312 milliseconds, which on a hot API path is the difference between a snappy response and a timeout under load.

The fix, unsurprisingly, is an index:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Re-run the same query:

Index Scan using idx_orders_customer_id on orders  (cost=0.42..8.44 rows=12 width=142) (actual time=0.028..0.041 rows=8 loops=1)
  Index Cond: (customer_id = 48213)
Planning Time: 0.098 ms
Execution Time: 0.067 ms

312ms down to 0.067ms. That's not a made-up number for dramatic effect, that's roughly the order of magnitude I've genuinely seen on tables in the low millions of rows. The lesson generalizes: any time you see Seq Scan on a table with more than a few thousand rows, in a query that filters or joins on a specific column, that column is a candidate for an index. Postgres will tell you exactly where the time is going if you just ask it.

One caveat that trips people up: the cost numbers at the start of each line are the planner's estimate before running, in arbitrary units, not milliseconds. The actual time numbers are the real ones, in milliseconds, and that's what you should trust when diagnosing a slow query.

Indexes aren't free, and more isn't always better

I went through a phase after learning this where I wanted to index everything, which is its own mistake. Every index speeds up reads on that column but slows down every INSERT, UPDATE, and DELETE that touches it, because Postgres has to maintain the index structure too. On a write-heavy table, five indexes you don't actually query against are pure overhead.

The practical rule I use: index columns that show up in WHERE, JOIN, and ORDER BY clauses on your genuinely hot queries, and periodically check for unused indexes with a query like this:

SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Anything showing zero scans after a few weeks of real production traffic is a candidate for dropping. I've found this query alone shaves surprising amounts of write latency on tables that accumulated "just in case" indexes over a couple years of different engineers adding them defensively.

Composite indexes deserve a specific mention because I got this wrong for years. If you frequently query WHERE customer_id = ? AND status = 'pending', a composite index on (customer_id, status) will usually beat two separate single-column indexes, because Postgres can satisfy the whole filter from one index lookup instead of intersecting two:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Column order in a composite index matters — put the column you filter on with an equality check first, and range/sort columns after. Get this backwards and the index is far less useful than it looks.

The N+1 problem, in the wild

If you've used an ORM — Prisma, Sequelize, ActiveRecord, SQLAlchemy, whatever — you've hit this even if nobody told you its name. You fetch a list of orders, then loop over them to fetch each customer:

const orders = await db.order.findMany({ take: 50 });
for (const order of orders) {
  const customer = await db.customer.findUnique({ where: { id: order.customerId } });
  // ...
}

That's 51 round trips to the database for what should be one or two. On localhost you won't even notice. In production, with real network latency between your app server and your database, this is where "the dashboard is slow" tickets come from. The fix is almost always to either join in a single query or batch-fetch:

const orders = await db.order.findMany({
  take: 50,
  include: { customer: true }, // Prisma does the join for you
});

Or, doing it by hand in raw SQL, which I still prefer for anything performance-critical because I want to see exactly what's being sent:

SELECT o.*, c.name AS customer_name, c.email AS customer_email
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC
LIMIT 50;

One round trip, one execution plan, and you can EXPLAIN ANALYZE the whole thing as a unit rather than debugging 51 separate queries scattered across your logs.

Connection pooling: the thing that saves you at 3am

Postgres connections are not free — each one spins up a backend process with its own memory overhead, and Postgres starts to choke well before you hit its hard connection limit (often configured around 100). If you're running a serverless or highly concurrent app, it's shockingly easy to exhaust available connections during a traffic spike, especially with frameworks that open a new connection per request without pooling.

This is what PgBouncer is for. It sits between your app and Postgres, and multiplexes many client connections onto a much smaller number of real Postgres connections. A minimal config looks like this:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

pool_mode = transaction is the setting that matters most — it releases the real Postgres connection back to the pool after each transaction completes, rather than holding it for the lifetime of the client connection. This is what lets you serve a thousand app-level "connections" off of twenty real database connections. The trade-off is that you lose session-level features like prepared statements that persist across transactions, which occasionally bites people using certain ORM features, so it's worth testing your specific stack against it before flipping it on in production.

If you're on a managed Postgres provider — Supabase, Neon, RDS with RDS Proxy — pooling is often available as a checkbox rather than something you self-host, and I'd genuinely recommend just using the managed version unless you have a specific reason not to. I spent a weekend once hand-rolling a PgBouncer setup that a managed provider would have given me in about ninety seconds, purely out of stubbornness.

The habit that matters more than any single technique

None of this is exotic knowledge, and none of it requires deep database internals expertise. What actually changed my relationship with Postgres performance wasn't learning any single trick — it was developing the habit of running EXPLAIN ANALYZE on any query that touches a table over about ten thousand rows before I ship it, not after users complain. Performance problems in Postgres are almost always diagnosable in under five minutes once you know to look, and the tool for looking is already sitting there, built into the database, waiting for you to ask it a question.