postgres index create - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/sql-createindex.html

• CREATE INDEX,

• CREATE INDEX CONCURRENTLY,

• REINDEX, or

• REINDEX CONCURRENTLY

Tunning

• https://www.cybertec-postgresql.com/en/postgresql-parallel-create-index-for-better-performance/
• https://postgresqlco.nf/doc/en/param/max_parallel_maintenance_workers/?utm_source=chatgpt.com
• https://www.postgresql.org/docs/current/runtime-config-resource.html
• https://pgpedia.info/m/max_parallel_maintenance_workers.html

Most operations have to do some sorting or memory allocation of some kind. The administrative ones, such as the CREATE INDEX clause, don’t rely on the work_mem variable and use the maintenance_work_mem variable instead.

SET maintenance_work_mem = '40GB'; -- zwiekszenie pamięć dostępną do budowy indeksów, co przyspieszy operację i pozwoli uniknąć problemów z pamięcią.
--SET work_mem = '1GB'; -- więcej pamięci na operacje sortowania przy selectach - nie jest uzywana przy budowie indeksow

PostgreSQL 11+ - build btree indexes in parallel, which can dramatically speed up the indexing of large tables.

 SHOW max_parallel_maintenance_workers;

As for every parallel operation, PostgreSQL will determine the number of workers based on table sizes. When indexing large tables, index creation can see drastic improvements.

Keep in mind that we use memory:

max_parallel_maintenance_workers x maintenance_work_mem

Another factot to speed up index is data types. The numeric is worsts typ Much faster index is create on int4,int8,integer.

If checkpoints and I/O have started to become a limiting factor we can

checkpoint_timeout = 120min
max_wal_size = 50GB
min_wal_size = 80GB

SELECT pg_reload_conf();

Monitoring (Postgres 12+)

SELECT * FROM pg_stat_progress_create_index;

• jest odpowiednikiem widoku pg_progress_vacuum
• Column phase in example building index: scanning table or building index: loading tuples in tree
• Columns tuples_total and tuples_done give a rough estimate of the percentage of work done. tuples_total tuples_done wypwlnija sie gdy

Etapy

1. Inicjalizacja procesu

• Jeśli używana jest opcja CONCURRENTLY, proces zostaje przygotowany do minimalizacji blokad na tabeli.

2. Skanowanie tabeli

• Pełne przeszukanie tabeli w celu zebrania danych potrzebnych do budowy struktury indeksu.​
• W trybie CONCURRENTLY wykonywane są dwa skanowania: pierwsze do zebrania danych, drugie do uwzględnienia zmian, które zaszły podczas pierwszego skanowania.

3. Budowa struktury indeksu

• Tworzenie struktury indeksu w pamięci na podstawie zebranych danych.​
• Dla indeksów B-tree, dane są sortowane, a następnie organizowane w strukturę drzewa.

4. Zapis indeksu na dysku

• Zapisanie zbudowanej struktury indeksu na dysku w odpowiednim formacie.​
• W przypadku indeksów tworzonych z opcją CONCURRENTLY, po drugim skanowaniu i uwzględnieniu wszystkich zmian, indeks jest zapisywany na dysku.

5. Walidacja i aktywacja indeksu

• Sprawdzenie integralności i poprawności zbudowanego indeksu.​
• Po pomyślnej walidacji, indeks zostaje oznaczony jako aktywny i jest dostępny dla zapytań. Phase
• building index: scanning table - wypelnija sie blocks_total i blocks_done
• building index: loading tuples in tree - wypelniaja sie tuples_total i tuples_done

Parametry

max_parallel_maintenance_workers

Określa maksymalną liczbę pracowników równoległych, którzy mogą być uruchomieni przez pojedyncze polecenie narzędziowe, takie jak CREATE INDEX czy VACUUM. Domyślna wartość to 2. Zwiększenie tej wartości może przyspieszyć tworzenie indeksów, o ile dostępne są odpowiednie zasoby systemowe.

max_parallel_workers

Ustawia globalny limit dla wszystkich pracowników równoległych w systemie. Jeśli max_parallel_maintenance_workers jest ustawiony na wartość wyższą niż max_parallel_workers, to i tak nie zostanie wykorzystana większa liczba pracowników niż ta określona przez max_parallel_workers. Dlatego warto upewnić się, że max_parallel_workers jest ustawiony na wartość równą lub wyższą niż max_parallel_maintenance_workers.

max_worker_processes

Określa maksymalną liczbę wszystkich procesów pracowników w systemie, w tym tych używanych przez funkcje równoległe. Jeśli suma wszystkich pracowników (równoległych i innych) przekroczy tę wartość, nowe procesy nie będą mogły zostać uruchomione. Dlatego warto upewnić się, że max_worker_processes jest ustawiony na wartość wystarczająco wysoką, aby pomieścić wszystkie potrzebne procesy.

maintenance_work_mem

Określa ilość pamięci roboczej dostępnej dla operacji konserwacyjnych, takich jak tworzenie indeksów. W przypadku operacji równoległych, ta pamięć jest współdzielona między proces główny a pracowników równoległych. Każdy pracownik potrzebuje co najmniej 32 MB z tej puli. Dlatego, aby efektywnie wykorzystać większą liczbę pracowników, należy odpowiednio zwiększyć maintenance_work_mem.

parallel_workers (ustawienie na poziomie tabeli)

Możesz ustawić liczbę pracowników równoległych dla konkretnej tabeli za pomocą polecenia:

    ALTER TABLE nazwa_tabeli SET (parallel_workers = 4);

To ustawienie pozwala na określenie liczby pracowników równoległych używanych podczas operacji na tej tabeli, niezależnie od globalnych ustawień. Może to być przydatne, gdy chcesz dostosować równoległość dla konkretnych dużych tabel.