В каком виде хранятся данные в PostgreSQL?

Хранение данных в PostgreSQL

В PostgreSQL данные хранятся в двухуровневой иерархической структуре, сочетающей физическое хранение на диске с логической организацией. Понимание этой архитектуры критически важно для оптимизации производительности и администрирования.

Физический уровень хранения

На физическом уровне данные организованы в следующие структуры:

1. Кластер баз данных - совокупность всех данных PostgreSQL на сервере:

   /var/lib/postgresql/16/main/  # типичный путь в Linux
   

   Содержит подкаталоги base/, global/, pg_wal/, pg_xact/ и другие.

2. Файлы таблиц (OID и relfilenode): Каждая таблица физически представлена одним или несколькими файлами (обычно 1 ГБ каждый):

   -- Просмотр OID и файловых узлов таблиц
   SELECT oid, relname, relfilenode FROM pg_class WHERE relname = 'users';
   

   Файлы хранятся как base/{database_oid}/{relfilenode}. При TRUNCATE или некоторых ALTER TABLE командах создается новый файл.

3. Страницы (блоки) - фундаментальная единица хранения:

   • Размер страницы обычно 8 КБ (настраивается при компиляции)
   • Каждая страница содержит заголовок, массив указателей на строки и сами строки:

   | Заголовок (24 байта) | Строка 1 | Свободное пространство | Строка 2 | ...
   

   -- Проверка размера страницы
   SHOW block_size;  -- Обычно 8192
   

4. TOAST (The Oversized-Attribute Storage Technique): Для больших значений (> 2 КБ) используется механизм TOAST:

   -- Создание таблицы с текстовым полем, где может использоваться TOAST
   CREATE TABLE documents (
       id SERIAL PRIMARY KEY,
       content TEXT,  -- TOAST-автоматически для больших значений
       metadata JSONB
   );
   

   Большие значения сжимаются и выносятся в отдельные TOAST-таблицы.

Логический уровень организации

Логически данные структурированы следующим образом:

1. Табличные пространства (Tablespaces):

   -- Создание табличного пространства
   CREATE TABLESPACE fastspace LOCATION '/ssd/postgresql/data';
   
   -- Создание таблицы в определенном tablespace
   CREATE TABLE metrics (id SERIAL, value DOUBLE PRECISION) 
   TABLESPACE fastspace;
   

   Позволяет распределять данные по разным дискам.

2. Таблицы и индексы: Данные организованы в виде таблиц с индексами:

   • Куча (Heap) - основное хранилище табличных данных в неупорядоченном виде
   • Индексы (B-дерево, Hash, GiST, SP-GiST, GIN, BRIN) - отдельные структуры

3. Транзакционные журналы (WAL - Write-Ahead Logging):

   -- Настройка WAL в postgresql.conf
   # wal_level = replica          # уровень детализации WAL
   # max_wal_size = 1GB           # максимальный размер WAL
   

   Все изменения сначала записываются в WAL, обеспечивая надежность и репликацию.

Формат строк данных

Каждая строка (tuple) имеет следующую структуру:

/*
Типичная структура строки:
| Заголовок строки (23+ байт) | Данные колонки 1 | Данные колонки 2 | ...
Заголовок включает:
- xmin, xmax (транзакционные данные для MVCC)
- ctid (физическое расположение)
- инфомаска (битовая карта NULL-значений)
- флаги и длина
*/

Пример представления данных:

-- Просмотр физического расположения строки
SELECT ctid, * FROM users WHERE id = 1;
-- ctid вида (0,1) означает: блок 0, строка 1

Особенности хранения

1. MVCC (Multiversion Concurrency Control):

   -- Каждая строка хранит xmin и xmax
   -- xmin - ID транзакции, создавшей строку
   -- xmax - ID транзакции, удалившей строку (или 0 для активной строки)
   

2. Сжатие и оптимизация:

   • Автоматическое сжатие длинных строк через TOAST
   • Дедупликация значений в индексах (с PostgreSQL 13 для B-деревьев)
   • Использование "free space map" для отслеживания свободного места

3. Партиционирование:

   -- Пример партиционированной таблицы
   CREATE TABLE logs (
       id BIGSERIAL,
       created_at TIMESTAMP NOT NULL,
       message TEXT
   ) PARTITION BY RANGE (created_at);
   
   CREATE TABLE logs_2024_01 PARTITION OF logs
   FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
   

   Каждая партиция хранится как отдельная физическая таблица.

Управление хранилищем

Администрирование через служебные функции:

-- Анализ использования дискового пространства
SELECT pg_size_pretty(pg_total_relation_size('users'));

-- Просмотр статистики по таблице
SELECT * FROM pg_stat_user_tables WHERE relname = 'users';

-- Очистка мусора (VACUUM)
VACUUM VERBOSE ANALYZE users;

PostgreSQL предоставляет гибкую, надежную систему хранения, которая балансирует между производительностью, надежностью и функциональностью. Понимание внутренней организации позволяет эффективно проектировать схемы баз данных, оптимизировать запросы и планировать масштабирование систем.