Radim @ boringSQL

Radim @ boringSQL

@boringsql.com

Straightforward PostgreSQL and SQL tips, tricks, and tools to get stuff done (no fluff).

64 followers 40 following66 posts

Radim @ boringSQL

Every row lock in #postgres is a write to the page. the locker's xid goes into the same field as a delete marker; flags say "not actually deleted". commit doesn't clean up, vacuum does, at 3am. pg_locks shows nothing. the row is not unlocked. boringsql.com/posts/row-lo...

boringsql.comWhere PostgreSQL stores row locksWhat SELECT FOR UPDATE, FOR SHARE, and a foreign key check actually write into the tuple header. t_xmax as a locker, the infomask bits that say so, MultiXactIds when two sessions lock the same row, an...

Radim @ boringSQL

Are AI agents over-indexing the schemas they create? First it was a hunch. Then I had to verify it. And there was it! Every agent dumps its indexes onto the single table taking all the writes. boringsql.com/posts/unbear...

boringsql.comThe unbearable lightness of one more indexThirty PostgreSQL schemas written by coding agents, loaded and measured. The indexes are competent; the cumulative cost is not.

Radim @ boringSQL

The part I didn't expect: four words at the end of a prompt. Adding "make it production-ready" bought more indexes! Were those indexes wrong? No - they would pass regular PR review. Except their impact on maintenance. Writes going up and vacuum taking longer every week. #postgresql #vibecoding

Radim @ boringSQL

postgres 19 has WAIT FOR LSN, which means you can finally drop the "sleep and hope" workarounds for reading your own writes from replicas. I did all the digging around. Sample Go implementation, and the Rails workaround (Active Record thinks WAIT FOR is a write 🙃) boringsql.com/posts/read-y...

boringsql.comRead your own writes, off the primaryWAIT FOR in PostgreSQL 19 lets a reader block until the replica has replayed a specific LSN. Measured against sticky routing, sleeps, and synchronous commit, on a replica that misses the write 99% of ...

Radim @ boringSQL

@apatheticmagpie.bsky.social wrote the mechanics piece last week (seriously read hers first) she saved me from the boring part. what i did was stress-test it and measure what actually happens. clickhouse.com/blog/postgre...

clickhouse.comRead your writes: WAIT FOR in PostgreSQL 19 | ClickHousePostgreSQL 19's new `WAIT FOR` command enables read-your-writes consistency on asynchronous replicas by letting individual reads wait for a specific WAL position.

Radim @ boringSQL

For impatient here's TL;DR: even with replica on the same machine, loopback, naive reads miss 99.2% of the time. not sometimes. not under load. always.

Radim @ boringSQL

Spent months benchmarking AlloyDB. The engineering is real, but the compatibility story is more nuanced than the marketing suggests. boringsql.com/posts/google... #postgres #benchmark #htap

boringsql.comThe curious case of Google's AlloyDBAlloyDB offers Postgres wire compatibility with a divergent storage engine. A deep-dive review into HTAP claims, ScaNN limits, adaptive autovacuum, and costs

Radim @ boringSQL

Postgres MVCC is a 40-year-old design mistake" is one of those claims that just keeps getting repeated outside #postgresql community. So I wrote the article. And got the backlash. boringsql.com/posts/mvcc-b...

boringsql.comPostgreSQL's MVCC is bad. So is everyone else's.The critics are right: PostgreSQL's MVCC bloats tables and amplifies writes. Oracle, InnoDB, SQL Server, MongoDB, LSM stores, and etcd answer the same four design questions differently, and each pays ...

Radim @ boringSQL

Striking while the iron was hot, we took it to Postgres FM with Michael and Nik and nobody in that room can be accused of being a fanboy. postgres.fm/episodes/mvcc

postgres.fmPostgres FM | MVCCNik and Michael are joined by Radim Marek to discuss MVCC, including his recent article on how Postgres chose to implement it compared to other systems. Here are some links to things they mention...

Radim @ boringSQL

No, it didn't feel like an interrogation. Judge for yourself 😉 Jury's out.

Radim @ boringSQL reposted

@pavlogolub.bsky.social

Only 10 hours left! 2026.pgconf.pl/call-for-pap...

2026.pgconf.plCall for Papers - PGConf.PL 2026Submit your talk for PGConf.PL 2026!

Radim @ boringSQL

Everyone knows #postgres VACUUM reclaims dead tuples. Far fewer people can tell you that a deleted tuple loses its storage at one moment and its line-pointer slot at a completely different one. boringsql.com/posts/vacuum...

boringsql.comVACUUM at the Page LevelWatch a VACUUM cycle with pageinspect, pg_freespace, and pg_visibility: how a dead tuple loses its storage, then its index entry, then its slot, and how the freed space gets reused.

Radim @ boringSQL

Everyone says store money as numeric, not float. Fewer people can say what breaks if you don’t. Your float SUM changes every run. Same rows, nothing touched, three different totals. That’s floats plus parallel aggregation. #postgres boringsql.com/posts/same-r...

boringsql.comSame rows, different SUMSame query, same rows, different SUM totals in PostgreSQL. The culprit is parallel aggregation over double precision floats, not a bug. Here is the mechanism and the fix.

Radim @ boringSQL

A single NULL turns NOT IN into a query that silently returns zero rows, and the planner couldn't optimize around it for as long as anyone can remember, until a PostgreSQL 19 patch fixed that. boringsql.com/posts/not-in...

boringsql.comThe NULL in your NOT INA single NULL turns NOT IN into a query that silently returns zero rows, and the planner couldn't optimize around it for as long as anyone can remember, until a PostgreSQL 19 patch.

Radim @ boringSQL reposted

lukas.fittl.com

@lukas.fittl.com

Gave a talk today at #PGData2026 on #Postgres plan management and monitoring. Some exciting changes coming to Postgres 19, and pg_stat_plans (plan statistics) now supports the new release. Full slides from the talk: resources.pganalyze.com/pganalyze_PG...

resources.pganalyze.comresources.pganalyze.com

Radim @ boringSQL

Postgres chat with a view - amazing warmup for @pg-data.bsky.social - let’s go #postgres Chicago

Radim @ boringSQL

You trust #postgresql pg_stat_statements bit too much. That might be the problem. It's quietly bending what it reports, and none of it is a bug. Part 1 - everything it tells you boringsql.com/posts/pg-sta... Part 2 - everything it can't boringsql.com/posts/pg-sta...

boringsql.compg_stat_statements: everything it tells youWhat pg_stat_statements records and what it quietly drops: the queryid jumble that splits one query into many rows, the frozen first-seen query text, and averages that hide your p99.

Radim @ boringSQL

Without fail, VIEWs are source of heated discussions. What should be a cleanest SQL abstraction layer comes with a lot of baggage. boringsql.com/posts/strong...

boringsql.comStrong views on PostgreSQL VIEWsViews are PostgreSQL's cleanest abstraction and its most rigid one. How rewrite rules, attribute numbers, and the dependency catalog conspire to make every column change a teardown, and what is missin...

Radim @ boringSQL

The third boringSQL visualizer is live, and this one is the piece I've been building toward: MVCC. boringsql.com/visualizers/... #postgresql

boringsql.comPostgreSQL MVCC VisualizedInteractive visualization of PostgreSQL's MVCC: xmin, xmax, snapshots, two concurrent sessions, and VACUUM reclaim.

View full thread

Radim @ boringSQL

It comes down to eight bytes on every tuple (xmin, xmax) and a three-number snapshot on every transaction. That's the whole mechanism. The consequences: bloat, VACUUM horizons,and isolation-level surprises.

Radim @ boringSQL

This is the core knowledge I will cover during my session(s) "Visualizing PostgreSQL Storage Internals" next week in Essen (German Postgres Conf) and in June at @pg-data.bsky.social

Radim @ boringSQL

The third boringSQL visualizer is live, and this one is the piece I've been building toward: MVCC. boringsql.com/visualizers/... #postgresql

boringsql.comPostgreSQL MVCC VisualizedInteractive visualization of PostgreSQL's MVCC: xmin, xmax, snapshots, two concurrent sessions, and VACUUM reclaim.

Radim @ boringSQL

Shared Buffers showed you the cache. The 8KB Page showed you what's inside. MVCC is what bind them together in concurrent access. The reason two sessions can read the same bytes and legitimately see different rows.

Radim @ boringSQL reposted

Jimmy Angelakos

If you're interested in #PostgreSQL, make sure you attend my April 3rd LinkedIn Live event (10am ET/3pm BST)! I'll break down real #SQL anti-patterns from my book (via @manning.com) and fix them live. 👇 🎟️ Register: linkedin.com/events/74433... 📖 Book: hubs.la/Q03Nc0hv0 #Postgres #webinar

Radim @ boringSQL

Rewriting badly written CTEs is a constant source of work for me. Not because CTEs are bad. Mostly because developers use them to force execution order on the database. "First do this, then do that". Learn more at boringsql.com/posts/good-c... #postgresql #sql

boringsql.comGood CTE, bad CTEThe planner treats CTEs very differently depending on how you write them. Here's what happens under the hood, version by version, through PostgreSQL 18.

Radim @ boringSQL

That imperative mindset leads to a pattern I see everywhere: one CTE filters rows, the next LEFT JOINs and aggregates with GROUP BY, the next filters the result. Reads like a pipeline. Performs terribly. The GROUP BY creates a wall the planner can't optimize past, even after in-lining.

Radim @ boringSQL reposted

@postgresedi.bsky.social

This week: #PostgreSQL #Edinburgh #meetup on 📅 Thurs, March 12th! Join @boringsql.com and @vyruss.org for talks on making sure your queries don't break production & on how easy it is to run #Postgres on #Kubernetes with @cloudnativepg.bsky.social 👇 luma.com/5pglgx8h #CloudNativePG #k8s #TechMeetups

Radim @ boringSQL

Major progress for RegreSQL 2.0. It can now force pretty much any statistics. Yesterday I highlighted the current limitation of overriding statistics. Today I can show you planner being completely independent and choosing Bitmap Index Scan on table with 1 tuple.

Radim @ boringSQL

What happens when you inject production statistics into a 10,000-row PostgreSQL test table? The query plan flips.   PostgreSQL 18 makes this a one-liner. pg_dump --statistics-only  Plain SQL, under 1MB, no data needed. Learn the mechanics in my latest blog post.   boringsql.com/posts/portab...

boringsql.comProduction query plans without production dataPostgreSQL 18 makes optimizer statistics portable. Export them from production and inject them into test databases to get realistic query plans without the data.

Radim @ boringSQL

#postgresql takes a bite of 30,000 rows from your 50 million row table and calls it a day. That's 0.06% of your data deciding if your query will be fast or slow. boringsql.com/posts/postgr...

boringsql.comPostgreSQL Statistics: Why queries run slowSlow PostgreSQL queries? Stale statistics are often to blame. Learn how the query planner uses statistics to estimate row counts and how to fix them.

Radim @ boringSQL

Or what about cases where there are no statistics at all? Like materialized CTEs, temporary tables, or computed expressions in WHERE clauses. The planner falls back to hardcoded constants. 0.5% selectivity for equality. 33% for ranges. Good luck.

Radim @ boringSQL

Step 1: "LGTM, this query is fine" Step 2: Deploy to production Step 3: Pray 🙏 Step 4: SRE - "This query is definitely NOT fine" Step 5: "But... it worked on MY database" 🔥 No ifs. No buts. On 12 March @postgresedi.bsky.social find out how to become an atheist when it comes to your SQL queries.

Radim @ boringSQL

It's not magic. It's called SQL query regression testing. 🍻 as postmortem 😉

Radim @ boringSQL

#postgresql season is officially on. I'll be on the ground at 🇪🇺 Nordic pgDay in Helsinki (March) 🇪🇺 German pgConf in Essen (April) 🇺🇸 PGDATA in Chicago (June) 🇬🇧 pgDay in London (September) I’m looking forward to all the chats and fun that's ahead.

Radim @ boringSQL

Epitome of boring. What else to write on site called boringSQL then dry introduction of what is inside #postgresql 8KB page? boringsql.com/posts/inside...

boringsql.comInside PostgreSQL's 8KB PageA byte-level tour of the 8KB page. PostgreSQL's atomic unit of storage. We deep dive into page headers, line pointers, and free space using pageinspect to see exactly how your data lives on disk.

Radim @ boringSQL

To make it bit more fun, you can play with the 2nd visualizer. boringsql.com/visualizers/...

boringsql.comInside the 8KB Page: PostgreSQL Page Layout VisualizedInteractive visualization of PostgreSQL's 8KB page: header, line pointers, free space, and tuple data.

Radim @ boringSQL

"The Alchemy of Shared Buffers" must have been amazing presentation by Josef Machytka during CERN pgDay (had to skip that one unfortunately). It's worth checking the slides. medium.com/@josef.machy...

medium.comThe Alchemy of Shared Buffers: Balancing Concurrency and PerformanceJosef Machytka: Speaker portfolio

Radim @ boringSQL

Back to Buffers. This time you can learn how to read and understand Buffer statistics in EXPLAIN output in my latest article boringsql.com/posts/explai... #postgresql

boringsql.comReading Buffer statistics in EXPLAIN outputLearn how to interpret buffer statistics in PostgreSQL EXPLAIN output. Understand shared hits, disk reads, temp spills, and planning buffers to accurately diagnose and fix query performance bottleneck...

Radim @ boringSQL

There are weeks of full wins, and those where it's time to eat the humble cake. RegreSQL has been making great progress, but I've been fighting the fixture system more and more. After many conversations and endless evenings - it's time to admit: fixtures are a genuinely hard problem

View full thread

Radim @ boringSQL

That's where new project "fixturize" comes in: github.com/boringSQL/fi... its goal is to retrieve data snapshots with referential integrity intact, while masking sensitive data in the process.

github.comGitHub - boringSQL/fixturize: Extract seed data from any PostgreSQL databaseExtract seed data from any PostgreSQL database. Contribute to boringSQL/fixturize development by creating an account on GitHub.

Radim @ boringSQL

While this led to 2748 lines dropped from RegreSQL it gets things done. Stay tuned for 2.0 release!

Radim @ boringSQL

There are weeks of full wins, and those where it's time to eat the humble cake. RegreSQL has been making great progress, but I've been fighting the fixture system more and more. After many conversations and endless evenings - it's time to admit: fixtures are a genuinely hard problem

Radim @ boringSQL

While other changes leading up to v2.0 are strong quality-of-life improvements, I've decided to pull generated fixture support out of RegreSQL entirely for now.

Radim @ boringSQL

But it's not bad news. Agonising over fixtures forced me to look at the problem from another direction - how to quickly and securely use production data. Gave up on sampling, only to find a better solution: graph-traversal based data extraction with masking to strip customer data and PII.

Radim @ boringSQL

Still recharging after this week. But it's that good kind of tired - the one you get when something actually matters. @praguepgdevday.bsky.social was my first time speaking at a bigger Postgres event. The RegreSQL session went well, but honestly? The conversations afterwards were the real thing

Radim @ boringSQL reposted

Prague PostgreSQL Developer Day

@praguepgdevday.bsky.social

That’s a wrap at #P2D2! 🎉 Huge thanks to our amazing speakers, organizers, and attendees for making it unforgettable. PostgreSQL, people, and Prague—what a combo. We can’t wait to do it again next year. See you soon — brzy na viděnou! 🇨🇿💙

Radim @ boringSQL

Buffers came up during Postgres FM chat and people asked why they matter so much. So I wrote about it - with interactive demos to make it click. Introduction to Buffers: boringsql.com/posts/introd... Do the visuals help you to grasp the concept easier? Let me know.

boringsql.comIntroduction to Buffers in PostgreSQLHow PostgreSQL actually manages memory, from shared_buffers and dirty pages to the OS page cache sitting underneath it all.

Radim @ boringSQL

First testing of RegreSQL integration with Rails / ActiveRecord via `rake regresql:export`.

Radim @ boringSQL

I got myself talking about the motivation behind RegreSQL on Postgres FM 🎙️ We nerded out a bit about why timing is a terrible metric, why buffers actually matter, statistics, performance – and why all of this is more relevant in the age of agentic coding. postgres.fm/episodes/reg...

postgres.fmPostgres FM | RegreSQLNik and Michael are joined by Radim Marek from boringSQL to talk about RegreSQL, a regression testing tool for SQL queries they forked and improved recently. Here are some links to things they ment...

Radim @ boringSQL

Test if queries hit the right indexes at production scale – without needing to have production data. I'll show you exactly how during @praguepgdevday.bsky.social - Session "It works on my database - Regression testing of SQL queries" is last of the day, but I intend to make it worth staying for.

Radim @ boringSQL

Where do you stand when it comes to #postgresql arrays? If you want to learn more, check out my latest deep-dive into the topic of arrays, exploring TOAST, GIN indexing gotchas, and when to just use a link table. boringsql.com/posts/good-b...

boringsql.comThe hidden cost of PostgreSQL arraysDeep dive into PostgreSQL arrays: why they're document storage in disguise, the TOAST performance trap, GIN vs B-tree indexing, the dangerous ANY() operator, and when junction tables beat arrays.

Radim @ boringSQL

Writing about instant clones of PostgreSQL databases was not a clever move. Now I have to keep repeating to myself: I will NOT rewrite the whole control plane I will NOT scrap everything for #freebsd jails I will NOT rebuild everything with native ZFS snapshots