# PostgreSQL Performance Tips Every Backend Developer Should Know
PostgreSQL is rock solid, but out-of-the-box defaults won't cut it under real load. Here are the optimizations I apply to every production database.
## 1. Use EXPLAIN ANALYZE Before Optimizing Anything
Never guess. Always check the query plan:
```sql
EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = 42 AND status = 'published'
ORDER BY created_at DESC;
```
Look for Seq Scan on large tables — that's your first target.
## 2. Index Strategically
```sql
-- Composite index for the query above
CREATE INDEX idx_posts_author_status_date
ON posts(author_id, status, created_at DESC);
```
Rules of thumb:
- Put equality columns first, range/sort columns last
- Partial indexes for filtered queries: WHERE status = 'published'
- Don't over-index — writes pay the cost
## 3. Avoid SELECT *
Always select only what you need. It reduces memory, network I/O, and prevents accidental full-table scans on wide tables.
## 4. Connection Pooling with PgBouncer
Spring Boot opens a new connection per request without pooling. PgBouncer sits in front of Postgres and reuses connections:
```ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
```
Point your app at port 6432 instead of 5432.
## 5. Tune work_mem for Sort-Heavy Queries
```sql
SET work_mem = '64MB';
```
Set this per-session for heavy analytical queries. Raising it globally can cause OOM on high-concurrency workloads.
## 6. Vacuum and Autovacuum
Dead rows from UPDATEs and DELETEs bloat your tables. Ensure autovacuum is on and monitor with:
```sql
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```
These six habits alone will handle most performance issues before you ever need to scale horizontally.