PostgreSQL
PostgreSQL LISTEN/NOTIFY: How Far It Goes, and When to Move to a Message Queue
Sorting out the practical range of PostgreSQL's built-in pub/sub. How notifications ship at commit time as part of the transaction, the 8,000-byte payload cap and the send-only-keys principle, the volatility of notifications with no listener, the commit-serialization bottleneck at high publish volume, the resulting standard combination — notifications as the wake-up signal, work in a SKIP LOCKED table queue — and the criteria for moving to a real message queue.
PostgreSQL Row-Level Security: Making the Database Enforce Multi-Tenant Isolation
A practical introduction to row-level access control (RLS). The structure that stops the worst multi-tenant accident — a missing WHERE tenant_id — with database enforcement instead of application discipline, the basic form of ENABLE ROW LEVEL SECURITY and CREATE POLICY, the standard current_setting pattern for per-request tenant selection and its connection-pool caveat, the performance condition (an index on tenant_id), and the cases where not using RLS is the better call.
timestamptz Pitfalls: The Easy Mistakes in PostgreSQL Time Handling
A collection of practical traps in PostgreSQL time handling. The misconception that timestamptz stores a time zone, why results look different depending on the session TimeZone, the dual behavior of AT TIME ZONE that changes depending on input type, how date_trunc and daily grouping change with where midnight is anchored, why future appointments need more than timestamptz (time zone rules change), and now() versus clock_timestamp().
UUID vs bigint Primary Keys: What the Performance Gap Really Is, and uuidv7
Settling the perennial UUID-vs-bigint primary key debate with measurable grounds. Why bigint is the default (size, insert locality, join cost), the mechanism by which UUID v4 scatters the index and hurts writes and cache efficiency, how time-ordered uuidv7 resolves most of that and PostgreSQL 18's built-in function, uuidv7's remaining trade-offs (creation-time exposure, 16 bytes), and the internal-bigint-plus-external-UUID hybrid design.
The N+1 Query Problem: Symptoms, Detection, and Fixes
N+1 queries, the most common culprit when ORM-backed services slow down. How lazy loading builds the structure where one listing fires 101 queries, detection via application logs and the calls column of pg_stat_statements, the three fixes (JOIN, batched IN loading, and the ORM's eager-loading options), and the prevention principle of designing for the shape of data a screen needs rather than counting queries afterward.
PostgreSQL Upsert Done Right: ON CONFLICT Usage and Traps
Handling "update if present, insert if not" without race conditions using ON CONFLICT. Why the SELECT-then-INSERT approach breaks under concurrency, how to use DO NOTHING and DO UPDATE, referencing new values with EXCLUDED, why the conflict target (a unique constraint) should be named explicitly, the cost of rows being locked and rewritten even when nothing changed, the relationship with MERGE, and the sequence-gap phenomenon.
pgvector in Practice: What to Know When Running Vector Search on PostgreSQL
A practical guide to running embedding search on PostgreSQL without a separate vector database. The vector type and distance operators, why HNSW is the default index and how it compares to IVFFlat, the underfetching problem when combined with filters and iterative index scans (0.8.0), halfvec for half the size and 4,000 dimensions, and the criteria for when a dedicated vector database actually becomes necessary.
PostgreSQL vs MySQL: Which One for a New Project in 2026
Sorting out the choice between the two open-source RDBMSs. The conclusion: new projects default to PostgreSQL unless there is a specific reason not to, while teams already standing on the MySQL ecosystem usually have no reason to switch. Covers the timing of MySQL 8.0 end of support (April 2026) and the 8.4 LTS transition, the difference in feature breadth (JSONB, window functions, extensions), MVCC implementation differences, replication-ecosystem maturity, and the cases where either choice is fine.
PostgreSQL in Practice #9 Backups, PITR, Upgrades: The Art of Rewinding to Just Before the Incident
The practice series closes with the last layer that protects data. Physical backups (pg_basebackup) combined with WAL archiving to get past pg_dump's limits, how PITR (Point-In-Time Recovery) rewinds to a chosen moment and setting the recovery target, the role split of "a delete accident cannot be undone by failover" between replication and backups, the three roads of a major version upgrade (pg_dump, pg_upgrade, logical replication), and a retrospective of the whole track.
PostgreSQL in Practice #8 Replication and High Availability: Streaming Replication, Read Scaling, Failover
Replication, the standard means of getting past a single server's limits. How streaming (physical) replication ships WAL as-is, replication lag under the asynchronous default and the read-your-writes problem, the trade-offs of synchronous replication, cautions when spreading read load to standbys, what automated failover actually requires (Patroni and friends) and split-brain, where logical replication fits with its per-table selection, and how it all maps onto managed services.
PostgreSQL in Practice #7 Partitioning: Splitting Giant Tables by Time
Declarative partitioning, the standard prescription for tables in the hundreds of millions of rows. What partitioning actually solves (deleting old data becomes a single DROP, partition pruning, spreading the VACUUM burden) and what it does not, the RANGE syntax for monthly tables, the constraint that the partition key must be part of the primary key, what query conditions enable pruning, the operational duty of creating future partitions and pg_partman, and the criteria for when to adopt it.
PostgreSQL in Practice #6 Locks and Concurrency: Deadlocks, DDL Locks, SKIP LOCKED
The territory MVCC does not solve: locking. The two layers of row locks (write-vs-write conflicts) and table locks (DDL), tracing lock waits with pg_locks and pg_blocking_pids, the structure that produces deadlocks (cross-order updates) and the prevention rule (always lock in the same order), making read-decide-write safe with SELECT FOR UPDATE, and the standard work-queue pattern FOR UPDATE SKIP LOCKED.