pgvector Index Tuning: What Actually Matters If you're running vector search on Postgres with pgvector, three decisions determine whether you get a search service you can reason about or one that quietly lies to you: which index type you pick, how you tune it, and how you handle filtered queries. Get the first two wrong …
Intuition is what gets you a two-hour outage. You "have a feeling" it's the connection pool, you bounce the app tier, it doesn't help, and now you've lost fifteen minutes plus whatever evidence was sitting in the app logs. The fastest path back is a fixed sequence run in a fixed order: disk, connections, OOM, …
pg_cancel_backend vs pg_terminate_backend: a DBA's runbook It was 02:14 on a Tuesday. A migration job fired an UPDATE against a 40M-row orders table without a WHERE clause narrow enough to matter, took an exclusive row lock on half of it, and the checkout path started queueing behind it. PagerDuty went off at the twelve-minute mark. …
Postgres moves any row value over roughly 2KB (TOAST_TUPLE_THRESHOLD) out of the main table and into a hidden pg_toast table, compressing or splitting it along the way. That's TOAST — The Oversized-Attribute Storage Technique — and it's why a table full of jsonb or text columns can behave very differently from a table of integers, …
What Postgres Row-Level Security Actually Does Row-level security (RLS) lets Postgres filter and check rows per query, per role, based on a policy you define in SQL. Instead of adding WHERE tenant_id = :current_tenant to every query in every service, you write the rule once and the database enforces it — on your API, your …
A team I worked with added Elasticsearch for a search box over a documents table with roughly 400,000 rows. Six weeks later they owned a CDC pipeline, a nightly reindex job that nobody could run in business hours, a queue that backed up whenever someone bulk-edited a category, and a support ticket class I still …
"No pg_hba.conf Entry for Host": What the Error Actually Means and How to Fix It If Postgres just handed you FATAL: no pg_hba.conf entry for host "10.0.2.15", user "app", database "orders", SSL off, here's the short version: your connection reached the server, but no line in pg_hba.conf matched the client address, database, user, and encryption …
Your query is slow. Postgres already told you why. The usual sequence when something goes sideways: someone pastes a slow query into Slack, someone else runs EXPLAIN, a third person SSHes into the box and stares at top. Forty minutes later you know the plan is fine and the CPU is at 30% and you …
Postgres Replication Lag Is Four Numbers, Not One The page comes in at 03:14: "replica is lagging." That sentence tells you nothing useful. Lagging where? Postgres doesn't have a lag counter. It has a pipeline with four measurable checkpoints, and the primary exposes all four side by side in pg_stat_replication. Know which gap is growing …
UUID vs BigInt Primary Keys in Postgres If you're choosing between bigint and uuid for a primary key, the short answer is: bigint (via bigserial or generated always as identity) wins on raw performance and index size, but uuid wins on distributed generation and merge-friendliness. The real decision hinges on how your data is inserted …