Onde e como os meus dados são armazenados? Mergulhando no exemplo de uma tabela ordinária

Por /
Contents hide
1 Introdução
2 Demonstrações
2.1 Setup geral (database, tablespace, schema, usuário, privilégios)
2.2 Criando uma tabela vazia e inspecionando ela
2.3 Inserindo um registro na tabela e localizando o dado
2.4 Estrutura da página de uma tabela
2.4.1 Layout dos dados do cabeçalho da página da tabela
2.4.2 Layout do ItemIdData, espaço livre, itens e espaço especial da página da tabela
2.5 Visualizando a estrutura da página da tabela com a extensão pageinspect
2.6 Adicionando mais linhas à tabela
2.7 Excluindo algumas linhas/dados da tabela
2.8 Layout dos arquivos da tabela
2.8.1 Segmentos da tabela
2.8.2 Free space map da tabela (visualizando com a extensão pg_freespacemap)
2.8.3 Visibility map da tabela (visualizando com a extensão pg_visibility)
2.8.4 Tabela TOAST
2.8.5 Initialization fork
2.9 Dados criptografados (com a extensão pgcrypto)
3 Considerações
4 Referências

Introdução

Bancos de dados utilizam diferentes tipos de objetos (relations) para armazenar dados em disco. Mesmo dentro do mesmo tipo de objeto, os dados podem ser armazenados, acessados ou referenciados de maneiras distintas, dependendo de vários fatores, como o tipo de dado, o tamanho dos dados, o tipo de operação (insert, update, delete), o custo estimado de execução, técnicas de TOAST, otimização HOT, dentre outros. Também é importante observar que os dados podem residir na memória sem necessariamente serem persistidos em disco.

Um objeto comum para armazenar dados persistentes é a tabela regular (ordinária), razão pela qual ela foi escolhida para uma análise mais detalhada nas demonstrações. Uma tabela ordinária será criada e populada para ilustrar conceitos-chave da arquitetura de bancos de dados. Embora essas operações não representem todos os mecanismos envolvidos no armazenamento, gerenciamento ou acesso aos dados, elas cobrem cenários típicos e amplamente encontrados.

Além dos comandos básicos de DDL e DML, arquivos em disco serão acessados diretamente por meio de comandos bash ou indiretamente via funções de extensão do banco de dados para dar suporte às demonstrações. O objetivo é inspecionar o conteúdo desses arquivos, no entanto, a interação direta com arquivos em disco deve ser evitada, exceto quando absolutamente necessário. Para fins investigativos, o acesso aos dados por meio de funções de extensão geralmente é preferível. De modo geral, os comandos utilizados são relativamente simples, mas, se você pretende reproduzir esses passos, é recomendável fazê-lo em um ambiente de teste para evitar impactos em produção ou em sistemas críticos.

Demonstrações

As demonstrações nas próximas seções irão girar em torno de duas tabelas regulares simples, table01 e table02, sendo que a maioria dos exemplos estará relacionada à primeira.

O ambiente do cluster PostgreSQL e o cliente psql utilizados nas demonstrações estão na versão 18, e o sistema operacional é o Ubuntu 24.04.3 LTS.

P.S: Tenha em mente que algumas definições podem mudar entre diferentes versões do PostgreSQL.
postgres@pg01:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
postgres@pg01:~$ 
postgres@pg01:~$ psql --version
psql (PostgreSQL) 18.1 (Ubuntu 18.1-1.pgdg24.04+2)
postgres@pg01:~$ 
postgres@pg01:~$ cat /etc/os-release | head -7
PRETTY_NAME="Ubuntu 24.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="24.04"
VERSION="24.04.3 LTS (Noble Numbat)"
VERSION_CODENAME=noble
ID=ubuntu
ID_LIKE=debian

Setup geral (database, tablespace, schema, usuário, privilégios)

Para simular o ambiente, um banco de dados (db_test) e recursos adicionais serão criados. Um usuário de banco de dados (user_app_test) será o proprietário das tabelas (table_01 e table02). Essas tabelas serão organizadas logicamente dentro de um schema (schema_user_app_test_default) e armazenadas fisicamente em um tablespace (tbs_app_test_tbl). Privilégios adicionais são necessários para criar e acessar os dados. A imagem abaixo ilustra esses recursos, seguida pelos comandos usados para criá-los.

Created resources

O bloco de código abaixo contém os comandos para configurar o ambiente geral.

root@pg01:~# sudo mkdir -p /data/tbs_app_test_tbl
root@pg01:~# 
root@pg01:~# sudo chown -R postgres:postgres /data
root@pg01:~# 
root@pg01:~# sudo su - postgres
postgres@pg01:~$ 
postgres@pg01:~$ psql
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

postgres=# 
postgres=# create database db_test;
CREATE DATABASE
postgres=# 
postgres=# \c db_test
You are now connected to database "db_test" as user "postgres".
db_test=#
db_test=# create tablespace tbs_app_test_tbl location '/data/tbs_app_test_tbl';
CREATE TABLESPACE
db_test=#
db_test=# create user user_app_test with encrypted password 'strongpassword01';
CREATE ROLE
db_test=# 
db_test=# grant create on tablespace tbs_app_test_tbl to user_app_test;
GRANT
db_test=# 
db_test=# create schema schema_user_app_test_default;
CREATE SCHEMA
db_test=# 
db_test=# grant all privileges on schema schema_user_app_test_default to user_app_test;
GRANT
db_test=# 
db_test=# alter user user_app_test set search_path to schema_user_app_test_default;
ALTER ROLE

Criando uma tabela vazia e inspecionando ela

A tabela é criada com o usuário (user_app_test) como seu proprietário, dentro do schema (schema_user_app_test_default), e é armazenada fisicamente no tablespace (tbs_app_test_tbl). Após a criação da tabela, sua localização também pode ser verificada por meio de consultas ao catálogo do sistema ou funções nativas.

Os arquivos associados à tabela podem ser encontrados em disco dentro do diretório da tablespace pg_tblspc, seguindo o caminho <data_directory>/pg_tblspc/<tablespace_oid>/<version>/<database_oid>/<relation_relfilenode>*. Se o tablespace for definido em um diretório personalizado, então o caminho anterior <data_directory>/pg_tblspc/<tablespace_oid> será um link simbólico apontando para a localização real da tablespace </path_to_tablespace_directory>/<version>/<database_oid>/<relation_relfilenode>*.

A imagem abaixo resume a organização lógica da tabela e a localização física de seus arquivos no disco. O bloco de código a seguir contém comandos para recuperar informações da tabela.

Table organization and files location

O bloco de código abaixo inclui os comandos usados para criar e inspecionar a tabela.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# set role 'user_app_test';
SET
db_test=>
db_test=> create table schema_user_app_test_default.table_01 (id integer,
                                                    name varchar(30),
                                                    data text) tablespace tbs_app_test_tbl;
CREATE TABLE
db_test=>
db_test=> select oid, relowner, relnamespace, relname, reltablespace, relfilenode from pg_class where relname = 'table_01';
  oid  | relowner | relnamespace | relname  | reltablespace | relfilenode 
-------+----------+--------------+----------+---------------+-------------
 65604 |    40964 |        65603 | table_01 |         65602 |       65604
(1 row)

db_test=> 
db_test=> select oid, nspname from pg_namespace where nspname = 'schema_user_app_test_default';
  oid  |           nspname            
-------+------------------------------
 65603 | schema_user_app_test_default
(1 row)

db_test=> 
db_test=> select oid, spcname from pg_tablespace where spcname = 'tbs_app_test_tbl';
  oid  |     spcname      
-------+------------------
 65602 | tbs_app_test_tbl
(1 row)

db_test=> 
db_test=> select oid, datname from pg_database where datname = 'db_test';
  oid  | datname 
-------+---------
 65601 | db_test
(1 row)

db_test=> 
db_test=> select pg_relation_filenode('schema_user_app_test_default.table_01');
 pg_relation_filenode 
----------------------
                65604
(1 row)

db_test=> 
db_test=> select pg_relation_filepath('schema_user_app_test_default.table_01');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/65602/PG_18_202506291/65601/65604
(1 row)

db_test=>
db_test=> select pg_tablespace_location(65602);
 pg_tablespace_location 
------------------------
 /data/tbs_app_test_tbl
(1 row)

db_test=> 
db_test=> exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/
total 0
lrwxrwxrwx 1 postgres postgres 22 Jan 28 15:03 65602 -> /data/tbs_app_test_tbl
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/
total 8
-rw------- 1 postgres postgres    0 Jan 28 15:05 65604
-rw------- 1 postgres postgres    0 Jan 28 15:05 65607
-rw------- 1 postgres postgres 8192 Jan 28 15:05 65608
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /data/tbs_app_test_tbl
total 4
drwx------ 3 postgres postgres 4096 Jan 28 15:05 PG_18_202506291
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /data/tbs_app_test_tbl/PG_18_202506291/65601/
total 8
-rw------- 1 postgres postgres    0 Jan 28 15:05 65604
-rw------- 1 postgres postgres    0 Jan 28 15:05 65607
-rw------- 1 postgres postgres 8192 Jan 28 15:05 65608

Inserindo um registro na tabela e localizando o dado

Após a criação da tabela, ela permaneceu vazia, e seu arquivo associado no disco também estava vazio (0 bytes). Uma vez que um registro foi inserido, o arquivo passou a ser preenchido com dados, crescendo de 0 bytes para 8.192 bytes. Os dados inseridos podem ser encontrados dentro do arquivo de relação subjacente da tabela no disco. Vale ressaltar que uma tabela é organizada internamente como um array de páginas. Por padrão, cada página tem 8 KB de tamanho, o que corresponde ao valor de block_size de 8.192 bytes.

O bloco de código abaixo contém os comandos usados para inserir a linha e localizar os dados.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# begin;
BEGIN
db_test=*# 
db_test=*# insert into schema_user_app_test_default.table_01 (id, name, data) values (1,'test_a1','test_b1');
INSERT 0 1
db_test=*# 
db_test=*# commit;
COMMIT
db_test=# 
db_test=# select current_setting('block_size');
 current_setting 
-----------------
 8192
(1 row)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
-rw------- 1 postgres postgres 8192 Jan 28 15:11 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
00000000  05 00 00 00 60 56 cd 23  33 94 00 00 1c 00 d0 1f  |....`V.#3.......|
00000010  00 20 04 20 00 00 00 00  d0 9f 58 00 00 00 00 00  |. . ......X.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001fd0  95 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001fe0  01 00 03 00 02 08 18 00  01 00 00 00 11 74 65 73  |.............tes|
00001ff0  74 5f 61 31 11 74 65 73  74 5f 62 31 00 00 00 00  |t_a1.test_b1....|
00002000
postgres@pg01:~$ 
postgres@pg01:~$ echo -n "01 00 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
1
postgres@pg01:~$ 
postgres@pg01:~$ echo "11 74 65 73 74 5f 61 31" | xxd -r -p | strings
test_a1
postgres@pg01:~$ echo "11 74 65 73 74 5f 62 31" | xxd -r -p | strings
test_b1

Agora que o conceito geral de criação e armazenamento de dados foi introduzido, podemos explorar com mais detalhes como os dados são fisicamente organizados e localizados dentro dos arquivos no disco. As seções seguintes abordarão esses aspectos.

Estrutura da página de uma tabela

O layout geral da página de uma tabela consiste em cinco partes, como mostrado nas imagens abaixo. Uma vez que a tabela é “inicializada” com dados, seu arquivo de relação no disco é preenchido com páginas que possuem uma estrutura. O primeiro elemento é o “page header data“, que contém informações gerais sobre a página. No layout da página, após o cabeçalho vem o identificador de item/ItemId, também conhecido como ItemIdData. No “meio” da estrutura está o “free space”, ou espaço não alocado, que é reservado para uso interno para armazenar identificadores de itens e os próprios itens. No final estão os “item“, que são os dados, seguidos pelo espaço “special“, que é reservado para métodos de acesso, mas está vazio em tabelas comuns.

Page layout
Overall page layout

Nas próximas seções, a estrutura da página da tabela será mostrada usando o exemplo da tabela anterior.

Layout dos dados do cabeçalho da página da tabela

Os dados do cabeçalho da página contêm informações sobre a página, e seu layout é mostrado na imagem abaixo. Conforme descrito na documentação do PostgreSQL, a versão da página é considerada da seguinte forma: “A partir do PostgreSQL 8.3, o número da versão é 4; PostgreSQL 8.1 e 8.2 usavam o número de versão 3; PostgreSQL 8.0 usava o número de versão 2; PostgreSQL 7.3 e 7.4 usavam o número de versão 1; versões anteriores usavam o número de versão 0.”

Page header data layout

Para a table01, após inspecionar seu arquivo de relação no disco, os dados do cabeçalho da página podem ser interpretados.

As transformações de dados em hexadecimal para interpretar o cabeçalho da página são mostradas no código abaixo.

P.S.: Essas transformações poderiam ser realizadas de maneira mais rápida e eficiente, mas foram mantidas nesta forma para serem mais intuitivas.
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
00000000  05 00 00 00 60 56 cd 23  33 94 00 00 1c 00 d0 1f  |....`V.#3.......|
00000010  00 20 04 20 00 00 00 00  d0 9f 58 00 00 00 00 00  |. . ......X.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001fd0  95 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001fe0  01 00 03 00 02 08 18 00  01 00 00 00 11 74 65 73  |.............tes|
00001ff0  74 5f 61 31 11 74 65 73  74 5f 62 31 00 00 00 00  |t_a1.test_b1....|
00002000
postgres@pg01:~$ 
postgres@pg01:~$ ## PageHeaderData
postgres@pg01:~$ # pd_lsn
postgres@pg01:~$ echo -n "05 00 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
5
postgres@pg01:~$ 
postgres@pg01:~$ # pd_checksum
postgres@pg01:~$ echo -n "33 94" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | python3 -c 'import sys; b=sys.stdin.read().strip(); n=len(b); v=int(b,2); print(v-(1<<n) if b[0]=="1" else v)'
-27597
postgres@pg01:~$ 
postgres@pg01:~$ # pd_flags
postgres@pg01:~$ echo -n "00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
0
postgres@pg01:~$ 
postgres@pg01:~$ # pd_lower
postgres@pg01:~$ echo -n "1c 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
28
postgres@pg01:~$ 
postgres@pg01:~$ # pd_upper
postgres@pg01:~$ echo -n "d0 1f" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
8144
postgres@pg01:~$ 
postgres@pg01:~$ # pd_special
postgres@pg01:~$ echo -n "00 20" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
8192
postgres@pg01:~$ 
postgres@pg01:~$ # pd_pagesize_version
postgres@pg01:~$ echo -n "04 20" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16
0010000000000100
postgres@pg01:~$ 
postgres@pg01:~$ echo -n "04 20" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
8196
postgres@pg01:~$ 
postgres@pg01:~$ # pd_prune_xid
postgres@pg01:~$ echo -n "00 00 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
0

Layout do ItemIdData, espaço livre, itens e espaço especial da página da tabela

O identificador de item (ItemIdData) especifica o deslocamento em bytes até o início de um item, o comprimento do item em bytes e inclui alguns bits adicionais que podem afetar sua interpretação. O identificador de item é alocado conforme necessário no início do espaço não alocado e não é movido até ser liberado. Seu índice é usado pelo PostgreSQL como parte de um ponteiro de item (ctid), junto com o número da página.

src/include/storage/itemid.h

O espaço livre é reservado para operações na tabela/relação, permitindo que novos identificadores de itens e itens sejam alocados.

Os itens são armazenados a partir do final do espaço não alocado, crescendo em direção ao início da página. Para tabelas, a estrutura do item corresponde ao cabeçalho do heap tuple e aos dados efetivamente armazenados.

Heap tuple header data
src/include/storage/itemptr.h
src/include/access/htup_details.h

O espaço especial é reservado para métodos de acesso, mas, para tabelas comuns, permanece vazio.

Para a table01, após inspecionar seu arquivo de relação no disco, os identificadores de itens e os dados do cabeçalho do heap tuple podem ser interpretados. O espaço livre é indicado por “*” na coluna de offset dos dados do arquivo.

P.S.: Existem outras maneiras de inserir e atualizar dados, como a técnica TOAST (armazenamento de atributos de grande tamanho) e a otimização HOT (heap-only tuple update), portanto, as localizações de dados descritas anteriormente podem não se aplicar em todos os casos.

As transformações de dados em hexadecimal usadas para interpretar os identificadores de itens e o cabeçalho do heap tuple são mostradas no código abaixo.

P.S.: Essas transformações poderiam ser realizadas de forma mais rápida e eficiente, mas foram mantidas dessa maneira para permanecerem intuitivas.
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
00000000  05 00 00 00 60 56 cd 23  33 94 00 00 1c 00 d0 1f  |....`V.#3.......|
00000010  00 20 04 20 00 00 00 00  d0 9f 58 00 00 00 00 00  |. . ......X.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001fd0  95 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001fe0  01 00 03 00 02 08 18 00  01 00 00 00 11 74 65 73  |.............tes|
00001ff0  74 5f 61 31 11 74 65 73  74 5f 62 31 00 00 00 00  |t_a1.test_b1....|
00002000
postgres@pg01:~$ 
postgres@pg01:~$ # lp info
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' ; echo
00000000010110001001111111010000
postgres@pg01:~$ 
postgres@pg01:~$ # lp_off
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-15
000000000101100
postgres@pg01:~$ 
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-15 | xargs -I{} bash -c 'echo $((2#{}))'
44
postgres@pg01:~$ 
postgres@pg01:~$ # lp_flags
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c16-17
01
postgres@pg01:~$ 
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c16-17 | xargs -I{} bash -c 'echo $((2#{}))'
1
postgres@pg01:~$ 
postgres@pg01:~$ # lp_len
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c18-32
001111111010000
postgres@pg01:~$ 
postgres@pg01:~$ echo -n "d0 9f 58 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c18-32 | xargs -I{} bash -c 'echo $((2#{}))'
8144
postgres@pg01:~$ 
postgres@pg01:~$ ## heap tuple header
postgres@pg01:~$ # t_xmin
postgres@pg01:~$ echo -n "95 03 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
917
postgres@pg01:~$ 
postgres@pg01:~$ # t_xmax
postgres@pg01:~$ echo -n "00 00 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
0
postgres@pg01:~$ 
postgres@pg01:~$ # t_cid
postgres@pg01:~$ echo -n "00 00 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
0
postgres@pg01:~$ 
postgres@pg01:~$ # t_xvac
postgres@pg01:~$ echo -n "00 00 00 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-32 | xargs -I{} bash -c 'echo $((2#{}))'
0
postgres@pg01:~$ 
postgres@pg01:~$ # t_ctid index identifier
postgres@pg01:~$ echo -n "01 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
1
postgres@pg01:~$ 
postgres@pg01:~$ # t_infomask2
postgres@pg01:~$ echo -n "03 00" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
3
postgres@pg01:~$ 
postgres@pg01:~$ # t_infomask
postgres@pg01:~$ echo -n "02 08" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-16 | xargs -I{} bash -c 'echo $((2#{}))'
2050
postgres@pg01:~$ 
postgres@pg01:~$ # t_hoff
postgres@pg01:~$ echo -n "18" | xxd -r -p | xxd -b | awk '{ for (i=2; i<=NF; i++) { if ($i ~ /^[01]{8}$/) printf "%s ", $i } print ""}' | tr -d ' ' | fold -w8 | tac | tr -d '\n' | cut -c1-8 | xargs -I{} bash -c 'echo $((2#{}))'
24

Visualizando a estrutura da página da tabela com a extensão pageinspect

Em vez de inspecionar manualmente o arquivo de relação da tabela no disco, isso pode ser feito diretamente no banco de dados usando funções da extensão pageinspect. Tanto o cabeçalho da página quanto os itens da heap page podem ser examinados no nível de bloco. Nos exemplos abaixo, o bloco número 0 (o primeiro bloco da relação) foi inspecionado.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# create extension if not exists pageinspect;
CREATE EXTENSION
db_test=# 
db_test=# select * from page_header(get_raw_page('schema_user_app_test_default.table_01',0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 5/23CD5660 |        0 |     0 |    28 |  8144 |    8192 |     8192 |       4 |         0
(1 row)

db_test=# 
db_test=# select * from heap_page_items(get_raw_page('schema_user_app_test_default.table_01',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 |   8144 |        1 |     44 |    917 |      0 |        0 | (0,1)  |           3 |       2050 |     24 |        |       | \x0100000011746573745f613111746573745f6231
(1 row)
db_test=# 
db_test=# select tableoid, xmin, cmin, xmax, cmax, ctid, id, name, data from schema_user_app_test_default.table_01;
 tableoid | xmin | cmin | xmax | cmax | ctid  | id |  name   |  data   
----------+------+------+------+------+-------+----+---------+---------
    65604 |  917 |    0 |    0 |    0 | (0,1) |  1 | test_a1 | test_b1
(1 row)

Adicionando mais linhas à tabela

Para observar o que acontece à medida que a tabela cresce, dados adicionais foram inseridos em lotes de tamanho crescente: primeiro 4 linhas, depois 9.995 linhas, seguidas por 4.990.000 linhas e, finalmente, 45.000.000 linhas. O tamanho dos arquivos de relação da tabela no disco cresceu de alguns bytes para vários gigabytes, e o único segmento de arquivo da tabela se expandiu para 4 arquivos separados. Quando uma tabela excede 1 GB, ela é dividida em novos segmentos, cada um com tamanho padrão de 1 GB. Nas imagens abaixo, a tabela inicialmente tinha um único arquivo de segmento de 8.192 bytes e, eventualmente, se expandiu para 4 arquivos de segmento de 1 GB, 1 GB, 1 GB e 0,104 GB.

O bloco de código abaixo contém os comandos usados para realizar essas operações de inserção.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# begin;
BEGIN
db_test=*# 
db_test=*# insert into schema_user_app_test_default.table_01 (id, name, data) values (2,'test_a2','test_b2');
INSERT 0 1
db_test=*# 
db_test=*# commit;
COMMIT
db_test=# 
db_test=# begin;
BEGIN
db_test=*# 
db_test=*# insert into schema_user_app_test_default.table_01 (id, name, data) values (3,'test_a3','test_b4');
INSERT 0 1
db_test=*# insert into schema_user_app_test_default.table_01 (id, name, data) values (4,'test_a3','test_b4');
INSERT 0 1
db_test=*# insert into schema_user_app_test_default.table_01 (id, name, data) values (5,'test_a5','test_b5');
INSERT 0 1
db_test=*# 
db_test=*# commit;
COMMIT
db_test=# 
db_test=# checkpoint;
CHECKPOINT
db_test=# 
db_test=# select * from page_header(get_raw_page('schema_user_app_test_default.table_01',0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 5/23D39D10 |   -27597 |     0 |    44 |  7952 |    8192 |     8192 |       4 |         0
(1 row)

db_test=# 
db_test=# select * from heap_page_items(get_raw_page('schema_user_app_test_default.table_01',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 |   8144 |        1 |     44 |    917 |      0 |        0 | (0,1)  |           3 |       2306 |     24 |        |       | \x0100000011746573745f613111746573745f6231
  2 |   8096 |        1 |     44 |    919 |      0 |        0 | (0,2)  |           3 |       2050 |     24 |        |       | \x0200000011746573745f613211746573745f6232
  3 |   8048 |        1 |     44 |    920 |      0 |        0 | (0,3)  |           3 |       2050 |     24 |        |       | \x0300000011746573745f613311746573745f6234
  4 |   8000 |        1 |     44 |    920 |      0 |        1 | (0,4)  |           3 |       2050 |     24 |        |       | \x0400000011746573745f613311746573745f6234
  5 |   7952 |        1 |     44 |    920 |      0 |        2 | (0,5)  |           3 |       2050 |     24 |        |       | \x0500000011746573745f613511746573745f6235
(5 rows)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/
total 16
-rw------- 1 postgres postgres 8192 Jan 28 20:04 65604
-rw------- 1 postgres postgres    0 Jan 28 15:05 65607
-rw------- 1 postgres postgres 8192 Jan 28 15:05 65608
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
00000000  05 00 00 00 10 9d d3 23  bc 93 00 00 2c 00 10 1f  |.......#....,...|
00000010  00 20 04 20 00 00 00 00  d0 9f 58 00 a0 9f 58 00  |. . ......X...X.|
00000020  70 9f 58 00 40 9f 58 00  10 9f 58 00 00 00 00 00  |p.X.@.X...X.....|
00000030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001f10  98 03 00 00 00 00 00 00  02 00 00 00 00 00 00 00  |................|
00001f20  05 00 03 00 02 08 18 00  05 00 00 00 11 74 65 73  |.............tes|
00001f30  74 5f 61 35 11 74 65 73  74 5f 62 35 00 00 00 00  |t_a5.test_b5....|
00001f40  98 03 00 00 00 00 00 00  01 00 00 00 00 00 00 00  |................|
00001f50  04 00 03 00 02 08 18 00  04 00 00 00 11 74 65 73  |.............tes|
00001f60  74 5f 61 33 11 74 65 73  74 5f 62 34 00 00 00 00  |t_a3.test_b4....|
00001f70  98 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001f80  03 00 03 00 02 08 18 00  03 00 00 00 11 74 65 73  |.............tes|
00001f90  74 5f 61 33 11 74 65 73  74 5f 62 34 00 00 00 00  |t_a3.test_b4....|
00001fa0  97 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001fb0  02 00 03 00 02 08 18 00  02 00 00 00 11 74 65 73  |.............tes|
00001fc0  74 5f 61 32 11 74 65 73  74 5f 62 32 00 00 00 00  |t_a2.test_b2....|
00001fd0  95 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001fe0  01 00 03 00 02 09 18 00  01 00 00 00 11 74 65 73  |.............tes|
00001ff0  74 5f 61 31 11 74 65 73  74 5f 62 31 00 00 00 00  |t_a1.test_b1....|
00002000
postgres@pg01:~$
postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# create or replace function schema_user_app_test_default.insert_table_01(
    p_start_x integer,
    p_count   integer
)
returns void
language plpgsql
as $$
declare
    i integer;
    v_x integer := p_start_x;
begin
    for i in 1..p_count loop
        insert into schema_user_app_test_default.table_01 (id, name, data)
        values (
            v_x,
            'test_a' || v_x,
            'test_b' || v_x
        );

        v_x := v_x + 1;
    end loop;
end;
$$;
CREATE FUNCTION
db_test=# 
db_test=# SELECT schema_user_app_test_default.insert_table_01(6, 9995);
 insert_table_01 
-----------------
 
(1 row)

db_test=# 
db_test=# checkpoint;
CHECKPOINT
db_test=# 
db_test=# select count(*) from schema_user_app_test_default.table_01;
 count 
-------
 10000
(1 row)

db_test=# 
db_test=# select relname,                                                                     
       pg_relation_size(relid) / current_setting('block_size')::int as block_count
from pg_catalog.pg_statio_user_tables
where relname = 'table_01';
 relname  | block_count 
----------+-------------
 table_01 |          73
(1 row)

db_test=# 
db_test=# select * from page_header(get_raw_page('schema_user_app_test_default.table_01',72));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid 
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 5/23EACEE8 |        0 |     0 |   324 |  3992 |    8192 |     8192 |       4 |         0
(1 row)

db_test=# 
db_test=# select * from heap_page_items(get_raw_page('schema_user_app_test_default.table_01',72)) order by lp asc limit 5;
 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 |   8136 |        1 |     50 |    922 |      0 |     9920 | (72,1) |           3 |       2306 |     24 |        |       | \xc626000017746573745f613939323617746573745f6239393236
  2 |   8080 |        1 |     50 |    922 |      0 |     9921 | (72,2) |           3 |       2306 |     24 |        |       | \xc726000017746573745f613939323717746573745f6239393237
  3 |   8024 |        1 |     50 |    922 |      0 |     9922 | (72,3) |           3 |       2306 |     24 |        |       | \xc826000017746573745f613939323817746573745f6239393238
  4 |   7968 |        1 |     50 |    922 |      0 |     9923 | (72,4) |           3 |       2306 |     24 |        |       | \xc926000017746573745f613939323917746573745f6239393239
  5 |   7912 |        1 |     50 |    922 |      0 |     9924 | (72,5) |           3 |       2306 |     24 |        |       | \xca26000017746573745f613939333017746573745f6239393330
(5 rows)

db_test=# 
db_test=# select * from heap_page_items(get_raw_page('schema_user_app_test_default.table_01',72)) order by lp desc limit 5;
 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                           
----+--------+----------+--------+--------+--------+----------+---------+-------------+------------+--------+--------+-------+------------------------------------------------------------
 75 |   3992 |        1 |     52 |    922 |      0 |     9994 | (72,75) |           3 |       2306 |     24 |        |       | \x1027000019746573745f61313030303019746573745f623130303030
 74 |   4048 |        1 |     50 |    922 |      0 |     9993 | (72,74) |           3 |       2306 |     24 |        |       | \x0f27000017746573745f613939393917746573745f6239393939
 73 |   4104 |        1 |     50 |    922 |      0 |     9992 | (72,73) |           3 |       2306 |     24 |        |       | \x0e27000017746573745f613939393817746573745f6239393938
 72 |   4160 |        1 |     50 |    922 |      0 |     9991 | (72,72) |           3 |       2306 |     24 |        |       | \x0d27000017746573745f613939393717746573745f6239393937
 71 |   4216 |        1 |     50 |    922 |      0 |     9990 | (72,71) |           3 |       2306 |     24 |        |       | \x0c27000017746573745f613939393617746573745f6239393936
(5 rows)

db_test=# 
db_test=# exit
postgres@pg01:~$
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/
total 624
-rw------- 1 postgres postgres 598016 Jan 28 20:06 65604
-rw------- 1 postgres postgres  24576 Jan 28 20:06 65604_fsm
-rw------- 1 postgres postgres   8192 Jan 28 20:07 65604_vm
-rw------- 1 postgres postgres      0 Jan 28 15:05 65607
-rw------- 1 postgres postgres   8192 Jan 28 15:05 65608
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604*
-rw------- 1 postgres postgres 598016 Jan 28 20:06 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
-rw------- 1 postgres postgres  24576 Jan 28 20:06 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_fsm
-rw------- 1 postgres postgres   8192 Jan 28 20:07 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_vm
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604 | head
00000000  05 00 00 00 48 60 d4 23  34 f4 00 00 8c 02 90 02  |....H`.#4.......|
00000010  00 20 04 20 00 00 00 00  d0 9f 58 00 a0 9f 58 00  |. . ......X...X.|
00000020  70 9f 58 00 40 9f 58 00  10 9f 58 00 e0 9e 58 00  |p.X.@.X...X...X.|
00000030  b0 9e 58 00 80 9e 58 00  50 9e 58 00 20 9e 5c 00  |..X...X.P.X. .\.|
00000040  f0 9d 5c 00 c0 9d 5c 00  90 9d 5c 00 60 9d 5c 00  |..\...\...\.`.\.|
00000050  30 9d 5c 00 00 9d 5c 00  d0 9c 5c 00 a0 9c 5c 00  |0.\...\...\...\.|
00000060  70 9c 5c 00 40 9c 5c 00  10 9c 5c 00 e0 9b 5c 00  |p.\.@.\...\...\.|
00000070  b0 9b 5c 00 80 9b 5c 00  50 9b 5c 00 20 9b 5c 00  |..\...\.P.\. .\.|
00000080  f0 9a 5c 00 c0 9a 5c 00  90 9a 5c 00 60 9a 5c 00  |..\...\...\.`.\.|
00000090  30 9a 5c 00 00 9a 5c 00  d0 99 5c 00 a0 99 5c 00  |0.\...\...\...\.|
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604 | tail
00091f70  c8 26 00 00 17 74 65 73  74 5f 61 39 39 32 38 17  |.&...test_a9928.|
00091f80  74 65 73 74 5f 62 39 39  32 38 00 00 00 00 00 00  |test_b9928......|
00091f90  9a 03 00 00 00 00 00 00  c1 26 00 00 00 00 48 00  |.........&....H.|
00091fa0  02 00 03 00 02 08 18 00  c7 26 00 00 17 74 65 73  |.........&...tes|
00091fb0  74 5f 61 39 39 32 37 17  74 65 73 74 5f 62 39 39  |t_a9927.test_b99|
00091fc0  32 37 00 00 00 00 00 00  9a 03 00 00 00 00 00 00  |27..............|
00091fd0  c0 26 00 00 00 00 48 00  01 00 03 00 02 08 18 00  |.&....H.........|
00091fe0  c6 26 00 00 17 74 65 73  74 5f 61 39 39 32 36 17  |.&...test_a9926.|
00091ff0  74 65 73 74 5f 62 39 39  32 36 00 00 00 00 00 00  |test_b9926......|
00092000
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_fsm 
00000000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004000  05 00 00 00 20 a6 d5 23  ef 7c 00 00 18 00 00 20  |.... ..#.|..... |
00004010  00 20 04 20 00 00 00 00  00 00 00 00 01 01 00 01  |. . ............|
00004020  00 00 00 01 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00004030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00004040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00004050  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00004060  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004090  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
000040a0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004110  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00004120  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004210  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00004220  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004410  00 00 00 00 00 00 00 00  00 00 00 00 01 00 00 00  |................|
00004420  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00004810  00 00 00 00 00 00 00 00  00 00 00 00 00 00 01 00  |................|
00004820  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00005020  00 01 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00005030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00006000
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_vm 
00000000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00002000
postgres@pg01:~$
postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# select schema_user_app_test_default.insert_table_01(10001, 4990000);
 insert_table_01 
-----------------
 
(1 row)

db_test=# 
db_test=# checkpoint;
CHECKPOINT
db_test=# 
db_test=# select count(*) from schema_user_app_test_default.table_01;
  count  
---------
 5000000
(1 row)

db_test=# 
db_test=# select relname,                                                                     
       pg_relation_size(relid) / current_setting('block_size')::int as block_count
from pg_catalog.pg_statio_user_tables
where relname = 'table_01';
 relname  | block_count 
----------+-------------
 table_01 |       36764
(1 row)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604*
-rw------- 1 postgres postgres 301170688 Jan 28 20:14 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
-rw------- 1 postgres postgres     98304 Jan 28 20:14 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_fsm
-rw------- 1 postgres postgres     16384 Jan 28 20:14 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_vm
postgres@pg01:~$ 
postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# select schema_user_app_test_default.insert_table_01(5000001, 45000000);
 insert_table_01 
-----------------
 
(1 row)

db_test=# 
db_test=# checkpoint;
CHECKPOINT
db_test=# 
db_test=# select count(*) from schema_user_app_test_default.table_01;
  count   
----------
 50000000
(1 row)

db_test=# 
db_test=# select relname,                                                                     
       pg_relation_size(relid) / current_setting('block_size')::int as block_count
from pg_catalog.pg_statio_user_tables
where relname = 'table_01';
 relname  | block_count 
----------+-------------
 table_01 |      406862
(1 row)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604*
-rw------- 1 postgres postgres 1073741824 Jan 28 20:22 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
-rw------- 1 postgres postgres 1073741824 Jan 28 20:21 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604.1
-rw------- 1 postgres postgres 1073741824 Jan 28 20:21 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604.2
-rw------- 1 postgres postgres  111788032 Jan 28 20:22 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604.3
-rw------- 1 postgres postgres     835584 Jan 28 20:20 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_fsm
-rw------- 1 postgres postgres      24576 Jan 28 20:21 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_vm

Excluindo algumas linhas/dados da tabela

Para observar o que acontece com os dados da tabela e os arquivos no disco quando linhas são excluídas, o exemplo a seguir foi apresentado. Após a exclusão de linhas da tabela, seu tamanho permaneceu inalterado e só diminuiu quando foi executado um VACUUM FULL (com a opção ANALYZE realizada em conjunto para coletar estatísticas da tabela). Esse comportamento ocorre por causa do modelo MVCC (Multi-Version Concurrency Control) do PostgreSQL, que garante a consistência dos dados. Quando uma linha é atualizada ou excluída, sua versão anterior permanece armazenada, mesmo que não seja mais necessária. Sob certas circunstâncias, esse espaço pode ser recuperado usando o VACUUM.

Nas imagens abaixo, após a exclusão de algumas linhas, o número de blocos da tabela permaneceu o mesmo (406682) e só diminuiu (para 340686) após a execução do VACUUM FULL.

P.S.: Observe que o relfilenode da tabela mudou, e o ctid das linhas de dados também se altera. Tenha cuidado ao usar padrões para recuperar dados, pois eles podem não permanecer válidos.

O bloco de código abaixo contém os comandos para excluir linhas e realizar o vacuum da tabela.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# begin;
BEGIN
db_test=*# 
db_test=*# delete from schema_user_app_test_default.table_01 where (id > 1000000 and id <= 10000000);
DELETE 9000000
db_test=*# 
db_test=*# commit;
COMMIT
db_test=# 
db_test=# select relname,                                                                     
       pg_relation_size(relid) / current_setting('block_size')::int as block_count
from pg_catalog.pg_statio_user_tables
where relname = 'table_01';
 relname  | block_count 
----------+-------------
 table_01 |      406862
(1 row)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604*
-rw------- 1 postgres postgres 1073741824 Jan 28 20:25 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604
-rw------- 1 postgres postgres 1073741824 Jan 28 20:25 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604.1
-rw------- 1 postgres postgres 1073741824 Jan 28 20:25 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604.2
-rw------- 1 postgres postgres  111788032 Jan 28 20:23 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604.3
-rw------- 1 postgres postgres     835584 Jan 28 20:20 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_fsm
-rw------- 1 postgres postgres      73728 Jan 28 20:25 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/65604_vm
postgres@pg01:~$ 
postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# vacuum full analyze schema_user_app_test_default.table_01;
VACUUM
db_test=# 
db_test=# select relname,                                                                     
       pg_relation_size(relid) / current_setting('block_size')::int as block_count
from pg_catalog.pg_statio_user_tables
where relname = 'table_01';
 relname  | block_count 
----------+-------------
 table_01 |      340686
(1 row)

db_test=# 
db_test=# select oid, relowner, relnamespace, relname, reltablespace, relfilenode from pg_class where relname = 'table_01';
  oid  | relowner | relnamespace | relname  | reltablespace | relfilenode 
-------+----------+--------------+----------+---------------+-------------
 65604 |    40964 |        65603 | table_01 |         65602 |       73741
(1 row)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73741*
-rw------- 1 postgres postgres 1073741824 Jan 28 20:27 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73741
-rw------- 1 postgres postgres 1073741824 Jan 28 20:26 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73741.1
-rw------- 1 postgres postgres  643416064 Jan 28 20:26 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73741.2
-rw------- 1 postgres postgres      65536 Jan 28 20:27 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73741_fsm
-rw------- 1 postgres postgres       8192 Jan 28 20:27 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73741_vm
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/
total 2725596
-rw------- 1 postgres postgres          0 Jan 28 20:26 65604
-rw------- 1 postgres postgres          0 Jan 28 20:26 65607
-rw------- 1 postgres postgres          0 Jan 28 20:26 65608
-rw------- 1 postgres postgres 1073741824 Jan 28 20:27 73741
-rw------- 1 postgres postgres 1073741824 Jan 28 20:26 73741.1
-rw------- 1 postgres postgres  643416064 Jan 28 20:26 73741.2
-rw------- 1 postgres postgres      81920 Jan 28 20:27 73741_fsm
-rw------- 1 postgres postgres       8192 Jan 28 20:27 73741_vm
-rw------- 1 postgre
s postgres          0 Jan 28 20:26 73744
-rw------- 1 postgres postgres       8192 Jan 28 20:26 73745

Layout dos arquivos da tabela

Os arquivos da tabela são armazenados dentro da especificação do tablespace, e os arquivos gerais possíveis são o main file, o free space map, o visibility map, o initialization fork e a tabela TOAST.

A imagem abaixo mostra a tabela dos exemplos anteriores, onde é possível ver os main files (arquivos principais) da tabela (65604, 65604.1, 65604.2 e 65604.3), o free space map (65604_fsm) e o visibility map (65604_vsm).

Segmentos da tabela

O main file (arquivo principal, ou main fork) de relações ordinárias é nomeado com base no relfilenode da tabela em pg_class. Para relações temporárias, eles são nomeados como tBBB_FFF, onde BBB é o número do processo do backend que criou o arquivo e FFF é o número do filenode.

Free space map da tabela (visualizando com a extensão pg_freespacemap)

As tabelas também possuem um arquivo de free space map, que armazena informações sobre o espaço livre e é nomeado como o relfilenode da tabela seguido do sufixo _fsm. Em vez de inspecionar manualmente o arquivo no disco, isso pode ser feito no banco de dados executando funções da extensão pg_freespacemap. Os exemplos abaixo mostram os valores do free space map da tabela.

P.S.: A documentação indica o seguinte

"Os valores armazenados no free space map não são exatos. Eles são arredondados com uma precisão de 1/256 de BLCKSZ (32 bytes com o BLCKSZ padrão) e não são mantidos completamente atualizados à medida que tuples são inseridos ou atualizados."
postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# create extension if not exists pg_freespacemap;
CREATE EXTENSION
db_test=# 
db_test=# select * from pg_freespace('schema_user_app_test_default.table_01') order by blkno asc limit 10;
 blkno | avail 
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |     0
     4 |     0
     5 |     0
     6 |    32
     7 |     0
     8 |     0
     9 |     0
(10 rows)

db_test=# 
db_test=# select * from pg_freespace('schema_user_app_test_default.table_01') order by blkno desc limit 10;
 blkno  | avail 
--------+-------
 406861 |     0
 406860 |     0
 406859 |     0
 406858 |     0
 406857 |     0
 406856 |     0
 406855 |     0
 406854 |     0
 406853 |     0
 406852 |     0
(10 rows)

db_test=# 
db_test=# select * from pg_freespace('schema_user_app_test_default.table_01', 6);
 pg_freespace 
--------------
           32
(1 row)

Visibility map da tabela (visualizando com a extensão pg_visibility)

As tabelas possuem um arquivo de visibility map para rastrear quais páginas contêm apenas tuples visíveis para todas as transações ativas e quais páginas contêm apenas tuples congelados. Este arquivo é nomeado como o relfilenode da tabela em pg_class seguido do sufixo _vsm. Em vez de inspecionar manualmente o arquivo no disco, isso pode ser feito no banco de dados executando funções da extensão pg_visibility.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# create extension if not exists pg_visibility;
CREATE EXTENSION
db_test=# 
db_test=# select * from pg_visibility('schema_user_app_test_default.table_01') order by blkno asc limit 10;
 blkno | all_visible | all_frozen | pd_all_visible 
-------+-------------+------------+----------------
     0 | t           | f          | t
     1 | t           | f          | t
     2 | t           | f          | t
     3 | t           | f          | t
     4 | t           | f          | t
     5 | t           | f          | t
     6 | t           | f          | t
     7 | t           | f          | t
     8 | t           | f          | t
     9 | t           | f          | t
(10 rows)

db_test=# 
db_test=# select * from pg_visibility('schema_user_app_test_default.table_01') order by blkno desc limit 10;
 blkno  | all_visible | all_frozen | pd_all_visible 
--------+-------------+------------+----------------
 406861 | f           | f          | f
 406860 | f           | f          | f
 406859 | f           | f          | f
 406858 | f           | f          | f
 406857 | f           | f          | f
 406856 | f           | f          | f
 406855 | f           | f          | f
 406854 | f           | f          | f
 406853 | f           | f          | f
 406852 | f           | f          | f
(10 rows)

db_test=# 
db_test=# select * from pg_visibility('schema_user_app_test_default.table_01',406861);
 all_visible | all_frozen | pd_all_visible 
-------------+------------+----------------
 f           | f          | f
(1 row)

Tabela TOAST

Uma tabela também pode ter uma tabela TOAST associada caso possua colunas com entradas potencialmente grandes. Os dados que não cabem nas linhas normais são armazenados na tabela TOAST.

Initialization fork

Se a tabela for do tipo unlogged (o que não é o caso nos exemplos aqui), ela terá outro arquivo fork com o sufixo _init.

Dados criptografados (com a extensão pgcrypto)

O PostgreSQL pode ter criptografia em vários níveis. Aqui, o foco é na criptografia de colunas específicas usando a extensão pgcrypto. A ideia é demonstrar um cenário em que o arquivo existe no disco, mas os dados não podem ser descriptografados inicialmente.

Comparado com os cenários anteriores, após criptografar os dados de uma coluna com a extensão, os dados são gravados em forma criptografada no arquivo da tabela no disco, como mostrado nas imagens abaixo.

O bloco de código abaixo contém os comandos para criar os dados criptografados.

postgres@pg01:~$ psql -d db_test
psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2))
Type "help" for help.

db_test=# create extension if not exists pgcrypto;
CREATE EXTENSION
db_test=# 
db_test=# create table schema_user_app_test_default.table_02 (id integer,
                                                    name varchar(200),
                                                    data text) tablespace tbs_app_test_tbl;
CREATE TABLE
db_test=# 
db_test=# begin;
BEGIN
db_test=*# 
db_test=*# insert into schema_user_app_test_default.table_02 (id,name, data) values (1,pgp_sym_encrypt('a1', 'strong_password'), pgp_sym_encrypt('b1', 'strong_password'));
INSERT 0 1
db_test=*# 
db_test=*# commit;
COMMIT
db_test=# 
db_test=# checkpoint;
CHECKPOINT
db_test=# 
db_test=# select id, name, data from schema_user_app_test_default.table_02;
db_test=# 
db_test=# \x on
Expanded display is on.
db_test=# 
db_test=# select id, name, data from schema_user_app_test_default.table_02;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------
id   | 1
name | \xc30d040703027fd20a79c21fbc4a70d23301f71416b086ea711d2ba014be047ba0029671f69662be88a39f3ad3a547ca4413ba35a1cd3947aadbd232108ca0597af4e694
data | \xc30d04070302e10e104bade4c3767fd23301221c3fe035b9c4656d56f9a64f87d5a8e91df3ffc4accb0e244006533345ad7cb8aadbd016c31990e83321c6bf0dbabf3b06

db_test=#
db_test=# select id, pgp_sym_decrypt(name::bytea, 'strong_password') as name, pgp_sym_decrypt(data::bytea, 'strong_password') as data from schema_user_app_test_default.table_02 where id = 1;
-[ RECORD 1 ]
id   | 1
name | a1
data | b1

db_test=#
db_test=# 
db_test=# \x off
Expanded display is off.
db_test=# 
db_test=# select oid, relowner, relnamespace, relname, reltablespace, relfilenode from pg_class where relname = 'table_02';
  oid  | relowner | relnamespace | relname  | reltablespace | relfilenode 
-------+----------+--------------+----------+---------------+-------------
 73784 |       10 |        65603 | table_02 |         65602 |       73784
(1 row)

db_test=# 
db_test=# exit
postgres@pg01:~$ 
postgres@pg01:~$ ls -l /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73784*
-rw------- 1 postgres postgres 8192 Jan 28 20:31 /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73784
postgres@pg01:~$ 
postgres@pg01:~$ hexdump -C /var/lib/postgresql/18/main/pg_tblspc/65602/PG_18_202506291/65601/73784
00000000  08 00 00 00 b0 4d 03 b3  0e f8 00 00 1c 00 c0 1e  |.....M..........|
00000010  00 20 04 20 00 00 00 00  c0 9e 74 02 00 00 00 00  |. . ......t.....|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00001ec0  a6 03 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00001ed0  01 00 03 00 02 09 18 00  01 00 00 00 38 02 00 00  |............8...|
00001ee0  5c 78 63 33 30 64 30 34  30 37 30 33 30 32 37 66  |\xc30d040703027f|
00001ef0  64 32 30 61 37 39 63 32  31 66 62 63 34 61 37 30  |d20a79c21fbc4a70|
00001f00  64 32 33 33 30 31 66 37  31 34 31 36 62 30 38 36  |d23301f71416b086|
00001f10  65 61 37 31 31 64 32 62  61 30 31 34 62 65 30 34  |ea711d2ba014be04|
00001f20  37 62 61 30 30 32 39 36  37 31 66 36 39 36 36 32  |7ba0029671f69662|
00001f30  62 65 38 38 61 33 39 66  33 61 64 33 61 35 34 37  |be88a39f3ad3a547|
00001f40  63 61 34 34 31 33 62 61  33 35 61 31 63 64 33 39  |ca4413ba35a1cd39|
00001f50  34 37 61 61 64 62 64 32  33 32 31 30 38 63 61 30  |47aadbd232108ca0|
00001f60  35 39 37 61 66 34 65 36  39 34 00 00 38 02 00 00  |597af4e694..8...|
00001f70  5c 78 63 33 30 64 30 34  30 37 30 33 30 32 65 31  |\xc30d04070302e1|
00001f80  30 65 31 30 34 62 61 64  65 34 63 33 37 36 37 66  |0e104bade4c3767f|
00001f90  64 32 33 33 30 31 32 32  31 63 33 66 65 30 33 35  |d23301221c3fe035|
00001fa0  62 39 63 34 36 35 36 64  35 36 66 39 61 36 34 66  |b9c4656d56f9a64f|
00001fb0  38 37 64 35 61 38 65 39  31 64 66 33 66 66 63 34  |87d5a8e91df3ffc4|
00001fc0  61 63 63 62 30 65 32 34  34 30 30 36 35 33 33 33  |accb0e2440065333|
00001fd0  34 35 61 64 37 63 62 38  61 61 64 62 64 30 31 36  |45ad7cb8aadbd016|
00001fe0  63 33 31 39 39 30 65 38  33 33 32 31 63 36 62 66  |c31990e83321c6bf|
00001ff0  30 64 62 61 62 66 33 62  30 36 00 00 00 00 00 00  |0dbabf3b06......|
00002000

Considerações

Criar e usar uma tabela inicialmente requer esforço mínimo e tem pouco impacto. À medida que a tabela cresce e a carga de trabalho do sistema aumenta, seu uso pode afetar o desempenho. Compreender como os dados são armazenados e onde estão localizados, junto com outros conceitos de banco de dados, ajuda a tomar decisões mais assertivas, tanto do ponto de vista arquitetônico quanto funcional.

Referências

  1. Extension pageinspect
  2. Extension pgcrypto
  3. Postgres architecture database file layout
  4. Postgres architecture database Page Layout
  5. Postgres code postgres/src/include/storage/bufpage.h
  6. Postgres code postgres/src/include/storage/itemid.h
  7. Postgres code postgres/src/include/access/htup_details.h
  8. Postgres code postgres/src/include/storage/itemptr.h
  9. Table system columns
  10. Storage free space map
  11. Module pg_freespacemap
  12. Storage visibility map
  13. Module pg_visibility
  14. Postgres vacuum
  15. Postgres block_size

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Rolar para cima