postgres CTID - ghdrako/doc_snipets GitHub Wiki

• https://nidhig631.medium.com/ctid-field-in-postgresql-d26977de7b58

CTID`` field in PostgreSQL which is equivalent to ROWID` in Oracle.

The CTID field is a field that exists in every PostgresSQL table, it is always unique for each and every record in the table. It denotes the Physical location of the data.

One of the main advantages of having CTID in PostgreSQL is denoting the Physical location and providing an idea of how the rows are actually stored in a database table.

select ctid, id from users where id=1;
(0,1) , 1

value (0,1) here, the first digit 0 represents the page number, and the second 1 represents the tuple number. CTID field values are always sequential and unique. We can reset the CTID values by running VACUUM FULL on the table.

On a brand new database, this might return a value of (0,1) for ctid. This means the row version is in page number 0, and is tuple number 1.

update users set name='Jane' where id=1; 

in doing that, there’s a new tuple. When we inspect the ctid again, we’ll see that in the second portion of the ctid, the tuple number has changed. The page number may or may not change, depending on whether the new tuple is in the same page as before or in a different page. Even updating the same row again with the same value produces a new tuple, likely still in the same page (unless the page has been filled).