PostgreSQL vadovas

Kas yra PostgreSQL ir kodėl verta jį rinktis?

PostgreSQL – tai atviro kodo reliacinė duomenų bazių valdymo sistema, kuri egzistuoja nuo 1986 metų ir per tą laiką tapo viena patikimiausių bei galingiausių duomenų bazių rinkoje. Skirtingai nei MySQL ar SQLite, PostgreSQL nuo pat pradžių buvo kuriamas kaip rimtas, enterprise lygio sprendimas, kuris gali susidoroti su sudėtingomis užklausomis, dideliais duomenų kiekiais ir aukštais patikimumo reikalavimais.

Kodėl verta rinktis PostgreSQL, o ne, pavyzdžiui, MySQL? Pirma, PostgreSQL pilnai atitinka ACID principus (Atomicity, Consistency, Isolation, Durability), kas reiškia, kad jūsų duomenys bus saugūs net ir sistemos gedimų atveju. Antra, jis palaiko daug daugiau duomenų tipų – nuo standartinių integer ir varchar iki JSON, XML, geometrinių duomenų ir net tinkintų tipų, kuriuos galite sukurti patys. Trečia, PostgreSQL turi labai gerą palaikymą sudėtingoms užklausoms, rekursinėms CTE (Common Table Expressions) ir langų funkcijoms.

Praktinis patarimas: jei kuriate naują projektą ir dar nesate apsisprendę dėl duomenų bazės, PostgreSQL yra beveik visada saugesnis pasirinkimas ilgalaikėje perspektyvoje. Jis gali atrodyti sudėtingesnis nei SQLite ar MySQL pradžioje, bet ta investicija atsipirks, kai projektas pradės augti.

Diegimas ir pirmieji žingsniai

PostgreSQL diegimas priklauso nuo jūsų operacinės sistemos, bet visais atvejais tai nėra sudėtinga. Ubuntu/Debian sistemose pakanka kelių komandų:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

MacOS naudotojams rekomenduoju naudoti Homebrew:

brew install postgresql@15
brew services start postgresql@15

Windows naudotojai gali parsisiųsti oficialų diegimo paketą iš postgresql.org – ten yra grafinė sąsaja, kuri viską padaro automatiškai. Diegimo metu bus paprašyta nustatyti slaptažodį postgres vartotojui – tai yra pagrindinė administracinė paskyra, todėl pasirinkite stiprų slaptažodį ir nepamirškite jo.

Po diegimo, pirmą kartą prisijungti prie duomenų bazės galite naudodami psql – komandinės eilutės įrankį:

sudo -u postgres psql

Čia jūs pateksite į PostgreSQL aplinką, kur galėsite vykdyti SQL komandas. Keletas naudingų psql komandų, kurias verta žinoti iš karto:

• \l – parodo visas duomenų bazes
• \c duomenu_baze – prisijungia prie konkrečios duomenų bazės
• \dt – parodo visas lenteles dabartinėje duomenų bazėje
• \d lentele – parodo lentelės struktūrą
• \q – išeina iš psql

Jei komandinė eilutė jums nepatinka, galite naudoti pgAdmin – grafinę administravimo sąsają, kuri leidžia valdyti duomenų bazes per naršyklę. Tai ypač naudinga pradedantiesiems, nes vizualiai matote duomenų struktūrą.

Duomenų bazių ir lentelių kūrimas

Pradėkime nuo pagrindų. Naują duomenų bazę sukuriate taip:

CREATE DATABASE mano_projektas
    WITH ENCODING 'UTF8'
    LC_COLLATE = 'lt_LT.UTF-8'
    LC_CTYPE = 'lt_LT.UTF-8';

Svarbu nurodyti UTF-8 koduotę, ypač jei dirbate su lietuviška kalba ar kitais ne-ASCII simboliais. Tai yra klaida, kurią daro daugelis pradedančiųjų – sukuria duomenų bazę su numatytaisiais parametrais ir vėliau susiduria su problemomis dėl specialių simbolių.

Lentelių kūrimas yra šiek tiek sudėtingesnis, nes reikia apgalvoti duomenų tipus ir apribojimus. Štai pavyzdys, kaip galėtų atrodyti vartotojų lentelė:

CREATE TABLE vartotojai (
    id SERIAL PRIMARY KEY,
    vardas VARCHAR(100) NOT NULL,
    pavarde VARCHAR(100) NOT NULL,
    el_pastas VARCHAR(255) UNIQUE NOT NULL,
    sukurta_data TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    aktyvus BOOLEAN DEFAULT TRUE,
    amzius INTEGER CHECK (amzius >= 0 AND amzius <= 150)
);

Čia matome keletą svarbių konceptų. SERIAL automatiškai generuoja unikalų skaičių kiekvienam naujam įrašui – tai patogus būdas sukurti pirminio rakto stulpelį. NOT NULL reiškia, kad stulpelis negali būti tuščias. UNIQUE garantuoja, kad du įrašai negali turėti vienodo el. pašto. CHECK leidžia nurodyti sąlygą, kurią turi atitikti įrašomi duomenys.

Modernesniuose PostgreSQL versijose (10+) vietoj SERIAL rekomenduojama naudoti GENERATED ALWAYS AS IDENTITY:

id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Tai yra SQL standartą atitinkantis būdas, kuris veikia šiek tiek patikimiau nei SERIAL sekvencijų atveju.

Užklausos: nuo paprastų iki sudėtingų

Pagrindinės SELECT užklausos PostgreSQL veikia taip pat kaip ir kitose SQL duomenų bazėse, bet PostgreSQL siūlo daug papildomų galimybių, kurias verta žinoti.

Pradėkime nuo paprasto pavyzdžio – duomenų įterpimas ir išrinkimas:

-- Duomenų įterpimas
INSERT INTO vartotojai (vardas, pavarde, el_pastas)
VALUES ('Jonas', 'Jonaitis', '[email protected]'),
       ('Petras', 'Petraitis', '[email protected]');

-- Paprasta užklausa
SELECT vardas, pavarde, el_pastas
FROM vartotojai
WHERE aktyvus = TRUE
ORDER BY pavarde ASC;

Dabar pažiūrėkime į galingesnes funkcijas. Window funkcijos yra vienas iš tų dalykų, dėl kurių PostgreSQL išsiskiria:

SELECT 
    vardas,
    pavarde,
    amzius,
    AVG(amzius) OVER () as vidutinis_amzius,
    RANK() OVER (ORDER BY amzius DESC) as vieta_pagal_amziu
FROM vartotojai
WHERE aktyvus = TRUE;

Ši užklausa vienu metu parodo kiekvieno vartotojo amžių, bendrą vidurkį ir jo vietą pagal amžių – be jokio papildomo subquery. Tai labai efektyvu tiek kodo skaitomumo, tiek ir vykdymo greičio požiūriu.

CTE (Common Table Expressions) leidžia rašyti sudėtingas užklausas daug aiškiau:

WITH aktyvus_vartotojai AS (
    SELECT id, vardas, pavarde
    FROM vartotojai
    WHERE aktyvus = TRUE
),
vartotojai_su_uzsakymais AS (
    SELECT av.vardas, av.pavarde, COUNT(u.id) as uzsakymu_skaicius
    FROM aktyvus_vartotojai av
    LEFT JOIN uzsakymai u ON u.vartotojo_id = av.id
    GROUP BY av.id, av.vardas, av.pavarde
)
SELECT *
FROM vartotojai_su_uzsakymais
WHERE uzsakymu_skaicius > 0
ORDER BY uzsakymu_skaicius DESC;

Rekursinės CTE leidžia dirbti su hierarchiniais duomenimis, pavyzdžiui, organizacijos struktūra ar kategorijų medžiu – tai yra funkcija, kurios MySQL ilgą laiką neturėjo.

Indeksai ir našumas

Viena dažniausių problemų, su kuriomis susiduria kūrėjai, yra lėtos užklausos. Dažniausiai tai sprendžiama teisingai naudojant indeksus. PostgreSQL siūlo kelis indeksų tipus, ir svarbu žinoti, kada naudoti kurį.

B-tree indeksas yra numatytasis ir tinka daugumai atvejų – lyginimui, rikiavimui, LIKE su prefiksu:

CREATE INDEX idx_vartotojai_el_pastas ON vartotojai(el_pastas);
CREATE INDEX idx_vartotojai_pavarde ON vartotojai(pavarde);

Dalinis indeksas (Partial Index) yra labai naudingas, kai dažnai filtruojate pagal konkrečią sąlygą:

CREATE INDEX idx_aktyvus_vartotojai ON vartotojai(el_pastas)
WHERE aktyvus = TRUE;

Jei jūsų sistemoje 90% vartotojų yra aktyvūs, šis indeksas bus daug mažesnis ir greitesnis nei pilnas indeksas.

GIN indeksas tinka JSON duomenims ir pilno teksto paieškai:

-- Pilno teksto paieška
CREATE INDEX idx_aprasymas_fts ON produktai 
USING GIN(to_tsvector('lithuanian', aprasymas));

-- JSON paieška
CREATE INDEX idx_metadata ON vartotojai USING GIN(metadata);

Kaip patikrinti, ar jūsų užklausa naudoja indeksą? Naudokite EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM vartotojai WHERE el_pastas = '[email protected]';

Ši komanda parodo užklausos vykdymo planą ir realų laiką. Ieškokite žodžių "Seq Scan" – tai reiškia, kad PostgreSQL skenuoja visą lentelę, o tai dažniausiai yra problema didelėse lentelėse. "Index Scan" ar "Index Only Scan" rodo, kad indeksas naudojamas.

Svarbus praktinis patarimas: nekurkite indeksų ant visų stulpelių. Kiekvienas indeksas lėtina INSERT, UPDATE ir DELETE operacijas, nes PostgreSQL turi atnaujinti ir indeksą. Kurkite indeksus tik ten, kur tikrai reikia – ant stulpelių, pagal kuriuos dažnai filtruojate ar rikiuojate dideliuose duomenų kiekiuose.

JSON palaikymas ir modernūs duomenų tipai

Vienas iš PostgreSQL privalumų prieš daugelį kitų reliacinių duomenų bazių yra puikus JSON palaikymas. Tai leidžia turėti ir struktūrizuotų reliacinių duomenų privalumus, ir lankstumo, kurį suteikia NoSQL duomenų bazės.

PostgreSQL turi du JSON tipus: json ir jsonb. Skirtumas yra tas, kad jsonb saugo duomenis binariniame formate, kas leidžia greičiau juos apdoroti ir naudoti indeksus. Beveik visada turėtumėte naudoti jsonb.

CREATE TABLE produktai (
    id SERIAL PRIMARY KEY,
    pavadinimas VARCHAR(200) NOT NULL,
    kaina DECIMAL(10,2) NOT NULL,
    savybes JSONB
);

INSERT INTO produktai (pavadinimas, kaina, savybes)
VALUES ('Nešiojamas kompiuteris', 999.99, 
        '{"spalva": "juoda", "svoris": 1.5, "garantija": 2, "kategorijos": ["elektronika", "kompiuteriai"]}');

Dabar galite daryti užklausas pagal JSON laukus:

-- Ieškoti pagal konkrečią savybę
SELECT pavadinimas, kaina
FROM produktai
WHERE savybes->>'spalva' = 'juoda';

-- Ieškoti pagal skaičių
SELECT pavadinimas
FROM produktai
WHERE (savybes->>'svoris')::DECIMAL < 2.0;

-- Ieškoti masyve
SELECT pavadinimas
FROM produktai
WHERE savybes->'kategorijos' ? 'elektronika';

Kitas naudingas duomenų tipas yra ARRAY. Vietoj to, kad kurtumėte atskirą lentelę telefonų numeriams, galite saugoti juos masyve:

CREATE TABLE kontaktai (
    id SERIAL PRIMARY KEY,
    vardas VARCHAR(100),
    telefonai TEXT[]
);

INSERT INTO kontaktai (vardas, telefonai)
VALUES ('Jonas', ARRAY['+37060000001', '+37060000002']);

-- Ieškoti pagal masyvą
SELECT vardas FROM kontaktai
WHERE '+37060000001' = ANY(telefonai);

PostgreSQL taip pat turi UUID tipą, kuris labai naudingas paskirstytose sistemose, kur negalite pasikliauti automatiškai generuojamais skaičiais:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sesijos (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    vartotojo_id INTEGER REFERENCES vartotojai(id),
    sukurta TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Transakcijos, saugumas ir atsarginės kopijos

Transakcijos yra vienas iš svarbiausių konceptų dirbant su duomenų bazėmis. Jos garantuoja, kad grupė operacijų arba visos įvykdoma sėkmingai, arba nė viena – tai labai svarbu finansinėms ar kitoms kritinėms sistemoms.

BEGIN;

UPDATE saskaitos SET balansas = balansas - 100 WHERE id = 1;
UPDATE saskaitos SET balansas = balansas + 100 WHERE id = 2;

-- Jei viskas gerai:
COMMIT;

-- Jei kažkas nepavyko:
-- ROLLBACK;

PostgreSQL taip pat palaiko SAVEPOINT, kuris leidžia atšaukti tik dalį transakcijos:

BEGIN;
INSERT INTO uzsakymai (vartotojo_id, suma) VALUES (1, 50.00);
SAVEPOINT po_uzsakymo;
INSERT INTO mokejimas (uzsakymo_id, suma) VALUES (1, 50.00);
-- Jei mokėjimas nepavyko, grįžtame tik iki savepoint
ROLLBACK TO po_uzsakymo;
COMMIT;

Dėl saugumo – PostgreSQL turi labai gerą vartotojų ir teisių valdymo sistemą. Rekomenduoju kiekvienai programai sukurti atskirą duomenų bazės vartotoją su minimaliai reikalingomis teisėmis:

-- Sukurti naują vartotoją
CREATE USER mano_programa WITH PASSWORD 'stiprus_slaptazodis';

-- Suteikti teises tik konkrečiai duomenų bazei
GRANT CONNECT ON DATABASE mano_projektas TO mano_programa;
GRANT USAGE ON SCHEMA public TO mano_programa;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mano_programa;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO mano_programa;

Atsarginės kopijos yra absoliučiai būtinos. PostgreSQL turi du pagrindinius įrankius:

pg_dump – sukuria vienos duomenų bazės kopiją:

pg_dump -U postgres -h localhost mano_projektas > backup_$(date +%Y%m%d).sql

pg_dumpall – sukuria visų duomenų bazių kopiją kartu su vartotojų informacija:

pg_dumpall -U postgres > visas_backup.sql

Atkurti duomenis galite taip:

psql -U postgres -d mano_projektas < backup_20240101.sql

Praktinis patarimas: automatizuokite atsarginių kopijų kūrimą naudodami cron užduotis ir saugokite kopijas kitoje fizinėje vietoje. Duomenų bazė be atsarginių kopijų yra tik laiko klausimas, kada prarasite duomenis.

Kai PostgreSQL tampa jūsų geriausiu draugu

PostgreSQL nėra paprasčiausias įrankis, bet tai yra įrankis, kuris tikrai neapvils. Jei pradėjote skaityti šį straipsnį nežinodami, nuo ko pradėti, tikėtina, kad dabar turite pakankamai žinių, kad galėtumėte sukurti savo pirmą duomenų bazę, optimizuoti užklausas ir pasirūpinti saugumu.

Svarbiausi dalykai, kuriuos reikia įsiminti: visada naudokite UTF-8 koduotę, kurkite indeksus apgalvotai, naudokite transakcijas ten, kur duomenų vientisumas yra svarbus, ir nepamirškite atsarginių kopijų. Šie keturi principai išgelbės jus nuo daugumos dažniausiai pasitaikančių problemų.

PostgreSQL ekosistema yra labai turtinga – yra TimescaleDB laiko eilučių duomenims, PostGIS geografiniams duomenims, pgvector vektoriniams duomenims dirbtinio intelekto aplikacijoms. Tai reiškia, kad net ir augant jūsų projekto poreikiams, PostgreSQL greičiausiai turės sprendimą.

Jei norite gilintis toliau, rekomenduoju oficialią dokumentaciją postgresql.org – ji yra viena geriausių bet kurio atviro kodo projekto dokumentacijų. Taip pat verta išbandyti EXPLAIN ANALYZE ant savo realių užklausų ir suprasti, kaip PostgreSQL planuoja jų vykdymą – tai suteiks jums intuityvų supratimą apie tai, kaip duomenų bazė mąsto, ir padės rašyti efektyvesnes užklausas ateityje.

Galiausiai – nesibijokite eksperimentuoti. Sukurkite testinę duomenų bazę, įkraukite į ją keletą tūkstančių įrašų naudodami generate_series() funkciją ir bandykite skirtingas užklausas, indeksus, duomenų tipus. PostgreSQL yra pakankamai atleistinas mokymosi aplinkoje, o patirtis, gauta eksperimentuojant, yra daug vertingesnė nei bet koks vadovas.