Skip to content

ctid and other PostgreSQL table internals

While attending a conference (yay) the discussion turned to system columns, and especially ctid. This was in context of backups, and the physical position of data on disk. The question was if that can be used to determine if an incremental backup is required, and if it is enough to just copy that field where the ctid points to. Not quite, but I’d like to use this question to describe the system columns more in detail.

In a PostgreSQL table one may find - depending on the server version - the following additional system columns:

  • tableoid
  • xmin
  • xmax
  • cmin
  • cmax
  • ctid
  • oid

Most of the columns exist in every table, but are excluded when doing a SELECT *. However one can include the column(s) in a query, and then PostgreSQL will return the values for the columns as well.

 

 

Let’s go into details:

tableoid

The tableoid is the OID from the table where the row is originally coming from. In a simple table, this is just the OID of the table itself.

oid=# CREATE TABLE show_oid (id INT);
CREATE TABLE
oid=# SELECT 'public.show_oid'::regclass::oid;
  oid  
-------
 79824
(1 row)

oid=# INSERT INTO show_oid VALUES (1), (2);
INSERT 0 2
oid=# SELECT tableoid, id FROM show_oid;
 tableoid | id 
----------+----
    79824 |  1
    79824 |  2
(2 rows)

All rows are coming from the table with the oid = 79824.

This changes once partitioning is used. In PostgreSQL, a partition is an inherited table. Simply said, the data lives in a child table (the partition), and when the parent table is queried, PostgreSQL also searches all child tables for data. Here is an example with partitions. First let's create a parent table, and partition it by range:

oid=# DROP TABLE IF EXISTS show_oid;
DROP TABLE
oid=# CREATE TABLE show_oid (id INT) PARTITION BY RANGE (id);
CREATE TABLE
oid=# CREATE TABLE show_oid_p1 PARTITION OF show_oid
oid-#    FOR VALUES FROM (1) TO (10);
CREATE TABLE
oid=# CREATE TABLE show_oid_p2 PARTITION OF show_oid
oid-#    FOR VALUES FROM (10) TO (20);
CREATE TABLE
oid=# CREATE TABLE show_oid_p3 PARTITION OF show_oid
oid-#    FOR VALUES FROM (20) TO (30);
CREATE TABLE
oid=# SELECT 'public.show_oid'::regclass::oid AS show_oid,
oid-#        'public.show_oid_p1'::regclass::oid AS show_oid_p1,
oid-#        'public.show_oid_p2'::regclass::oid AS show_oid_p2,
oid-#        'public.show_oid_p3'::regclass::oid AS show_oid_p3;
 show_oid | show_oid_p1 | show_oid_p2 | show_oid_p3 
----------+-------------+-------------+-------------
    79827 |       79830 |       79833 |       79836
(1 row)

We can see that each partition has a different oid. Now let's insert some data:

oid=# INSERT INTO show_oid VALUES (1), (2), (9), (10), (11), (19), (20), (21), (29);
INSERT 0 9
oid=# SELECT tableoid, id FROM show_oid ORDER BY tableoid, id;
 tableoid | id 
----------+----
    79830 |  1
    79830 |  2
    79830 |  9
    79833 | 10
    79833 | 11
    79833 | 19
    79836 | 20
    79836 | 21
    79836 | 29
(9 rows)

Even though the data is selected from the parent table (show_oid, oid = 79827), there is not a single row returning data from this table. All the data comes from one of the partitions. During inserting the data, PostgreSQL moved the data into the partition, according to the partition key. The parent table stays empty:

oid=# SELECT tableoid, id FROM ONLY show_oid ORDER BY tableoid, id;
 tableoid | id 
----------+----
(0 rows)

The ONLY keyword here tells PostgreSQL not to scan any inherited tables. Since the parent table is empty, no rows are returned.

xmin + xmax

The xmin and xmax columns are holding transaction IDs. xmin shows when the row becomes visible (minimum required transaction ID), xmax shows when the row is no longer visible (maximum valid transaction ID). In a relational database like PostgreSQL, there can be many ongoing transactions at the same time. Every transaction has it's own transaction ID, which can be queried:

oid=# SELECT txid_current();
 txid_current 
--------------
         7034
(1 row)

oid=# SELECT txid_current();
 txid_current 
--------------
         7035
(1 row)

The example above is wasting two transaction IDs: every command in PostgreSQL is wrapped in a transaction. If you don't start a transaction, PostgreSQL will automatically wrap every command in a single transaction. That is the reason why the transaction ID in the example is increasing with every call of the txid_current() function.

Back to our problem: every transaction can change data. The transaction isolation level specifies which transaction can see which changes. Once data is committed, every later transaction can always see the changes - that part is easy. For ongoing transactions, the transaction ID specifies which changes become visible at which point. Let's look at an example. First we need a table:

oid=# CREATE TABLE show_xmin_xmax (id INT PRIMARY KEY);
CREATE TABLE

Then we start a transaction and insert data:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7058
(1 row)

oid=# INSERT INTO show_xmin_xmax VALUES (1), (2);
INSERT 0 2
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  1
 7058 |    0 |  2
(2 rows)

oid=# COMMIT;
COMMIT

The transaction ID is 7058, and this value appears in the xmin column. The xmax column is 0, because this data is not deleted (or updated).

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7059
(1 row)

oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  1
 7058 |    0 |  2
(2 rows)

oid=# UPDATE show_xmin_xmax SET id = 3 WHERE id = 1;
UPDATE 1
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  2
 7059 |    0 |  3
(2 rows)

oid=# COMMIT;
COMMIT

Once the data is updated, a new row version appears, with xmin = 7059. What we can't see here is that the old row (id = 1) is also still in the table, however the xmax value is now set to 7059 as well, and becomes invisible to the current and any newer transaction. Right now we can't see this deleted row, however it is still there. Later in this blog post we learn how to make this data visible.

There is however a trick to make the changes visible when deleting data:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7060
(1 row)

oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7058 |    0 |  2
 7059 |    0 |  3
(2 rows)

oid=# DELETE FROM show_xmin_xmax WHERE id = 2 RETURNING xmin, xmax, *;
 xmin | xmax | id 
------+------+----
 7058 | 7060 |  2
(1 row)

DELETE 1
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7059 |    0 |  3
(1 row)

oid=# COMMIT;
COMMIT

The RETURNING clause for DELETE can also return the xmin and xmax columns. As we can see in this example, the returnes data has xmax set to 7060 - the current transaction ID. Afterwards the deleted data is no longer visible for regular queries.

cmin + cmax

Like xmin and xmax, cmin and cmax store transaction information. However these two columns store the command sequence number inside the transaction. Imagine you start a transaction and execute multiple commands: every time a command changes data, this changes are stored with an increased cmin and cmax information. This is useful to trace back which changes happened at which point during a transaction, and it helps replaying changes in the correct order. Example table:

oid=# CREATE TABLE show_cmin_cmax (id INT PRIMARY KEY);
CREATE TABLE

Let's insert some data, but this time using multiple commands in a transaction:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7042
(1 row)

oid=# INSERT INTO show_cmin_cmax VALUES (1), (2);
INSERT 0 2
oid=# INSERT INTO show_cmin_cmax VALUES (3), (4);
INSERT 0 2
oid=# INSERT INTO show_cmin_cmax VALUES (5), (6);
INSERT 0 2
oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    0 |    0 |  1
 7042 |    0 |    0 |    0 |  2
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
(6 rows)

oid=# COMMIT;
COMMIT

We can see that xmin for all rows is 7042, however cmin and cmax are increased with every INSERT. Data which was inserted using the same INSERT command has the same cmin and cmax number. Let's update some data:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7043
(1 row)

oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    0 |    0 |  1
 7042 |    0 |    0 |    0 |  2
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
(6 rows)

oid=# UPDATE show_cmin_cmax SET id = 10 WHERE id = 1;
UPDATE 1
oid=# UPDATE show_cmin_cmax SET id = 20 WHERE id = 2;
UPDATE 1
oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
 7043 |    0 |    0 |    0 | 10
 7043 |    0 |    1 |    1 | 20
(6 rows)

oid=# COMMIT;
COMMIT

We can see how the transaction ID in xmin changes, because the UPDATE happens in a new transaction (7043). And the cmin and cmax again start at zero. Let's also try the trick with DELETE and RETURNING:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7044
(1 row)

oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    1 |    1 |  3
 7042 |    0 |    1 |    1 |  4
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
 7043 |    0 |    0 |    0 | 10
 7043 |    0 |    1 |    1 | 20
(6 rows)

oid=# DELETE FROM show_cmin_cmax WHERE id = 3 RETURNING xmin, xmax, cmin, cmax, *;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 | 7044 |    0 |    0 |  3
(1 row)

DELETE 1
oid=# DELETE FROM show_cmin_cmax WHERE id = 4 RETURNING xmin, xmax, cmin, cmax, *;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 | 7044 |    1 |    1 |  4
(1 row)

DELETE 1
oid=# SELECT xmin, xmax, cmin, cmax, id FROM show_cmin_cmax order by id;
 xmin | xmax | cmin | cmax | id 
------+------+------+------+----
 7042 |    0 |    2 |    2 |  5
 7042 |    0 |    2 |    2 |  6
 7043 |    0 |    0 |    0 | 10
 7043 |    0 |    1 |    1 | 20
(4 rows)

oid=# COMMIT;
COMMIT

Because both deletes happen in the same transaction (7044), both returned rows show this transaction ID as xmax. cmin and cmax is increasing for every command.

ctid

The ctid column consists of two parts: the page or block in the table, and the position in this page. Let's show at a slightly more complex example:

oid=# CREATE TABLE show_ctid (id INT PRIMARY KEY, data TEXT);
CREATE TABLE
oid=# ALTER TABLE show_ctid ALTER COLUMN data SET STORAGE plain;
ALTER TABLE
oid=# INSERT INTO show_ctid VALUES (1, 'Lorem'), (2, 'ipsum'), (3, 'dolor'), (4, 'sit'), (5, 'amet');
INSERT 0 5

This table has an integer as primary key, and a text field as data field. The first 5 words from Lorem ipsum are used as text. The storage type for the column is changed to "plain", to keep the data uncompressed and in the table itself, and don't "outsource" it into a TOAST table.

oid=# \d+ show_ctid
                                 Table "public.show_ctid"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           | not null |         | plain   |              | 
 data   | text    |           |          |         | plain   |              | 
Indexes:
    "show_ctid_pkey" PRIMARY KEY, btree (id)
Access method: heap

Let's look at the ctid:

oid=# SELECT ctid, id, data FROM show_ctid order by id;
 ctid  | id | data  
-------+----+-------
 (0,1) |  1 | Lorem
 (0,2) |  2 | ipsum
 (0,3) |  3 | dolor
 (0,4) |  4 | sit
 (0,5) |  5 | amet
(5 rows)

Page 0, positions 1 to 5. But that's not really helpful, isn't it? Luckily there is a very useful extension which allows us to peek into the details: pageinspect. It comes in PostgreSQL contrib, and should be available on every system. You need to install it:

oid=# CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION

Now we can use the get_raw_page() function to select the page, and decode it using heap_page_items():

oid=# SELECT *
oid-#   FROM heap_page_items(get_raw_page('show_ctid', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------
  1 |   8152 |        1 |     34 |   7049 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000d4c6f72656d
  2 |   8112 |        1 |     34 |   7049 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d697073756d
  3 |   8072 |        1 |     34 |   7049 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x030000000d646f6c6f72
  4 |   8040 |        1 |     32 |   7049 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x0400000009736974
  5 |   8000 |        1 |     33 |   7049 |      0 |        0 | (0,5)  |           2 |       2306 |     24 |        |       | \x050000000b616d6574
(5 rows)

There is t_ctid, which is the position in the page. There are a couple more details, described here. And there is t_data, which holds the row data. For decoding the data, we need to have a bit of knowledge what this data is. First of all, the data is a bytea, and is therefore hex-encoded. This can be decoded:

oid=# SELECT *,
oid-#        encode(t_data, 'escape') AS decoded
oid-#   FROM heap_page_items(get_raw_page('show_ctid', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         |           decoded           
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------+-----------------------------
  1 |   8152 |        1 |     34 |   7049 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000d4c6f72656d | \x01\000\000\000\rLorem
  2 |   8112 |        1 |     34 |   7049 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d697073756d | \x02\000\000\000\ripsum
  3 |   8072 |        1 |     34 |   7049 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x030000000d646f6c6f72 | \x03\000\000\000\rdolor
  4 |   8040 |        1 |     32 |   7049 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x0400000009736974     | \x04\000\000\000        sit
  5 |   8000 |        1 |     33 |   7049 |      0 |        0 | (0,5)  |           2 |       2306 |     24 |        |       | \x050000000b616d6574   | \x05\000\000\000\x0Bamet
(5 rows)

That makes a bit more sense, but not much more. For PostgreSQL, the entire t_data field is the data from each column combined. And since the table structure is known, there is no separator required. Let's have a look at the table structure again:

  1. id INT
  2. data TEXT

Integers are 4 bytes wide, which means we need to look at the first 4 bytes:

  • 01\000\000\000 -> 01 00 00 00
  • 02\000\000\000 -> 02 00 00 00
  • 03\000\000\000 -> 03 00 00 00
  • 04\000\000\000 -> 04 00 00 00

This system is little-endian, which means that the least significant byte is stored at the smallest (first) address. These 4 numbers are the primary key in the id field, expanded to 32 bit (4 bytes), because it's an integer.

But what about this part here: "\rLorem"? That's a bit more complicated.

It's a text field (variable length). The text in PostgreSQL is a C struct: a length field vl_len, which is - usually - 4 bytes long. And a data field vl_dat, which can hold up to 1 GB of data. Specifying the length allows PostgreSQL to store arbitrary data, including the famous \000 in C. However it's more complicated than that, and vl_len holds more information. We need to look at this field in a bit representation:

oid=# SELECT *,
oid-#        encode(t_data, 'escape') AS decoded,
oid-#        ('x' || encode(SUBSTR(t_data, 5, 1), 'hex'))::BIT(8) AS bits
oid-#   FROM heap_page_items(get_raw_page('show_ctid', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |         t_data         |           decoded           |   bits   
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------------------+-----------------------------+----------
  1 |   8152 |        1 |     34 |   7049 |      0 |        0 | (0,1)  |           2 |       2306 |     24 |        |       | \x010000000d4c6f72656d | \x01\000\000\000\rLorem     | 00001101
  2 |   8112 |        1 |     34 |   7049 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000000d697073756d | \x02\000\000\000\ripsum     | 00001101
  3 |   8072 |        1 |     34 |   7049 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x030000000d646f6c6f72 | \x03\000\000\000\rdolor     | 00001101
  4 |   8040 |        1 |     32 |   7049 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x0400000009736974     | \x04\000\000\000        sit | 00001001
  5 |   8000 |        1 |     33 |   7049 |      0 |        0 | (0,5)  |           2 |       2306 |     24 |        |       | \x050000000b616d6574   | \x05\000\000\000\x0Bamet    | 00001011
(5 rows)

When the lowest order bit is set (on little-endian), the length field is only the remaining 7 bits, not the entire 4 bytes. It also means that the remaining data is up to 127 bytes long (2^7 - 1 = 127), the total length includes the byte for the length. This is a shortcut to store short values (up to 127 bytes) more efficiently.

If the lowest bit is not set, the second lowest bit is used to store more information. This leaves 30 bits (4 bytes = 32 bits, minus 2 bits status) for the length information, or 2^30 - 1 = 1073741823 bytes for uncompressed data. Toast data can be compressed, and can therefore store more than roughly the 1 GB.

In our case, the lowest bit is set for all 5 rows, and we need to look at the remaining bits:

  1. 0000110 -> 6
  2. 0000110 -> 6
  3. 0000110 -> 6
  4. 0000100 -> 4
  5. 0000101 -> 5

6 bytes ("Lorem" = 5 bytes, plus length byte), 4 bytes ("sit" = 3 bytes, plus length byte) and 5 bytes ("Bamet" = 4 bytes, plus length byte).

Shorter values will always go into the one byte form, to save space. Decoding of this information is usually done by C macros.

Back to xmin and xmax

Earlier we looked into the show_xmin_xmax table, updated and deleted some data, but could not verify that the deleted data is still in the table. pageinspect can help with that as well:

oid=# SELECT *,
oid-#        encode(t_data, 'escape') AS decoded
oid-#   FROM heap_page_items(get_raw_page('show_xmin_xmax', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   |     decoded      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------+------------------
  1 |   8160 |        1 |     28 |   7058 |   7059 |        0 | (0,3)  |        8193 |       1280 |     24 |        |       | \x01000000 | \x01\000\000\000
  2 |   8128 |        1 |     28 |   7058 |   7060 |        0 | (0,2)  |        8193 |        256 |     24 |        |       | \x02000000 | \x02\000\000\000
  3 |   8096 |        1 |     28 |   7059 |      0 |        0 | (0,3)  |           1 |      10496 |     24 |        |       | \x03000000 | \x03\000\000\000
(3 rows)

We see 3 entries in the table:

  • The deleted entry (id = 1) is at the first position, and xmax is 7059
  • The second entry is id = 2, also deleted, by transaction 7060
  • The third entry is the result of the UPDATE operation, which marked the id = 1 row as deleted, and at the same time inserted a new row with id = 3, this operation has xmin = 7059

Let's run a VACUUM on the table:

oid=# VACUUM show_xmin_xmax;
VACUUM

And look at the table again:

oid=# SELECT *,
       encode(t_data, 'escape') AS decoded
  FROM heap_page_items(get_raw_page('show_xmin_xmax', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   |     decoded      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------+------------------
  1 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |            | 
  2 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |            | 
  3 |   8160 |        1 |     28 |   7059 |      0 |        0 | (0,3)  |           1 |      10496 |     24 |        |       | \x03000000 | \x03\000\000\000
(3 rows)

The first two entries are gone, the space is empty and can be re-used by future DML operations. However PostgreSQL does not remove the space, or give it back to the operating system. Let's insert a new row:

oid=# BEGIN;
BEGIN
oid=# SELECT txid_current();
 txid_current 
--------------
         7061
(1 row)

oid=# INSERT INTO show_xmin_xmax VALUES (5);
INSERT 0 1
oid=# SELECT xmin, xmax, id FROM show_xmin_xmax ORDER BY id;
 xmin | xmax | id 
------+------+----
 7059 |    0 |  3
 7061 |    0 |  5
(2 rows)

oid=# COMMIT;
COMMIT

Just one row, Vasili.

oid=# SELECT *,
       encode(t_data, 'escape') AS decoded
  FROM heap_page_items(get_raw_page('show_xmin_xmax', 0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data   |     decoded      
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+------------+------------------
  1 |   8128 |        1 |     28 |   7061 |      0 |        0 | (0,1)  |           1 |       2048 |     24 |        |       | \x05000000 | \x05\000\000\000
  2 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |            | 
  3 |   8160 |        1 |     28 |   7059 |      0 |        0 | (0,3)  |           1 |      10496 |     24 |        |       | \x03000000 | \x03\000\000\000
(3 rows)

The database did not append this new row at the end of the page, or wrote it into another page. Instead it re-used the available space, and wrote it into ctid = (0, 1).

oid

The last of the columns is oid. These days, having an oid for rows is no longer supported. In a more recent version of PostgreSQL, trying to add an OID field raises an error:
 

oid=# CREATE TABLE with_oid () WITH (OIDS=TRUE);
ERROR:  tables declared WITH OIDS are not supported

In versions up to 11, this was still supported, but not encouraged. What it means is that every table with OIDS=TRUE has an additional column named "oid", which is populated by a different transaction id - increasing the need to run VACUUM to prevent a Transaction ID Wraparound.

Summary

Every PostgreSQL table has a couple additional columns, which are hidden by default, but can be shown when included in a query. Knowing the details of each column is helpful, as it gives very good insight into the table structure and the way PostgreSQL organizes the data.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options