Databases
-
TIL: sqlite3 .backup is way better than .dump
The online backup API produces a consistent copy without locking writers.
-
Tuning pgvector HNSW without giving up
Defaults are fine for toys. Here is what I changed to make pgvector work on a real dataset.
-
Migrating to SQLite STRICT tables: mostly boring, sometimes not
We turned on STRICT mode on a decade-old SQLite schema. Here is what broke.
-
TIL: DuckDB's COPY reads from S3 URLs directly
Point DuckDB at an s3:// URL and it will read Parquet files without downloading them first.
-
Autovacuum tuning, one table at a time
Global autovacuum settings are a lie. I tune per-table now.
-
Logical replication slot lag ate our WAL
A forgotten logical replication slot accumulated 380GB of WAL before we caught it. Here's what we changed.
-
When GIN on JSONB is wrong
The default JSONB index in Postgres is GIN, and there's a very common case where that's the wrong choice.
-
TIL: pg_try_advisory_lock for cheap, per-key mutual exclusion
Turns out Postgres has application-level locks you can use without a lock table.
-
pgbouncer transaction pooling broke our prepared statements
A multi-day outage-adjacent incident caused by prepared statements not making it across pool boundaries.
-
Partition attach locks and the bloat we didn't see coming
We moved a 1.2TB table to native range partitioning. The migration was the easy part.
-
What they don't tell you about SQLite WAL in production
WAL mode is a huge usability win for SQLite, but it has teeth that bite during backups, fsync, and NFS.
-
Notes: pgvector HNSW m parameter rule of thumb
Turns out 'just pick m=16' is a surprisingly reasonable default, and I've been overthinking it.