25. Základy relačních databází (PostgreSQL/MySQL) pro provoz aplikací

Úvod a kontext

Téměř každá webová aplikace potřebuje někam ukládat data — uživatele,
objednávky, články, měření ze senzorů. Nejrozšířenějším řešením jsou
dodnes relační databáze (PostgreSQL, MySQL/MariaDB, dříve i Oracle či
MS SQL Server). DevOps inženýr obvykle databázi nenavrhuje ani nepíše
aplikační dotazy — o to se stará vývojář — ale musí rozumět tomu, jak
databáze funguje z provozního pohledu: jak ji nainstalovat a nastartovat,
jak se k ní bezpečně připojit, jak zkontrolovat, že běží a je zdravá, a
jak základní SQL dotazy použít při diagnostice problému. Tato lekce dává
tento provozní základ.

Teorie

Co je relační databáze

Relační databáze ukládá data do tabulek — mřížek řádků a sloupců,
podobných listu v tabulkovém procesoru, ale s přesně definovanými typy
sloupců a vztahy mezi tabulkami. Každá tabulka má sloupce (např. id,
jmeno, email) a řádky (jednotlivé záznamy). Vztahy mezi tabulkami se
vyjadřují přes cizí klíče (foreign key) — např. tabulka objednavky
odkazuje přes sloupec zakaznik_id na tabulku zakaznici.

"Relační" znamená, že data jsou rozdělená do více souvisejících tabulek
místo jedné velké tabulky se vším — tomu se říká normalizace a cílem
je vyhnout se duplicitě dat (jméno zákazníka se uloží jednou v tabulce
zakaznici, ne znovu u každé jeho objednávky).

K práci s daty se používá jazyk SQL (Structured Query Language) —
standardizovaný jazyk pro definici tabulek, vkládání, čtení, úpravu a
mazání dat, kterému rozumí PostgreSQL, MySQL i většina ostatních
relačních databází (s drobnými rozdíly v syntaxi).

PostgreSQL vs. MySQL/MariaDB

Obě jsou open-source relační databáze a v drtivé většině běžných případů
zvládnou totéž. Praktické rozdíly, které DevOps inženýr běžně řeší:

• PostgreSQL je považován za striktnější v dodržování SQL standardu
  a bohatší na pokročilé datové typy (JSON, pole, geodata přes rozšíření
  PostGIS) a pokročilé indexy. Často výchozí volba pro nové projekty.
• MySQL/MariaDB je historicky rozšířenější (WordPress a spousta PHP
  aplikací), má o něco jednodušší replikaci pro základní scénáře a je
  často mírně rychlejší na jednoduchých čtecích dotazech.
• Provozně jsou si podobné: obě běží jako démon naslouchající na portu
  (PostgreSQL 5432, MySQL 3306), obě mají klientský nástroj příkazové
  řádky (psql, mysql), obě podporují zálohování, replikaci i
  transakce.

Volba mezi nimi je většinou dána zvyklostmi týmu nebo požadavky aplikace,
ne zásadním technickým rozdílem pro běžné použití.

Základní SQL příkazy

Definice tabulky:

CREATE TABLE zakaznici (
    id SERIAL PRIMARY KEY,
    jmeno VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    vytvoreno TIMESTAMP DEFAULT NOW()
);

• SERIAL PRIMARY KEY — automaticky rostoucí unikátní identifikátor
  řádku (v MySQL by se použilo AUTO_INCREMENT),
• NOT NULL — sloupec musí mít vždy hodnotu,
• UNIQUE — hodnota se v tabulce nesmí opakovat.

Vložení a čtení dat:

INSERT INTO zakaznici (jmeno, email) VALUES ('Jana Nováková', 'jana@example.cz');

SELECT * FROM zakaznici WHERE email = 'jana@example.cz';

UPDATE zakaznici SET jmeno = 'Jana Svobodová' WHERE id = 1;

DELETE FROM zakaznici WHERE id = 1;

Spojení dvou tabulek přes cizí klíč (JOIN) — typický dotaz, na který
DevOps při diagnostice narazí, i když ho sám nenapsal:

SELECT zakaznici.jmeno, objednavky.castka
FROM objednavky
JOIN zakaznici ON objednavky.zakaznik_id = zakaznici.id
WHERE objednavky.vytvoreno > NOW() - INTERVAL '7 days';

Transakce a ACID

Databáze garantují tzv. ACID vlastnosti: Atomicity (transakce
proběhne buď celá, nebo vůbec), Consistency (data zůstanou v konzistentním
stavu), Isolation (souběžné transakce se navzájem neruší nekontrolovaně),
Durability (jednou potvrzená data přežijí i pád serveru). Transakce se v
SQL ohraničuje:

BEGIN;
UPDATE ucty SET zustatek = zustatek - 100 WHERE id = 1;
UPDATE ucty SET zustatek = zustatek + 100 WHERE id = 2;
COMMIT;

Kdyby aplikace spadla mezi oběma UPDATE, bez transakce by peníze
"zmizely" — s transakcí se buď provedou obě změny, nebo žádná
(ROLLBACK).

Indexy

Index je pomocná datová struktura, která databázi umožní najít řádky
rychle bez procházení celé tabulky — podobně jako rejstřík na konci
knihy. Bez indexu na sloupci email by hledání zákazníka podle e-mailu
u milionu řádků znamenalo projít je všechny (tzv. full table scan):

CREATE INDEX idx_zakaznici_email ON zakaznici (email);

Cena za rychlejší čtení je pomalejší zápis (index se musí při každém
INSERT/UPDATE také aktualizovat) a extra místo na disku — proto se
indexy nedávají automaticky na každý sloupec, jen na ty, podle kterých se
často vyhledává nebo řadí.

Praktický příklad

Instalace PostgreSQL na Ubuntu/Debianu a základní ověření provozu:

sudo apt update
sudo apt install postgresql

# ověření, že služba běží
sudo systemctl status postgresql

# připojení jako uživatel postgres (výchozí administrátorský účet)
sudo -u postgres psql

Uvnitř psql konzole — vytvoření databáze a uživatele pro aplikaci:

CREATE DATABASE moje_appka;
CREATE USER appka_user WITH PASSWORD 'silne-heslo';
GRANT ALL PRIVILEGES ON DATABASE moje_appka TO appka_user;

Užitečné diagnostické psql příkazy, které DevOps používá při řešení
provozních problémů:

\l          seznam všech databází
\c nazev    přepnutí na danou databázi
\dt         seznam tabulek v aktuální databázi
\d tabulka  popis struktury konkrétní tabulky
\du         seznam uživatelů a jejich oprávnění

Kontrola aktivních připojení a dlouho běžících dotazů (časté při ladění
"proč je databáze pomalá"):

SELECT pid, now() - query_start AS doba_behu, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY doba_behu DESC;

Shrnutí

Relační databáze ukládají data do provázaných tabulek a k práci s nimi
slouží jazyk SQL; PostgreSQL a MySQL/MariaDB jsou nejrozšířenější
open-source zástupci a pro běžné provozní účely jsou si podobné.
Transakce garantují, že se skupina změn provede celá, nebo vůbec, a
indexy zrychlují vyhledávání za cenu pomalejšího zápisu. DevOps inženýr
nemusí umět navrhovat databázové schéma, ale musí umět databázi
nainstalovat, připojit se k ní, zkontrolovat její stav a použít základní
SQL dotazy při diagnostice provozních problémů.

Kontrolní otázky

1. Co znamená zkratka ACID a proč jsou transakce důležité při převodu
   peněz mezi dvěma účty?
2. K čemu slouží index a proč se nepřidává automaticky na každý sloupec?
3. Jakým příkazem v psql zjistíte seznam tabulek v aktuální databázi a
   jakým strukturu konkrétní tabulky?
4. Napište SQL dotaz, který vypíše jméno zákazníka a částku objednávky
   pro všechny objednávky vytvořené za posledních 7 dní.