Tag
43 articles tagged #Database.
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.03HyperLogLog counts millions of unique visitors in about 12 KB with roughly 0.81% error. How it works, a Python sketch, and Redis PFADD in production.
P.04Beta 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.05PostgreSQL 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.06GraphQL resolvers hide N+1 queries differently than an ORM does. Why it happens per field, per request, and how DataLoader's batch-and-cache fixes it.
P.07Postgres 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.08Offset pagination breaks under concurrent writes and gets slower on every page. Cursor pagination fixes both, at the cost of features you might actually need.
P.09Shared database or one per customer? Most SaaS teams pick wrong for their stage and pay later. How the three patterns trade off, and which one you need.
P.10Random UUIDs wreck index locality; auto-increment leaks counts and won't shard. What UUIDv7, ULID, and Snowflake each give you, and the real tradeoffs.
P.11Polling misses deletes, adds load, and always lags. CDC reads the write-ahead log instead, turning every insert, update, and delete into an event stream.
P.12Three tools solve three different versions of "this query is slow." How materialized views, read replicas, and caches differ, and how to pick one.
P.13PostgreSQL 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.14A Bloom filter answers one question in almost no memory: is this definitely absent, or possibly present. How it works, and why false positives are a feature.
P.15Prepared 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.16An 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.17Four philosophies for one problem: moving a schema from what it is to what it should be, safely, in a team. How they differ and which fits your stack.
P.18Pessimistic locking stops one request from starting; optimistic lets both run and catches the conflict at the end. How each works, with SQL, and how to pick.
P.19Soft delete sounds like the safe default, but it quietly breaks unique constraints, foreign keys, and query performance unless you design for it up front.
P.20Partitioning and sharding get confused constantly. Partitioning stays on one server. How range, list, and hash work, and when it solves it before sharding.
P.21Most developers use whatever their ORM defaults to. What Read Committed, Repeatable Read, and Serializable prevent, what they allow, and how to choose.
P.22A 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.23MySQL 9 shipped a native VECTOR type and distance functions, no extension needed. How it compares to Postgres plus pgvector for real semantic search.
P.24PostgreSQL 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.25Event sourcing stores every change as an immutable event and replays them for current state. What that buys, and the operational cost most systems skip.
P.26CAP theorem forces a consistency-versus-availability choice during a network partition, not all the time. Here is what it constrains and how to pick a model.
P.27One developer rebuilt Postgres in Rust with AI agents in under three months, and pgrust now matches 18.3 across 46,000+ regression queries. What it isn't.
P.28Read replicas are the standard fix for read load, but they open a gap between writing data and reading it back. What lag breaks, and how to design around it.
P.29An N+1 bug turns one page load into hundreds of round trips. What causes it, how to spot it in Django, Rails, and Prisma, and the eager-loading fixes.
P.30PostgreSQL 19 Beta 1 adds REPACK, which rewrites bloated tables without a maintenance window, plus parallel autovacuum and smarter async I/O.
P.31A 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.32ClickHouse answers queries over billions of rows in seconds that take Postgres minutes. Here's how it works and what to know before adopting it.
P.33SQLite runs in every phone and browser. Turso extends it to the server and edge with replication, branching, and a network protocol. Here's when it makes sense.
P.34Most 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.35The queries that wreck production Postgres share a few root causes. How to find them, read EXPLAIN ANALYZE properly, and fix them without guessing.
P.36Neon 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.37Connection 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.38Add similarity search to your existing Postgres database using pgvector. Real setup, indexing strategies, and when you actually need a dedicated vector database.
P.39The 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.40Running totals, rankings, moving averages, and lag comparisons without application loops or self-joins. How window functions actually work, in Postgres.
P.41Drizzle is the TypeScript-first alternative to Prisma: schema in TS, migrations in SQL, no query engine binary. How it works, and when to pick it.
P.42ALTER 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.
P.43How we built QueryLytic, an AI tool that turns plain English into validated queries for PostgreSQL, MySQL and MongoDB, cutting time-to-insight to seconds.