Radim @ boringSQL
@boringsql.com
Straightforward PostgreSQL and SQL tips, tricks, and tools to get stuff done (no fluff).
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...
PostgreSQL'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 ...
boringsql.com
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...
VACUUM at the Page Level
Watch 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.
boringsql.com
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...
Same rows, different SUM
Same 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.
boringsql.com
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...
The NULL in your NOT IN
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.
boringsql.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.com
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...
pg_stat_statements: everything it tells you
What 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.
boringsql.com
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...
Strong views on PostgreSQL VIEWs
Views 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...
boringsql.com
The third boringSQL visualizer is live, and this one is the piece I've been building toward: MVCC. boringsql.com/visualizers/... #postgresql
PostgreSQL MVCC Visualized
Interactive visualization of PostgreSQL's MVCC: xmin, xmax, snapshots, two concurrent sessions, and VACUUM reclaim.
boringsql.com
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
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
Good CTE, bad CTE
The planner treats CTEs very differently depending on how you write them. Here's what happens under the hood, version by version, through PostgreSQL 18.
boringsql.com
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
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.
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...
Production query plans without production data
PostgreSQL 18 makes optimizer statistics portable. Export them from production and inject them into test databases to get realistic query plans without the data.
boringsql.com
#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...
PostgreSQL Statistics: Why queries run slow
Slow PostgreSQL queries? Stale statistics are often to blame. Learn how the query planner uses statistics to estimate row counts and how to fix them.
boringsql.com
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.
#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.
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...
Inside PostgreSQL's 8KB Page
A 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.
boringsql.com
"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...
The Alchemy of Shared Buffers: Balancing Concurrency and Performance
Josef Machytka: Speaker portfolio
medium.com
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
Reading Buffer statistics in EXPLAIN output
Learn 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...
boringsql.com
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
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
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! 🇨🇿💙
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.
Introduction to Buffers in PostgreSQL
How PostgreSQL actually manages memory, from shared_buffers and dirty pages to the OS page cache sitting underneath it all.
boringsql.com
First testing of RegreSQL integration with Rails / ActiveRecord via `rake regresql:export`.
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 FM | RegreSQL
Nik 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...
postgres.fm
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.
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...
The hidden cost of PostgreSQL arrays
Deep 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.
boringsql.com
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
