Baza pytań rekrutacyjnych i wiedzy. Filtruj, szukaj i sprawdzaj swoją wiedzę.
Indeks to osobna struktura danych (zwykle B‑tree) przechowująca uporządkowane klucze i wskaźniki do wierszy. Pozwala PostgreSQL odnaleźć dane bez pełnego skanowania tabeli, przyspieszając wyszukiwanie i joiny kosztem dodatkowego miejsca i wolniejszych zapisów.
Indeks złożony warto stosować, gdy zapytania często filtrują lub sortują po tym samym zestawie kolumn w tej samej kolejności. PostgreSQL używa zasady leftmost‑prefix, więc kolejność kolumn ma znaczenie. Unikaj indeksów złożonych, gdy pierwsze kolumny mają niską selektywność lub rzadko występują razem.
Transakcja grupuje instrukcje SQL w jednostkę „wszystko albo nic” z gwarancjami ACID. Poziomy izolacji w PostgreSQL to Read Committed (domyślny), Repeatable Read i Serializable, które kontrolują zjawiska typu non‑repeatable reads czy phantom reads.
VACUUM usuwa martwe wersje wierszy pozostawione przez MVCC, aktualizuje mapy widoczności i statystyki oraz zapobiega puchnięciu tabel i zawijaniu identyfikatorów transakcji. Autovacuum wykonuje to automatycznie w tle, utrzymując bazę w dobrej kondycji.
MVCC (multi‑version concurrency control) utrzymuje wiele wersji wierszy. Aktualizacja tworzy nową wersję z identyfikatorami transakcji; czytelnicy widzą spójny snapshot bez blokowania zapisów. Stare wersje są później sprzątane przez VACUUM.
MVCC (Multi-Version Concurrency Control) pozwala czytelnikom widzieć spójny snapshot, a zapisy tworzą nowe wersje wierszy, co zmniejsza blokowanie odczyt/zapis przy współbieżności.
Ponieważ MVCC tworzy „martwe” wersje wierszy, VACUUM je sprząta, żeby zwolnić miejsce i utrzymać wydajność. Autovacuum robi to automatycznie i zapobiega bloatowi oraz problemom z wraparound transaction ID.
`EXPLAIN` pokazuje plan wykonania; `EXPLAIN ANALYZE` dodatkowo uruchamia zapytanie i pokazuje realne czasy/wiersze. To podstawowe narzędzie do zrozumienia, czemu zapytanie jest wolne.
Wstawia wiersz, ale jeśli naruszy unique constraint/indeks, aktualizuje istniejący wiersz. To bezpieczny sposób na „insert or update” w jednym poleceniu.
GIN (Generalized Inverted Index) świetnie pasuje do zapytań typu „contains” na typach złożonych jak tablice i `jsonb` (np. `@>`, `?`). Indeksuje relację wiele-do-wielu między kluczami a wierszami.
Blokuje wybrane wiersze na czas transakcji, uniemożliwiając innym transakcjom ich aktualizację (lub zablokowanie) do commit/rollback. Używa się do bezpiecznego read-modify-write bez utraty aktualizacji.
Tworzenie wielu połączeń do Postgresa jest kosztowne (pamięć, praca procesów). Pool odzyskuje połączenia i ogranicza współbieżność, chroniąc DB; kosztem części funkcji sesyjnych (zależnie od trybu poolingu) zyskujesz stabilność.
Deadlock powstaje, gdy dwie transakcje trzymają blokady potrzebne drugiej (A czeka na B, B czeka na A). Ograniczasz przez spójny porządek blokowania, krótkie transakcje i unikanie niepotrzebnego `FOR UPDATE`.
Partycjonowanie pomaga przy dużych tabelach, gdy zapytania filtrują po kluczu partycji (czas, tenant) i planner może pominąć partycje (partition pruning). Pułapka: brak filtra po kluczu partycji — wtedy skanujesz wiele partycji i zysk znika.
JSON przechowuje tekst; JSONB trzyma format binarny, który jest szybszy do zapytań i indeksowania. JSONB dobrze współpracuje z operatorami i indeksami GIN, dlatego jest częstym wyborem.
`ANALYZE` aktualizuje statystyki tabel (liczba wierszy, rozkład wartości). Planner używa ich do wyboru planu; stare statystyki mogą dać słaby plan, np. zły join albo brak użycia indeksu.
BRIN jest dobry dla ogromnych tabel, gdy dane są naturalnie uporządkowane na dysku (np. time-series po created_at). Jest mały i tani w utrzymaniu, ale mniej precyzyjny niż B-tree i opiera się na korelacji z fizycznym porządkiem danych.
`work_mem` limituje pamięć na pojedynczą operację (sort, hash join, agregacja). Gdy potrzeba więcej niż `work_mem`, Postgres zapisuje dane tymczasowe na dysk, co jest dużo wolniejsze i pogarsza wydajność.
WAL (Write-Ahead Logging) zapisuje zmiany do logu zanim trafią do plików danych. Po crashu Postgres odtwarza zmiany przez replay WAL. Replikacja może przesyłać WAL do replik, które aplikują te same zmiany w tej samej kolejności.
Index-only scan oznacza, że zapytanie da się obsłużyć z indeksu bez czytania tabeli, bo indeks zawiera potrzebne kolumny. Postgres musi jednak wiedzieć, czy wiersze są widoczne dla transakcji; visibility map oznacza strony, gdzie wszystkie wiersze są widoczne, dzięki czemu można pominąć odczyt z tabeli.
Pokazuje aktualne połączenia i działające zapytania: stan, czas trwania, oczekiwania i treść SQL. Przydaje się do wykrywania długich zapytań, blokad i problemów z połączeniami.
`VACUUM FULL` przepisuje całą tabelę, żeby ją skompaktować i odzyskać miejsce. Bierze mocniejsze locki i może blokować odczyt/zapis tej tabeli, więc jest uciążliwe; preferuj zwykłe VACUUM/autovacuum i najpierw usuń przyczyny bloatu.
`REINDEX` przebudowuje indeksy. Używa się go, gdy indeks jest spuchnięty (bloat) lub podejrzewasz uszkodzenie, albo gdy przebudowa poprawi wydajność. Może brać locki i zużywać zasoby, więc trzeba to planować.
Replikacja fizyczna wysyła WAL i utrzymuje dokładną kopię na poziomie bajtów (świetna do HA). Replikacja logiczna replikuje zmiany na poziomie tabel (INSERT/UPDATE/DELETE), co jest elastyczne przy migracjach i selektywnej replikacji, ale jest bardziej złożone.
Sequence generuje liczby niezależnie od transakcji. Jeśli transakcja zrobi rollback po pobraniu wartości, albo wartości są cache’owane i nieużyte, powstają „dziury”. To normalne; nie zakładaj, że ID będą ciągłe.
Schema to przestrzeń nazw w ramach bazy (grupuje tabele, widoki, funkcje itd.). Używa się jej do porządkowania obiektów, rozdzielenia odpowiedzialności (np. app vs audit) i zarządzania uprawnieniami. `search_path` decyduje, które schemy są przeszukiwane, gdy nie kwalifikujesz nazw.
Partial index indeksuje tylko wiersze spełniające warunek WHERE. Przydaje się, gdy większość wierszy nie musi być indeksowana (np. tylko aktywne lub nieusunięte), dzięki czemu indeks jest mniejszy i szybszy w użyciu oraz utrzymaniu.
Window functions liczą wartości na “oknie” wierszy, ale zachowują każdy wiersz w wyniku (w przeciwieństwie do GROUP BY, które grupuje wiersze w jeden). Są świetne do rankingów, sum narastających i “top N per grupa”.
Advisory locki to locki zdefiniowane przez aplikację (`pg_advisory_lock`), niezwiązane z konkretnym wierszem tabeli. Pomagają, gdy potrzebujesz koordynacji typu “tylko jeden worker to robi” (np. jeden job schedulera). Nie zastępują constraintów w DB i trzeba ostrożnie obsłużyć timeouty oraz awarie.
`LISTEN/NOTIFY` to lekki pub/sub w Postgresie: klient nasłuchuje kanału i dostaje powiadomienie, gdy ktoś zrobi `NOTIFY`. Jest dobry do sygnałów (invalidacja cache, “coś się zmieniło”), ale nie jest trwały (można zgubić wiadomość przy rozłączeniu) i nie jest pełnoprawną kolejką.
`COPY` służy do masowego importu/eksportu danych (z/do pliku albo STDIN/STDOUT). Jest szybkie, bo jest zoptymalizowane pod bulk i unika narzutu per wiersz, który często masz przy wielu pojedynczych INSERT-ach.
CTE to nazwane podzapytanie, które ułatwia czytanie złożonego SQL. Gotcha: w niektórych przypadkach planner może zmaterializować CTE (policzyć je w całości) zamiast je zinline'ować, co może spowolnić. W nowszych Postgresach CTE często jest inline, ale nadal da się wymusić materializację.
W transaction poolingu PgBouncer może podmieniać fizyczne połączenie do bazy między transakcjami. To znaczy, że stan sesji (temp tables, zmienne sesyjne, prepared statements) może nie przetrwać. Aplikacje zakładające stałą sesję mogą się psuć; czasem potrzebujesz session poolingu albo unikania stanu sesji.
Przez MVCC update/delete tworzą “martwe” wiersze (dead tuples), które musi sprzątać VACUUM. Bloat pojawia się, gdy sprzątanie nie nadąża (dużo update'ów, długie transakcje, złe ustawienia autovacuum), więc tabele/indeksy rosną. Mitigacja: tuning autovacuum, unikanie długich transakcji oraz okresowo `REINDEX`/`VACUUM (FULL)` lub narzędzia online typu pg_repack, gdy trzeba.
HOT update (Heap-Only Tuple) zachodzi, gdy UPDATE nie zmienia kolumn indeksowanych i jest miejsce na stronie, więc Postgres może uniknąć aktualizacji indeksów. `fillfactor` zostawia wolne miejsce na stronach, żeby HOT update było bardziej prawdopodobne. To zmniejsza bloat indeksów i może poprawić wydajność zapisów.
Postgres używa MVCC (multi‑version concurrency control): update tworzy nową wersję wiersza, a odczyty korzystają ze snapshotu spójnego widoku. Dzięki temu odczyty mogą czytać stare wersje bez blokowania zapisów; VACUUM sprząta martwe wiersze później.
VACUUM odzyskuje martwe wiersze do ponownego użycia bez ciężkiego locka na tabeli. VACUUM FULL przepisuje tabelę, aby ją fizycznie zmniejszyć, ale wymaga ekskluzywnego locka i bywa wolne. FULL używaj rzadko.
Autovacuum uruchamia się po przekroczeniu progów zmian w tabeli (liczby update/delete). Sprząta martwe wiersze, aktualizuje statystyki i zapobiega bloatowi oraz wraparoundowi ID transakcji. Bez niego wydajność i niezawodność spadają.
ANALYZE zbiera statystyki o danych w tabeli (liczba wierszy, rozkład wartości). Planner używa tych danych do estymacji kosztów i wyboru kolejności joinów oraz użycia indeksów. Stare statystyki mogą prowadzić do złych planów.
GIN nadaje się do indeksowania wartości złożonych typu array, JSONB i full‑text (szybkie odczyty, cięższe zapisy). GiST to elastyczny indeks dla zakresów, geometrii i wyszukiwania podobieństwa; wspiera własne operatory i często używa się go do danych przestrzennych.
BRIN (Block Range INdex) jest przydatny dla bardzo dużych tabel z naturalnym uporządkowaniem danych (np. time‑series). Przechowuje podsumowania zakresów bloków, więc jest mały i szybki w budowie, ale mniej precyzyjny niż B‑tree.
Advisory locks to blokady definiowane przez aplikację w Postgresie (nie są powiązane z konkretnymi wierszami). Przydają się do koordynacji pracy, np. jobów lub zapewnienia, że tylko jedna instancja wykona zadanie. Są dobrowolne — aplikacje muszą je respektować.
LISTEN/NOTIFY daje lekki pub/sub wewnątrz Postgresa. Sesja może LISTEN na kanale, a inna może wysłać NOTIFY. To przydatne np. do invalidacji cache lub budzenia workerów bez ciągłego pollingu.
View to zapisane zapytanie wykonywane przy każdym odczycie. Materialized view przechowuje wynik fizycznie i wymaga odświeżenia, aby się zaktualizować. Materialized view może być dużo szybsze przy ciężkich odczytach, ale bywa nieaktualne.
`pg_stat_activity` pokazuje bieżące sesje i uruchomione zapytania. `pg_stat_statements` agreguje statystyki zapytań (liczba wywołań, łączny czas), co pomaga znaleźć wolne lub częste SQL. Razem ułatwiają wykrywanie blockerów, długich transakcji i ciężkich zapytań.