↩️ Your migration broke something. Here's how to undo it.

Three levels of rollback, from easy to nuclear.

LEVEL 1: SCHEMA ROLLBACK

You added a column that breaks things. Just drop it.

-- You ran:
ALTER TABLE users ADD COLUMN middle_name TEXT;

-- Undo:
ALTER TABLE users DROP COLUMN middle_name;

Simple. No data loss (the column was new and empty anyway).

Works for: new columns, new indexes, new constraints, new tables.

LEVEL 2: DATA ROLLBACK

You ran an UPDATE that changed data you shouldn't have.

The trick: always create a backup column before modifying data.

-- Before migration:
ALTER TABLE users ADD COLUMN _email_backup TEXT;
UPDATE users SET _email_backup = email;

-- Run your migration:
UPDATE users SET email = lower(email);

-- Oh no, it broke something. Undo:
UPDATE users SET email = _email_backup;
ALTER TABLE users DROP COLUMN _email_backup;

Clunky? Yes. Saves you at 2 AM? Also yes.

LEVEL 3: POINT-IN-TIME RECOVERY

Everything is broken. The migration corrupted data across multiple tables. You need to go back in time.

If you set up WAL archiving (Week 6):

-- 1. Note the exact time BEFORE you ran the migration
-- 2. If it all goes wrong:

./pitr_restore.sh "2026-03-18 14:30:00"

-- Your entire database is back to that moment.

THE REAL LESSON:

Before running any migration on production:

1. Note the current time
2. Run a fresh backup: ./pg_backup_complete.sh dump
3. Test the migration on a copy first
4. Have the rollback script written BEFORE you run up
5. Then run it

5 minutes of prep. Saves hours of panic.

How do you handle migration rollbacks? 👇

@postgres

🔍 Your database is talking to you. You're just not listening.

PostgreSQL collects stats on everything:
- Which queries are slow
- Which tables are bloated
- Which indexes are never used
- How much cache you're hitting
- Where connections are going

Most solo devs never look at any of it. Then wonder why things are slow.

Paid monitoring tools want $50-500/month to show you this data. But PostgreSQL already has it. You just need to know where to look.

This week:

📅 Tuesday — The 5 views every dev should check weekly
📅 Wednesday — 💰 Complete monitoring dashboard (3⭐)
📅 Thursday — Finding and fixing slow queries
📅 Friday — Check-in

No Datadog. No pganalyze. No external agent. Just SQL.

When was the last time you checked your database stats? 👇

@postgres

📊 5 queries. Run them once a week. Know exactly what's happening.

QUERY 1: TABLE BLOAT AND SIZE

SELECT
relname as table_name,
pg_size_pretty(pg_total_relation_size(oid)) as total_size,
n_live_tup as live_rows,
n_dead_tup as dead_rows,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0 END as dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

-- dead_pct > 20%? Run VACUUM ANALYZE on that table.

QUERY 2: UNUSED INDEXES (wasting disk and slowing writes)

SELECT
indexrelname as index_name,
relname as table_name,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
idx_scan as times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND pg_relation_size(indexrelid) > 1048576
ORDER BY pg_relation_size(indexrelid) DESC;

-- times_used = 0 and it's >1MB? Probably safe to drop.

QUERY 3: CACHE HIT RATIO

SELECT
sum(heap_blks_hit) as cache_hits,
sum(heap_blks_read) as disk_reads,
round(100.0 * sum(heap_blks_hit) /
nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) as hit_ratio
FROM pg_statio_user_tables;

-- Below 95%? You need more shared_buffers or RAM.
-- Above 99%? Your database fits in memory. Nice.

QUERY 4: CONNECTION STATUS

SELECT
state,
count(*) as connections,
round(100.0 * count(*) /
current_setting('max_connections')::INT, 1) as pct_of_max
FROM pg_stat_activity
GROUP BY state
ORDER BY connections DESC;

-- idle connections > 50%? You need connection pooling.
-- total > 80% of max? Danger zone.

QUERY 5: SLOWEST QUERIES (requires pg_stat_statements)

SELECT
round(mean_exec_time::numeric, 2) as avg_ms,
calls,
round(total_exec_time::numeric, 0) as total_ms,
left(query, 80) as query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- If pg_stat_statements isn't enabled:
-- ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- Restart PostgreSQL. Worth it.

5 queries. 30 seconds. You now know more about your database than most teams with paid monitoring.

Tomorrow: the complete dashboard with alerting, health scores, and weekly reports. 3⭐.

@postgres

🐌 Finding and fixing slow queries. The 80/20 approach.

Step 1: Find the worst offenders.

-- Enable if not already:
-- ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- Restart PostgreSQL.

-- Top 5 by total time (these hurt your server the most)
SELECT
round(total_exec_time::numeric, 0) as total_ms,
calls,
round(mean_exec_time::numeric, 2) as avg_ms,
left(query, 100) as query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Step 2: Understand WHY it's slow.

-- Copy the slow query and run:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ... your slow query here ...;

-- What to look for:
-- Seq Scan on large table → needs an index
-- Nested Loop with high row count → join strategy problem
-- Sort with external merge → needs more work_mem
-- Buffers: shared read (high) → data not in cache

Step 3: Common fixes.

FIX 1 — MISSING INDEX
-- Seq Scan on users WHERE email = ?
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Seq Scan → Index Scan. 500ms → 2ms.

FIX 2 — MISSING COMPOSITE INDEX
-- Seq Scan on orders WHERE user_id = ? AND status = ?
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders(user_id, status);
-- Put the equality column first.

FIX 3 — N+1 QUERY PATTERN
-- Your app runs SELECT * FROM orders WHERE user_id = ?
-- once per user in a loop. 100 users = 100 queries.
-- Fix in your app:
SELECT * FROM orders WHERE user_id = ANY($1::UUID[]);
-- One query. Pass an array of user IDs.

FIX 4 — COUNT(*) ON LARGE TABLE
-- SELECT count(*) FROM orders; scans entire table.
-- Approximate count (instant):
SELECT n_live_tup FROM pg_stat_user_tables
WHERE relname = 'orders';
-- Good enough for dashboards.

FIX 5 — OVER-SELECTING COLUMNS
-- SELECT * FROM users; fetches every column including blobs.
-- Be specific:
SELECT id, email, name FROM users;
-- Less data transferred, faster query.

Most performance problems are a missing index or an N+1 pattern. Fix those two and you've solved 80% of slow queries.

What's your slowest query? 👇

@postgres

📊 Week 10 done. Migrations without fear.

This week:

✅ Monday — Why ALTER TABLE is terrifying (and doesn't have to be)
✅ Tuesday — Safe vs dangerous operations (know before you run)
✅ Wednesday — 💰 Complete migration system (3⭐)
✅ Thursday — Three levels of rollback

The takeaway: every migration should have a written rollback plan before you run it. Takes 5 minutes. Saves you from the worst night of your career.

---


10 WEEKS. THE FULL STACK.

Auth → Jobs → Performance → Search → Real-time → Backups → Multi-tenancy → File storage → Caching → Migrations

That's everything you need to build, run, and evolve a SaaS. All PostgreSQL.

If you joined recently: every week's free content is still here. Scroll back and catch up.

---

NEXT WEEK

Taking suggestions. What's missing from your PostgreSQL toolkit?

🔴 Email & notifications (triggers, templates, sending from PG)
🟡 Monitoring & observability (pg_stat, slow queries, dashboards)
🟢 Data import/export (CSV, JSON, bulk operations)
🔵 Testing & CI (test your database layer properly)

Or something else entirely. Tell me 👇

@postgres

"What happens when you type a URL into a browser?" is one of the most common technical interview questions. Most online answers cover DNS and TCP but skip everything that happens server-side — load balancers, reverse proxies, framework routing, database queries.

I made an animated explainer that walks through the full path: browser → DNS → TCP → TLS → load balancer → reverse proxy → application server → controller → service → database → and the full trip back.

The thing I wish someone had drawn for me when I was learning: the actual sequence and timing. Like that the TCP + TLS handshakes happen BEFORE your HTTP request even leaves your machine, and they account for ~300ms by themselves on slow connections.

For learners here: what's a concept you've struggled to visualize? I'm planning more of these (databases, git push, Docker), so genuinely curious what would help.

Video is on my channel, link is here if interested https://youtu.be/_x_8EmVok-c

#ad

Made an animated explainer on what actually happens when you go from no index to a B-tree index on a column. Hopefully useful for folks who are comfortable writing SQL but haven't dug into the execution side.

The core comparison the video covers:

Without index:
- Database scans every row sequentially
- Million-row table = up to 1 million comparisons
- Roughly 500ms on typical hardware

With B-tree index:
- Database traverses tree from root → branches → leaves
- Million-row table = 3-4 comparisons
- Roughly 1ms

The interesting parts that often trip people up:

- A B-tree is logarithmic, so going from 1M rows to 1B rows only adds ~2 more comparisons
- The query planner can choose NOT to use an index even when one exists, often because statistics are stale or the index would still scan most of the table
- Composite indexes only help if your WHERE clause uses the leftmost columns

The video walks through an actual query execution step by step, including when indexes don't help (leading wildcards in LIKE patterns, function calls on indexed columns, etc.).

https://youtu.be/_-TG_HRSlY4

#ad

Every RAG app, semantic search feature, and “related to this” button depends on one hidden operation:

Find the nearest points among millions — fast.

That sounds simple until you realize each document is stored as a high-dimensional vector, often with more than a thousand numbers. If your app has two million help articles, the brute-force approach means comparing the query against every single vector, every time.

That is where normal SQL indexes break down.

A B-tree can help with IDs, prices, dates, and sorted values. But vector search asks a different question:

Which document is closest in meaning across 1,536 dimensions?

There is no single sorted line for that.

In this AI Concepts explainer, we look at why AI systems need vector databases, how approximate nearest-neighbor search works, and why HNSW-style indexes can search millions of vectors in milliseconds without checking every point.

https://youtu.be/7vOt3CJEOJA

#ad