🐢 WHY YOUR QUERIES ARE SLOW

"PostgreSQL is slow."

No. Your queries are slow.
PostgreSQL is waiting for you to help it.

---

THE USUAL SUSPECTS

1. Missing indexes
PostgreSQL scans every row.
Millions of rows = slow.

2. Wrong indexes
You have indexes.
They're not being used.

3. Too much data
SELECT * when you need 2 columns.
JOINs that pull entire tables.

4. N+1 queries
1 query to get users.
100 queries to get their posts.
Death by a thousand cuts.

---

THE GOOD NEWS

Most slow queries are fixed with:
- One or two indexes
- Minor query rewrites
- EXPLAIN ANALYZE (so you know what's happening)

No magic. No expensive consultants.
Just knowing where to look.

---

THIS WEEK

Tue: Indexes that actually matter
Wed: 💰 Query speedup cheat sheet (3 ⭐)
Thu: EXPLAIN ANALYZE decoded
Fri: Week 3 check-in

---

What's your slowest query right now?

Paste it below. Let's fix it together.

#January2026

📇 INDEXES THAT ACTUALLY MATTER

"Just add an index."

Which one? Where? On what?

Here's what actually matters.

---

RULE 1: INDEX YOUR WHERE CLAUSES

-- This query:
SELECT * FROM users WHERE email = '[email protected]';

-- Needs this index:
CREATE INDEX idx_users_email ON users(email);

No index = scan every row.
With index = jump straight to it.

---

RULE 2: INDEX YOUR JOIN COLUMNS

-- This query:
SELECT * FROM posts 
JOIN users ON posts.user_id = users.id;

-- Needs:
CREATE INDEX idx_posts_user_id ON posts(user_id);
-- (users.id is already indexed as PRIMARY KEY)

---

RULE 3: INDEX YOUR ORDER BY

-- This query:
SELECT * FROM posts 
WHERE user_id = $1 
ORDER BY created_at DESC;

-- Needs a composite index:
CREATE INDEX idx_posts_user_created 
ON posts(user_id, created_at DESC);

One index. Filters AND sorts.

---

RULE 4: PARTIAL INDEXES FOR FILTERED QUERIES

-- If you always query active users:
SELECT * FROM users WHERE status = 'active';

-- Don't index everything:
CREATE INDEX idx_users_active 
ON users(id) 
WHERE status = 'active';

-- Smaller index. Faster lookups.

---

THE MISTAKE EVERYONE MAKES

Adding indexes on everything.

More indexes = slower writes.
Every INSERT/UPDATE updates every index.

Index what you query.
Nothing more.

---

Tomorrow: The full cheat sheet.

When to use B-tree vs GIN vs GiST.
Composite index column order.
Indexes that hurt more than help.

3 ⭐ — Know exactly which index to add.

#January2026

🔍 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