Postgresql

Database migrations are where deployments go to die. One bad migration can corrupt data, lock tables for hours, or bring down production entirely. Here’s how to make them boring. The Golden Rules Every migration must be reversible (or explicitly marked as not) Never run migrations during deploy (separate the concerns) Always test against production-scale data (10 rows works, 10 million doesn’t) Assume the migration will fail halfway (design for it) The Expand-Contract Pattern The safest way to change schemas: expand first, contract later. ...

Schema changes are scary. One bad migration can take down your entire application. Here’s how to evolve your database safely, even with users actively hitting it. The Core Problem You need to rename a column. Sounds simple: 1 ALTER TABLE users RENAME COLUMN name TO full_name; But your application is running. The moment this executes: Old code looking for name breaks New code looking for full_name also breaks (until deploy finishes) You have a window of guaranteed failures This is why database migrations need careful choreography. ...

Every database connection has a cost. TCP handshake, TLS negotiation, authentication, session setup—all before your first query runs. For PostgreSQL, that’s typically 20-50ms. For a single request, barely noticeable. For thousands of requests per second, catastrophic. Connection pooling solves this by maintaining a set of pre-established connections that your application reuses. Done right, it’s one of the highest-impact performance optimizations you can make. The Problem: Connection Overhead Without pooling, every request cycle looks like this: ...

Database migrations are the scariest part of deployments. Get them wrong and you’re looking at downtime, data loss, or a 3 AM incident call. Here’s how to migrate safely. The Problem Naive migrations cause problems: 1 2 3 4 5 6 -- This locks the entire table ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL; -- 10 million rows, exclusive lock held for minutes -- Application queries queue up -- Users see errors Safe Migration Patterns Adding Columns Bad: Adding NOT NULL column without default ...

Your query is slow. You add an index. It gets faster. Magic, right? Not quite. Indexes are powerful but misunderstood. Used well, they turn seconds into milliseconds. Used poorly, they slow down writes, waste storage, and sometimes make queries slower. Let’s demystify them. What Is an Index? An index is a separate data structure that helps the database find rows without scanning the entire table. Think of it like a book’s index—instead of reading every page to find “PostgreSQL,” you look it up in the index and jump directly to page 247. ...

Opening a database connection is expensive. TCP handshake, SSL negotiation, authentication, session setup—it all adds up. Do that for every query and your application crawls. Connection pooling fixes this by reusing connections. Here’s how to do it right. The Problem Without pooling, every request opens a new connection: 1 2 3 4 5 6 7 8 # BAD: New connection per request def get_user(user_id): conn = psycopg2.connect(DATABASE_URL) # ~50-100ms cursor = conn.cursor() cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,)) user = cursor.fetchone() conn.close() return user At 100 requests per second, that’s 100 connections opening and closing per second. Your database server has a connection limit (typically 100-500). You’ll exhaust it fast. ...

Database migrations are where deploys go to die. Here’s how to make them safe. The Golden Rule Every migration must be backward compatible. Your old code will run alongside the new schema during deployment. If it can’t, you’ll have downtime. Safe Operations These are always safe: Adding a nullable column Adding a table Adding an index (with CONCURRENTLY) Adding a constraint (as NOT VALID, then validate later) Dangerous Operations These need careful handling: ...

You don’t need to be a DBA to work effectively with PostgreSQL. Here’s what developers need to know for day-to-day operations. Connection Basics 1 2 3 4 5 6 7 8 9 10 11 12 13 # Connect psql -h localhost -U myuser -d mydb # With password prompt psql -h localhost -U myuser -d mydb -W # Connection string psql "postgresql://user:pass@localhost:5432/mydb" # Common options psql -c "SELECT 1" # Run single command psql -f script.sql # Run file psql -A -t -c "SELECT 1" # Unaligned, tuples only psql Commands \ \ \ \ \ \ \ \ \ \ \ l c d d d d d d x t q t t i u f i d + t m b a i n b n a l g m e e n a m e L C L L D L L L T T Q i o i i e i i i o o u s n s s s s s s g g i t n t t c t t t g g t e r l l d c t t i i u f e e a t a a b n s u t b b e d e n e q a t l l e r c x u b o e e t x s t p e a s s a e / i a r s d b s r o n y e a w l o n d s t i e l s e t a t e d i b h s m a o i s s u n e i t g z p e u s t Essential Queries Table Information 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 -- Table sizes SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size FROM pg_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC; -- Row counts (estimate) SELECT relname AS table, reltuples::bigint AS row_estimate FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace ORDER BY reltuples DESC; -- Exact row counts (slow on large tables) SELECT 'SELECT ''' || tablename || ''' AS table, COUNT(*) FROM ' || tablename || ' UNION ALL' FROM pg_tables WHERE schemaname = 'public'; Index Information 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -- Index sizes SELECT indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC; -- Unused indexes SELECT schemaname || '.' || relname AS table, indexrelname AS index, idx_scan AS scans FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC; -- Index usage SELECT relname AS table, indexrelname AS index, idx_scan AS scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_stat_user_indexes ORDER BY idx_scan DESC; Active Queries 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 -- Running queries SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC; -- Long-running queries (> 5 minutes) SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' AND state != 'idle'; -- Kill a query SELECT pg_cancel_backend(pid); -- Graceful SELECT pg_terminate_backend(pid); -- Force Lock Investigation 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -- Blocked queries SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted; EXPLAIN ANALYZE 1 2 3 4 5 6 7 8 -- Show query plan with actual timing EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com'; -- With buffers (I/O stats) EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com'; -- Format as JSON EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE email = 'test@example.com'; Key things to look for: ...

PostgreSQL is fast out of the box. But “fast enough for development” and “fast enough for production” are different conversations. These techniques will help you find and fix performance bottlenecks. Finding Slow Queries Enable Query Logging 1 2 3 4 5 6 -- Log queries slower than 500ms ALTER SYSTEM SET log_min_duration_statement = '500ms'; SELECT pg_reload_conf(); -- Check current setting SHOW log_min_duration_statement; pg_stat_statements Extension The most valuable performance tool: ...

Every junior developer learns that indexes make queries fast. What they don’t learn is that indexes also make writes slow, consume disk space, and can actively hurt performance when misused. Let’s fix that. What Indexes Actually Do An index is a separate data structure that maintains a sorted copy of specific columns, with pointers back to the full rows. Think of it like the index in a book — instead of reading every page to find “PostgreSQL,” you flip to the index, find the entry, and jump directly to page 247. ...