Latest posts
PostgreSQL Pro | Database Mastery pinned «https://youtu.be/imBbqzIOQdo #ad»
5 Jul, 05:05
PostgreSQL Pro | Database Mastery
5 Jul, 05:05
https://youtu.be/imBbqzIOQdo#adYouTubeWhich Vector Database Should You Use?Choosing a vector database is not really about picking the “best” logo. The better question is: Where should retrieval live in your product? In this AI Concepts explainer, we look at how to choose between pgvector, Pinecone, Qdrant, Weaviate, Elastics…PostgreSQL Pro | Database Mastery pinned «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…»
1 Jul, 20:04
PostgreSQL Pro | Database Mastery
1 Jul, 19:53
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 checkingYouTubeWhy SQL Can’t Do AI Search — Vector DBs ExplainedEvery 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…PostgreSQL Pro | Database Mastery
19 May, 13:39
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 hardwareWith B-tree index: - Database traverses tree from root → branches → leaves - Million-row table = 3-4 comparisons - Roughly 1msThe 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 columnsYouTubeHow Databases Actually Find Your Data — Why Indexes MatterYou write a SQL query. SELECT * FROM users WHERE id = 42. Simple, right? But behind that one line, the database makes a decision that determines whether your query takes 1 millisecond — or 500. The difference is whether you have an index. This video visualizes…PostgreSQL Pro | Database Mastery
12 May, 14:04
"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 myPostgreSQL Pro | Database Mastery
27 Mar, 06:22
📊 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 rollbackThe 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 → MigrationsThat'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.PostgreSQL Pro | Database Mastery
26 Mar, 12:48
🐌 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.PostgreSQL Pro | Database Mastery pinned «🔐 Complete Monitoring Dashboard — See Everything, Pay Nothing What's inside: 📦 COMPLETE SYSTEM (3 ⭐) 1. HEALTH CHECK VIEW - Single query returns overall database health score (0-100) - Cache hit ratio, connection usage, bloat, replication lag …»
25 Mar, 07:23
PostgreSQL Pro | Database Mastery
25 Mar, 07:23
PostgreSQL Pro | Database Mastery
24 Mar, 07:25
📊 5 queries. Run them once a week. Know exactly what's happening.QUERY 1: TABLE BLOAT AND SIZESELECT 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)PostgreSQL Pro | Database Mastery
23 Mar, 09:33
🔍 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 goingMost 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-inPostgreSQL Pro | Database Mastery
20 Mar, 10:57
↩️ Your migration broke something. Here's how to undo it.Three levels of rollback, from easy to nuclear.LEVEL 1: SCHEMA ROLLBACKYou 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 ROLLBACKPostgreSQL Pro | Database Mastery
19 Mar, 06:50
PostgreSQL Pro | Database Mastery
17 Mar, 06:39
🔒 Some ALTER TABLE commands lock your entire table. Some don't. Know the difference.SAFE — No lock (or very brief lock):-- Add column with no default ✅ ALTER TABLE users ADD COLUMN bio TEXT;-- Add column with DEFAULT (PostgreSQL 11+) ✅ ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true; -- Instant. PG stores the default in the catalog, doesn't rewrite rows.-- Create index without blocking writes ✅ CREATE INDEX CONCURRENTLY idx_users_email ON users(email); -- Takes longer but doesn't lock the table.-- Add a CHECK constraint without validation ✅ ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age > 0) NOT VALID; -- Applies to new rows only. Validate later.-- Validate constraint separately ✅PostgreSQL Pro | Database Mastery
16 Mar, 08:15
💀 The scariest command in production:ALTER TABLE users ...Every solo dev knows the feeling. You need to change your schema. Add a column. Rename a field. Drop a table. And your app is live. Users are active.One wrong migration and: - App crashes - Data disappears - Users see errors - You're rolling back at midnightIt doesn't have to be like this.PostgreSQL has tools that let you change your schema while your app is running. No downtime. No locked tables. No panic.This week:📅 Tuesday — Safe vs dangerous operations (what locks what) 📅 Wednesday — 💰 Zero-downtime migration toolkit (3⭐)PostgreSQL Pro | Database Mastery
13 Mar, 07:04
🤝 When you actually need Redis. No dogma.PostgreSQL caching works great for most solo dev apps. But Redis exists for a reason. Here's where the line is.POSTGRESQL CACHE IS ENOUGH WHEN:✅ You cache hundreds to low thousands of keys ✅ Cache reads happen tens of times per second, not thousands ✅ TTLs are minutes to hours (not sub-second) ✅ You want cached data in your backups ✅ You value simplicity over raw speedCONSIDER REDIS WHEN:⚠️ You need sub-millisecond reads at massive scale PostgreSQL cache: ~1-5ms reads Redis: ~0.1-0.5ms reads Does your app notice the difference? Probably not.⚠️ You're doing pub/sub at high volumePostgreSQL Pro | Database Mastery pinned «🔐 Complete Caching System — Replace Redis With PostgreSQL What's inside: 📦 COMPLETE SYSTEM (3 ⭐) 1. KEY-VALUE CACHE - UNLOGGED cache table with TTL - GET/SET/DELETE functions (same mental model as Redis) - Batch get/set - Auto-cleanup of expired…»
12 Mar, 07:01
PostgreSQL Pro | Database Mastery
12 Mar, 07:01
PostgreSQL Pro | Database Mastery
10 Mar, 08:34
🧰 Three PostgreSQL caching tools you already have.TOOL 1: UNLOGGED TABLESRegular tables write to WAL (write-ahead log) for crash safety. UNLOGGED tables skip that. Faster writes, faster reads. Data lost on crash — which is fine for cache.CREATE UNLOGGED TABLE cache ( key TEXT PRIMARY KEY, value JSONB NOT NULL, expires_at TIMESTAMPTZ NOT NULL DEFAULT now() + interval '1 hour', created_at TIMESTAMPTZ NOT NULL DEFAULT now() );CREATE INDEX idx_cache_expires ON cache(expires_at);-- Write cache INSERT INTO cache (key, value, expires_at) VALUES ('user:123:profile', '{"name":"John"}', now() + interval '15 minutes') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, expires_at = EXCLUDED.expires_at;
Related Channels
Other channels in the same section of the catalogue.
