On the Ctid of PostgreSQL

Ctid: Represents the physical business information of a data record, which is the displacement of the data block in which a record is located.   Same as the pseudo-column ROWID in Oracle, except in the form of a different one. For example, there is a table test; View the ctid of each row of records

mydb=> select ctid,* from test;
 ctid | id | name -------+------+-------- (0,1) | 1001 | lottu
 (0,2) | 1002 | rax
 (0,3) | 1003 | xuan
 (0,4) | 1004 | li0924
 (0,5) | 1001 | ak

Format (BLOCKID,ITEMID): Take one of them (0,1); 0 indicates that the block id;1 represents the first record in this section. 1. de-weight: We know that ROWID has an important role in Oracle, is used as a table to record the weight, the same ctid in PostgreSQL can also be used. For example, test table ID 1001 has two records; now demo;

mydb=> delete from test where ctid not in (select min(ctid) from test group by id); DELETE 1 mydb=> select ctid,* from test;
 ctid | id | name -------+------+-------- (0,1) | 1001 | lottu
 (0,2) | 1002 | rax
 (0,3) | 1003 | xuan
 (0,4) | 1004 | li0924
(4 rows)

We have just removed (0,5) this record; Now let's insert this record;

mydb=> insert into test values (1001,‘ak‘); INSERT 0 1 mydb=> select ctid,* from test;
 ctid | id | name -------+------+-------- (0,1) | 1001 | lottu
 (0,2) | 1002 | rax
 (0,3) | 1003 | xuan
 (0,4) | 1004 | li0924
 (0,6) | 1001 | ak
(5 rows)

Strange, why not (0,5), but (0,6) this is related to the multi-version of PostgreSQL, and the pseudo-column Cmin,cmax, with the ctid of this article is not related to, this is the characteristics of PostgreSQL; There is no concept of rollback in PostgreSQL, so how do you show it (0,5), think of this space (0,5) to store data, PostgreSQL autovacuum process, and of course we can manually reclaim this space;

mydb=> delete from test where name = ‘ak‘; DELETE 1 mydb=> vacuum test;          
VACUUM
mydb=> insert into test values (1001,‘ak‘); INSERT 0 1 mydb=> select ctid,* from test;
 ctid | id | name -------+------+-------- (0,1) | 1001 | lottu
 (0,2) | 1002 | rax
 (0,3) | 1003 | xuan
 (0,4) | 1004 | li0924
 (0,5) | 1001 | ak
(5 rows)

2. We have just said that 0 represents the block ID; The test data is too small; Create a new table Test2

mydb=> drop table test2;
DROP TABLE
mydb=> create table test2 (id int primary key, name varchar(10));
CREATE TABLE
mydb=> insert into test2 select generate_series(1,1000),‘lottu‘ || generate_series(1,1000);
INSERT 0 1000

We look at the blockid of the Ctid of id=1000, the answer is 5; it means that the record of the table is recorded in the 6th block; (because it starts from 0)

mydb=> select ctid,* from test2 where id = 1000;
  ctid  |  id  |   name    
--------+------+-----------
 (5,75) | 1000 | lottu1000
(1 row)

Of course, the table records accounted for a few blocks; if I were to insert it randomly, that id=1000; not necessarily in the 6th block; we can use the system view Pg_class, where relpages,reltuples represents the number of blocks, the number of records!

mydb=> analyze test2;
ANALYZE
mydb=> select relpages,reltuples from pg_class where relname = ‘test2‘;
 relpages | reltuples 
----------+-----------
        6 |      1000
(1 row)

Summary: The meaning of the existence of Ctid: the physical business information that represents the data record, which refers to which displacement of the data block is a record. Same as the pseudo-column ROWID in Oracle, except in the form of a different one. Vacuum: To reclaim a physical location that is not shown, indicating that it can continue to be used. Generate_series: For a sequence function, for example 1-100; generate_series (1,100), 0-100 Direct even generate_series (0,100,2) 0 Indicates the starting position of the sequence, 100 represents the end position, and 2 is the offset.

On the Ctid of PostgreSQL