Crunchy Data

Filtered vector search in Postgres forces a tradeoff between recall and speed because ANN indexes and WHERE clauses do not intersect like B-trees. Read about pgvector iterative scans, partial HNSW indexes, oversampling with pg_stats, caching, and more.

With Postgres 19 switching the default from pglz to LZ4, this post walks through the compression decision path, storage strategies, and index-size limits that shape real-world behavior.

This year, British Column has moved to year-round Pacific Time. How does that affect date data?

Integer overflow can happen if you have a sequencing data type exceeding integer limits. Jesse has a query to help you spot it and recommendations for a short term and long term fix.

Postgres 18 defaults to checksums on. This is a good feature for data integrity but might catch you off guard with an upgrade.

What is a sequential scan vs index scan vs parallel scan .... and what is a bitmap heap scan? Postgres scan types explained and diagrammed.

Postgres 18 defaults to checksums on. This is a good feature for data integrity but might catch you off guard with an upgrade.

Get under the hood of Postgres by looking at psql, system views, and internal tables.

How do you return the Nth related record from joined tables in PostgreSQL? Did you know that it has a few more options than other databases? Use DISTINCT ON and window functions that solve common temporal join challenges and avoid N+1 query problems.

A query to find out if Postgres is read heavy or write heavy and tips for optimizing Postgres for both read and write workloads.