Relations

Forks and files

./configure --with-segsize

=> SELECT pg_relation_filepath('accounts');

pg_relation_filepath ---------------------- base/41493/41496 (1 row)

=> SELECT oid FROM pg_database WHERE datname = 'test';

oid ------- 41493 (1 row)

=> SELECT relfilenode FROM pg_class WHERE relname = 'accounts';

relfilenode ------------- 41496 (1 row)

postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41496

-rw------- 1 postgres postgres 8192 /var/lib/postgresql/11/main/base/41493/41496

=> ALTER TABLE accounts SET UNLOGGED; => SELECT pg_relation_filepath('accounts');

pg_relation_filepath ---------------------- base/41493/41507 (1 row)

postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41507_init

-rw------- 1 postgres postgres 0 /var/lib/postgresql/11/main/base/41493/41507_init

=> VACUUM accounts;

postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41507_fsm

-rw------- 1 postgres postgres 24576 /var/lib/postgresql/11/main/base/41493/41507_fsm

postgres$ ls -l --time-style=+ /var/lib/postgresql/11/main/base/41493/41507_vm

-rw------- 1 postgres postgres 8192 /var/lib/postgresql/11/main/base/41493/41507_vm

Pages

./configure --with-blocksize

0 +-----------------------------------+ | header | 24 +-----------------------------------+ | array of pointers to row versions | lower +-----------------------------------+ | free space | upper +-----------------------------------+ | row versions | special +-----------------------------------+ | special space | pagesize +-----------------------------------+

=> CREATE EXTENSION pageinspect; => SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('accounts',0));

lower | upper | special | pagesize -------+-------+---------+---------- 40 | 8016 | 8192 | 8192 (1 row)

Pointers

a reference to the row version

the size of this row version

several bytes to determine the status of the row version

Data format

Row versions and TOAST

\d+

=> SELECT attname, atttypid::regtype, CASE attstorage WHEN 'p' THEN 'plain' WHEN 'e' THEN 'external' WHEN 'm' THEN 'main' WHEN 'x' THEN 'extended' END AS storage FROM pg_attribute WHERE attrelid = 'accounts'::regclass AND attnum > 0;

attname | atttypid | storage ---------+----------+---------- id | integer | plain number | text | extended client | text | extended amount | numeric | main (4 rows)

plain — TOAST is unused (used for data types known to be short, such as «integer»).

extended — both compression and storage in a separate TOAST table are allowed

external — long values are stored in the TOAST table without compression.

main — long values are first compressed and only get into the TOAST table if the compression did not help.

First we go through the attributes with the «external» and «extended» strategies from the longest attribute to the shortest. «Extended» attributes are compressed (if it is effective) and if the value itself exceeds one forth of the page, it immediately gets into the TOAST table. «External» attributes are processed the same way, but aren't compressed. If after the first pass, the row version does not fit the page yet, we transmit the remaining attributes with the «external» and «extended» strategies to the TOAST table. If this did not help either, we try to compress the attributes with the «main» strategy, but leave them in the table page. And only if after that, the row is not short enough, «main» attributes get into the TOAST table.

=> ALTER TABLE accounts ALTER COLUMN number SET STORAGE external;

attname | atttypid | storage ---------+----------+---------- id | integer | plain number | text | external client | text | extended amount | numeric | main

=> SELECT relnamespace::regnamespace, relname FROM pg_class WHERE oid = ( SELECT reltoastrelid FROM pg_class WHERE relname = 'accounts' );

relnamespace | relname --------------+---------------- pg_toast | pg_toast_33953 (1 row)

=> \d+ pg_toast.pg_toast_33953

TOAST table "pg_toast.pg_toast_33953" Column | Type | Storage ------------+---------+--------- chunk_id | oid | plain chunk_seq | integer | plain chunk_data | bytea | plain

=> SELECT indexrelid::regclass FROM pg_index WHERE indrelid = ( SELECT oid FROM pg_class WHERE relname = 'pg_toast_33953' );

indexrelid ------------------------------- pg_toast.pg_toast_33953_index (1 row)

=> \d pg_toast.pg_toast_33953_index

Unlogged index "pg_toast.pg_toast_33953_index" Column | Type | Key? | Definition -----------+---------+------+------------ chunk_id | oid | yes | chunk_id chunk_seq | integer | yes | chunk_seq primary key, btree, for table "pg_toast.pg_toast_33953"

=> UPDATE accounts SET client = repeat('A',3000) WHERE id = 1; => SELECT * FROM pg_toast.pg_toast_33953;

chunk_id | chunk_seq | chunk_data ----------+-----------+------------ (0 rows)

=> UPDATE accounts SET client = ( SELECT string_agg( chr(trunc(65+random()*26)::integer), '') FROM generate_series(1,3000) ) WHERE id = 1 RETURNING left(client,10) || '...' || right(client,10);

?column? ------------------------- TCKGKZZSLI...RHQIOLWRRX (1 row)

=> SELECT chunk_id, chunk_seq, length(chunk_data), left(encode(chunk_data,'escape')::text, 10) || '...' || right(encode(chunk_data,'escape')::text, 10) FROM pg_toast.pg_toast_33953;

chunk_id | chunk_seq | length | ?column? ----------+-----------+--------+------------------------- 34000 | 0 | 2000 | TCKGKZZSLI...ZIPFLOXDIW 34000 | 1 | 1000 | DDXNNBQQYH...RHQIOLWRRX (2 rows)

For more details of the internal data structure, you can read the documentation.

