🔍 EXPLAIN ANALYZE DECODED

The most useful command nobody understands.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Let's decode what it tells you.

---

THE OUTPUT (SCARY VERSION)


Seq Scan on users (cost=0.00..1234.00 rows=1 width=65) (actual time=45.123..45.456 rows=1 loops=1)
Filter: (email = '[email protected]'::text)
Rows Removed by Filter: 50000
Planning Time: 0.123 ms
Execution Time: 45.567 ms


---

THE OUTPUT (TRANSLATED)


Seq Scan on users
→ "I scanned every row in the table"
→ 🚨 RED FLAG for large tables
cost=0.00..1234.00
→ PostgreSQL's guess at effort
→ Higher = more work
actual time=45.123..45.456
→ Real time in milliseconds
→ This is what matters
rows=1
→ Found 1 matching row
Rows Removed by Filter: 50000
→ "I looked at 50,000 rows to find 1"
→ 🚨 You need an index


---

AFTER ADDING INDEX

CREATE INDEX idx_users_email ON users(email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=65) (actual time=0.045..0.047 rows=1 loops=1)
Index Cond: (email = '[email protected]'::text)
Planning Time: 0.156 ms
Execution Time: 0.078 ms


45ms → 0.078ms = 577x faster

---

WHAT TO LOOK FOR

✅ Good signs:
- Index Scan or Index Only Scan
- Low actual time
- rows close to estimated

🚨 Bad signs:
- Seq Scan on big tables
- Rows Removed by Filter is huge
- actual time in seconds
- loops=10000 (N+1 problem)

---

YOUR TURN

Run EXPLAIN ANALYZE on your slowest query.
What do you see?

#January2026

📊 WEEK 3 CHECK-IN

Performance week: done.

---

THIS WEEK

Mon: Why queries are slow (the usual suspects)
Tue: Indexes that matter (rules 1-4)
Wed: Query speedup cheat sheet 💰
Thu: EXPLAIN ANALYZE decoded

---

THE SIMPLE TRUTH

90% of slow PostgreSQL:
→ Missing index
→ Fix: CREATE INDEX

9% of slow PostgreSQL:
→ Wrong query pattern
→ Fix: Rewrite query

1% of slow PostgreSQL:
→ Actually needs tuning
→ Fix: postgresql.conf

Start with indexes. Always.

---

DID YOU TRY IT?

- ⚡ Added an index, saw improvement
- 🔍 Ran EXPLAIN ANALYZE, learned something
- 📚 Just reading for now
- 🤷 My queries are fine (lucky you)

---

NEXT WEEK

Week 4. What do you want?

Options:
A) Full-text search (replace Algolia)
B) JSONB patterns (flexible schemas)
C) Database backups (don't lose everything)
D) Something else?

Reply with A, B, C, or your idea.

Most votes wins.

---

JANUARY PROGRESS

Week 1: Auth ✓
Week 2: Background jobs ✓
Week 3: Performance ✓
Week 4: You decide

---

See you Monday.

#January2026

🔍 YOU DON'T NEED ALGOLIA

"We need search. Let's add Algolia."

Algolia pricing:
- Free: 10K requests/month
- Grow: $1 per 1K requests
- Real usage: $50-500/month

PostgreSQL pricing:
- $0
- Forever

---

WHAT ALGOLIA DOES

- Full-text search
- Typo tolerance
- Ranking
- Fast responses

WHAT POSTGRESQL DOES

- Full-text search ✓
- Typo tolerance (with pg_trgm) ✓
- Ranking ✓
- Fast responses ✓

The difference? Marketing budget.

---

WHEN YOU ACTUALLY NEED ALGOLIA

- Searching millions of documents
- Sub-10ms response required
- Complex faceting and filters
- You have the budget

WHEN POSTGRESQL IS ENOUGH

- Searching thousands to hundreds of thousands
- <100ms response is fine
- Basic to moderate filtering
- You'd rather not pay

That's most of us.

---

THIS WEEK

Tue: PostgreSQL search fundamentals
Wed: 💰 Copy-paste search system (3 ⭐)
Thu: Fuzzy matching with pg_trgm
Fri: January retro

---

Last week's vote: Search won.
Let's build it.

#January2026

📚 POSTGRESQL SEARCH FUNDAMENTALS

Full-text search in PostgreSQL is built-in.
No extensions. No setup. Just SQL.

---

THE BASICS

-- Simple search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) 
   @@ to_tsquery('english', 'postgresql');

That's it. That's search.

---

WHAT'S HAPPENING

to_tsvector = converts text to searchable tokens
to_tsquery = converts search term to query
@@ = "matches"

SELECT to_tsvector('english', 'PostgreSQL is amazing');
-- Result: 'amaz':3 'postgresql':1
-- Words are stemmed: "amazing" → "amaz"

---

MAKE IT FAST

-- Add a search column
ALTER TABLE posts ADD COLUMN search_vector tsvector;

-- Populate it
UPDATE posts SET search_vector = 
  to_tsvector('english', title || ' ' || COALESCE(body, ''));

-- Index it
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Search it (fast now)
SELECT * FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgresql');

---

KEEP IT UPDATED

-- Auto-update on insert/update
CREATE FUNCTION posts_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || COALESCE(NEW.body, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_search_trigger
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION posts_search_update();

---

RANKING RESULTS

SELECT title,
       ts_rank(search_vector, query) as rank
FROM posts, to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Most relevant first. Like Algolia.

---

Tomorrow: The complete system.

Multi-column search.
Highlighting matches.
Search suggestions.
All copy-paste ready.

3 ⭐

#January2026

🎯 FUZZY MATCHING WITH PG_TRGM

Full-text search is great.
But what about typos?

"postgre" should find "postgresql"
"recieved" should find "received"

Enter: pg_trgm

---

ENABLE IT

CREATE EXTENSION pg_trgm;

That's it. Now you have fuzzy matching.

---

HOW IT WORKS

Trigrams = 3-character chunks

"postgresql" → {" po", "pos", "ost", "stg", "tgr", "gre", "res", "esq", "sql", "ql "}

Similar words share trigrams.
More shared = more similar.

---

SIMILARITY SEARCH

-- Find similar to "postgre"
SELECT name, similarity(name, 'postgre') as sim
FROM products
WHERE similarity(name, 'postgre') > 0.3
ORDER BY sim DESC;

---

MAKE IT FAST

-- GIN index for similarity
CREATE INDEX idx_products_name_trgm 
ON products USING GIN(name gin_trgm_ops);

-- Now this is fast:
SELECT * FROM products
WHERE name % 'postgre'  -- % means "similar to"
ORDER BY name <-> 'postgre';  -- <-> is distance

---

ILIKE THAT'S FAST

Remember how LIKE '%search%' is slow?

-- With pg_trgm, this uses the index:
SELECT * FROM products
WHERE name ILIKE '%data%';

-- Because the GIN index supports it!

---

COMBINE WITH FULL-TEXT

-- Best of both worlds
SELECT title,
       ts_rank(search_vector, query) as relevance,
       similarity(title, $1) as fuzzy_score
FROM posts, to_tsquery('english', $1) query
WHERE search_vector @@ query
   OR title % $1
ORDER BY relevance + fuzzy_score DESC;

Finds exact matches AND fuzzy matches.

---

THAT'S THE WHOLE SEARCH STACK

- tsvector + tsquery = full-text
- pg_trgm = fuzzy/typo tolerance
- GIN indexes = fast

No external services.

#January2026

📊 JANUARY RETROSPECTIVE

First month of 2026: done.

---

WHAT WE COVERED

Week 1: Auth queries
Week 2: Background jobs
Week 3: Performance & indexes
Week 4: Full-text search

Four fundamental patterns.
Zero external dependencies.
Pure PostgreSQL.

---

THE THEME

"You don't need that service."

Auth → No Auth0
Jobs → No Redis
Search → No Algolia
Everything → PostgreSQL

Not because services are bad.
Because simple is good.

---

WHAT WORKED

✓ Shorter, focused content
✓ Copy-paste ready code
✓ Lower prices (3 ⭐)
✓ One topic per week

---

FEBRUARY PREVIEW

Thinking about:
- Real-time with LISTEN/NOTIFY
- Multi-tenancy patterns
- Backup & recovery
- API rate limiting

What do you want to see?

---

HONEST QUESTION

Is this content useful?

- 🔥 Yes, using it in real projects
- 📚 Yes, learning for future
- 😐 Meh, not really
- 👎 No, want something different

Reply or react.
Helps me know what to make.

---

THANK YOU

1,000+ of you here now.
That's not nothing.

See you in February.

@postgres

#January2026

📡 FEBRUARY KICKOFF: REAL-TIME WEEK

New month. New topic.

January covered:
- Auth ✓
- Background jobs ✓
- Performance ✓
- Full-text search ✓

February starts with: Real-time.

---

THE PROBLEM

Your app needs live updates:
- New message notifications
- Activity feeds
- Live dashboards
- Collaborative features

The "normal" solution:
- Pusher: $49/month
- Ably: $29/month
- Firebase: Usage-based $$
- Socket.io + Redis: Complexity

---

THE POSTGRESQL WAY

LISTEN/NOTIFY.

Built into PostgreSQL since forever.
Zero extra services.
Zero extra cost.

-- That's it. That's real-time.
NOTIFY my_channel, 'Hello from the database';

---

THIS WEEK

Tue: How LISTEN/NOTIFY works
Wed: 💰 Copy-paste real-time system (3 ⭐)
Thu: WebSocket patterns
Fri: Week 5 check-in

---

JANUARY RESULTS

4 weeks. 4 topics. 4 utilities.

Small wins > big masterclasses.

Let's keep going.

#February2026

📡 HOW LISTEN/NOTIFY WORKS

PostgreSQL has built-in pub/sub.
Most people don't know this exists.

---

THE BASICS

-- Connection 1: Subscribe
LISTEN order_updates;

-- Connection 2: Publish
NOTIFY order_updates, '{"order_id": 123, "status": "shipped"}';

-- Connection 1 receives it instantly

That's the whole API.
LISTEN. NOTIFY. Done.

---

FROM YOUR APPLICATION

-- Trigger on table changes
CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('order_updates', json_build_object(
        'action', TG_OP,
        'order_id', NEW.id,
        'status', NEW.status
    )::TEXT);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_changed
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Now every order change broadcasts automatically.

---

IN NODE.JS

const { Client } = require('pg');
const client = new Client();

await client.connect();
await client.query('LISTEN order_updates');

client.on('notification', (msg) => {
    const data = JSON.parse(msg.payload);
    console.log('Order updated:', data);
    // Send to WebSocket clients
});

---

LIMITATIONS

- Payload max: 8KB (enough for IDs and small data)
- No persistence (miss it if not listening)
- No delivery guarantee

For most apps? Totally fine.
For critical data? Store first, notify second.

---

Tomorrow: The complete system.

Notifications, activity feeds, live dashboards.
All copy-paste ready.

3 ⭐

#February2026

🔌 WEBSOCKET PATTERNS

LISTEN/NOTIFY gets data out of PostgreSQL.
WebSockets get it to the browser.

Here's how to connect them.

---

THE SIMPLE PATTERN

PostgreSQL → Node.js listener → WebSocket server → Browser

// server.js
const WebSocket = require('ws');
const { Client } = require('pg');

const wss = new WebSocket.Server({ port: 8080 });


const pg = new Client();

// Listen to PostgreSQL
pg.connect();
pg.query('LISTEN updates');

pg.on('notification', (msg) => {
    // Broadcast to all connected clients
    wss.clients.forEach(client => {
        if (client.readyState === WebSocket.OPEN) {
            client.send(msg.payload);
        }
    });
});

---

USER-SPECIFIC CHANNELS

// Track which users are connected
const userConnections = new Map();

wss.on('connection', (ws, req) => {
    const userId = authenticateConnection(req);
    
    if (!userConnections.has(userId)) {
        userConnections.set(userId, new Set());
        // Subscribe to user's channel
        pg.query(`LISTEN user_${userId}`);
    }
    userConnections.get(userId).add(ws);
    
    ws.on('close', () => {
        userConnections.get(userId).delete(ws);
    });
});

// In notification handler
pg.on('notification', (msg) => {
    const userId = msg.channel.replace('user_', '');
    const connections = userConnections.get(userId);
    
    connections?.forEach(ws => {
        ws.send(msg.payload);
    });
});

---

ROOM-BASED (LIKE CHAT)

-- Notify a specific room
SELECT pg_notify(
    'room_' || room_id::TEXT,
    json_build_object('message', content, 'user', sender)::TEXT
);

// Join room
ws.on('message', (data) => {
    const { action, roomId } = JSON.parse(data);
    
    if (action === 'join') {
        pg.query(`LISTEN room_${roomId}`);
        // Track membership...
    }
});

---

SCALING NOTE

One Node.js process = one LISTEN connection.
For multiple servers, each listens independently.

PostgreSQL handles the fan-out.
No Redis pub/sub needed.

---

How would you use real-time in your app?

#February2026

📊 WEEK 5 CHECK-IN

Real-time week: done.

---

THIS WEEK

Mon: February kickoff, LISTEN/NOTIFY intro
Tue: How it works + triggers
Wed: Complete real-time system 💰
Thu: WebSocket patterns

---

THE KEY INSIGHT

PostgreSQL already has pub/sub.

LISTEN/NOTIFY has been there since PostgreSQL 9.0.
That was 2010.

Most people still don't know it exists.

Now you do.

---

WHEN TO USE IT

✅ Good for:
- Notifications (new message, new follower)
- Activity feeds
- Live dashboards
- Presence (who's online)
- Simple chat

⚠️ Consider alternatives for:
- High-frequency updates (100+ per second)
- Messages larger than 8KB
- Guaranteed delivery requirements

---

WHAT DID YOU BUILD?

- 📡 Added real-time to my app
- 🔔 Set up notification system
- 📚 Just learning
- 🤔 Still figuring out use case

---

FEBRUARY PREVIEW

Week 6: Backups & Recovery (don't lose everything)
Week 7: Multi-tenancy patterns
Week 8: TBD - you decide

What should Week 8 be?

---

5 WEEKS DOWN

We've covered:
1. Auth
2. Background jobs
3. Performance
4. Full-text search
5. Real-time

That's a complete PostgreSQL toolkit.

More coming.

#February2026

🔥 Your backups are probably broken. You just don't know it yet.

Here's how most solo devs handle backups:

Step 1: Set up pg_dump in a cron job
Step 2: Forget about it
Step 3: Server dies 8 months later
Step 4: Discover the cron job stopped working 6 months ago
Step 5: Cry


Sound familiar?
The problem isn't making backups. It's making backups that actually work when you need them.

This week we fix that:

📅 Tuesday — The three backup methods and when to use each
📅 Wednesday — 💰 Complete backup & recovery system you can copy-paste (3⭐)
📅 Thursday — How to test your backups (the step everyone skips)
📅 Friday — Check-in

No managed service needed. No S3 bill surprise. Just PostgreSQL doing what PostgreSQL does well.

How do you handle backups right now? Be honest — no judgment 👇

@postgres

🛠️ Three backup methods. Pick the right one.

PostgreSQL gives you three options. Each solves a different problem.

1. pg_dump — The snapshot

bash# Full database dump, compressed

pg_dump -Fc -Z 6 mydb > backup_$(date +%Y%m%d).dump

# Restore

pg_restore -d mydb backup_20260210.dump


Good for: Small databases (<10 GB), simple projects, quick exports.
Bad for: Large databases (slow), point-in-time recovery (impossible).

2. pg_basebackup — The full copy

bash# Complete cluster backup

pg_basebackup -D /backups/base \
-Ft -z -P -v \
-U replicator

Good for: Full server restore, setting up replicas.

Bad for: Frequent backups (copies everything every time).


3. WAL archiving — The timeline

sql-- Enable in postgresql.conf
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command =
'cp %p /backups/wal/%f';
ALTER SYSTEM SET archive_timeout = 300;
SELECT pg_reload_conf();

Good for: Point-in-time recovery, minimal data loss, large databases.

Bad for: Nothing. This is what you want.

The solo dev answer:

Database < 5 GB?
→ pg_dump daily + WAL archiving

Database 5-50 GB?
→ pg_basebackup weekly + WAL archiving

Database > 50 GB?
→ pg_basebackup weekly + WAL archiving + incremental strategy

WAL archiving is the common thread. It gives you something the others can't: go back to any point in time.

Accidentally deleted production data at 2:47 PM?

Restore base backup → replay WAL → stop at 2:46 PM

Everything back. Like it never happened.

Tomorrow: the complete system that ties all three together.

Copy-paste ready. 3⭐.

@postgres

🧪 Your backup is worthless until you test the restore.
The number one backup mistake: assuming it works.
Here's a dead simple restore test you can run right now:

#!/bin/bash
# test_restore.sh — Run this weekly

TEST_DB="restore_test_$(date +%s)"
BACKUP_FILE=$(ls -t /backups/dumps/*.dump | head -1)

echo "Testing restore of: $BACKUP_FILE"

# Create temporary database
createdb $TEST_DB

# Restore into it
pg_restore -d $TEST_DB $BACKUP_FILE 2>/dev/null

# Verify: count tables
TABLE_COUNT=$(psql -t -A -d $TEST_DB \
-c "SELECT count(*) FROM information_schema.tables
WHERE table_schema = 'public'")

# Verify: count rows in your main table
ROW_COUNT=$(psql -t -A -d $TEST_DB \
-c "SELECT count(*) FROM users" 2>/dev/null || echo "0")

# Cleanup
dropdb $TEST_DB

# Report
echo "Tables restored: $TABLE_COUNT"
echo "Users found: $ROW_COUNT"

if [ "$TABLE_COUNT" -gt 0 ] && [ "$ROW_COUNT" -gt 0 ]; then
echo "✅ Backup is valid"
else
echo "❌ BACKUP FAILED — investigate immediately"
# Add your alert here: curl webhook, send email, etc.
fi

Schedule it:

# Every Sunday at 3 AM
echo "0 3 * * 0 /usr/local/bin/test_restore.sh \
>> /var/log/backup_test.log 2>&1" | crontab -


Three things to check when testing:
1. Can you restore at all?
Sounds obvious. Many dumps silently fail (permissions, disk space, version mismatch).
2. Is the data complete?
Row counts should match production within the backup window.
3. How long does restore take?
If your 50 GB database takes 4 hours to restore, that's your minimum downtime. Plan accordingly.
The script above takes 2 minutes to set up. Runs automatically. Tells you immediately if something breaks.
Two minutes now vs finding out during an emergency at 3 AM.
When was the last time you tested a restore? 👇
@postgres

📊 Week 6 done. Your backups should be smarter now.

Quick recap:

✅ Monday — The honest truth about backup failures
✅ Tuesday — Three methods: pg_dump, pg_basebackup, WAL archiving
✅ Wednesday — 💰 Complete backup system (3⭐)
✅ Thursday — Restore testing script

The one thing to take away this week: backups without tested restores are just wasted disk space.


NEXT WEEK: MULTI-TENANCY PATTERNS

Week 7 tackles the question every SaaS builder faces: how do you serve multiple customers from one database?

Shared tables with row-level security
Schema-per-tenant
When to pick which
The copy-paste RLS setup

One of the most requested topics. See you Monday.
What did you implement this week? 👇
@postgres