I am working on a logistics project that uses PostgreSQL as the main database. At first, everything runs smoothly. But as the data grows—with millions of transactions, complex queries, and real-time analytics—the database starts slowing down. Queries that once took milliseconds now take seconds or even minutes.
I knew I had to optimize PostgreSQL before it became a bottleneck. I went through a lot of trial and error, but over time, I found some practical performance tips that made a huge difference. In this blog, I want to share what I learned from real experience—simple but effective ways to make PostgreSQL run faster.
1. Understanding Query Performance with EXPLAIN ANALYZE
At first, I had no idea why my queries were slow. Someone told me:
“Before optimizing anything, understand what’s wrong first.”
The best way to do that in PostgreSQL is with EXPLAIN ANALYZE.
How I Used EXPLAIN ANALYZE to Debug Slow Queries
I had a query similar like this:
SELECT * FROM transactions WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10;
It was slow when the transactions table grew to millions of rows. So I ran:
EXPLAIN ANALYZE
SELECT * FROM transactions WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10;
Output (simplified):
Seq Scan on transactions (cost=0.00..43210.50 rows=5000 width=64)
The problem? PostgreSQL was doing a sequential scan (scanning all rows).
Fix: I added an index.
CREATE INDEX idx_transactions_user_created ON transactions (user_id, created_at DESC);
Now, PostgreSQL used an index scan instead of a slow sequential scan.
2. Create the Right Indexes
Indexes are the #1 way to speed up queries, but I learned the hard way that not all indexes are useful.
Common Index Mistakes I Made
- Creating too many indexes
- Indexes speed up reads but slow down writes (INSERT/UPDATE).
- I once indexed almost every column, and my writes became super slow.
- Using a single-column index when a multi-column index was better
- Example: If I always filter by user_id and created_at, this is better:
CREATE INDEX idx_user_created ON transactions (user_id, created_at DESC);Instead of two separate indexes on user_id and created_at.
- Not using GIN indexes for JSONB data
- I had a table storing metadata in a JSONB column. Searching inside it was slow.
- I fixed it by adding a GIN index:
CREATE INDEX idx_metadata ON transactions USING GIN (metadata);
3. Use VACUUM and ANALYZE Regularly
One day, I noticed that even indexed queries were slowing down. After some research, I found out PostgreSQL needs regular maintenance with VACUUM and ANALYZE.
What These Commands Do
- VACUUM: Cleans up dead rows from updates/deletes
- ANALYZE: Updates statistics so PostgreSQL can choose the best query plan
I set up autovacuum but also ran these manually sometimes: VACUUM ANALYZE;
For big tables, I used pg_repack to rebuild indexes without downtime:
pg_repack -d mydatabase -t transactions4. Optimize Joins and Avoid SELECT *
At one point, my team noticed our API was slow. After debugging, we found this query:
SELECT * FROM users u
JOIN transactions t ON u.id = t.user_id
WHERE t.amount > 100;The problem?
- SELECT * was returning unnecessary columns
- Joins can be expensive if indexes are missing
The Fix
Only select the needed columns:
SELECT u.name, t.amount FROM users u
JOIN transactions t ON u.id = t.user_id
WHERE t.amount > 100;
Add an index on the join column:
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
After this, the query time dropped from seconds to milliseconds!
5. Use Connection Pooling for High Traffic
At scale, I ran into another problem: too many database connections.
- Each PostgreSQL connection takes memory.
- Too many connections slow down the database.
The Fix: Use PgBouncer
Instead of letting every app instance open 100+ connections, I used PgBouncer, a connection pooler.
Config pgbouncer.uni
[databases]
mydatabase = host=127.0.0.1 port=5432 dbname=mydatabase
[pgbouncer]
max_client_conn = 200
default_pool_size = 20
Start PgBouncer and change my app to connect to pgbouncer instead of PostgreSQL.
Now, instead of hundreds of direct connections, my app uses a small pool of efficient connections. This reduced CPU load and improved response times.
Hope you are enjoying coding.