postgres storage - ghdrako/doc_snipets GitHub Wiki

Physical storage

All the data needed for your PostgreSQL database cluster is stored within the cluster’s data directory and is controlled by PGDATA variable. The common location of the data directory for the RHEL-based system is /var/lib/pgsql/15/data

db # show data directory
var/lib/pgsql/15/data

Change location

  1. Stop server
  2. Create New dir
mkdir /pg_data ; chown postgres:postgres /pg_data
  1. Move data
rsync -av /var/lib/pgsql/15/data/ /pg_data
  1. Modify config
  2. Start server

Relation - postgres term describe tables, index

In Postgresql, all these objects are referred to by the generic term relation.

All information associated with a relation is stored in several different forks,each containing data of a particular type.

fork is represented by a single file. Its filename consists of a numeric (oid), which can be extended by a suffix that corresponds to the fork’s type.

The file grows over time, and when its size reaches 1 GB, another file of this fork is created (such files are sometimes called segments). The sequence number of the segment is added to the end of its filename. You can change 1GB limit when building Postgresql (./configure --with-segsize).

There are several standard types of forks.

  • The main fork represents actual data: table rows or index rows. This fork is available for any relations (except for views, which contain no data). Files of the main fork are named by their numeric IDs, which are stored as relfilenode values in the pg_class table.
  • The initialization fork is available only for unlogged tables (created with the UNLOGGED clause) and their indexes. Such objects are the same as regular ones, except that any actions performed on them are not written into the write-ahead log. It has the same name as the main fork, but with the _init suffix:
  • The free space map keeps track of available space within pages.Its volume changes all the time, growing after vacuuming and getting smaller when new row versions appear. The free space map is used to quickly fi nd a page that can accommodate new data being inserted. All files related to the free space map have the _fsm suffix. Initially, no such files are created; they appear only when necessary. The easiest way to get them is to vacuum a table.
  • The visibility map can quickly show whether a page needs to be vacuumed or frozen. For this purpose, it provides two bits for each table page. The first bit is set for pages that contain only up-to-date row versions. Vac-uum skips such pages because there is nothing to clean up. Besides, when a transaction tries to read a row from such a page, there is no point in checking its visibility, so an index-only scan can be used. The second bit is set for pages that contain only frozen row versions. I will use the term freeze map to refer to this part of the fork. Visibility map files have the _vm suffix

TOAST (The Oversized-Attribute Storage Technique)

Wiersz nie może przekroczyć rozmiaru bloku, a blok ma domyślnie 8 KB. To nie wystarcza w przypadku niektórych znacznie dłuższych pól, takich jak niektóre teksty, ale także typów złożonych (json, jsonb, hstore), binarnych (bytea), a nawet liczbowych.

PostgreSQL może następnie skompresować pola, ale to nie zawsze wystarczy. Następnie aktywowany jest mechanizm TOAST. Polega on na przeniesieniu zawartości określonych pól z rekordu do tabeli systemowej powiązanej z tabelą główną, zarządzanej transparentnie dla użytkownika. Mechanizm ten zapobiega przekroczeniu rozmiaru bloku przez rekord.

Mechanizm TOAST ma również inne zalety:

  • główna część tabeli z bardzo długimi polami jest mniejsza, podczas gdy „duże pola” są rzadziej systematycznie wykorzystywane przez kod aplikacji;
  • pola te można kompresować w sposób przezroczysty, często ze znacznymi oszczędnościami miejsca;
  • jeśli UPDATE nie modyfikuje żadnego z tych „skompresowanych” pól, tabela TOAST nie jest aktualizowana: wskaźnik do rekordu tej tabeli jest po prostu „klonowany” do nowej wersji rekordu.

Każde pole ma właściwość przechowywania:

CREATE TABLE unetable (i int, t text, b bytea, j jsonb);
# \d+ unetable
Table « public.unetable » 
Colonne | Type | Col... | NULL-able | Par défaut | Stockage | … 
---------+---------+--------+-----------+------------+----------+--
i | integer | | | | plain | 
t | text | | | | extended | 
b | bytea | | | | extended | 
j2 | jsonb | | | | extended | 
Méthode d’accès : heap

Istnieją różne zasady przechowywania danych:

  • PLAIN, która zezwala na przechowywanie wyłącznie w tabeli, bez kompresji (w szczególności pól numerycznych i dat);
  • MAIN, która zezwala na przechowywanie w tabeli tak dużo, jak to możliwe, w miarę możliwości w formie skompresowanej (zasada rzadko stosowana);
  • EXTERNAL, która zezwala na przechowywanie w tabeli TOAST, bez kompresji;
  • EXTENDED, która zezwala na przechowywanie w tabeli TOAST, w miarę możliwości w formie skompresowanej (ogólny przypadek pól tekstowych lub binarnych).

Zmiana tego ustawienia zdarza się rzadko, ale się zdarza. Na przykład, niektóre długie pola (często binarne, np. JPEG) kompresują się tak słabo, że nie warto marnować procesora na to zadanie. W skrajnym przypadku, gdy skompresowane pole jest większe od oryginału, PostgreSQL powraca do pierwotnej wartości, ale to również jest nieekonomiczne. W takim przypadku warto zmienić ustawienie z EXTENDED na EXTERNAL, aby zaoszczędzić czas:

ALTER TABLE t1 ALTER COLUMN champ SET STORAGE EXTERNAL ;

Zmiana ta nie ma wpływu na istniejące dane.

Table pg_toast_XXX

Każda tabela użytkownika jest powiązana z tabelą TOAST, i tak jest od momentu jej utworzenia, jeśli tabela zawiera pole „toastable”. Rekordy są przechowywane w blokach o rozmiarze nieco mniejszym niż 2 KB. Wszystkie „toastowane” pola tabeli znajdują się w tej samej tabeli pg_toast_XXX, w oddzielnej przestrzeni nazw o nazwie pg_toast. Dla użytkownika tabele TOAST są całkowicie transparentne. Programista musi jedynie wiedzieć, że nie musi przenosić dużych pól tekstowych (ani JSON, ani binarnych...) z tabeli głównej do osobnej tabeli ze względu na objętość: PostgreSQL już to robi, i to wydajnie! Często nie ma też potrzeby kompresowania danych na poziomie aplikacji tylko po to, by zmniejszyć ilość miejsca na dane.

Toastowanie i kompresja to kosztowne operacje. Przechowywanie dużych danych binarnych w bazie danych nie jest zalecane.

Obecność tabel pg_toast_XXX jest widoczna tylko w pg_class, na przykład:

SELECT * FROM pg_class c 
 WHERE c.relname = 'longs_textes' 
    OR c.oid = (SELECT reltoastrelid 
                FROM pg_class 
               WHERE relname = 'longs_textes');

Sekcja TOAST to osobna tabela z kluczem podstawowym. Nie wolno i nie należy jej zmieniać!

\d+ pg_toast.pg_toast_16614 Table TOAST 
« pg_toast.pg_toast_16614 » 
Colonne | Type | Stockage 
------------+---------+----------
chunk_id | oid | plain 
chunk_seq | integer | plain 
chunk_data | bytea | plain 
Table propriétaire : « public.textes_comp » 
Index :
"pg_toast_16614_index" PRIMARY KEY, btree (chunk_id, chunk_seq) Méthode d'accès : heap

Indeks jest zawsze używany do dostępu do fragmentów. Objętość poszczególnych elementów (część główna, TOAST, możliwe indeksy) można obliczyć za pomocą następującego zapytania:

SELECT oid AS table_oid, c.relnamespace::regnamespace || '.' || relname AS TABLE, reltoastrelid, reltoastrelid::regclass::text AS table_toast, reltuples AS nb_lignes_estimees, pg_size_pretty(pg_table_size(c.oid)) AS " Table (dont TOAST)", pg_size_pretty(pg_relation_size(c.oid)) AS " Heap", pg_size_pretty(pg_relation_size(reltoastrelid)) AS " Toast", pg_size_pretty(pg_indexes_size(reltoastrelid)) AS " Toast (PK)", pg_size_pretty(pg_indexes_size(c.oid)) AS " Index", pg_size_pretty(pg_total_relation_size(c.oid)) AS "Total" FROM pg_class c WHERE relkind = 'r' AND relname = 'longs_textes' \gx -[ RECORD 1 ]
------+------------------------
table_oid | 16614 
table | public.longs_textes 
reltoastrelid | 16617 
table_toast | pg_toast.pg_toast_16614 
nb_lignes_estimees | 2421 
Table (dont TOAST) | 578 MB 
Heap | 280 kB 
Toast | 572 MB 
Toast (PK) | 6448 kB 
Index | 560 kB 
Total | 579 MB

Rozmiar indeksów w polach, które można „tostować”, jest liczony na podstawie wszystkich indeksów w tabeli (klucz podstawowy tabeli TOAST jest oddzielny). Tabele TOAST zawsze pozostają w tej samej przestrzeni tabel, co tabela główna. Ich konserwacja (w tym automatyczne odkurzanie) odbywa się w tym samym czasie, co tabela główna, co obrazuje polecenie VERBOSE VACUUM.

Podsumowując, mechanizm TOAST jest uruchamiany dla rekordu, gdy jego rozmiar przekracza 2 KB. Pola „toastable” można następnie skompresować, aby zmniejszyć rozmiar rekordu poniżej 2 KB. Jeśli to nie wystarczy, pola są dzielone i przenoszone do tabeli TOAST. W tych polach tabeli głównej rekord zawiera teraz tylko wskaźnik do powiązanej tabeli TOAST.

Pole MAIN nadal może być przechowywane w tabeli TOAST, jeśli rekord przekracza 2 KB: lepiej jest „usunąć” rekord niż go blokować. Wartość 2 KB jest zazwyczaj odpowiednia. W razie potrzeby można ją zwiększyć (od wersji 11) za pomocą parametru pamięci toast_tuple_target w następujący sposób:

ALTER TABLE t1 SET (toast_tuple_target = 3000);

ale rzadko jest to przydatne.

Compression pglz vs lz4 :

Od wersji 14 możliwa jest modyfikacja algorytmu kompresji. Jest to definiowane przez nowy parametr default_toast_compression, którego domyślna wartość to:

=# SHOW default_toast_compression ;
default_toast_compression 
---------------------------
pglz

Oznacza to, że PostgreSQL korzysta z zlib, jedynej dostępnej kompresji do wersji 13 włącznie. Od wersji 14 często preferowane jest użycie lz4, nowego algorytmu, jeśli PostgreSQL został skompilowany z biblioteką o tej samej nazwie (dotyczy to pakietów dystrybuowanych przez PGDG). Aktywacja wymaga modyfikacji domyślnej wartości w pliku postgresql.conf:

default_toast_compression = lz4

Deklaracja metody kompresji podczas tworzenia tabeli

CREATE TABLE t1 ( c1 bigint GENERATED ALWAYS AS identity, c2 text COMPRESSION lz4 ) ;

albo po fakcie :

ALTER TABLE t1 ALTER c2 SET COMPRESSION lz4 ;

Ogólnie rzecz biorąc, algorytm lz4 nie kompresuje typowych danych lepiej niż pglz, ale to zależy od sposobu użycia. Co ważne, lz4 jest znacznie szybszy w kompresji, a czasem i dekompresji. Na przykład, może przyspieszyć logiczne przywracanie danych w przypadku dużej ilości spakowanych i skompresowanych danych. Jeśli lz4 nie był domyślnie włączony, można go użyć natychmiast po załadowaniu:

$ PGOPTIONS='-c default_toast_compression=lz4' pg_restore …

Zalecaną opcją jest lz4, jako kompromis między użyciem procesora podczas zapisu lub odczytu oraz przestrzenią dyskową można uzyskać jedynie poprzez dokładne przetestowanie na rzeczywistych danych.

Tabela TOAST może zawierać kombinację wierszy o różnej kompresji. Użycie SET COMPRESSION na istniejącej już kolumnie nie powoduje ponownej kompresji danych tabeli TOAST. Dodatkowo, podczas zapytania, dane typu toasted, które są odczytywane przez zapytanie, a następnie ponownie wstawiane w niezmienionej postaci, są kopiowane do pól docelowych bez żadnych kroków dekompresji.

pg_column_compression (nazwa_kolumny) – sprawdza kompresję pola w danym wierszu. Aby wymusić rekompresję wszystkich danych w kolumnie, należy zmodyfikować ich zawartość, co niekoniecznie jest interesujące.

TOAST is solution for storing large values inside columns when the page size is just 8 kB and individual rows cannot spill over into the next pages. It supports in-line compression of the values, but also storing them out-of-line (in a different, associated table).

  • A page size of 8KB is used for storing tuples, indexes, and queries execution plan.
  • A row or tuple cannot extend across multiple pages, but there are no restrictions on the size of individual database rows.
  • Index ca by use TOAST but we want generally avoid that.

The Oversized-Attribute Storage Technique (TOAST) mechanizm ensures that a tuple does not surpass the size of the default page size by storing oversized attributes separately. Therefore, the block size serves as an absolute maximum limit for row size.

\d+ book.author_list

In output in column storage we can see plain or extend.

select relname, relfilenode, reltoastrelid from pg_class where relname = 'author_list'; 
 # Following the Output of the above command
 relname  | relfilenode | reltoastrelid
 -------------+-------------+---------------
 author_list | 24581 | 24584
  • 24581 is OID of regularne table
  • 24584 is OID of TOAST
\d+ pg_toast.pg_toast_24581;
select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_24581;

Heap-Only Tuples (HOT)

Filfactor

Logs

# Verify the log directory and current log file
 SELECT pg_current_logfile();
 # Execution output of the above SQL
 pg_current_logfile 
 ------------------------
 log/postgresql-Sat.csv

# Verify the Log enteries
 cat /pg_data/log/postgresql-Sat.csv

Logical storage