mirror of
https://github.com/karatektus/pl_mail.git
synced 2026-10-25 01:54:10 +00:00
Paul LütznerandClaude Opus 5
2348b75621
The original rejection of pgvector was tested rather than reasoned, and it stands: pgvector/pgvector:pg18 means musl to glibc on an existing data volume, and 44 collation-dependent indexes — 18 of them UNIQUE — are then walked with the wrong comparator, silently, because musl records no collation version so Postgres's own mismatch check never fires. So the extension is compiled INTO the alpine image instead. libc never changes, and there is nothing to rebuild. Verified end to end rather than argued: an existing volume migrated on stock alpine, stopped, restarted on the pgvector image, then bt_index_check(heapallindexed => true) across 175 btree indexes — zero errors. Two build flags carry more weight than they look. OPTFLAGS="" because pgvector's Makefile defaults to -march=native, which bakes the build host's instruction set into vector.so and gives SIGILL mid-query on a host without it, with an empty log. with_llvm=no because a missing .bc file makes the JIT skip inlining silently, and inlining does not apply to index scans anyway. Pinned at v0.8.6, not the earliest that works. v0.8.0 does not compile against Postgres 18 at all (vacuum_delay_point gained a parameter); v0.8.1 is the first that does. But 0.8.3 fixed possible index corruption with HNSW vacuuming and 0.8.4 fixed an unrepaired HNSW graph, and message_embedding is ON DELETE CASCADE from message on a system that deletes mail in bulk — so vacuum is a hot path here and shipping the earliest that merely compiles would ship two known corruption bugs. THE HANDBOOK'S 24x WAS WRONG AND IS CORRECTED TO 1.75x. The SIMD dot product is microseconds; what dominates is that embedding is real[], so every row pays a 4KB detoast and a fresh real[] to vector conversion. 74,000 rows: 7.70s becomes 4.51s. NO INDEX IS CREATED, and that is the finding rather than an omission. An HNSW index answers ORDER BY ... LIMIT k and the search asks distance <= 0.45, which no ANN index serves. The expression form needed to build one at all is (embedding::vector(1024)), which Postgres treats as a DIFFERENT expression from the inlined function's (embedding::vector) — so the index exists, the planner ignores it, and nothing says so. And it costs 7m40s and 578MB to build at the maintenance_work_mem this stack ships, behind the one advisory lock every entrypoint waits on: an eight-minute dead stack, not a slow migration. The measured route to an index is written down in ai-assist.md rather than half taken. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>