Databases
Choosing a database, designing tables, migrations, indexes, connection pooling, and backups you have actually restored.
112 posts · page 2 of 5
Semantic Search vs Keyword Search: What's the Difference?
Keyword search matches the words people type; semantic search matches what they mean, using embeddings and cosine similarity. How each works, where each fails, why hybrid search usually wins, and how to build both in Postgres.
Chunking for RAG: How to Split Documents So Retrieval Works
How you split documents into chunks decides what a RAG system can retrieve. Fixed-size, recursive, structure-aware and semantic chunking, choosing chunk size and overlap, adding context to chunks, parent-child retrieval, chunking code and tables, and how to evaluate it.
Prisma Migrations in Production: migrate dev vs migrate deploy
How to run Prisma migrations safely: migrate dev locally, migrate deploy in production, why db push and migrate reset don't belong near real data, where to run migrations in CI/CD, handling failed migrations with migrate resolve, baselining an existing database, and avoiding destructive changes.
Postgres Window Functions: Running Totals, Rankings and Top-N per Group
Window functions compute values across related rows without collapsing them like GROUP BY. OVER, PARTITION BY and ORDER BY explained, with practical queries: ranking, top-N per group, running totals, moving averages, comparing with the previous row (LAG), and frames.
The Postgres Write-Ahead Log (WAL) Explained
Every change in Postgres is written to the write-ahead log before the data files. How WAL gives durability and crash recovery, LSNs and segments, checkpoints and full-page writes, synchronous_commit, wal_level, archiving and replication, and the replication-slot trap that fills disks.
Postgres Upsert: INSERT ... ON CONFLICT (and MERGE) Explained
Insert a row, or update it if it already exists, atomically. ON CONFLICT DO NOTHING and DO UPDATE with EXCLUDED, conflict targets and partial indexes, conditional updates, bulk upserts, RETURNING, MERGE, the race conditions a select-then-insert has, and upserts in Prisma and Drizzle.
Postgres Triggers: Automatic Actions on Insert, Update and Delete
A trigger runs a function automatically when rows change. How to write trigger functions in PL/pgSQL, BEFORE vs AFTER and row vs statement triggers, practical examples — updated_at, audit logs, denormalised counters, validation — and the pitfalls that make triggers hard to debug.
Postgres TOAST: How Large Values Are Stored (and Why It Affects Performance)
Postgres pages are 8 KB, so large values are compressed and moved out of line into a TOAST table. The ~2 KB threshold, storage strategies (PLAIN, MAIN, EXTERNAL, EXTENDED), pglz vs lz4 compression, measuring TOAST size, and the performance traps with SELECT *, big JSONB documents and frequent updates.
Postgres Timeouts: statement_timeout, lock_timeout and Friends
Postgres ships with every timeout disabled, so a stuck query or forgotten transaction can hold locks forever. What statement_timeout, lock_timeout, idle_in_transaction_session_timeout, transaction_timeout and idle_session_timeout do, the lock-queue outage they prevent, safe values, and where to set them.
Postgres Roles and Permissions: CREATE USER, GRANT, and Least Privilege
Most apps connect to Postgres as a superuser, so one SQL injection or rogue AI command can drop everything. How Postgres roles, privileges, schemas and default privileges work, and a practical setup with separate owner, app and read-only roles.
Postgres "ERROR: relation does not exist": Causes and Fixes
Postgres can't find the table you named. The usual causes — migrations not run, connected to the wrong database, uppercase names and double quotes, the table is in a different schema, or permissions — and how to check each with psql.
Postgres Read Replicas: Streaming Replication, Lag, and Read-Your-Writes
How Postgres physical streaming replication works, sync vs async, measuring replication lag, query conflicts and hot_standby_feedback, routing reads in your app without breaking read-your-writes, replication slots that fill disks, and when a replica is the wrong fix.
Postgres Planner Statistics: Why the Query Plan Is Wrong
Postgres picks query plans from statistics about your data. When they're stale or too coarse, row estimates are off by orders of magnitude and the planner chooses a terrible plan. How ANALYZE samples tables, reading pg_stats, default_statistics_target, correlated columns and CREATE STATISTICS, and fixing misestimates.
Postgres "password authentication failed for user": How to Fix It
Postgres rejected the username or password your app sent. The real causes — wrong password in the connection string, special characters not URL-encoded, a Docker volume that kept an old password, the wrong user or host, pg_hba.conf rules — and how to reset the password safely.
Postgres MVCC Explained: Tuples, xmin/xmax, Snapshots and Why VACUUM Exists
How PostgreSQL lets readers and writers work without blocking each other: every UPDATE writes a new row version, transactions see a snapshot, and visibility is decided by xmin and xmax. See it with real queries, and how it explains bloat, VACUUM, HOT updates, long transactions and wraparound.
Postgres Memory Tuning: shared_buffers, work_mem and the Rest
Postgres's memory settings are conservative by default and easy to get dangerously wrong. How shared_buffers and the OS page cache work together, why work_mem is per operation not per query, maintenance_work_mem, effective_cache_size, huge pages, and worked settings for 4, 8 and 16 GB servers.
Postgres Materialized Views: Fast Reports Without Slowing Your App
A view is a saved query; a materialized view also saves the result. When materialized views make dashboards and reports fast, REFRESH vs REFRESH CONCURRENTLY, indexing them, scheduling refreshes with pg_cron or your job runner, staleness, and alternatives like summary tables.
Postgres Index Types: B-tree, GIN, GiST, BRIN, Hash — and When Each Wins
Postgres has more index types than most databases, and choosing well can turn a sequential scan into milliseconds. How B-tree, GIN, GiST, SP-GiST, BRIN and hash indexes work, which operators each supports, partial and expression indexes, covering indexes, and how to verify the planner uses them.
Postgres HOT Updates and Fillfactor: Cheaper UPDATEs
Every Postgres UPDATE writes a new row version — and normally new entries in every index. Heap-only tuple (HOT) updates skip the index work. When HOT applies, why indexed columns and full pages prevent it, tuning fillfactor, measuring the HOT ratio, and designing hot tables to stay HOT.
Postgres CTEs (WITH Queries): Readable SQL, Recursion and Data-Modifying CTEs
Common table expressions let you name subqueries and build complex SQL step by step. Basic WITH queries, recursive CTEs for trees and hierarchies, data-modifying CTEs that insert or delete and return rows, MATERIALIZED vs NOT MATERIALIZED, and when a CTE affects performance.
Change Data Capture in Postgres: Streaming Every Row Change
Change data capture streams every insert, update and delete out of Postgres as it happens. How logical decoding and replication slots work, publications and pgoutput, REPLICA IDENTITY, Debezium and lighter alternatives, the outbox pattern, and the operational traps — retained WAL, schema changes and failover.
pg_stat_statements: Find the Queries Slowing Down Your Postgres
pg_stat_statements records statistics for every distinct query your database runs — calls, total and mean time, rows, cache hits. How to enable it, the queries to find what costs the most, interpreting the results, resetting, and turning findings into indexes and fixes.
Optimistic vs Pessimistic Locking: Preventing Lost Updates
Two users edit the same record and one silently overwrites the other. Pessimistic locking (SELECT FOR UPDATE) blocks the second writer; optimistic locking (a version column) detects the conflict. How each works, code for both, atomic updates that avoid locks entirely, and how to choose.
ON DELETE CASCADE Explained (and When Not to Use It)
When you delete a row that other rows reference, the foreign key decides what happens: block the delete, cascade it, or set the reference to NULL. How CASCADE, RESTRICT, SET NULL and NO ACTION work, examples, the risks of cascading, and how to change an existing constraint.