Why are my PostgreSQL queries slower in production than locally?

There are a lot of common reasons PostgreSQL queries feel fast locally but slow in production. Usually it’s not “Postgres is slower,” but “the production environment is different in ways that matter.”

Here are the main causes to check:

1. Different data size and distribution

Locally you often have:

• much smaller tables
• fewer indexes needed to scan
• more of the working set fitting in memory

In production:

• tables are larger
• indexes are larger
• data is less uniform
• queries may hit many more rows

A query that’s instant on 10k rows can be slow on 10M rows.

2. Different query plans

The same SQL can use a different execution plan in production because:

• statistics differ
• row counts differ
• parameter values differ
• work_mem, random_page_cost, effective_cache_size, etc. differ

A plan that uses an index locally might choose a sequential scan or a worse join strategy in production.

Check:

EXPLAIN (ANALYZE, BUFFERS) your_query;

Compare local vs production.

3. Missing or stale statistics

If autovacuum/analyze isn’t keeping up, the planner may make bad choices.

Check:

• when the table was last analyzed
• whether autovacuum is working
• if row estimates in EXPLAIN are far from actual rows

Useful:

SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

4. Disk and I/O are slower in production

Local development often runs on:

• SSD
• low contention
• dedicated machine

Production may have:

• shared disks
• network-attached storage
• noisy neighbors
• slower IOPS

If the query is doing lots of reads or writes, storage latency can dominate.

Clue in EXPLAIN: lots of shared read vs shared hit.

5. Cache warm vs cache cold

Locally, the same query may run after your OS/Postgres cache has already loaded needed pages. In production:

• the server may be under memory pressure
• queries may run on cold cache
• restarts or failovers flush caches

First run slow, later runs faster = cache effect.

6. Locking and contention

In production, the query may not actually be “executing slowly” — it may be waiting.

Possible waits:

• row locks
• table locks
• connection pool saturation
• CPU contention
• WAL/commit pressure

Check active waits:

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE state <> 'idle';

7. Too many concurrent connections

A common production issue is many app instances opening too many DB connections.

Effects:

• context switching overhead
• contention on shared resources
• higher latency
• memory pressure

If you’re not using pooling, consider pgbouncer or lowering connection counts.

8. Different configuration settings

Production and local may differ in settings like:

• shared_buffers
• work_mem
• maintenance_work_mem
• effective_cache_size
• max_connections
• synchronous_commit
• checkpoint_timeout
• random_page_cost

A too-small work_mem can cause sorts/hashes to spill to disk. A too-low effective_cache_size can discourage index usage.

9. Bad indexes or missing indexes

Local tests might be on small enough data that missing indexes don’t hurt much.

In production, you may need:

• composite indexes
• partial indexes
• covering indexes
• different index order

Also, an index can exist but still not be used if the query shape prevents it.

10. Query shape changed in production

Sometimes production differs because:

• different parameters are passed
• there are many more nulls or duplicates
• locale/collation differs
• functions/casts prevent index use
• ORM generates different SQL

Look closely at the exact SQL and bound parameters.

11. Long transactions and vacuum issues

If there are long-running transactions:

• autovacuum can’t clean dead tuples effectively
• tables bloat
• indexes bloat
• queries slow down over time

Check for bloat and long transactions:

SELECT pid, now() - xact_start AS tx_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY tx_age DESC;

12. Network latency

If the DB is remote from the app:

• multiple round trips add up
• chatty ORM behavior is expensive
• fetching many rows can be slow

A single query may be fine, but many small queries can feel very slow in production.

Best way to diagnose

1. Run EXPLAIN (ANALYZE, BUFFERS) in both environments.
2. Compare:
   • execution plan
   • estimated vs actual rows
   • buffer hits vs reads
   • sorts/hashes spilling to disk
3. Check for waits/locks in production.
4. Verify config differences.
5. Compare table sizes, index sizes, and stats.

Quick checklist

• Is production data much bigger?
• Are statistics current?
• Is the query waiting on locks?
• Are there enough indexes?
• Is disk I/O slower?
• Is memory/work_mem too low?
• Are too many connections open?
• Is the app issuing many queries instead of one?

If you want, paste:

• the query
• EXPLAIN (ANALYZE, BUFFERS) from local and prod
• table sizes/indexes
• relevant Postgres settings

and I can help pinpoint the likely cause.