Databases
Choosing a database, designing tables, migrations, indexes, connection pooling, and backups you have actually restored.
62 posts · page 1 of 3
What Is MongoDB? A Beginner's Guide to Document Databases
MongoDB stores data as flexible JSON-like documents instead of tables. How it works, what collections and documents are, where it shines, where Postgres is the better pick, and why AI tools sometimes reach for it.
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 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 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 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.
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.
ECONNREFUSED 127.0.0.1:5432: Why Your App Can't Reach the Database
"connect ECONNREFUSED 127.0.0.1:5432" means nothing was listening where your app tried to connect. The causes: database not running, wrong host inside Docker, wrong port, listening on the wrong address, or a firewall. How to find which, plus the related timeout and authentication errors.
Database Normalization Explained: 1NF, 2NF, 3NF in Plain English
Normalization means storing each fact once, so updates can't leave your data contradicting itself. What 1NF, 2NF and 3NF actually require, with one example table taken through each step, the anomalies they prevent, and when deliberately denormalizing is the right call.
What Is Supabase? A Beginner's Guide to the Backend Behind Many AI-Built Apps
Supabase gives your app a Postgres database, logins, file storage and serverless functions from one dashboard. What each part does, how the publishable and secret keys work, why row-level security matters, free-plan limits, and when to use something else.
What Are Embeddings? How AI Turns Meaning Into Numbers
An embedding is a list of numbers that captures what a piece of text means, so a computer can find similar things. How embeddings work, what they're used for (search, RAG, recommendations), how to store them, and practical tips on models, dimensions and cost.
How to View Your Postgres Database: psql, GUIs, and VS Code
Want to see what's actually in your app's database? How to look inside Postgres with psql, desktop tools like pgAdmin, DBeaver and TablePlus, VS Code extensions, and your ORM's studio — plus how to connect safely to a database on a server.
UUID vs Auto-Increment IDs: Which Primary Key Should You Use?
Sequential integers or UUIDs for your primary keys? The real trade-offs — size, index performance, guessability, merging data, leaking business metrics — why UUIDv7 changes the answer, Postgres 18's uuidv7(), and the common hybrid of internal IDs plus public IDs.
Is SQLite Good Enough for Production? When It Works and When It Doesn't
SQLite now runs real production apps. When a single-file database is a great choice, the settings you must change (WAL mode, busy timeout, foreign keys), backups with Litestream, the single-writer limit, and the hosting setups where SQLite will lose your data.
SQL Joins Explained Simply: INNER, LEFT, RIGHT, and FULL JOIN
A beginner's guide to SQL joins with one small example database: what a join does, INNER JOIN vs LEFT JOIN with real output, RIGHT and FULL joins, joining three tables, and the classic mistakes — duplicated rows and WHERE clauses that cancel a LEFT JOIN.
Role-Based Access Control (RBAC) for Your App: A Practical Guide
How to add roles and permissions to a web app without making a mess: roles vs permissions, a simple database schema, checking permissions on the server, multi-tenant roles per organisation, enforcing in the UI and the API, and testing it.
Primary Key vs Foreign Key: What's the Difference?
Primary keys identify each row; foreign keys link rows between tables. What each one does, examples in SQL, composite and unique keys, what ON DELETE CASCADE really means, and why AI-generated schemas sometimes skip foreign keys — and why you shouldn't.
PostgreSQL vs MySQL: Which Database Should You Choose?
Postgres and MySQL are the two most popular open-source databases. How they differ on features, JSON, extensions, performance, hosting and ecosystem — and why most new apps (and AI app builders) default to Postgres.
Postgres VACUUM and Table Bloat: How It Works and How to Keep It Under Control
Why Postgres tables bloat, what VACUUM and autovacuum actually do, tuning autovacuum for large tables, what blocks cleanup (long transactions, replication slots), transaction ID wraparound, and how to reclaim space without VACUUM FULL's exclusive lock.
Postgres Table Partitioning: When It Helps, When It Hurts, and How to Do It
Declarative partitioning in PostgreSQL: range, list and hash partitions, partition pruning, primary key and unique constraint rules, dropping old data instantly, automating new partitions, converting an existing table, and the cases where partitioning makes things slower.
Reading Postgres EXPLAIN ANALYZE: A Practical Guide to Query Plans
How to read a Postgres query plan: EXPLAIN vs EXPLAIN ANALYZE, BUFFERS, costs vs actual times, loops, scan and join types, spotting bad row estimates, sorts spilling to disk — plus pg_stat_statements and auto_explain for finding the queries worth fixing.
Postgres "Deadlock Detected": Why It Happens and How to Prevent It
ERROR: deadlock detected. How Postgres deadlocks happen, how to read the log detail, the common causes — inconsistent lock ordering, batch updates, foreign keys, upserts — and the fixes: consistent ordering, shorter transactions, explicit locking, and safe retries.
Postgres Connection Strings Explained: Format, Examples, and Common Errors
What every part of a PostgreSQL connection string (DATABASE_URL) means, how to write one, special characters in passwords, sslmode options, pooled vs direct connections (including Supabase's ports), and how to fix the errors people hit most.
Postgres Advisory Locks: Distributed Locking Without Redis
Advisory locks let your application lock arbitrary things — a job, a customer, a migration — using Postgres. Session vs transaction locks, blocking vs try-locks, turning strings into lock keys, the connection-pooler trap, and patterns for singleton cron jobs and per-entity mutexes.
pgvector Tutorial: Vector Search in Postgres for RAG and Semantic Search
Add semantic search and RAG to your app without a separate vector database. A hands-on pgvector guide: install the extension, store embeddings, query by cosine distance, add HNSW indexes, filter results correctly, choose dimensions and halfvec, and know when you've outgrown it.