postgres storage toast - ghdrako/doc_snipets GitHub Wiki

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;