Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table people(id int);
insert into people select generate_series(1,3) returning *;

CREATE TABLE




id


1

2

3



INSERT 0 3


--ruining the perceived order
update people set id=0 where id=1;
select * from people;

UPDATE 1




id


2

3

0



SELECT 3


alter table people set (fillfactor=10);
create index idx1 on people(id asc);

ALTER TABLE



CREATE INDEX


cluster people using idx1;

CLUSTER


--illusion of usable order, kind of restored
select * from people;


id


0

2

3



SELECT 3


select tableoid,xmin,cmin,xmax,cmax,ctid,* from people;


tableoid
xmin
cmin
xmax
cmax
ctid
id


16384
731
0
0
0
(0,1)
0

16384
730
1
0
1
(0,2)
2

16384
730
1
0
1
(0,3)
3



SELECT 3


--the db doesn't care it's the same
update people set id=2 where id=2
returning tableoid,xmin,cmin,xmax,cmax,ctid,*;


tableoid
xmin
cmin
xmax
cmax
ctid
id


16384
734
0
0
0
(0,4)
2



UPDATE 1


--the new value is written elsewhere
--10% fillfactor doesn't help because rows are too small
--small enough that they can all fit in a page and still leave it 90% empty.
--There's no attempt at spreading them out over the page space
--so they are all bunched up at the top, with new values being added after them.
select tableoid,xmin,cmin,xmax,cmax,ctid,* from people;


tableoid
xmin
cmin
xmax
cmax
ctid
id


16384
731
0
0
0
(0,1)
0

16384
730
1
0
1
(0,3)
3

16384
734
0
0
0
(0,4)
2



SELECT 3


--a table doesn't strictly have to have columns
alter table people drop column id;

ALTER TABLE


--but now it can't be updated, because there are no real columns to target
--and you can't target system columns
update test set ctid=ctid 
  returning tableoid,xmin,cmin,xmax,cmax,ctid,*;

ERROR:  relation "test" does not exist
LINE 3: update test set ctid=ctid 
               ^


--there are no real columns, but rows remain
select * from people;

SELECT 3


--these columns are always there, even though there are no "regular" columns left
select tableoid,xmin,cmin,xmax,cmax,ctid,* from people;


tableoid
xmin
cmin
xmax
cmax
ctid


16384
731
0
0
0
(0,1)

16384
730
1
0
1
(0,3)

16384
734
0
0
0
(0,4)



SELECT 3