Tag
28 articles tagged #PostgreSQL.
P.01PostgreSQL 14 gets its last release on November 12, 2026. Pick 18 (or 17), choose pg_upgrade or logical replication, and fix what breaks between 14 and 18.
P.02Two-phase commit makes several databases commit or abort together. How prepare and commit work, why a coordinator crash blocks you, and Postgres examples.
P.03MVCC keeps several versions of each row so reads never wait on writes. See xmin and xmax in a live Postgres session, and why long transactions cause bloat.
P.04Postgres LISTEN/NOTIFY pushes events to connected clients with no extra infrastructure. Working Node.js code, a trigger, and the limits that bite in production.
P.05A write-ahead log makes a database durable by recording changes before touching data files. How WAL commits, recovers, replicates, and what it trades.
P.06Beta 4 reverted six features, including SQL/PGQ graph queries, and PostgreSQL 19 turns JIT off by default. What to test before the October release.
P.07PostgreSQL 19 beta adds ON CONFLICT DO SELECT, GROUP BY ALL, FOR PORTION OF and JSON output for COPY. Runnable examples and what each one replaces.
P.08A practical RBAC pattern for Node.js and Postgres: roles, permissions, a middleware that checks both, and the traps that turn clean RBAC into unmaintainable spaghetti.
P.09Postgres won't convert a live table into a partitioned one in place. Here's the CHECK-constraint trick that avoids a full-table rewrite and a long lock.
P.10PostgreSQL 18.6, 17.11, 16.15, 15.19, and 14.24 fixed 28 CVEs on August 13, 17 of them CVSS 8.0 or higher. Which matter, and who's actually exposed.
P.11Prepared statements and parameterized queries stop SQL injection, as the OWASP cheat sheet says. Where ORMs quietly reintroduce it, and how to check your code.
P.12An index turns a table scan into a few comparisons, but only for the queries it was built for. How B-trees work, and the write cost nobody budgets for.
P.13Partitioning and sharding get confused constantly. Partitioning stays on one server. How range, list, and hash work, and when it solves it before sharding.
P.14Most developers use whatever their ORM defaults to. What Read Committed, Repeatable Read, and Serializable prevent, what they allow, and how to choose.
P.15A practical comparison of the four vector databases teams actually shortlist for RAG, with real pricing, when each wins, and the question that decides it.
P.16MySQL 9 shipped a native VECTOR type and distance functions, no extension needed. How it compares to Postgres plus pgvector for real semantic search.
P.17PostgreSQL 18 is on 18.4 and 19 is still beta, so 18 is what you should run today. What async I/O, UUIDv7, and virtual generated columns actually buy you.
P.18PostgreSQL 19 Beta 1 adds REPACK, which rewrites bloated tables without a maintenance window, plus parallel autovacuum and smarter async I/O.
P.19A database engineer designs schemas and tunes queries; a data engineer builds pipelines. How to screen for the one who keeps your app fast, plus 2026 rates.
P.20Most slow queries come from a small set of fixable problems: missing indexes, N+1 patterns, and over-fetching. This is the practical diagnostic and fix guide.
P.21Row Level Security moves tenant isolation into the database itself. How to set it up for a multi-tenant SaaS, handle the edge cases, and avoid the traps that break it.
P.22The queries that wreck production Postgres share a few root causes. How to find them, read EXPLAIN ANALYZE properly, and fix them without guessing.
P.23Neon separates storage from compute so Postgres can branch like git. Here's how the branching model works and when it's worth migrating for.
P.24Connection exhaustion is a common production failure at scale. Here's how pooling actually works, which tool fits which setup, and the config decisions that matter.
P.25Add similarity search to your existing Postgres database using pgvector. Real setup, indexing strategies, and when you actually need a dedicated vector database.
P.26The multi-tenancy model you pick early is hard to change later. Compare database-per-tenant, schema-per-tenant, and row-level security by cost and isolation.
P.27Running totals, rankings, moving averages, and lag comparisons without application loops or self-joins. How window functions actually work, in Postgres.
P.28ALTER TABLE locks your database. Your migration takes longer than expected. Users get errors. Here's how to handle schema changes that don't interrupt production traffic.