PostgreSQL tips I wish I knew earlier

I have been using PostgreSQL for years across different projects. These are the things I wish someone had told me earlier, not the basics you find in every tutorial, but the practical patterns that come up in real applications.

Use EXPLAIN ANALYZE, not just EXPLAIN

EXPLAIN shows you the query plan. EXPLAIN ANALYZE actually runs the query and shows you the real execution times. The difference matters because the planner's estimates can be wildly off.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Look at the actual time vs estimated time. If they are far apart, your statistics might be stale. Run ANALYZE users; to update them.

Partial indexes save space and speed

If you frequently query a subset of rows, a partial index is smaller and faster than a full index:

-- Instead of indexing all orders
CREATE INDEX idx_orders_status ON orders(status);
 
-- Index only the ones you actually query
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

This is especially useful for status columns where you almost always filter on one or two values.

JSONB is powerful but do not overuse it

PostgreSQL's JSONB type is great for semi-structured data. You can index into it, query nested fields, and it is surprisingly fast:

-- Query a nested JSONB field
SELECT * FROM events
WHERE payload->>'type' = 'purchase'
AND (payload->'metadata'->>'amount')::numeric > 100;
 
-- Create a GIN index for fast JSONB lookups
CREATE INDEX idx_events_payload ON events USING GIN (payload);

But I have seen teams store everything in JSONB columns because it is "flexible." You lose type safety, foreign keys, and query optimization. Use JSONB for genuinely dynamic data. Use regular columns for everything else.

Connection pooling matters

Every PostgreSQL connection consumes memory (roughly 10MB per connection). If your application opens too many connections, the database slows down or runs out of memory.

Use a connection pooler like PgBouncer in front of PostgreSQL. It maintains a pool of database connections and multiplexes your application connections through them:

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp
 
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

Transaction pooling mode is the most efficient for web applications. It assigns a connection for the duration of each transaction, not each client session.

Use CTEs for readable queries

Common Table Expressions make complex queries readable:

WITH active_users AS (
  SELECT id, email FROM users WHERE last_login > now() - interval '30 days'
),
user_orders AS (
  SELECT user_id, count(*) as order_count
  FROM orders
  WHERE created_at > now() - interval '30 days'
  GROUP BY user_id
)
SELECT au.email, uo.order_count
FROM active_users au
JOIN user_orders uo ON au.id = uo.user_id
WHERE uo.order_count > 5;

In recent PostgreSQL versions (12+), the planner can inline CTEs, so there is no performance penalty.

pg_stat_statements is your best friend

This extension tracks query statistics. Enable it and you can find your slowest queries instantly:

-- Enable the extension
CREATE EXTENSION pg_stat_statements;
 
-- Find the slowest queries by total time
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This has helped me find performance problems that would have taken hours to track down through application logs.

Backups: pg_dump is not enough

pg_dump works for small databases, but for anything serious, use continuous archiving with WAL (Write-Ahead Log) shipping. This gives you point-in-time recovery:

# In postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Combined with a base backup, you can recover to any point in time, not just the last dump. For production databases, this is non-negotiable.

Sources

• PostgreSQL Documentation
• PostgreSQL Tutorial