“CTID” field in PostgreSQL

In this article, we will look at the “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.

Let’s now move on to the practical way of checking how our rows are actually stored in a table. Consider a table called information.

Add some dummy data to this table from the information.csv mentioned below.

Medium_article_data/information.csv at master · Nidhig631/Medium_article_data

Verify once all the entries are made to the table.

select count(*) as total_record from information;

Now, It’s time to query our data along with the “CTID” field.

select ctid, id from information limit 5;

Observations:

1. Check how the physical location of the row is represented.
2. For the CTID first value (0,1) here, the first digit 0 represents the page number, and the second 1 represents the tuple number.
3. “CTID” field values are always sequential and unique.
4. If we update any row value in PostgreSQL its Physical location “CTID” field value also gets updated which means the new value will have a different “CTID”.

Problem statement:

Write an SQL query to update the id in the information table from 1 to 101. And verify the new and previous “CTID”

Solution:

UPDATE information SET id=101 WHERE id=1;

NEW CTID : select ctid,id from information where id=101;

OLD CTID : select ctid,id from information where id=1;

Note: After the update, the previous “CTID” is no longer visible.

So, we can conclude “CTID” are always sequential and unique. We can reset the “CTID” values by running “VACUUM FULL” on the table. (For more detailing on vacuum full refer: https://nidhig631.medium.com/vacuum-in-postgresql-9623a3b7e433)For more information on the max page number and tuple number.

Refer: https://www.postgresql.org/docs/12/limits.html

Do clap, if find useful✌. Do share your learnings from the databases🙂.