Postgresql

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. ...

PostgreSQL’s default configuration is designed to run on minimal hardware without crashing. It’s not designed to perform well. Out of the box, Postgres uses a fraction of available memory and I/O capacity. Proper tuning can improve performance by 10x or more. Memory Configuration shared_buffers The most important setting. This is Postgres’s own cache. 1 2 3 -- Default: 128MB (way too low) -- Recommended: 25% of system RAM shared_buffers = 4GB -- On a 16GB server Don’t set above 40% — the OS needs memory for its own file cache. ...

Every database connection costs something: TCP handshake, TLS negotiation, authentication, session state allocation. For PostgreSQL, that’s 1.3-2MB of memory per connection. For MySQL, 256KB-1MB. At scale, creating connections on demand kills both your database and your latency. Connection pooling solves this by reusing connections across requests. But misconfigured pools are worse than no pools — you get connection starvation, deadlocks, and debugging nightmares. Here’s how to do it right. The Problem: Connection Overhead Without pooling, a typical web request: ...

The scariest deploy isn’t code—it’s schema changes. One wrong migration can lock tables, corrupt data, or bring down production. Zero-downtime migrations require discipline, but they’re achievable. The Problem Traditional migrations assume you can take the database offline: 1 2 -- Dangerous in production ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL; This locks the table, blocks all reads and writes, and fails if any existing rows lack a value. In a busy system, that’s an outage. ...