--- title: PostgreSQL migration lock patterns catalogued,... description: A detailed breakdown of 10 PostgreSQL migration patterns that cause table locks is worth bookmarking — the safe rewrites are concrete and actionable. A deep... canonical: https://daily.dev/posts/postgresql-migration-lock-patterns-catalogued-gin-fuzzy-search-limit-silently-breaks-aggregations-ysama0tmt twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: PostgreSQL migration lock patterns catalogued, gin_fuzzy_search_limit silently breaks aggregations | daily.dev og:description: A detailed breakdown of 10 PostgreSQL migration patterns that cause table locks is worth bookmarking — the safe rewrites are concrete and actionable. A deep... og:url: https://daily.dev/posts/postgresql-migration-lock-patterns-catalogued-gin-fuzzy-search-limit-silently-breaks-aggregations-ysama0tmt og:image: https://api.daily.dev/og/posts/ySaMa0TmT.png og:image:alt: PostgreSQL migration lock patterns catalogued, gin_fuzzy_search_limit silently breaks aggregations og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # PostgreSQL migration lock patterns catalogued, gin_fuzzy_search_limit silently breaks aggregations **[Databases Digest](https://daily.dev/sources/databases_digest)** · 4 min read · 0 upvotes · 0 comments ## Summary A detailed breakdown of 10 PostgreSQL migration patterns that cause table locks is worth bookmarking — the safe rewrites are concrete and actionable. A deep dive into gin_fuzzy_search_limit reveals it silently corrupts COUNT, SUM, and EXISTS results with no warning, and the recommendation is to leave it at 0. PostgreSQL 19 Beta 2 is out, and the Scaling Postgres roundup covers new checkpoint control parameters and a planned switch from PGLZ to LZ4 as the default TOAST compression. A Rust rewrite of a Go caching layer cut P99 latency from 50ms to 1.5ms and dropped memory usage 70%. ## Content **TLDR:** A detailed breakdown of 10 PostgreSQL migration patterns that cause table locks is worth bookmarking — the safe rewrites are concrete and actionable. A deep dive into gin_fuzzy_search_limit reveals it silently corrupts COUNT, SUM, and EXISTS results with no warning, and the recommendation is to leave it at 0. PostgreSQL 19 Beta 2 is out, and the Scaling Postgres roundup covers new checkpoint control parameters and a planned switch from PGLZ to LZ4 as the default TOAST compression. A Rust rewrite of a Go caching layer cut P99 latency from 50ms to 1.5ms and dropped memory usage 70%. --- ## 10 PostgreSQL migration patterns that lock tables, with safe rewrites for each The post catalogs dangerous one-step migrations — CREATE INDEX without CONCURRENTLY, SET NOT NULL, ADD FOREIGN KEY without NOT VALID, ADD COLUMN with volatile defaults, ALTER COLUMN TYPE — and explains why each grabs an ACCESS EXCLUSIVE lock that blocks reads and writes. The core pattern throughout: split any operation that combines a fast metadata change with a slow table scan or rewrite into separate steps, each with its own lock window. Lock_timeout best practices and a free CLI/GitHub Action for catching these in review are included. [Read more](https://daily.dev/posts/19NUVUqqN) ## gin_fuzzy_search_limit silently returns wrong aggregation results with no warning PostgreSQL's gin_fuzzy_search_limit tells GIN index scans to return a random subset of matches rather than the full result set — originally designed for full-text search where approximate results are fine. The problem is that GIN now backs jsonb, array, hstore, and trigram searches, and aggregations like COUNT, SUM, and EXISTS silently return wrong answers when this parameter is set. There's no warning, no EXPLAIN indication, nothing. Leave it at 0; if you genuinely need it, set it per session only, never in postgresql.conf. [Read more](https://daily.dev/posts/JW9vFI5nr) ## PostgreSQL 19 Beta 2 out, PGLZ replaced by LZ4 as default TOAST compression The Scaling Postgres roundup confirms PostgreSQL 19 Beta 2 is released. Notable changes include new checkpoint control parameters (mode and flush_unlogged) and a planned switch from PGLZ to LZ4 as the default TOAST compression — a meaningful change for anyone storing large text or jsonb columns. The pg_stat_log extension for aggregating log message statistics requires PG 18+. The roundup also covers MVCC bloat and vacuum, and PostgreSQL's 64-bit timestamp handling of the year 2038 problem. [Read more](https://daily.dev/posts/frO2HmqNI) ## Go GC pauses drove a caching layer rewrite to Rust: P99 dropped from 50ms to 1.5ms A team running a high-throughput in-memory cache in Go hit P99 latency spikes of 50–100ms traced to garbage collector pauses. They rewrote the layer in Rust using DashMap, eliminating GC pauses entirely. P99 latency landed at 1.5ms, memory usage fell 70%, and CPU usage halved. Their conclusion is reasonable: Go is still the default for standard microservices, but for extreme latency-sensitive workloads where GC jitter is measurable, Rust is the right call. [Read more](https://daily.dev/posts/MnZbCRfMJ) --- ## Also notable - **MySQL SKIP LOCKED for concurrent job queues, available since 8.0.1:** MySQL's SKIP LOCKED modifier lets multiple workers drain a job queue table concurrently without deadlocks — Laravel's database queue driver already uses it internally for MySQL 8.0.1+, MariaDB 10.6+, and PostgreSQL 9.5+, with caveats around statement-based replication. [Read more](https://daily.dev/posts/UiVMw3RyP) - **SQL agent accuracy jumped from 13% to 72% with a deterministic pipeline and hybrid RAG:** Replacing a ReAct agent with a 5-step deterministic pipeline and a BM25+MMR hybrid retrieval setup (70/30 split, 94% retrieval accuracy) drove the accuracy gain; standardizing num_ctx=8192 across Ollama components cut per-turn latency from 67 seconds to 2–4 seconds. [Read more](https://daily.dev/posts/WHtfQeqXg) - **Database tech debt triage: four categories and an impact-vs-effort matrix:** Schema, query, index, and constraint debt each have distinct signals — developer table avoidance, nonsensical naming, business logic leaking into application code — and the post frames cleanup as reclaiming developer capacity rather than housekeeping, with a 2x2 matrix for prioritization. [Read more](https://daily.dev/posts/ytbrhDMz5) - **Django 6.0 adds database-level cascades and fetch modes to address N+1 queries:** Carlton Gibson's Talk Python appearance confirms Django 6.0 and 6.1 include a Tasks framework for background work, fetch modes targeting N+1 query problems, and database-level cascades for large-scale deletes — plus a rewrite of async docs that previously scared developers away. [Read more](https://daily.dev/posts/40oqRBJYC) ## Similar posts on daily.dev - [The Postgres migration that locks your table at 3am, and how to catch it in review](https://daily.dev/posts/the-postgres-migration-that-locks-your-table-at-3am-and-how-to-catch-it-in-review-19nuvuqqn) · Medium · 0 upvotes · 0 comments - [Zero-Downtime Postgres Migrations: A 2026 Developer Guide](https://daily.dev/posts/zero-downtime-postgres-migrations-a-2026-developer-guide-drbnzykbh) · Alex CloudStar · 0 upvotes · 0 comments - [How to Use PostgreSQL as a Cache, Queue, and Search Engine](https://daily.dev/posts/how-to-use-postgresql-as-a-cache-queue-and-search-engine-ykurnyxgc) · freeCodeCamp · 90 upvotes · 1 comments --- Tags: [#database](https://daily.dev/tags/database), [#rust](https://daily.dev/tags/rust), [#postgresql](https://daily.dev/tags/postgresql) [View this post on daily.dev](https://daily.dev/posts/postgresql-migration-lock-patterns-catalogued-gin-fuzzy-search-limit-silently-breaks-aggregations-ysama0tmt) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"DiscussionForumPosting","mainEntityOfPage":"https://daily.dev/posts/postgresql-migration-lock-patterns-catalogued-gin-fuzzy-search-limit-silently-breaks-aggregations-ysama0tmt","headline":"PostgreSQL migration lock patterns catalogued, gin_fuzzy_search_limit silently breaks aggregations","text":"A detailed breakdown of 10 PostgreSQL migration patterns that cause table locks is worth bookmarking — the safe rewrites are concrete and actionable. A deep dive into gin_fuzzy_search_limit reveals it silently corrupts COUNT, SUM, and EXISTS results with no warning, and the recommendation is to leave it at 0. PostgreSQL 19 Beta 2 is out, and the Scaling Postgres roundup covers new checkpoint control parameters and a planned switch from PGLZ to LZ4 as the default TOAST compression. A Rust rewrite of a Go caching layer cut P99 latency from 50ms to 1.5ms and dropped memory usage 70%.","url":"https://daily.dev/posts/postgresql-migration-lock-patterns-catalogued-gin-fuzzy-search-limit-silently-breaks-aggregations-ysama0tmt","datePublished":"2026-07-27T04:19:14.283Z","dateModified":"2026-09-13T21:34:21.980Z","author":{"@type":"Organization","name":"Databases Digest","logo":"https://media.daily.dev/image/upload/s--ir1hdV8b--/f_auto,q_auto/v1773839400/logos/databases_digest?_a=BAMAMiiu0","url":"https://daily.dev/sources/databases_digest"},"interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"isPartOf":{"@type":"WebPage","url":"https://daily.dev/sources/databases_digest","name":"Databases Digest"}} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Databases Digest","item":"https://daily.dev/sources/databases_digest"},{"@type":"ListItem","position":3,"name":"PostgreSQL migration lock patterns catalogued, gin_fuzzy_search_limit silently breaks aggregations"}]} ```