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.
Continue reading "ctid and other PostgreSQL table internals"