When most people start working with PostgreSQL, they quickly learn SQL: [crayon-6a5789801521c171008655/] But very soon, another world opens up inside psql — a set of commands that don’t look like SQL, don’t end with semicolons. These are PostgreSQL Meta Commands, and they quietly power the daily workflow of almost every experienced DBA. Meta commands are … Continued

The post PostgreSQL Meta Commands that save time every day appeared first on Percona.

Moving is not for the faint of heart! The surgeon general should issue a warning against moving houses after age 50. Coordinating our cross-country move was one of the hardest thing I had done. Selling our house in Buffalo, finding a suitable rental house in the Bay Area, figuring out the logistics of the move, getting rid of the furniture we wouldn't transport, boxing everything up, and then on the other side unboxing everything and buying new furniture... It was simply exhausting.

Our move has been a long time in the works. For the last 6 years I have been working remotely, first for AWS and then for MongoDB Research, and I have been telling people I would move out of Buffalo any day now. Indeed we could have moved earlier, but we kept putting it off. We waited until my son finished high school, then tried to move last summer. But we got the house on the market too late and it fell through. By then I had already told people I was moving, including an entire table at OSDI 2025. So for this final attempt, I used the Russian approach and kept quiet until it was done. (As the story goes, the Soviet space program announced only the missions that succeeded, and stayed quiet about the ones that didn't.)

Escaping Buffalo's Gravity

I have been in Buffalo for 21 years, not counting two sabbaticals. That's a long time to stay in one place. Call it inertia or bad luck, but after so many stalled attempts I started to suspect that Buffalo had the escape velocity of a black hole. When I named my blog muratbuffalo, I didn't know the name would stick and almost become a curse.

I lived through 21 of the Buffalo winters, and they are tough. I remember one particularly bad one when the roads were covered with ice for a good 3 weeks and looked like Siberia (well, at least like what I imagine Siberia looks like, since I haven't been). There is virtually no sun during winter, and it gets bleak. I think I developed a seasonal affective disorder without even realizing it. I only caught on when my manager, after reading a post I wrote in February 2025, told me to take a couple of days off. 

If you are lucky enough to survive the winter (some people don't, seriously), you are rewarded with an unfamiliar bright orb in the sky come May. You get a couple of weeks of spring, and then you spring straight into summer, where it gets hot very quickly. Buffalo is humid too, so 80-90 degrees feels much hotter than it should. I am afraid I might be dragging this cursed humidity to the Bay Area with me, like Rob McKenna, the miserable Rain God lorry driver in Douglas Adams’s So Long, and Thanks for All the Fish.

The weather was only part of the reason. Buffalo is also not a big city. Every time I traveled to a proper big city like NYC, Seattle, or even Boston, I felt how much of the big-city action and energy we were missing.

But the biggest reason was family. My son Ahmet was already out in California. He had gone there for college, and then pivoted (as one does in California) to start his AI company. If we wanted to spend more time together as a family, this was the time and the place. We also figured the Bay Area, with all its opportunities, would be good for our two daughters' education and growth.

I am not claiming the Bay Area is all awesome, or that it beats Buffalo in every respect. I don't wear rose-tinted glasses. But one thing was clear: after 21 years, it was time to leave. Buffalo had come to feel routine, and change is good.

In Buffalo's defense, it was a great place to raise the kids, and I had good colleagues at CSE Buffalo and many fond memories. As Pat Helland liked to joke whenever I mentioned my plans to move out, "Buffalo is a great place to come from".

Oops, I did it again! Another cross-country trip

We were not going to take much furniture. Ours had been with us for a long time, and we wanted a fresh start there too. But even when you don't take much furniture, a family household depends on a surprising number of things that all need to be transported.

I realized this during our first move inside Buffalo. It felt like every closet in the house was springing with stuff, and no amount of boxing and cleaning got us to an empty house. Even knowing this, I got surprised again on every move since, including this last one. We sold, donated, and threw away so much stuff, I can't believe it. It turns out I keep wearing the same 3-5 things, and I found clothes I hadn't worn in more than 10 years.

I looked up the Pods moving solution, and it was ridiculously expensive: starting at $4K just to transport the Pod to the Bay Area, and dropped off (inshallah?) at a time they couldn't guarantee... These guys have higher margins than NVIDIA!

Then I looked at U-Haul. My 2022 Highlander came with a hitch included, and a U-Haul 12-by-6-foot trailer would solve our moving problem. It was surprisingly cheap, only $350 for a 9-day cross-country trip. So, somehow we were crazy enough to attempt another transcontinental drive.

When we picked up the trailer, it looked smaller than it had when we first went to see it. We thought this would leave half of our stuff behind. But playing Tetris as a child and as a procrastinating PhD student paid off. (True story: I used to play the Tetris built into Emacs, and I didn't know it tracked the highest score across the whole department until my friends congratulated me for topping it.) Well, thanks to all that training, we got everything in.

Best Laid Plans, Meet Cat

My plan for the roadtrip was to cross toward southwest coming from the north (I-90, I-70, I-44, and I-40), and finally driving back up to the Bay Area. This was a trailer friendly route that didn't cross high mountains.

It was more than 45 hours of driving. With a trailer you go slow. And since the trailer burns a lot of gas, you stop for fuel almost twice as often. We planned to leave on July 1st, and visit our friends at Kenyon College on the first day, so that first day was only a half day. Then Springfield, Missouri, then Amarillo, Texas, then Williams, Arizona, then a Grand Canyon visit, then Las Vegas, and finally the Bay Area.

Of course, we didn't book the hotels in advance. We would book each one on the day of travel from the phone, using Hotwire or the hotel sites.

Perfect plan, right?

On the morning of July 1st, we were doing the final cleanup and walkthrough prep on the house we had sold, and we let our cat Pasha out as usual. He rarely strayed far from the house and was always back soon. But the poor cat had been stressed for two weeks watching our furniture disappear. Every time a chair vanished, Pasha would inspect the empty void and glare at me as if to say, "You fools, what have you done to my house?" He must have been furious, because when we finished up with the final prep at the empty house, he was nowhere to be seen. We were supposed to leave at noon for our half-day first drive. Instead I spent the entire afternoon roaming the neighborhood like a deranged madman, calling his name and shaking his favorite snacks. It was brutally hot, and I got sunburned looking for him. I looked like a lobster... again.

Pasha didn't come back until 11:00 PM. We had to stay another night in Buffalo. At this point, my panic was real. I thought we were trapped forever in Buffalo's gravity well.

The next morning, after breakfast with friends in Buffalo, we finally got on our way, Pasha curled up in my daughters' laps. After all that buildup, leaving Buffalo felt anticlimactic.

The drive was nice and boring for the most part. Driving with a trailer is not hard, but backing up is very tricky. So I parked accordingly at the hotels and service stations. Not fun.

Another thing that wore on me was the state of the American highways. Some stretches looked like freshly bombarded potato fields. Missouri was the worst. You hit a crater, your spine compresses, you worry about your tire rims, and a full second later, the 5000-pound trailer hitched to your bumper hits the exact same hole with even a louder bang. The government seems to always find money for overseas misventures, but fails to fix the roads that millions of Americans drive on every day.

I listened to the Science of Discworld books while driving, which kept me occupied. But it was hard to do anything with the cat along. When he attempted another escape at lunch on day 2, we scratched the Grand Canyon and Las Vegas plans and just drove. My daughters were very cooperative with our crazy plan. As long as they had the phones to keep them busy, they didn't mind the drive. They even managed to keep Pasha soothed during the trip.

We traveled through the heatwave in the first week of July. More than 100F in Arizona, 95F in California almost the whole way, but the Bay Area still showed 75F? What is this black magic?

Landed, Still Partly Unpacked

Well, we did it. In one piece (well, two, if you count the trailer), and it has been a week now. We are still unpacking and buying new furniture.

The move itself was exhausting, and adjusting to a new place turns out to be its own kind of work. A lot of little things are different. For example, why are there no bottle redemption centers inside the supermarkets in the Bay Area? Where are we supposed to recycle the bottles? And what are these tiny microscopic ants coming into the house, and how do I stop them?

OK, let's not dwell on these. Good weather. A lot of CS and AI action here. Please suggest good meetups, activities, and places to see around the Bay Area.

MySQL 9.7, a Long-Term Support (LTS) release, incorporates a variety of potential features spanning across multiple technical domains. This article covers some of the primary features introduced and evaluates their practical utility within the MySQL database environment. Following the End-of-Life (EOL) status of MySQL 8.0, this subsequent LTS release is designed to provide enhanced stability … Continued

The post Inside MySQL 9.7 LTS Features appeared first on Percona.

About a year ago, we discussed how MyDumper refactored its locking mechanisms to move away from old, rigid flags and transitioned towards more flexible, streamlined execution. Since then, the MyDumper community hasn’t stood still. In recent releases, the locking architecture was further standardized under a single overarching option: --sync-thread-lock-mode. Along with this modernization came a … Continued

The post MyDumper Locking Mechanisms Revisited: Introducing SAFE_NO_LOCK appeared first on Percona.

PostgreSQL is extensible, allowing it to serve as a multi-model or converged database with built-in data types and indexes. If you're interested in exploring its features, compiling and installing all extensions can be quite a bit of work, but having an image packed with extensions makes things much easier. For this, I recommend using https://pglayers.github.io/ (thanks to Ismael Mejía).

Start pglayers-full in Docker

I began by using the ghcr.io/pglayers/pglayers-full:17 image to start the container. Not only did I get all the extensions installed, but it also ships the MongoDB-compatible endpoint offered by DocumentDB:

# remove previous container with same name ⚠️
docker rm -f pg-documentdb

# install DocumentDB extension before it can open the MongoDB-compatible endpoint
echo 'CREATE EXTENSION IF NOT EXISTS documentdb_core CASCADE;
CREATE EXTENSION IF NOT EXISTS documentdb CASCADE;' > /tmp/01-documentdb.sql

# start the container, exposes the PostgreSQL and MongoDB-compatible ports n host, set 
docker run -d --name pg-documentdb \
  -p 5432:5432 -p 10260:10260 \
  -e POSTGRES_PASSWORD=xxxMongoDBxxx\
  -v /tmp/01-documentdb.sql:/docker-entrypoint-initdb.d/01-documentdb.sql:ro \
  ghcr.io/pglayers/pglayers-full:17 \
  -c max_worker_processes=64 \
  -c max_connections=200 \
  -c documentdb.pg_gw_username=postgres \
  -c documentdb.pg_gw_password=xxxMongoDBxxx\
  -c cron.database_name=postgres

I waited for the PostgreSQL endpoint to be up:

echo -n "Waiting to get PostgreSQL endpoint up on 5432"
until docker exec pg-documentdb pg_isready -p 5432 2>/dev/null |
 grep "5432 - accepting connections"
 do echo -n . ; sleep 1 ; done

I listed the available extensions:

docker exec -i pg-documentdb psql -U postgres -t -c "select 'Available extensions: '||string_agg(name,', ') from pg_available_extensions;"

I could already connect to the PostgreSQL endpoint and use those extensions. As I also wanted to use the MongoDB-compatible API, I downloaded the MongoDB image that contains the client (MongoSH):

docker pull mongo

I waited for the MongoDB endpoint to be up:

echo -n "Waiting to get MongoDB emulation up on 10260"
until docker logs pg-documentdb 2>/dev/null |
 grep "bound to port 10260"
 do echo -n . ; sleep 1 ; done
echo "Ready."

I can connect with MongoSH simply by linking the PostgreSQL container when starting the MongoDB client:

docker run -it --rm --link pg-documentdb:pg mongo \
  mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsAllowInvalidCertificates=true"

Now I define aliases to connect to PostgreSQL via the two endpoints, with psql and mongosh:

alias p='docker exec -it pg-documentdb psql -U postgres'

alias m='docker run --rm -it --link pg-documentdb:pg mongo mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsInsecure=true"'

Ready to explore all extensions. I'll go further with DocumentDB.

Import MongoDB Sample Dataset to PostgreSQL DocumentDB

I tested with some data using the MongoDB client image to access the Sample Dataset and imported it into PostgreSQL via the DocumentDB endpoint with mongoimport:

#                  ⬇️ using --link to quickly connect to the other container
docker run --rm -i --link pg-documentdb:pg mongo bash <<'SH'
 # get wget
 apt update -qqy
 apt install wget -qy
 # get sample data
 wget -q -c https://atlas-education.s3.amazonaws.com/sampledata.archive
 # restore sample data
mongorestore -j 5 --drop --uri "mongodb://postgres:xxxMongoDBxxx@pg:10260/?tls=true&tlsInsecure=true" --archive=sampledata.archive
 # create the index that failed because of "textIndexVersion": 3
 mongosh "mongodb://postgres:xxxMongoDBxxx@pg:10260/sample_mflix?tls=true&tlsInsecure=true" --eval '
SH

...

One index creation failed because it sets textIndexVersion 2. No worries, we will create the same index later, just without mentioning the version, which is specific to vanilla MongoDB.

Look at MongoDB-compatible and DocumentDB execution plans

I can connect to the MongoDB API, create a text index on the movies collection, and execute a query just like I would in MongoDB. I used the m alias defined above, but you can connect with any MongoDB client.

Current Mongosh Log ID: 6a50e7576d644ce7f3c3a7d7
Connecting to:          mongodb://<credentials>@pg:10260/?tls=true&tlsInsecure=true&directConnection=true&appName=mongosh+2.9.2
Using MongoDB:          7.0.0
Using Mongosh:          2.9.2

For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/

To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.

[direct: mongos] test> disableTelemetry()
Telemetry is now disabled.

[direct: mongos] test> use sample_mflix;
switched to db sample_mflix

[direct: mongos] sample_mflix> show collections
comments
embedded_movies
movies
sessions
theaters
users

[direct: mongos] sample_mflix> db.movies.createIndex({
    "cast": "text",   
    "fullplot": "text",   
    "genres": "text",   
    "title": "text"   
  }, {   
    "name": "cast_text_fullplot_text_genres_text_title_text",  
    "weights": { "cast": 1, "fullplot": 1, "genres": 1, "title": 1 },  
    "default_language": "english",  
    "language_override": "language"
  }  
)

cast_text_fullplot_text_genres_text_title_text

[direct: mongos] sample_mflix> db.movies.find(
  {
    $text: {
      $search: "\"star wars\""
    }
  }
).explain("executionStats").executionStats
;

{
  nReturned: Long('13'),
  executionTimeMillis: 137.596,
  executionStartAtTimeMillis: 135.295,
  totalDocsExamined: Long('13'),
  totalKeysExamined: Long('13'),
  executionStages: {
    stage: 'FETCH',
    nReturned: Long('13'),
    executionTimeMillis: 137.596,
    executionStartAtTimeMillis: 135.295,
    totalDocsExamined: 13,
    totalKeysExamined: 13,
    numBlocksFromCache: 73,
    inputStage: {
      stage: 'FETCH',
      nReturned: Long('13'),
      executionTimeMillis: 137.57,
      executionStartAtTimeMillis: 135.292,
      totalDocsExamined: 13,
      totalKeysExamined: 13,
      exactBlocksRead: 12,
      numBlocksFromCache: 73,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: Long('13'),
        executionTimeMillis: 0.831,
        executionStartAtTimeMillis: 0.831,
        indexName: 'cast_text_fullplot_text_genres_text_title_text',
        totalKeysExamined: 13,
        numBlocksFromCache: 10
      }
    }
  }
}

I've shown the execution plan to verify that the MongoDB-compatible text index was utilized. I can also switch to the SQL API, using the p alias defined above, and run the same query to view the PostgreSQL execution plan for it:

psql (17.10 (Debian 17.10-1.pgdg13+1))
Type "help" for help.

postgres=# \pset pager off

postgres=# EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS OFF)
SELECT document FROM documentdb_api_catalog.bson_aggregation_find(
  'sample_mflix',
  '{
     "find":"movies",
     "filter":{
       "$text":{
         "$search":"\"star wars\""
       }
     }
   }'::documentdb_core.bson
);
                                                                                                                                                                                                  QUERY PLAN                                                                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (DocumentDBApiQueryScan) (actual time=36.533..38.597 rows=13 loops=1)
   Output: document
   Buffers: shared hit=34
   ->  Bitmap Heap Scan on documentdb_data.documents_112 collection (actual time=36.530..38.587 rows=13 loops=1)
         Output: document
         Filter: documentdb_api_internal.bson_text_meta_qual(collection.document, '''star'' <-> ''war'''::tsquery, '\x0400000000000000ffffffff000000000000000000000000000000007b0000002c0000007f000000040000000000803f000000000000803f000000000000803f000000000000803f000000000400000063617374000800000066756c6c706c6f74000600000067656e72657300050000007469746c6500b83300006c616e677561676500'::bytea, true)
         Heap Blocks: exact=12
         Buffers: shared hit=34
         ->  Bitmap Index Scan on cast_text_fullplot_text_genres_text_title_text (actual time=1.208..1.209 rows=13 loops=1)
               Index Cond: (collection.document OPERATOR(documentdb_api_catalog.@#%) '''star'' <-> ''war'''::tsquery)
               Buffers: shared hit=10
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.340 ms
 Execution Time: 39.203 ms
(15 rows)

postgres=# \d documentdb_data.documents_112
                  Table "documentdb_data.documents_112"
     Column      |         Type         | Collation | Nullable | Default
-----------------+----------------------+-----------+----------+---------
 shard_key_value | bigint               |           | not null |
 object_id       | documentdb_core.bson |           | not null |
 document        | documentdb_core.bson |           | not null |
Indexes:
    "collection_pk_112" PRIMARY KEY, btree (shard_key_value, object_id)
    "documents_rum_index_152" documentdb_rum (document documentdb_api_catalog.bson_rum_text_path_ops (weights='{ "cast" : 1.0, "fullplot" : 1.0, "genres" : 1.0, "title" : 1.0 }', defaultlanguage=english, languageoverride=language))
Check constraints:
    "shard_key_value_check" CHECK (shard_key_value = '112'::bigint)

Both execution plans indicate that the text index was used to directly retrieve 13 keys (totalKeysExamined: 13 in the MongoDB-compatible plan and rows=13 in the PostgreSQL plan) from 10 index pages (numBlocksFromCache: 10, Buffers: shared hit=10) and then fetch the corresponding documents for the results.

The MongoDB text index is implemented in PostgreSQL as a RUM index through the DocumentDB extension. This highlights PostgreSQL’s robust open-source ecosystem and community. Vanilla PostgreSQL offers a permissive license and supports extensions for additional data types, such as BSON. PostgresPro improved text search with RUM indexes that include scoring. Microsoft also added support for BSON text search with RUM. Ismael developed Docker images containing all necessary extensions. As a result, we can now easily connect and utilize this unified multi-model database.

The experimentation is limitless. With this pglayers-full image, you can use plenty of extensions (some were already enabled by DocumentDB):

postgres=# CREATE EXTENSION IF NOT EXISTS documentdb;

NOTICE:  extension "documentdb" already exists, skipping
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS vector;
NOTICE:  extension "vector" already exists, skipping
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS postgis;

NOTICE:  extension "postgis" already exists, skipping
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS timescaledb;

ERROR:  function "time_bucket" already exists with same argument types

postgres=# CREATE EXTENSION IF NOT EXISTS pg_textsearch;
CREATE EXTENSION
postgres=#

postgres=# CREATE EXTENSION IF NOT EXISTS pg_graphql;
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS orafce;
CREATE EXTENSION

postgres=# CREATE EXTENSION IF NOT EXISTS pg_duckdb;
CREATE EXTENSION

I selected DocumentDB for this demonstration because it offers numerous advantages as a document database. The DocumentDB extension for PostgreSQL provides a fully open-source solution for MongoDB applications—both the MongoDB client and PostgreSQL server are OSS, as is the DocumentDB extension, which is part of the Linux Foundation. Although other converged databases and MongoDB emulations exist, they often lack the same level of freedom and features. For example, the index shown here, which combines document and full-text search capabilities, isn't supported in Oracle's MongoDB emulation:

I haven't selected a particular case here. I just created the index from the Sample Dataset provided by MongoDB's beginner courses.

Next time you hear about multi-model, converged databases, or native APIs, remember that PostgreSQL was built by Michael Stonebraker before those marketing labels (THE DESIGN OF POSTGRES - 1986). It was specifically designed to take a relational foundation and make it extensible enough to natively support complex objects and abstract data types. Over time, SQL databases experimented with ORDBMS, and NoSQL databases promoted multi-model approaches, which became quite popular. RDBMS vendors responded by adding different API layers on top of their SQL schemas, calling it converged. Meanwhile, the PostgreSQL ecosystem continued to innovate by integrating native data types and access methods through its very flexible, extensible architecture. This extension framework allows developers to plug in new data types and index types as native extensions rather than transformations in the query layer. It's highly compatible with Docker image layers, and projects like pglayers.github.io use it to improve the developer experience.

ducksdb-mysql-engine is an experimental build of MySQL 9.7 where a table you mark ENGINE=DuckDB answers analytical queries from DuckDB instead of InnoDB. Same server, same connection, no second copy of the data. On TPC-H at scale factor 10, InnoDB times out on 6 of the 22 queries and burns 1317 seconds on the 16 it … Continued

The post Running DuckDB as a MySQL 9.7 storage engine appeared first on Percona.

I recently commented on Jonathan Lewis’s blog, Savepoint Funny, where I compared how PostgreSQL handles uniqueness differently: “PostgreSQL resolves uniqueness through heap tuple visibility". This deserves a more detailed explanation.

In Oracle, unique indexes store unique entries because the B-tree key is the index key, preventing duplicates. Non-unique indexes add the ROWID to ensure that all entries are physically unique, even when indexed column values are duplicated.

In PostgreSQL, all indexes, even unique ones, created explicitly by CREATE UNIQUE INDEX or implicitly to enforce a unique constraint, behave like non-unique indexes by appending the TID (tuple ID, similar to Oracle's ROWID) to the index key. This indicates that the index itself doesn't guarantee physical uniqueness, allowing multiple entries to have identical logical keys but point to different heap tuples. The actual uniqueness verification occurs at the heap level, not within the index entries.

Initially, this might seem unusual—a unique index that permits duplicates. However, PostgreSQL requires this because of its MVCC system. MVCC allows duplicate entries to coexist in an index, since they can represent different versions of the same logical row. Still, PostgreSQL must guarantee that no MVCC snapshot views two rows with the same index key. Oracle doesn't face this issue because its MVCC implementation also versions index blocks, allowing a single index version to maintain unique keys.

Let’s show that.

Page inspect

In PostgreSQL, the heap contains the table data, and index entries point to heap tuples. Visibility depends on the heap header, especially the transaction information. Index scans often visit the heap pages to check visibility, except for index-only scans, which use the heap's visibility maps as an optimization. B-tree indexes can store entries for multiple versions of the same logical row, including versions that are no longer visible to current snapshots. To ensure uniqueness, the B-tree must check the heap for matching keys to verify whether multiple entries point to visible heap tuples in the same MVCC snapshot.

I used pageinspect to look at heap and index pages. This is not application-level SQL. This is a physical page inspection, useful for debugging and understanding internals. I used it to see all tuple versions, including those not visible to my MVCC snapshot. In some ways, you can compare it to Oracle flashback query, which shows all versions of a row.

CREATE EXTENSION IF NOT EXISTS pageinspect;

DROP TABLE IF EXISTS demo_unique_mvcc;

CREATE TABLE demo_unique_mvcc
(
    id      bigint generated always as identity,
    email   text not null,
    payload text not null,
    marker  int  not null,
    CONSTRAINT demo_unique_mvcc_email_key UNIQUE (email)
) WITH ( FILLFACTOR = 10 );

CREATE INDEX demo_unique_mvcc_marker_idx
ON demo_unique_mvcc(marker);

ALTER TABLE demo_unique_mvcc SET (autovacuum_enabled = false);

I disabled auto-vacuum to avoid garbage collection during my experimentation.

Unique index

I created a unique constraint on email:

postgres=# \d demo_unique_mvcc
                     Table "public.demo_unique_mvcc"
 Column  |  Type   | Collation | Nullable |           Default
---------+---------+-----------+----------+------------------------------
 id      | bigint  |           | not null | generated always as identity
 email   | text    |           | not null |
 payload | text    |           | not null |
 marker  | integer |           | not null |
Indexes:
    "demo_unique_mvcc_email_key" UNIQUE CONSTRAINT, btree (email)
    "demo_unique_mvcc_marker_idx" btree (marker)

postgres=#

I also created another index on marker. This is deliberate. If I update only a non-indexed column, PostgreSQL may use HOT updates, so no new entries are needed in the indexes. I want a non-HOT update to show the general case, so I update an indexed column, marker, while keeping the same email. To prove my point, I've set FILLFACTOR to 10% so that HOT updates are possible.

I inserted one row:

postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
           VALUES ('a@example.com', 'first version', 1)
;

INSERT 0 1

postgres=# SELECT ctid, xmin, xmax, *
           FROM demo_unique_mvcc
;

 ctid  | xmin | xmax | id |     email     |    payload    | marker
-------+------+------+----+---------------+---------------+--------
 (0,1) |  697 |    0 |  1 | a@example.com | first version |      1

(1 row)

The precise xmin may vary as it's your transaction identifier. What's crucial is the ctid. In this case, the visible row version is (0,1), which is the first tuple on the first page.

Now let’s look at the heap page:

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |      0 | (0,1)  |       2306 |           4

(1 row)

This tuple was inserted by committed transaction 697, has not been deleted or updated (xmax is invalid), contains at least one variable-length column, and stores 4 attributes.

I inspect the unique index. For a B-tree index, block 0 is the metapage, so the first leaf page is usually block 1 for a tiny index:

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(1 row)

One visible row. One index entry in block 1 of the index (ctid) addressing block 1 of the heap table (htid). Nothing surprising yet.

Multiple updates

I updated the row two times, but I did not change the unique key:

postgres=# UPDATE demo_unique_mvcc
           SET payload = 'second version', marker  = 2
           WHERE email = 'a@example.com'
;

UPDATE 1

postgres=# UPDATE demo_unique_mvcc
           SET payload = 'third version', marker  = 3
           WHERE email = 'a@example.com'
;

UPDATE 1

The email value did not change. Logically, there is still one row with email = 'a@example.com':

postgres=# SELECT ctid, xmin, xmax, *
           FROM demo_unique_mvcc
;

 ctid  | xmin | xmax | id |     email     |    payload    | marker
-------+------+------+----+---------------+---------------+--------
 (0,3) |  711 |    0 |  1 | a@example.com | third version |      3

(1 row)

Only one row is visible to my SQL query.

But the heap page tells a different physical story:

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |      0 | (0,3)  |      10498 |           4

(3 rows)

There are three heap tuple versions. The first is no longer current and has been superseded by transaction 710, which has set xmax. The second one is the transaction 710 change superseded by transaction 711. The third is the current version with xmax set to 0. Note that t_ctid points to the next version, so it can chain from old versions to new ones, except for the current version, which points to itself. Typically, here, an index entry pointing to (0,1) (line pointer lp=1 in block 0) can continue to (0,2) and then (0,3).

The old versions are not visible to my current query, but they are still physically present because I disabled autovacuum and have not vacuumed the table.

Now inspect the unique index:

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,2) | (0,2) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          3 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(3 rows)

This is the important point.

The unique index can physically hold multiple entries for the same logical key (data) because these entries point to different versions of heap tuples (htid).

Duplicate key violations

I tried to insert the same email into a new row:

postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
           VALUES ('a@example.com', 'another logical row', 10)
;

ERROR:  duplicate key value violates unique constraint "demo_unique_mvcc_email_key"
DETAIL:  Key (email)=(a@example.com) already exists.

postgres=#

This fails. The B-tree found matching key entries in the index. Then PostgreSQL checked the heap tuple visibility of the referenced tuples. At least one conflicting tuple is visible, so this is a uniqueness violation. The index alone did not decide everything. The heap visibility check decides whether the duplicate index entry is a real conflict.

The duplicate tuple was inserted into the heap before the statement was aborted, and this is visible from the physical history:

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |      0 | (0,3)  |      10498 |           4
  4 |    712 |      0 | (0,4)  |       2050 |           4

(4 rows)

PostgreSQL does not determine visibility from xmin/xmax alone. The infomask bits tell whether the transaction status is already known (committed, aborted, invalid, updated, locked, etc.). When the bits are not set, PostgreSQL consults the transaction status and may later set hint bits in the tuple header:

postgres=# SELECT txid_status('744')
;

 txid_status
-------------
 aborted

(1 row)

postgres=# SELECT txid_status('742')
;

 txid_status
-------------
 committed

(1 row)

Nothing changed in the index. It was used to find the multiple versions, but a duplicate key was detected before updating it:

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,2) | (0,2) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          3 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(3 rows)

It is not visible here, but even if uniqueness is detected on the heap tuples, it is still part of the index access methods because concurrency control must synchronize transactions by locking the index leaf page, which is the only place where all transactions updating the same key must go, since heap tuples could be in different blocks. This explains why a unique index is required to enforce unique constraints, even though duplicate checking itself is done on the heap.

Heap-Only Tuples

I mentioned HOT updates. As I've set a low fill factor and still have lots of free space in the block (as we confirm with ctid, all updates are within the same block), an update of a single indexed column may not add a new entry:

postgres=# UPDATE demo_unique_mvcc
           SET payload = 'fourth version' --, marker  = 2
           WHERE email = 'a@example.com'
;

UPDATE 1

postgres=# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |    713 | (0,5)  |       8450 |       16388
  4 |    712 |      0 | (0,4)  |       2050 |           4
  5 |    713 |      0 | (0,5)  |      10242 |       32772

(5 rows)

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,1) | (0,1) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          2 | (0,2) | (0,2) | t    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000
          3 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(3 rows)

The t_infomask2 added HEAP_ONLY_TUPLE (32768) to the number of attributes (4), indicating it has no index entry of its own. The entry pointing to (0,3) will follow the HOT chain to find the other version within the same page.

Deferred unique constraint

One last test. We have seen that a duplicate key insert leaves a tuple in the heap but no new entry in the index because the duplicate is detected during index update. However, if the unique constraint checking is deferred, the entry will be stored, and the detection happens at commit:

postgres=# ALTER TABLE demo_unique_mvcc   
           DROP CONSTRAINT demo_unique_mvcc_email_key
;  

postgres=# ALTER TABLE demo_unique_mvcc   
           ADD CONSTRAINT demo_unique_mvcc_email_key   
           UNIQUE (email) DEFERRABLE;

postgres=# SELECT itemoffset, ctid, htid, dead,
    data, encode(decode(replace(substr(data,4),' ',''), 'hex'),'escape')
           FROM bt_page_items('demo_unique_mvcc_email_key', 1)
           ORDER BY itemoffset
;

 itemoffset | ctid  | htid  | dead |                      data                       |        encode
------------+-------+-------+------+-------------------------------------------------+-----------------------
          1 | (0,3) | (0,3) | f    | 1d 61 40 65 78 61 6d 70 6c 65 2e 63 6f 6d 00 00 | a@example.com\000\000

(1 row)

postgres=# BEGIN;

BEGIN

postgres=# SET CONSTRAINTS demo_unique_mvcc_email_key DEFERRED
;

SET CONSTRAINTS

postgres=# INSERT INTO demo_unique_mvcc(email, payload, marker)
           VALUES ('a@example.com', 'a duplicate row', 10);
;

INSERT 0 1

postgres=*# SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_infomask2
           FROM heap_page_items(get_raw_page('demo_unique_mvcc', 0))
           ORDER BY lp
;

 lp | t_xmin | t_xmax | t_ctid | t_infomask | t_infomask2
----+--------+--------+--------+------------+-------------
  1 |    697 |    710 | (0,2)  |       1282 |           4
  2 |    710 |    711 | (0,3)  |       9474 |           4
  3 |    711 |    713 | (0,5)  |       9474 |       16388
  4 |    712 |      0 | (0,4)  |       2562 |           4
  5 |    713 |      0 | (0,5)  |      10498 |       32772
  6 |    718 |      0 | (
                                    
                                    
                                    

                                        by Franck Pachot