PostgreSQL — co to jest i jak działa?

1986

Początek projektu POSTGRES na UC Berkeley. Open-source od 1996 jako PostgreSQL.

#1 RDBMS

Najpopularniejsza baza danych wg Stack Overflow Developer Survey 2023 (wśród profesjonalnych deweloperów)

ACID

Pełna zgodność z ACID (Atomicity, Consistency, Isolation, Durability) — gwarancja integralności danych

Numeryczne

• INTEGER, BIGINT, SMALLINT
• DECIMAL/NUMERIC (precyzja)
• REAL, DOUBLE PRECISION
• SERIAL, BIGSERIAL (auto-increment)

Tekstowe

• VARCHAR(n) — limit znaków
• TEXT — nieograniczona długość
• CHAR(n) — fixed-length
• CITEXT — case-insensitive text

Data i czas

• DATE — tylko data
• TIME — czas (z/bez timezone)
• TIMESTAMP — data + czas
• INTERVAL — przedział czasu

JSON

• JSON — walidacja składni, wolny
• JSONB — binarny, indeksowalny, szybki
• jsonpath — XPath dla JSON (pg12+)

Specjalne

• UUID — uniwersalne ID
• ARRAY — tablice dowolnych typów
• ENUM — lista wartości
• INET/CIDR — adresy IP

Geometryczne / GIS

• POINT, LINE, POLYGON
• geometry, geography (PostGIS)
• tsquery/tsvector — full-text search

Zaawansowane zapytanie z CTE i Window Functions

-- CTE (Common Table Expression) + window function
WITH ranked_orders AS (
  SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) as rn,
    SUM(amount) OVER (
      PARTITION BY customer_id
    ) as total_spent
  FROM orders
  WHERE status = 'completed'
)
SELECT customer_id, order_date, amount, total_spent
FROM ranked_orders
WHERE rn = 1  -- tylko ostatnie zamówienie każdego klienta
ORDER BY total_spent DESC
LIMIT 10;

Indeks i EXPLAIN ANALYZE

-- Utwórz indeks częściowy (partial index)
CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';  -- tylko dla pending zamówień

-- Sprawdź plan zapytania
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '7 days';

-- Indeks GIN dla JSONB
CREATE INDEX idx_products_attrs
ON products USING GIN (attributes);

-- Zapytanie po JSONB
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "XL"}'::jsonb;

Partitioning (dla dużych tabel)

-- Tabela partycjonowana po dacie
CREATE TABLE events (
  id BIGSERIAL,
  user_id INTEGER,
  event_type TEXT,
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Utwórz partycje per miesiąc
CREATE TABLE events_2024_01
  PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02
  PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

PostGIS

Wsparcie dla danych geospatialnych — geometry, geography, spatial queries. Standard dla GIS w PostgreSQL.

pgvector

Wektory i similarity search dla AI/ML — przechowuj embeddings, szukaj najbliższych sąsiadów (ANN).

TimescaleDB

Optymalizacja dla time-series data — automatyczne chunking, compression, continuous aggregates.

pg_trgm

Trigram similarity dla fuzzy string matching i full-text search z LIKE/ILIKE na indeksach GIN/GiST.

uuid-ossp

Generowanie UUID v1/v4. Alternatywa: gen_random_uuid() wbudowana od PostgreSQL 13.

pg_cron

Cron-like scheduler wewnątrz PostgreSQL — uruchamiaj SQL co godzinę, dzień, tydzień.

Co to jest PostgreSQL?

PostgreSQL (Postgres) to zaawansowana, open-source'owa relacyjna baza danych obiektowo-relacyjna (ORDBMS). Rozwijana od 1986 roku na Uniwersytecie Berkeley jako POSTGRES, od 1996 roku jako PostgreSQL. Znana z niezawodności, rozszerzalności i zgodności ze standardami SQL. Obsługuje zaawansowane funkcje: JSONB (dokumenty), pełnotekstowe wyszukiwanie (FTS), tablice, typy geometryczne, dziedziczenie tabel, procedury składowane (PL/pgSQL, Python, JavaScript), widoki zmaterializowane i rozszerzenia (PostGIS, TimescaleDB, pgvector).

Czym PostgreSQL różni się od MySQL?

PostgreSQL vs MySQL: PostgreSQL jest bardziej zgodny ze standardem SQL, obsługuje bardziej zaawansowane typy danych (JSONB, arrays, range types, custom types), lepsze wsparcie dla concurrent write (MVCC), zaawansowane indeksy (GiST, GIN, BRIN, partial, expression indexes). MySQL jest szybszy przy prostych odczytach (MyISAM), łatwiej skonfigurować, szeroko używany w LAMP stack (WordPress, Drupal). PostgreSQL preferowany w aplikacjach wymagających złożonych zapytań, transakcji i zaawansowanych typów danych.

Co to jest MVCC w PostgreSQL?

MVCC (Multi-Version Concurrency Control) to mechanizm kontroli współbieżności PostgreSQL. Zamiast blokować wiersze podczas odczytu — każda transakcja widzi 'snapshot' danych z momentu jej rozpoczęcia. Operacje SELECT nie blokują UPDATE/DELETE i odwrotnie — readers don't block writers. MVCC zapewnia izolację transakcji (domyślnie Read Committed, opcjonalnie Repeatable Read lub Serializable) bez kosztownych blokad. Skutkiem ubocznym są 'dead tuples' — stare wersje wierszy usuwane przez VACUUM.

Jak PostgreSQL obsługuje JSON?

PostgreSQL oferuje dwa typy JSON: JSON (przechowuje jako tekst, waliduje składnię) i JSONB (Binary JSON — przechowuje binarnie, indeksowalny, szybsze operacje). JSONB obsługuje operatory: -> (pobierz klucz), ->> (pobierz wartość jako tekst), @> (zawiera), <@ (jest zawarty), ? (klucz istnieje). Indeksy GIN na JSONB umożliwiają efektywne wyszukiwanie po zawartości dokumentów. PostgreSQL z JSONB łączy elastyczność NoSQL z niezawodnością ACID transakcji SQL.

Jakie są kluczowe optymalizacje PostgreSQL?

Optymalizacja PostgreSQL: indeksy (B-tree domyślnie, GIN dla FTS/JSONB, GiST dla geometrii, BRIN dla dużych tabel z sekwencyjnymi danymi, partial indexes dla podzbioru wierszy), EXPLAIN ANALYZE do analizy planów zapytań, connection pooling (PgBouncer — 1000+ połączeń do 20 pooled), vacuuming (autovacuum usuwa dead tuples), partitioning (Range, List, Hash dla bardzo dużych tabel), widoki zmaterializowane dla ciężkich zapytań read-heavy, read replicas dla odczytu.