# mvcc (blogs) — RSS Amplifier Recent posts from the 1 feeds in the RSS Amplifier directory that cover mvcc. Page: Feed: --- ## [The curious case of Google's AlloyDB](https://boringsql.com/posts/google-alloydb/) _2026-08-15 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Google launched AlloyDB in 2022. They claimed it is fully compatible with PostgreSQL. Can be up to 100 times faster for analytical queries than vanilla Postgres. Four years later, I haven't personally seen it gain significant traction. But it comes in discussions. When people ask me what AlloyDB actually is, I was able to pin point the features, but wasn't really sure what it delivers.… ## [The DISTINCT in your COUNT](https://boringsql.com/posts/distinct-in-your-count/) _2026-08-05 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Here is a query that shows up in every analytics workload: SELECT count ( DISTINCT user\_id) FROM events; It looks like the cheapest possible thing: count the distinct users. On a machine with cores to spare you would expect Postgres to throw a few parallel workers at it and be done. It does not. That one keyword, DISTINCT , switches off parallel query for the entire statement, and the larger your… ## [PostgreSQL's MVCC is bad. So is everyone else's.](https://boringsql.com/posts/mvcc-bad-bad/) _2026-07-27 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ The first thing you will probably learn about Postgres, if you follow people who don't like Postgres, is that MVCC is bad. The 40-year-old design mistake. It's signatures are everywhere. Bloated tables that double in size, 32-bit transaction counter limit, the never ending struggle with VACCUM, dead tuples nightmares. It comes with credentials, too: Uber measured the write amplification… ## [The tests passed. The plan didn't.](https://boringsql.com/posts/regresql20/) _2026-07-12 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ TL;DR - RegreSQL 1.0 tested that your queries return the right rows. 2.0 tests that they return them the right way, and it does the checking against production's real statistics instead of your empty dev database, which lies. A migration cleanup dropped an index nobody thought was that important. Every test passed: same rows, same order, green. Three days later the API started timing out on a… ## [VACUUM at the Page Level](https://boringsql.com/posts/vacuum-at-the-page-level/) _2026-07-05 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ In HOT Updates in Postgres we covered page pruning clean up HOT chains, an elegant shortcut where PostgreSQL reclaims dead tuple space during ordinary reads. All that without waiting for any background process. But pruning is exactly that: a shortcut. It only works within a single page, and only for HOT-updated tuples. For everything else (cold updates that touch indexed columns, plain DELETEs,… ## [Same rows, different SUM](https://boringsql.com/posts/same-rows-different-sum/) _2026-06-28 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Everyone knows not to store money as a double precision . One can hope. The rule is so well drilled that it has stopped being interesting, and it is also not where the trouble usually starts. The float is already in the schema before anyone weighs in on it: a measurement column someone later sums for a report, telemetry that drifts into a finance dashboard, a third-party feed ingested as double… ## [The NULL in your NOT IN](https://boringsql.com/posts/not-in-null/) _2026-06-14 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ A NOT IN query can return the wrong answer without telling you. It is valid SQL, it runs without an error, and it hands back a perfectly well-formed result set that happens to be empty when it should not be. No warning, no hint, nothing in the logs: just zero rows where you expected hundreds, and a database that considers it correct. Almost always the cause is a single NULL sitting somewhere you… ## [pg\_stat\_statements: everything it can't](https://boringsql.com/posts/pg-stat-statements-part-2/) _2026-06-03 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Part one made the core case: pg\_stat\_statements counts, it doesn't record. It walked through how the queryid jumble fragments one logical query into many rows, how the first-seen text freezes your per-request tags, and how the averages bury the p99 that actually pages you. All of that was about data the extension has and distorts. This part is about the rest: the entries it silently throws… ## [pg\_stat\_statements: everything it tells you](https://boringsql.com/posts/pg-stat-statements/) _2026-06-02 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ If not first, pg\_stat\_statements is one of the most used extensions in the PostgreSQL ecosystem. It ships in contrib and costs almost nothing to use. Most of us turn to it to answer the question: what is the database actually doing? It's genuinely useful. You can use it to get a snapshot of what happened in a given timeframe, and make a faster decision about what to fix. Coming from other… ## [TOAST: Where PostgreSQL hides big values](https://boringsql.com/posts/postgresql-toast/) _2026-05-24 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ In earlier posts in this series we established that every heap tuple lives inside a strict 8KB page . Everything else is built on top of that hard limit: MVCC , HOT updates , and indexes that point at (page, line\_pointer) . And yet this still works: CREATE TABLE docs (id int PRIMARY KEY , body jsonb); INSERT INTO docs VALUES ( 1 , ( SELECT jsonb\_agg(g) FROM generate\_series ( 1 , 100000 ) g)); That… ## [Every Channel. One Login. (Sponsored)](https://crawlproof.com/a/oyPAC4PQE2PV) _2026-05-24 · **Sponsored**_ 17,000+ live channels, movies and sports on any device. Free trial, no IP lock. ## [Welcome to ORDER BY jungle](https://boringsql.com/posts/order-by-jungle/) _2026-05-15 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ SQL is fun and not at all boring. The latest article by Markus Winand on Order by Has Come a Long Way sent me on quite a journey. First, set up a table called nums with one integer column and four rows: CREATE TABLE nums (a int ); INSERT INTO nums VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 ); Try to guess what these two queries return. SELECT - a AS a FROM nums ORDER BY a; SELECT - a AS a FROM nums ORDER BY… ## [Strong views on PostgreSQL VIEWs](https://boringsql.com/posts/strong-views/) _2026-05-10 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ VIEWs should be the cleanest abstraction SQL, and therefore Postgres, has on offer. I love the concept. The promise of decoupling logical intent from physical storage is perfect on paper. In practice, few things in the database world trigger such a heated debate or carry as much historical baggage. VIEWs mix big promises with false hopes, and the promises rarely survive contact with production.… ## [HOT Updates in Postgres](https://boringsql.com/posts/hot-updates/) _2026-04-28 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ In the previous article we watched every UPDATE leave dead tuple behind. The same copy-on-write behaviour shows up from the operational angle in DELETEs are difficult . That's the tradeoff of MVCC and on the heap alone it's tolerable. The problem is the indexes. Every UPDATE in PostgreSQL potentially writes to every index on the table, even when the indexed columns didn't change.… ## [PostgreSQL MVCC, Byte by Byte](https://boringsql.com/posts/postgresql-mvcc-byte-by-byte/) _2026-04-17 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ You run SELECT \* FROM orders in one psql session and see 50 million rows. A colleague in another session runs the same query at the same moment and sees 49,999,999. Neither of you is wrong, and neither is seeing stale data. You are both reading the same 8KB heap pages, the same bytes on disk. This is the promise of PostgreSQL's MVCC (Multi-Version Concurrency Control), and it's the… ## [Don't let AI touch your production database](https://boringsql.com/posts/dont-let-ai-to-prod/) _2026-04-06 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Not so long ago, the biggest threat to production databases was the developer who claimed it worked on their machine. If you've attended my sessions, you know this is a topic I'm particularly sensitive to. These days, AI agents are writing your SQL. The models are getting incredibly good at producing plausible code. It looks right, it feels right, and often it passes a cursory glance.… ## [Good CTE, bad CTE](https://boringsql.com/posts/good-cte-bad-cte/) _2026-03-29 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ The Common Table Expression , or CTE, is often the first feature developers reach for beyond basic SQL, and often the only one. You write a subquery after WITH , give it a name, and use it in the rest of your query. It only exists for the duration of that query. But the popularity of CTEs usually has less to do with modernizing code and more to do with the promise of imperative logic. For many,… ## [pg\_regresql: truly portable PostgreSQL statistics](https://boringsql.com/posts/regresql-extension/) _2026-03-21 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ The previous article showed that PostgreSQL 18 makes optimizer statistics portable, but left one gap open: It's not worth trying to inject relpages as the planner checks the actual file size and scales it proportionally. The planner doesn't trust pg\_class.relpages . It calls smgrnblocks() to read the actual number of 8KB pages from disk. Your table is 74 pages on disk but… ## [Production query plans without production data](https://boringsql.com/posts/portable-stats/) _2026-03-08 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ In the previous article we covered how the PostgreSQL planner reads pg\_class and pg\_statistic to estimate row counts, choose join strategies, and decide whether an index scan is worth it. The message was clear: when statistics are wrong, everything else goes with it. Streaming replication provides bit-to-bit replication, so all replicas share the same statistics with primary server. But there was… ## [PostgreSQL Statistics: Why queries run slow](https://boringsql.com/posts/postgresql-statistics/) _2026-02-26 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Every query starts with a plan, and a slow query is usually the result of a bad one. More often than not, stale statistics are to blame. But how does it really work? PostgreSQL doesn't run the query to find out - it estimates the cost. It reads pre-computed data from pg\_class and pg\_statistic and does the maths to figure out the cheapest path to your data. In ideal scenario, the numbers read… ## [Inside PostgreSQL's 8KB Page](https://boringsql.com/posts/inside-the-8kb-page/) _2026-02-19 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ If you read previous post about buffers , you already know PostgreSQL might not necessarily care about your rows. You might be inserting a user profile, or retrieving payment details, but all that Postgres works with are blocks of data. 8KB blocks, to be precise. You want to retrieve one tiny row? PostgreSQL hauls an entire 8,192-byte page off the disk just to give it to you. You update a single… ## [Chovy's Blog: Agentic Coding (Sponsored)](https://crawlproof.com/a/W1tgS48uBjdj) _2026-02-19 · **Sponsored**_ Personal posts on agentic coding, moshcode CLI, and security findings. ## [Reading Buffer statistics in EXPLAIN output](https://boringsql.com/posts/explain-buffers/) _2026-02-06 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ In the article about Buffers in PostgreSQL we kept adding EXPLAIN (ANALYZE, BUFFERS) to every query without giving much thought to the output. Time to fix that. PostgreSQL breaks down buffer usage for each plan node, and once you learn to read those numbers, you'll know exactly where your query spent time waiting for I/O - and where it didn't have to. That's about as… ## [Introduction to Buffers in PostgreSQL](https://boringsql.com/posts/introduction-to-buffers/) _2026-01-24 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ The work around RegreSQL led me to focus a lot on buffers . If you are a casual PostgreSQL user, you have probably heard about adjusting shared\_buffers and followed the good old advice to set it to 1/4 of available RAM. But after we went a little bit too enthusiastic about them on a recent Postgres FM episode I've been asked what that's all about. Buffers are one of those topics… ## [The hidden cost of PostgreSQL arrays](https://boringsql.com/posts/good-bad-arrays/) _2026-01-12 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Starting with arrays in PostgreSQL is as simple as declaring a column as integer\[\] , inserting some values, and you are done. Or building the array on the fly. SELECT '{1,2,3}' :: int \[\]; SELECT array \[1,2,3\]; int4 --------- {1,2,3} (1 row) array --------- {1,2,3} (1 row) The official documentation provides a good introduction. But beneath this straightforward interface lies a set of more complex… ## [Instant database clones with PostgreSQL 18](https://boringsql.com/posts/instant-database-clones/) _2025-12-22 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Have you ever watched a long running migration script , wondering if it's about to wreck your data? Or wish you can "just" spin a fresh copy of database for each test run? Or wanted to have reproducible snapshots to reset between runs of your test suite, (and yes, because you are reading boringSQL) needed to reset the learning environment? When your database is a few megabytes, pg\_dump and… ## [VACUUM Is a Lie (About Your Indexes)](https://boringsql.com/posts/vacuum-is-lie/) _2025-12-11 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ There is common misconception that troubles most developers using PostgreSQL: tune VACUUM or run VACUUM, and your database will stay healthy. Dead tuples will get cleaned up. Transaction IDs recycled. Space reclaimed. No further action needed. But there are couple of dirty "secrets" people are not aware of. First of them being VACUUM is lying to you about your indexes . The anatomy of storage When… ## [RegreSQL: Regression Testing for PostgreSQL Queries](https://boringsql.com/posts/regresql-testing-queries/) _2025-11-13 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ TL;DR - RegreSQL brings PostgreSQL's regression testing methodology to your application queries, catching both correctness bugs and performance regressions before production. As puzzling as it might seem, the common problem with production changes is the ever-present "AHA" moment when things start slowing down or crashing straight away. Testing isn't easy as it is, but there's a… ## [Beyond Start and End: PostgreSQL Range Types](https://boringsql.com/posts/beyond-start-end-columns/) _2025-11-02 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ One of the most read articles at boringSQL is Time to Better Know The Time in PostgreSQL where we dived into the complexities of storing and handling time operations in PostgreSQL. While the article introduced the range data types, there's so much more to them. And not only for handling time ranges. In this article we will cover why to consider range types and how to work with them. Bug Not… ## [PostgreSQL maintenance without superuser](https://boringsql.com/posts/postgresql-predefined-roles/) _2025-09-13 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ How many people/services have superuser access to your PostgreSQL cluster(s)? Did you ever ask why your software engineers might need it? Or your BI team? Why those use cases require same privileges as someone who can drop your databases? PostgreSQL historically offered limited options for operational access, and not enough people are aware of the options that do exist. So the common practice… ## [Beyond the Basics of Logical Replication](https://boringsql.com/posts/logical-replication-beyond-the-basics/) _2025-06-24 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ With First Steps with Logical Replication we set up a basic working replication between a publisher and a subscriber and were introduced to the fundamental concepts. This part covers the operational side: initial data copies, monitoring, evolving publications, and how logical decoding actually works. Initial Data Copy As we demonstrated in the first part, when setting up the subscriber, you can… ## [First steps with Logical Replication in PostgreSQL](https://boringsql.com/posts/logication-replication-introduction/) _2025-06-11 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ PostgreSQL's logical replication streams row-level changes from one PostgreSQL instance to another using a publish-subscribe model. Unlike physical replication, which copies the whole cluster byte for byte, it lets you pick what to replicate and where to send it, which makes it a building block for scaling out, distributing load, and integrating PostgreSQL with the rest of your architecture.… ## [Marketplace for AI-Powered Professionals (Sponsored)](https://crawlproof.com/a/x7b3RK3Z07P7) _2025-06-10 · **Sponsored**_ Find professionals who use ChatGPT, Copilot, and Claude to deliver work faster. ## [PostgreSQL Service Connections](https://boringsql.com/posts/postgresql-service-definition/) _2025-05-15 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ There are so many ways to connect to PostgreSQL. One of my favourite, yet underutilised is using service definition, the feature of any application using the libpq library. In this article we will explore what service definition is, where and how it can make your life with PostgreSQL much easier. PostgreSQL connection methods PostgreSQL offers several methods to establish connections to databases,… ## [Time to Better Know The Time in PostgreSQL](https://boringsql.com/posts/know-the-time-in-postgresql/) _2025-04-06 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ To honor the name of the site (boringSQL) let's deep dive into a topic which might sound obvious, but it might be never ending source of surprises and misunderstanding. Simple things We can start with the simple statement like SELECT '2025-03-30 00:30' as t; Result: t ------------------ 2025-03-30 00:30 which gives you (and I do hope that's not a big surprise) a simple text literal,… ## [VIEW inlining in PostgreSQL](https://boringsql.com/posts/view-inlining/) _2025-02-08 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Database VIEWs are powerful tools that often don't get the attention they deserve when building database-driven applications. They make our database work easier in several ways: They let us reuse common query patterns instead of writing them over and over They give us a place to define business rules once and use them everywhere They help us write cleaner, more organized queries Let's… ## [DELETEs are difficult](https://boringsql.com/posts/deletes-are-difficult/) _2024-11-23 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Your database is ticking along nicely - until a simple DELETE brings it to its knees. What went wrong? While we tend to focus on optimizing SELECT and INSERT operations, we often overlook the hidden complexities of DELETE. Yet, removing unnecessary data is just as critical. Outdated or irrelevant data can bloat your database, degrade performance, and make maintenance a nightmare. Worse, retaining… ## [Text identifiers in PostgreSQL database design](https://boringsql.com/posts/text-identifier-in-db-design/) _2024-11-09 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Whether you are designing a standalone application or a microservice, you will inevitably encounter the topic of sharing identifiers. Whether it’s URLs of web pages, RESTful API resources, JSON documents, CSV exports, or something else, the identifier of specific resources will be exposed. /orders/123 /products/345/variants/1 While an identifier is just a number and… ## [We need to talk about ENUMs](https://boringsql.com/posts/postgresql-enums/) _2024-09-04 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Designing a database schema, whether for a new application or a new feature, always raises a lot of questions. The choices you make can have a big impact on how well your database performs and how easy it is to maintain and scale. Whether you’re just getting started with PostgreSQL or consider yourself a seasoned pro, it’s easy to rely on old habits or outdated advice. In this article, I want to… ## [Beyond Simple Upserts with MERGE in PostgreSQL](https://boringsql.com/posts/beyond-upserts-with-merge/) _2024-08-25 · Radim Marek · boringSQL | Supercharge your SQL & PostgreSQL powers_ Understanding how comfortable someone is with databases and SQL often comes down to the features they use. In PostgreSQL, one such feature that distinguishes more advanced users is the MERGE command, introduced in version 15 and expanded in version 17 (in beta at the time of writing this article). Before MERGE , developers typically relied on INSERT ... ON CONFLICT DO UPDATE for upserts—a method…