Search code examples
databasepostgresqlrdbms

Why does Postgres return same ctid values for different rows across multiple tables?

My understanding of ctid was that it returns the physical location of the row on disk by giving page_id and the offset in that page where the particular row / tuple is stored. But when try to fetch ctid for different tables, i get same ctids. So I am really confused about what exactly ctid returns? It cannot be the "actual" physical location on disk since there can only be one row at a given location on disk. Attaching a picture below of the ctid values of 5 rows in two different postgres tables:

enter image description here

Solution

  • You're almost correct - it's not the absolute location of the tuple, it's its location within the table. See the doc on system columns:

    ctid The physical location of the row version within its table. (...)

    tableoid The OID of the table containing this row. (...)

    In a postal system analogy, tableoid is the building address, ctid is the floor and apartment number.

    The ctid's aren't globally unique on their own, but they are unique in combination with tableoid: demo at db<>fiddle

    create table a (a int);
create table b (b int);
insert into a select generate_series(1,3) returning tableoid,ctid;
    tableoid ctid
    16384 (0,1)
    16384 (0,2)
    16384 (0,3) 
    insert into b select generate_series(1,3) returning tableoid,ctid;
    tableoid ctid
    16387 (0,1)
    16387 (0,2)
    16387 (0,3)

    You might want to check out Chapter 73. Database Physical Storage of the official documentation.