PostgreSQL Update Internals
I recently covered the internals of a row in PostgreSQL, but that was just the storage piece. I got more curious and decided that I would look into what happens when a row gets updated. There are a lot of complexities to data, after all, and it’s nice to know how our database is going to be affected by updates.
Getting Set Up
I started by using the customer table from the pagila sample database. Rather than come up with a set of sample data, I figured it would be easy to work within an existing set of data.
The first trick was to find a customer to update. Since the goal is to look at an existing row, update it, and then see what happens to the row, we’ll need to be able to locate the row again. This is actually pretty easy to do. The first thing I did was retrieve the
ctid along with the rest of the data in the row. I did this by running:
SELECT ctid, * FROM customer ORDER BY ctid LIMIT 10 ;
This gives us the primary key of a customer to mess with as well as the location of the row on disk. We’re going to be looking at the customer with a
customer_id of 1: Mary Smith. Using that
select statement, we can see that Mary Smith’s data lives on page 0 and in row 1
Updating a Row
Now that we know who we’re going to update, we can go ahead and mess around with the data. We can take a look at the row on the disk using the
get_raw_page function to examine page 0 of the customer table. Mary Smith’s data is at the end of the page.
Why is Mary’s data the first row in the table but the last entry on the page? PostgreSQL starts writing data from the end of the page but writes item identifiers from the beginning of the page.
We already know that Mary’s row is in page 0, position 1 because of the
ctidwe retrieved in our first query. Let’s see what happens when we update some of Mary’s data. Open up a connection to PostgreSQL using your favorite interactive querying tool. I use
psql on the command prompt, but there are plenty of great tools out there.
BEGIN TRANSACTION UPDATE customer SET email = '[email protected]' WHERE customer_id = 1 ;
Don’t commit the transaction yet!
When we go to look for Mary’s data using the first
select ordered by
ctid, we won’t see her data anywhere.
Where did her data go? Interestingly enough, it’s in two places right now because we haven’t committed the transaction. In the current query window, run the following command:
SELECT ctid, xmin, xmax, * FROM customer WHERE customer_id = 1;
After running this, we can see that the customer’s row has moved off of page 0 and is now on page 8 in slot 2. The other interesting thing to note is that the
xmin value has changed. Transactions with a transaction id lower than
xminwon’t be able to see the row.
In another query window, run the previous select again. You’ll see that the row is still there with all of the original data present; the email address hasn’t changed. We can also see that both the
xmax columns now have values. This shows us the range of transactions where this row is valid.
Astute readers will have noticed that the row is on disk in two places at the same time. We’re going to dig into this in a minute, but for now go ahead and commit that first transaction. This is important because we want to look at what’s going on with the row after the update is complete. Looking at rows during the update process is interesting, but the after effects are much more interesting.
Looking at page 0 of the customer table, we can see that the original row is still present. It hasn’t been deleted yet. However, PostgreSQL has marked the row as being “old” by setting the
xmax value as well as setting the
t_ctid value to
00 00 00 08 00 02. This tells us that if we look on page 8 in position 2 we’ll find the newest version of the data that corresponds to Mary Smith. Eventually this old row (commonly called a dead tuple) will be cleaned up by the vacuum process.
If we update the row again, we’ll see that it moves to a new position on the page, from (8,2) to (8,3). If we did back in and look at the row, we’ll see that the
t_ctid value in Mary Smith’s record at page 8, slot 2 is updated from
00 00 00 00 00 00 to
00 00 00 08 00 03. We can even see the original row in the hex dump from page 8. We can see the same information much more legibly by using the
select * from heap_page_items(get_raw_page('customer', 8));
There are three rows listed on the page. The row with
lp 1 is the row that was originally on this page before we started messing around with Mary Smith’s email address.
lp 2 is the first update to Mary’s email address.
t_infomask2 on row 2 we can immediately see two things… I lied, I can’t immediately see anything apart from some large number. But, once I applied the bitmap deciphering technology that I call “swear repeatedly”, I was able to determine that this row was
HEAP_HOT_UPDATED and contains 10 attributes. Refer to
htup.h for more info about the bitmap meanings.
PostgreSQL has a unique feature called heap only tuples (HOT for short). The HOT mechanism is designed to minimize the load on the database server in certain update conditions:
- A tuple is repeatedly updated
- The updates do not change indexed columns
For definition purposes, an “indexed column” includes any columns in the index definition, whether they are directly indexes or are used in a partial-index predicate. If your index definition mentions it, it counts.
In our case, there are no indexes on the
customertable. The updates we’ve done are going to be HOT updates since they don’t touch any indexed columns. Whenever we update a new row, PostgreSQL is going to write a new version of the row and update the
t_ctid column in the most current row.
When we read from an index, PostgreSQL is going to read from the index and then follow the
t_ctid chains to find the current version of the row. This lets us avoid additional hits to disk when we’re updating rows. PostgreSQL just updates the row pointers. The indexes will still point to the original row, which points to the most current version of the row. We potentially take an extra hit on read, but we save on write.
To verify this, we can look at the index page contents using the
SELECT * FROM bt_page_items('idx_last_name', 2) ORDER BY ctid DESC;
We can find our record by moving through the different pages of the index. I found the row on page 2. We can locate our index row by matching up the
ctid from earlier runs. Looking at that row, we can see that it points to the
ctid of a row with a forwarding
ctid. PostgreSQL hasn’t changed the index at all. Instead, when we do a look up based on
idx_last_name, we’ll read from the index, locate any tuples with a last name of ‘SMITH’, and then look for those rows in the heap page. When we get to the heap page, we’ll find that the tuple has been updated. We’ll follow the update chain until we get to the most recent tuple and return that data.
If you want to find out more about the workings of the Heap Only Tuples feature of PostgreSQL, check out the
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.