boringsql.com

# boringSQL

> Practical SQL and PostgreSQL writing by Radim Marek. Deep-dives into Postgres internals (MVCC, buffers, pages, planner, statistics, HOT), production-grade techniques (logical replication, vacuum, indexing, range types, views), and open-source tools for testing and operating Postgres without drama.

boringSQL treats PostgreSQL as the workhorse it is. Posts favor mechanism over marketing — what actually happens on disk, in shared buffers, and inside the planner — and are written for working engineers who have to operate the database in production. Other SQL engines are referenced by category, not by product name.

The author also builds three open-source tools: **RegreSQL** (SQL query regression testing), **Fixturize** (subset and mask production data), and **dryrun** (offline-first PostgreSQL MCP server for AI assistants).

License: Content authored by Radim Marek, © 2024–2026 Clusterity s.r.o., licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Reuse and quotation are welcome with attribution to boringSQL (https://boringsql.com/) and a link to the source post.

## Start here

- [About boringSQL](https://boringsql.com/about/): Who runs the site, what it is, and how to get in touch.
- [Are SQL & databases boring?](https://boringsql.com/posts/boring-sql-and-databases/): The editorial thesis — boring is a feature, not a bug.
- [Newsletter](https://boringsql.com/newsletter/): Monthly dispatches on Postgres internals and tooling.

## PostgreSQL internals — deep dives

- [Introduction to Buffers in PostgreSQL](https://boringsql.com/posts/introduction-to-buffers/): How shared_buffers, dirty pages, and the OS page cache interact.
- [Reading buffer statistics in EXPLAIN output](https://boringsql.com/posts/explain-buffers/): Interpret shared hits, disk reads, temp spills, and planning buffers.
- [Inside PostgreSQL's 8KB page](https://boringsql.com/posts/inside-the-8kb-page/): Byte-level tour of page headers, line pointers, free space, and tuples via pageinspect.
- [PostgreSQL MVCC, byte by byte](https://boringsql.com/posts/postgresql-mvcc-byte-by-byte/): xmin, xmax, snapshots, and the visibility rules behind concurrent reads.
- [HOT updates in Postgres](https://boringsql.com/posts/hot-updates/): How HOT chains skip the index-rewrite tax, with pageinspect and fillfactor.
- [VACUUM at the page level](https://boringsql.com/posts/vacuum-at-the-page-level/): Line pointers to LP_UNUSED, the free space map, the visibility map, and freezing, one phase at a time.
- [TOAST: Where PostgreSQL hides big values](https://boringsql.com/posts/postgresql-toast/): How varlena attributes get compressed and relocated out-of-line, with pageinspect and the four storage strategies.
- [PostgreSQL statistics: why queries run slow](https://boringsql.com/posts/postgresql-statistics/): How the planner uses statistics to estimate row counts, and why stale stats hurt.
- [VACUUM is a lie (about your indexes)](https://boringsql.com/posts/vacuum-is-lie/): Why B-tree indexes fragment even when VACUUM reports a clean table.
- [VIEW inlining in PostgreSQL](https://boringsql.com/posts/view-inlining/): When the planner unfolds a view into the outer query — and when it doesn't.
- [Strong views on PostgreSQL VIEWs](https://boringsql.com/posts/strong-views/): Rewrite rules, attribute numbers, and why every column change is a teardown.
- [Good CTE, bad CTE](https://boringsql.com/posts/good-cte-bad-cte/): How the planner treats CTEs version by version, through PostgreSQL 18.
- [When and why PostgreSQL indexes are ignored](https://boringsql.com/posts/why-postgresql-indexes-are-ignored/): The planner's reasons for skipping a perfectly good index.

## Replication and recovery

- [First steps with logical replication in PostgreSQL](https://boringsql.com/posts/logication-replication-introduction/): Publishers, subscribers, and row-level streaming.
- [Beyond the basics of logical replication](https://boringsql.com/posts/logical-replication-beyond-the-basics/): Operational management, monitoring, logical decoding, and security.

## SQL features and modelling

- [Gentle introduction to window functions](https://boringsql.com/posts/window-functions-introduction/): Rank, running totals, and row-level comparisons.
- [Beyond simple upserts with MERGE](https://boringsql.com/posts/beyond-upserts-with-merge/): Conditional inserts, updates, and deletes in one atomic statement.
- [We need to talk about ENUMs](https://boringsql.com/posts/postgresql-enums/): ENUMs vs CHECK constraints vs reference tables.
- [Beyond start and end: PostgreSQL range types](https://boringsql.com/posts/beyond-start-end-columns/): tstzrange, daterange, int4range, and exclusion constraints.
- [The hidden cost of PostgreSQL arrays](https://boringsql.com/posts/good-bad-arrays/): TOAST traps, GIN vs B-tree, the dangerous ANY() pattern.
- [Text identifiers in PostgreSQL database design](https://boringsql.com/posts/text-identifier-in-db-design/): Readability vs performance trade-offs.
- [Time to better know the time in PostgreSQL](https://boringsql.com/posts/know-the-time-in-postgresql/): TIMESTAMPTZ vs TIMESTAMP vs DATE vs TIME vs INTERVAL.
- [DELETEs are difficult](https://boringsql.com/posts/deletes-are-difficult/): Why deleting rows is rarely just deleting rows.
- [Welcome to ORDER BY jungle](https://boringsql.com/posts/order-by-jungle/): Why bare names and expressions in ORDER BY take two different parser paths.
- [The NULL in your NOT IN](https://boringsql.com/posts/not-in-null/): How a single NULL silently empties a NOT IN result set via three-valued logic, and why the planner can't optimize it away.

## Operations and maintenance

- [The bloat busters: pg_repack vs pg_squeeze](https://boringsql.com/posts/the-bloat-busters-pg_repack-pg_squeeze/): Online bloat removal without VACUUM FULL.
- [The time keepers: pg_cron and pg_timetable](https://boringsql.com/posts/time-keepers-pg_cron-pg_timetable/): In-database scheduling, simple to advanced.
- [How not to change a PostgreSQL column type](https://boringsql.com/posts/how-not-to-change-postgresql-column-type/): Avoiding full-table rewrites and lock storms.
- [PostgreSQL maintenance without superuser](https://boringsql.com/posts/postgresql-predefined-roles/): pg_monitor, pg_maintain, and other predefined roles.
- [Instant database clones with PostgreSQL 18](https://boringsql.com/posts/instant-database-clones/): Reflink-based template copies in milliseconds.
- [PostgreSQL service connections](https://boringsql.com/posts/postgresql-service-definition/): Maintainable, secure connection management via service definitions.
- [Production query plans without production data](https://boringsql.com/posts/portable-stats/): Exporting planner statistics from PostgreSQL 18 into test environments.
- [pg_regresql: truly portable PostgreSQL statistics](https://boringsql.com/posts/regresql-extension/): Hooking the planner to trust injected statistics over physical sizes.
- [pg_stat_statements: everything it tells you](https://boringsql.com/posts/pg-stat-statements/): The queryid jumble, ORM row explosion, and why aggregate averages hide tail latency.
- [pg_stat_statements: everything it can't](https://boringsql.com/posts/pg-stat-statements-part-2/): Entry eviction, the vanishing query text, the plans and replicas it never sees.

## Testing, CI, and AI safety

- [RegreSQL: regression testing for PostgreSQL queries](https://boringsql.com/posts/regresql-testing-queries/): Catch SQL regressions before they ship.
- [RegreSQL 2.0: testing PostgreSQL query plans, not just rows](https://boringsql.com/posts/regresql20/): Catches plan drift and cardinality error, verified against production's real statistics.
- [Don't let AI touch your production database](https://boringsql.com/posts/dont-let-ai-to-prod/): Why most Postgres MCPs are unsafe, and what dryrun does differently.

## Extensions and ecosystem

- [Custom PostgreSQL extensions with Rust](https://boringsql.com/posts/custom-postgresql-extensions-with-rust/): Building safe extensions with pgrx.
- [Deep dive into PostgREST (part 1)](https://boringsql.com/posts/postgrest-tutorial-part1/): Building APIs straight from Postgres — schema design.
- [Deep dive into PostgREST (part 2)](https://boringsql.com/posts/postgrest-tutorial-part2/): Authentication, RLS, and roles.
- [Deep dive into PostgREST (part 3)](https://boringsql.com/posts/postgrest-tutorial-part3/): Procedures, custom logic, and production deployment.

## Interactive visualizers helping understand PostgreSQL concept

- [PostgreSQL shared buffers visualized](https://boringsql.com/visualizers/shared-buffers/): Buffer pool, clock-sweep eviction, and hash-table lookups in the browser.
- [Inside the 8KB page visualizer](https://boringsql.com/visualizers/8kb-page/): Page header, line pointers, free space, and tuple data.
- [PostgreSQL MVCC visualized](https://boringsql.com/visualizers/mvcc/): xmin/xmax, snapshots, two concurrent sessions, and VACUUM reclaim.
- [HOT updates and page pruning visualized](https://boringsql.com/visualizers/hot-updates/): ctid chains, HOT_UPDATED/HEAP_ONLY flags, and pruning on a single page.
- [VACUUM at the page level visualized](https://boringsql.com/visualizers/vacuum/): a DELETE that reclaims nothing, pruning stuck at LP_DEAD, then the three phases that free the slots, across three heap pages.
- [PostgreSQL TOAST visualized](https://boringsql.com/visualizers/toast/): main heap and pg_toast side-by-side; compression, chunking, and the four storage strategies as live controls.

## Long-form guides

- [PostgreSQL storage internals](https://boringsql.com/guides/postgresql-storage-internals/): Seven-chapter guide from shared buffers and the 8KB page to MVCC, HOT, TOAST, and VACUUM, with companion visualizers.
- [Mastering logical replication in PostgreSQL](https://boringsql.com/guides/mastering-logical-replication/): End-to-end guide — setup, schema changes, conflicts, scaling patterns.
- [PostgREST APIs made easy](https://boringsql.com/guides/postgrest-apis-made-easy/): Building production APIs on top of PostgreSQL with PostgREST.

## Products

- [RegreSQL](https://boringsql.com/products/regresql/): SQL query regression testing for PostgreSQL. Gates on measured actuals (buffers, cardinality error, plan drift) verified against production statistics, no data copied, plus cross-version planner A/B. Source: github.com/boringsql/regresql.
- [Fixturize](https://boringsql.com/products/fixturize/): Subset and mask PostgreSQL production data in one pass. Referential integrity intact, PII gone. Source: github.com/boringSQL/fixturize.
- [dryrun](https://boringsql.com/products/dryrun/): Offline-first PostgreSQL MCP server. Schema linting, migration safety checks, and query validation from a committed JSON snapshot — no production credentials shared with the AI. Source: github.com/boringSQL/dryrun.

## Talks

- [Talks index](https://boringsql.com/talks/): Slides from recent PostgreSQL conference and meetup talks (P2D2, Nordic PgDay, pgconf.de, Prague Postgres Meetup, and more).

## Optional

- [Tags](https://boringsql.com/tags/): Browse posts by topic.
- [Archive](https://boringsql.com/archive/): Full chronological post list.
- [Atom feed](https://boringsql.com/atom.xml): Subscribe in any reader.
- [SQL Labs](https://labs.boringsql.com/): Hands-on PostgreSQL exercises (separate site/product).