⚡ WEEK 2: NO MORE REDIS

Last week: Auth queries.
This week: Background jobs.

---

THE PROBLEM

Your app needs to:
- Send emails without blocking requests
- Process uploads after response
- Run daily reports
- Clean up old data
- Sync with external APIs

The "normal" solution:
Redis + Sidekiq/Bull/Celery

The cost:
- Another service to run ($15-50/month)
- Another thing to break
- Another thing to monitor

---

THE POSTGRESQL WAY

Your database can be your job queue.

Not "for small projects."
Not "until you scale."

For real production apps.

---

THIS WEEK

Tue: How PostgreSQL job queues work
Wed: 💰 Copy-paste job queue (4 ⭐)
Thu: Scheduling with pg_cron
Fri: Week 2 check-in

---

LAST WEEK'S WIN

The auth queries got their first buyer. 🎉

Small wins. Real progress.
That's the 2026 energy.

---

What background tasks do you need to run?

#January2026

🔄 POSTGRESQL AS A JOB QUEUE

"Use Redis for queues."

Why? PostgreSQL handles queues fine.

Here's how it works.

---

THE BASIC IDEA

CREATE TABLE jobs (
    id SERIAL PRIMARY KEY,
    task TEXT NOT NULL,
    payload JSONB,
    status TEXT DEFAULT 'pending',
    run_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Add job:

INSERT INTO jobs (task, payload) 
VALUES ('send_email', '{"to": "[email protected]"}');

Get next job:

UPDATE jobs SET status = 'processing'
WHERE id = (
    SELECT id FROM jobs 
    WHERE status = 'pending' AND run_at <= NOW()
    ORDER BY created_at 
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- This is the magic
)
RETURNING *;

---

THE MAGIC: FOR UPDATE SKIP LOCKED

Multiple workers can poll the same table.
Each gets a different job.
No duplicates. No conflicts.

PostgreSQL handles the locking.

---

WHAT YOU NEED FOR PRODUCTION

Basic table? Easy.
Production-ready? Needs more:

- Retry logic for failures
- Dead letter queue for permanent failures
- Priority levels
- Scheduled jobs (run at specific time)
- Job timeout handling
- Proper indexes

---

Tomorrow: All of that. Copy-paste ready.

4 ⭐ — Replace your Redis bill forever.

#January2026

⏰ SCHEDULING WITH PG_CRON

Background jobs: done.
But what about scheduled tasks?

"Run this every night at 3 AM"
"Send weekly report every Monday"
"Clean up old data daily"

---

PG_CRON: CRON INSIDE POSTGRESQL

-- Enable the extension
CREATE EXTENSION pg_cron;

-- Run every night at 3 AM
SELECT cron.schedule(
    'nightly-cleanup',
    '0 3 * * *',
    $$DELETE FROM sessions WHERE expires_at < NOW()$$
);

-- Run every Monday at 9 AM
SELECT cron.schedule(
    'weekly-report', 
    '0 9 * * 1',
    $$INSERT INTO jobs (task, payload) VALUES ('send_weekly_report', '{}')$$
);

That's it. Runs inside PostgreSQL.

---

COMMON SCHEDULES

-- Every minute
'* * * * *'

-- Every hour
'0 * * * *'

-- Every day at midnight
'0 0 * * *'

-- Every Monday at 9 AM
'0 9 * * 1'

-- First day of month
'0 0 1 * *'

---

MANAGE YOUR SCHEDULES

-- List all jobs
SELECT * FROM cron.job;

-- Disable a job
SELECT cron.unschedule('nightly-cleanup');

-- See job history
SELECT * FROM cron.job_run_details 
ORDER BY start_time DESC LIMIT 10;

---

THE COMBO

pg_cron schedules → inserts into jobs table → workers process

No external scheduler.
No cron on server.
All in PostgreSQL.

---

WHERE TO GET PG_CRON

✅ Supabase: Built-in
✅ Neon: Available
✅ Railway: Available
✅ Render: Available
✅ Self-hosted: apt install postgresql-14-cron

Most managed PostgreSQL has it now.

---

What do you need to schedule?

#January2026

📊 WEEK 2 CHECK-IN

Background jobs week: done.

---

THIS WEEK

Mon: Why PostgreSQL for job queues
Tue: How FOR UPDATE SKIP LOCKED works
Wed: Complete job queue system 💰
Thu: pg_cron for scheduling

---

THE REDIS QUESTION

"But Redis is faster for queues!"

Sure. Redis is faster.
But is your bottleneck really queue speed?

For 99% of apps:
- PostgreSQL queue: Fast enough
- One less service: Priceless
- Simpler stack: Fewer bugs

Optimize when you need to.
Not before.

---

WHAT DID YOU IMPLEMENT?

- ⚡ Set up a job queue
- ⏰ Added pg_cron schedules
- 📚 Just learning for now
- 🤔 Still thinking about it

---

NEXT WEEK PREVIEW

Making PostgreSQL fast.

Not "optimization theory."
Actual indexes. Actual queries.
Before/after benchmarks.

The stuff that makes your app stop being slow.

---

MONTH CHECK-IN

Week 1: Auth ✓
Week 2: Background jobs ✓
Week 3: Performance (coming)
Week 4: ???

What should Week 4 be?

Reply with requests.

---

See you Monday.

#January2026

🐢 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