This article looks at how the ctid field in PostgreSQL functions as an approximate equivalent to ROWID in Oracle databases. The ctid field is introduced and its function as a unique identifier is then explained.
PostgreSQL does not have the ROWID pseudocolumn found in Oracle. However, the ctid field can definitely be treated as an equivalent of ROWID in the PostgreSQL database.
The ctid field is a field that exists in every PostgreSQL table. It is unique for each record in a table and denotes the location of the tuple. Similarly, the ROWID in Oracle can be beneficial because it provides an idea of how the rows are stored in a table and can be treated as the unique identifier for rows in that table.
Let's take a look at how ctid can be used to get an idea of how exactly rows are stored in a table. We know that tables and indexes are stored as an array of pages of a fixed size (usually 8KB, although a different page size can be selected when compiling the server). But what about the internal of the page—i.e., how the table rows are stored? This can be easily understood using ctid.
Create a table:
edb=# create table pi_postgres(id numeric primary key);
CREATE TABLE
edb=#
edb=#
edb=# insert into pi_postgres values(generate_series(1,10));
INSERT 0 10
edb=# select ctid,id from pi_postgres;
ctid | id
-------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 9
(0,10) | 10
(10 rows)
See how the ctid values represent the physical location of the row version within the table. For the first value (0,1), the first digit (0) represents the page number, and the second (1) stands for the tuple number. The ctid values are sequential and also depict the fundamental of multiversion concurrency control (MVCC). For example, if you will update a value then you will observe the new value will have a different ctid—the location corresponding to the older value will not be used, and new values will be inserted into a new location having a different ctid.
edb=# update pi_postgres set id=11 where id=2;
UPDATE 1
edb=# select ctid,* from pi_postgres;
ctid | id
-------+----
(0,1) | 1
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6.
(0,7) | 7
(0,8) | 8
(0,9) | 9
(0,10) | 10
(0,11) | 11
(10 rows)
Note: The ctid (0,2) is no longer visible or assigned to a row.
This example makes it clear why ctid can be treated as a unique identifier. The ctid of the rows remains intact after a row value is changed until a VACUUM FULL is performed. The VACUUM FULL rewrites the tables, and the ctid is reset.
edb=# vacuum full pi_postgres ;
VACUUM
edb=# select ctid,* from pi_postgres;
ctid | id
-------+----
(0,1) | 1
(0,2) | 3
(0,3) | 4
(0,4) | 5
(0,5) | 6
(0,6) | 7
(0,7) | 8
(0,8) | 9
(0,9) | 10
(0,10) | 11
(10 rows)
After VACUUM FULL, the ctid values changed and can be seen to be assigned sequentially. Further, while VACUUM ANALYZE doesn't reassign the ctid for the live tuples (it remains the same), the older ctid value would be reused, which confirms the behavior that the space corresponding to the deleted tuple can be reused after VACUUM ANALYZE.
edb=# update pi_postgres set id=12 where id=3;
UPDATE 1
edb=#
edb=# select ctid,* from pi_postgres;
ctid | id
-------+----
(0,1) | 1
(0,3) | 4
(0,4) | 5
(0,5) | 6
(0,6) | 7
(0,7) | 8.
(0,8) | 9
(0,9) | 10
(0,10) | 11
(0,11) | 12
(10 rows)
The ctid (0,2) is no longer visible, and the row corresponding to it is marked as deleted and can be reused after VACUUM ANALYZE.
Perform VACUUM ANALYZE:
edb=# vacuum analyze pi_postgres ;
VACUUM
edb=# select ctid,* from pi_postgres;
ctid | id
-------+----
(0,1) | 1
(0,3) | 4
(0,4) | 5
(0,5) | 6
(0,6) | 7
(0,7) | 8
(0,8) | 9
(0,9) | 10
(0,10) | 11
(0,11) | 12
(10 rows)
Insert a new record, which will use the ctid (0,2) corresponding to older row version:
edb=# insert into pi_postgres values(2);
INSERT 0 1
edb=# select ctid,* from pi_postgres;
ctid | id
-------+----
(0,1) | 1
(0,2) | 2
(0,3) | 4
(0,4) | 5
(0,5) | 6
(0,6) | 7
(0,7) | 8
(0,8) | 9
(0,9) | 10
(0,10) | 11
(0,11) | 12
(11 rows)
edb=#
Hope it helps !