PostgreSQL

PostgreSQL 18 важлива для:

</syntaxhighlight>

psql

PostgreSQL використовує locks для захисту даних і schema changes.; PostgreSQL надає можливість писати functions і procedures.; Без нього оптимізація часто перетворюється на вгадування.;

Roles можуть:

Цікаві факти про PostgreSQL

PostgreSQL підтримує роботу:

WAL потрібен для:

Проблеми можуть виникати через:

CREATE PUBLICATION app_pub FOR TABLE users, orders;

• іншої PostgreSQL-бази;
• CSV/files;
• зовнішніх SQL-систем;
• data integration;
• migration;
• federation;
• reporting;
• ETL-процесів.; PostgreSQL 18 — сучасна основна реліз PostgreSQL.; VACUUM ANALYZE users;

Небезпека: PostgreSQL має змогу довго пробачати помилки, але потім одна погана міграція, відсутній backup або заповнений диск стають великою аварією.;== pg_hba.conf == PostgreSQL — це одна з найсильніших open source баз даних для сучасних застосунків.; total numeric(12,2) NOT NULL CHECK (total >= 0),

Тематичні мітки

id bigserial PRIMARY KEY,

payload jsonb NOT NULL,

Вони можуть включати:

• columns;
• rows;
• data types;
• constraints;
• indexes;
• primary key;
• foreign keys;
• default values;
• generated columns у відповідних сценаріях;
• permissions.;

BRIN підходить для:

• performance improvements;
• нової I/O-архітектури;
• оптимізації upgrade;
• покращення planner;
• розвитку SQL-функцій;
• production-середовищ;
• cloud deployments;
• extension ecosystem;
• довгострокового планування оновлень.; PgBouncer часто рятує системи, де застосунок створює забагато підключень.; * зменшити кількість backend connections;
• покращити latency;
• захистити PostgreSQL від connection storm;
• краще використовувати resources;
• працювати з web apps;
• масштабувати application servers.; * PostgreSQL License.; CREATE TABLE customers (

GRANT USAGE ON SCHEMA public TO app_user; Важливі метрики:

<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">

PostgreSQL добре підходить для web backend, SaaS, enterprise applications, GIS, аналітики, фінансових систем, internal tools і багатьох cloud-сценаріїв.;== Індекси ==
'''істотно:''' вимкнути autovacuum без дуже вагомої причини — один із найшвидших способів отримати проблеми в PostgreSQL.; Таблиця має:

* перегляду databases;
* виконання SQL;
* адміністрування ролей;
* перегляду схем;
* роботи з таблицями;
* backup/restore у частині сценаріїв;
* візуального аналізу об’єктів;
* навчання.; * PostgreSQL Release Notes.; '''Практична роль:''' BRIN має змогу бути маленьким і ефективним там, де B-tree став би занадто великим.; Вона поєднує SQL, ACID, MVCC, constraints, advanced indexes, JSONB, PostGIS, extensions, replication, backup-інструменти, security-модель і permissive PostgreSQL License.; -- Знайти об'єкти поруч із заданою точкою
<syntaxhighlight lang="sql">
</div>

CREATE EXTENSION IF NOT EXISTS pg_trgm;
'''View''' — збережений SQL-запит, який поводиться як віртуальна таблиця.;=== Internal analytics ===
 id bigint,
Потрібно враховувати:

</div>

WHERE user_id = 42

* open source;
* permissive PostgreSQL License;
* сильна SQL-підтримка;
* ACID;
* MVCC;
* advanced indexes;
* JSONB;
* PostGIS;
* extensions;
* replication;
* partitioning;
* full-text search;
* stored procedures;
* views і materialized views;
* roles і permissions;
* strong data integrity;
* активна спільнота;
* широка cloud-підтримка;
* хороша документація;
* підходить для startup і enterprise.; Помилка в policy має змогу або заблокувати потрібні інформаційні дані, або відкрити зайві.; '''Практична роль:''' managed PostgreSQL зменшує частину адміністративної роботи, але не скасовує потребу в schema design, індексах, backup-політиці й query tuning.;</div>

== Constraints ==
'''B-tree''' — стандартний і найпоширеніший тип індексу в PostgreSQL.; Відновлення потрібно тестувати.;<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
Найцікавіше, що PostgreSQL має змогу поводитися і як класична SQL-база, і як частково document-friendly платформа через JSONB, і як геопросторова база через PostGIS, і як платформа для розширень.; '''Критично:''' high availability не замінює backup.;</div>

{{SEO
|title=PostgreSQL — open source об’єктно-реляційна база даних для застосунків, аналітики, GIS, JSON і enterprise-систем
|description=PostgreSQL — Wiki-стаття про open source object-relational database management system. Розглянуто SQL, ACID, MVCC, транзакції, індекси, JSONB, PostGIS, extensions, roles, schemas, replication, WAL, backups, pg_dump, pg_restore, pg_upgrade, VACUUM, EXPLAIN, performance tuning, high availability, PostgreSQL License, переваги, обмеження, цікаві факти і хороші практики.
|keywords=PostgreSQL, Postgres, PostgreSQL 18, PostgreSQL 18.3, database, open source database, relational database, object-relational database, SQL, ACID, MVCC, JSONB, PostGIS, PostgreSQL extensions, WAL, replication, logical replication, streaming replication, pg_dump, pg_restore, pg_upgrade, VACUUM, EXPLAIN, psql, PostgreSQL License
|alternativeTo=MySQL для складніших SQL і extension-сценаріїв; MariaDB; Microsoft SQL Server у частині open source-середовищ; Oracle Database для частини enterprise-задач; SQLite для server-based multi-user систем; MongoDB у сценаріях, де потрібні SQL, транзакції й relational model; самописні storage-рішення; NoSQL без потреби
}}
psql надає можливість:
SET balance = balance - 100
Приклад:
'''Практична роль:''' SQL у PostgreSQL надає можливість описувати не “як пройти по даних”, а “який результат потрібен”.; Таблиці products, orders, users, payments і inventory працюють із constraints, transactions і indexes.; PostgreSQL добре підходить, якщо потрібно:
'''Практична роль:''' logical replication дає більше гнучкості, ніж проста фізична копія всього кластера.; Вона активно розвивається й регулярно отримує нові функціональні можливості.;<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
Приклад:
pg_dump -Fc -d appdb -f appdb.dump
High availability для PostgreSQL зазвичай будується з кількох компонентів.; '''Проста аналогія:''' WAL — це чорновий журнал, у який PostgreSQL спочатку записує, що має статися, щоб після збою знати, як відновитися.; email text NOT NULL UNIQUE,
</div>

Foreign keys корисні для:

Partitioning корисний для:

Materialized views, aggregates і read replica допомагають робити звіти без надмірного навантаження на primary.; LIMIT 10;

</div>

== PostgreSQL у хмарі ==

* довгі транзакції;
* migrations у peak time;
* неправильний порядок оновлень;
* unindexed foreign keys;
* manual locks;
* DDL у production;
* idle in transaction;
* deadlocks.;</div>
</div>
CREATE TABLE events (
<syntaxhighlight lang="sql">
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">

'''Query planner''' вирішує, як виконати SQL-запит.; status text NOT NULL DEFAULT 'new',
!;== Джерела ==
CREATE INDEX idx_orders_created_at ON orders (created_at);

VACUUM важливий для:
</div>
Приклад:

<div style="background:#f0eaff; border-left:6px solid #8e44ad; padding:12px; margin:12px 0;">

* нові типи даних;
* функції;
* індекси;
* foreign data wrappers;
* GIS;
* text search;
* cryptography;
* statistics;
* monitoring;
* scheduling;
* vector search у відповідних extensions;
* audit;
* replication helpers.; Критерій

* чи застосовується індекс;
* де full scan;
* скільки рядків читається;
* які join algorithms;
* де bottleneck;
* чи правильна statistics;
* чому запит повільний.; email text NOT NULL UNIQUE,
<syntaxhighlight lang="bash">
'''істотно:''' GUI зручний, але для production-адміністрування все одно потрібно розуміти SQL, permissions, backups і logs.; FROM users
Він сприяє:

<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">

'''Цікавий факт:''' завдяки наявності PostGIS PostgreSQL має змогу бути не без ускладнень базою даних, а справжнім GIS-двигуном для картографії й просторової аналітики.;</div>
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
<syntaxhighlight lang="sql">

</div>

FROM articles

PostgreSQL походить від проєкту POSTGRES, який розроблявся в University of California at Berkeley.; * JSONB надає можливість зберігати document-like інформаційні дані, але паралельно з цим залишатися в SQL-світі.;<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
<syntaxhighlight lang="sql">
 payload jsonb

== Приклад базової схеми ==

</syntaxhighlight>

• SQL;
• PL/pgSQL;
• PL/Python у відповідних сценаріях;
• інші procedural languages через extensions.; Приклад ідеї:

Запит:

GIN індекси часто використовують для JSONB, arrays і full-text search.;

Materialized view корисна для:

<syntaxhighlight lang="sql">
</div>
`pg_dump` створює logical backup бази, а `pg_restore` відновлює dump у custom format.; Це означає, що вона не обмежується мінімальним набором relational database-функцій.;</div>

* high availability;
* read scaling;
* disaster recovery;
* migration;
* reporting replicas;
* zero/low downtime upgrades у частині сценаріїв;
* data distribution.; Це ще платформа, яку можна розширювати під складні типи даних і поведінку.; SET balance = balance + 100
'''Практична роль:''' PostgreSQL 18 варто розглядати для нових проєктів і планових оновлень, але production upgrade потрібно тестувати на копії даних.; Через SQL можна:

<syntaxhighlight lang="sql">
Рекомендовано:
</div>
 customer_id bigint NOT NULL REFERENCES customers(id),
'''WAL''' або '''Write-Ahead Log''' — журнал змін PostgreSQL.; MongoDB

'''Проста аналогія:''' транзакція — це як переказ грошей: не можна списати з одного рахунку й “забути” зарахувати на інший.; Приклад ідеї:
<syntaxhighlight lang="text">
'''JSONB''' — binary JSON-тип у PostgreSQL.; * Матеріали щодо relational databases, open source databases, cloud PostgreSQL, high availability, monitoring і database performance tuning.;<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

COMMIT;

== Triggers ==

* потрібна тільки локальна embedded database — SQLite має змогу бути простішою;
* потрібен extreme distributed write scale без складної архітектури;
* workload в цілому document-first і не потребує SQL;
* потрібен спеціалізований search engine на рівні Elasticsearch/OpenSearch;
* потрібна massive columnar analytics platform;
* команда не готова адмініструвати backup, VACUUM, indexes і monitoring;
* застосунок створює тисячі connections без pooling;
* потрібна база “без схеми” через поганий дизайн, а не через реальну потребу.;== Міграції schema ==

== Views і materialized views ==

Приклади:

'''Schema''' у PostgreSQL — це namespace всередині database.; PostGIS застосовується для:

{| class="wikitable"
SELECT *
Приклад:

'''істотно:''' якщо statistics застарілі, planner має змогу вибрати поганий план навіть для правильно написаного запиту.; CREATE INDEX idx_orders_created_at ON orders (created_at);
'''VACUUM''' прибирає старі row versions, які виникають через MVCC.;== Stored procedures і functions ==
Розширення можуть додавати:
== PostGIS ==

LIMIT 20;

Основні етапи:

* roles;
* least privilege;
* SCRAM authentication;
* TLS;
* network restrictions;
* pg_hba.conf;
* row-level security;
* audit logging;
* secret management;
* encryption at rest на рівні платформи;
* backups encryption;
* patching;
* extension review;
* monitoring;
* access logs.; Вона принесла покращення продуктивності, нову I/O-підсистему, поліпшення upgrade-процесу, кращу роботу з індексами й інші зміни.; * MVCC дає PostgreSQL сильну concurrency-модель, але потребує VACUUM.; інформаційні дані, які мають чітку структуру й часто використовуються в JOIN, краще зберігати в нормальних columns.;<div style="background:#e8f8f5; border-left:6px solid #16a085; padding:12px; margin:12px 0;">
);
== ACID ==

Schema migrations змінюють структуру бази.;== EXPLAIN ==

<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
SELECT id, email
ACID означає:

 user_id bigint NOT NULL REFERENCES users(id),

== Обмеження PostgreSQL ==

• групувати таблиці;
• розділяти modules;
• ізолювати об’єкти;
• керувати permissions;
• підтримувати multi-tenant design у частині сценаріїв;
• уникати конфліктів імен.;</syntaxhighlight>

Foreign data wrappers або FDW дозволяють PostgreSQL працювати з зовнішніми джерелами даних як із таблицями.; * використовувати PostgreSQL без ліцензійної плати;

• запускати в комерційних продуктах;
• змінювати код;
• поширювати копії;
• використовувати в proprietary systems;
• створювати комерційні сервіси;
• будувати managed database platforms;
• використовувати PostgreSQL у SaaS.; MySQL

FROM users

</syntaxhighlight>

EXPLAIN показує query plan.;== VACUUM ==

• backend-застосунків;
• web applications;
• SaaS-платформ;
• фінансових систем;
• CRM і ERP;
• аналітичних систем;
• GIS і картографії;
• data warehouses малого й середнього масштабу;
• event logging;
• API-серверів;
• authentication systems;
• e-commerce;
• IoT backends;
• scientific data;
• internal tools;
• enterprise applications;
• cloud databases.; Вона корисна для:

істотно: triggers можуть бути дуже корисними, але прихована логіка в базі іноді ускладнює debugging.;== Logical replication ==

• user-defined types;
• custom functions;
• operators;
• extensions;
• inheritance у частині сценаріїв;
• composite types;
• arrays;
• JSONB;
• range types;
• domains;
• custom index behavior через extensions;
• procedural languages.;

Типові помилки початківців

!;

Приклад:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

завдяки наявності Foreign key зв’язує таблиці й користувачі можуть зберігати referential integrity.; Потрібні backup, план rollback, перевірка extensions і тест застосунку.;

Logical replication передає зміни на рівні таблиць і logical changes.; pg_dump -Fc -d appdb -f appdb.dump

B-tree

Практична роль: PostgreSQL зазвичай попереджає про проблеми метриками задовго до повної аварії.; CREATE INDEX idx_logs_created_brin

WHERE payload @> '{"type": "signup"}';

);

Partitioning

Висновок: MySQL часто простіший для старту в класичних web-сценаріях, а PostgreSQL сильніший у складному SQL, data integrity, extensibility і GIS.; );

Типові варіанти:

EXPLAIN ANALYZE

created_at timestamptz NOT NULL DEFAULT now()

CREATE TABLE products (

PostgreSQL і MongoDB

Головна перевага: extensions — одна з причин, чому PostgreSQL часто називають не без ускладнень базою, а платформою для даних.; Головне правило: PostgreSQL найкраще функціонує, коли база — це не без ускладнень “місце для зберігання”, а добре спроєктована частина архітектури.; * POSTGRES у Berkeley;

• еволюція object-relational ідей;
• поява Postgres95;
• перехід до назви PostgreSQL;
• еволюція SQL-сумісності;
• зростання open source-спільноти;
• поява MVCC, WAL, replication, JSONB, extensions;
• активне використання в enterprise і cloud;
• регулярні major і minor releases;
• сильна ecosystem навколо PostGIS, pgAdmin, psql, Patroni, PgBouncer і managed PostgreSQL-сервісів.; HA захищає від простою, але не від випадкового DELETE, помилки міграції або ransomware.; Погана схема й погані запити можуть зламати навіть дуже хорошу базу.; id bigserial PRIMARY KEY,

PgBouncer сприяє:

</syntaxhighlight>

Цікавий факт: PostgreSQL має змогу не лише зберігати інформаційні дані, а й “дивитися” в інші системи через foreign data wrappers.;

PostgreSQL потребує надійного backup-плану.;

Основна ідея: PostgreSQL — це база даних для випадків, коли потрібні надійність, SQL, транзакції, гнучкість і можливість рости від маленького застосунку до серйозної production-системи.; created_at timestamptz NOT NULL DEFAULT now() Індекси прискорюють пошук, сортування й фільтрацію даних.; Він корисний, коли застосунок відкриває багато database connections.; SELECT id, email pg_restore -d appdb_restore appdb.dump

PostgreSQL License

PostgreSQL і MySQL

Приклад:

!; '''Висновок:''' PostgreSQL часто кращий, коли потрібні SQL, joins і цілісність даних, а MongoDB — коли вся модель справді document-first.; Критерій
 name text NOT NULL,
PostgreSQL підтримує роботу:
 price numeric(12,2) NOT NULL CHECK (price >= 0)

!; Це permissive open source-ліцензія, подібна за духом до BSD або MIT License.; Oracle Database

`pg_upgrade` застосовують, коли потрібно для major version upgrade PostgreSQL.;== Приклад backup-команд ==
);

CREATE ROLE app_user LOGIN PASSWORD 'change_me';

createdb appdb_restore

* SQL-запити;
* індекси;
* schema design;
* statistics;
* VACUUM;
* memory settings;
* connection pooling;
* disk I/O;
* WAL settings;
* partitioning;
* hardware;
* application behavior;
* transactions length;
* locks;
* caching.; * PostgreSQL Documentation.; '''Практична роль:''' EXPLAIN — це рентген для SQL-запиту.; Якщо щось пішло не так, можна зробити:

pg_restore -d appdb_restore appdb.dump

MVCC означає:

Приклад для JSONB:

У PostgreSQL tuning передбачено багато рівнів.;</syntaxhighlight>

Full-text search корисний для:

Приклад:

• connections;
• query latency;
• locks;
• deadlocks;
• replication lag;
• cache hit ratio;
• index usage;
• table bloat;
• autovacuum activity;
• WAL generation;
• disk usage;
• CPU;
• memory;
• I/O;
• slow queries.; |-

| Тип | Object-relational database | Relational database |- | SQL features | Дуже сильний і розширюваний SQL | Широко використовуваний, простий старт |- | JSON | JSONB із потужними індексами | JSON-підтримка є собою, але інша за моделлю |- | Extensions | Дуже сильна extension ecosystem | Менш центральна роль extensions |- | Типові сценарії | Складні запити, data integrity, GIS, enterprise | Web apps, CMS, LAMP-екосистема, прості deployment |}

PostgreSQL і Oracle Database

Проста аналогія: PostgreSQL — це не тільки таблиці й рядки.;

GIN

PostgreSQL і SQLite

EXPLAIN сприяє зрозуміти: Приклад:

JSONB зберігає payload подій, а BRIN або partitioning допомагають працювати з великими time-based таблицями.;

переважні аспекти PostgreSQL

Практична роль: навіть проста схема вже показує сильні сторони PostgreSQL: primary key, foreign key, check constraint, defaults і indexes.;== MVCC == psql — стандартний command-line client для PostgreSQL.; * equality search;

• range queries;
• ORDER BY;
• primary keys;
• unique constraints;
• timestamp filtering;
• numeric ranges;
• text ordering.; * Багато сучасних “database products” фактично будуються навколо PostgreSQL або його extensions.;

</syntaxhighlight> </syntaxhighlight>

Висновок

PostGIS — одне з найвідоміших PostgreSQL-розширень для геопросторових даних.; Приклад: `pg_hba.conf` керує тим, хто й як має змогу підключатися до PostgreSQL.; істотно: найкращий performance tuning часто починається не з конфігурації сервера, а з правильного індексу або переписаного SQL-запиту.;== Загальний характеристика ==

SELECT a + b;

істотно: для великих production-баз одного `pg_dump` має змогу бути недостатньо.; * PostgreSQL Versioning Policy.; PgBouncer — connection pooler для PostgreSQL.; Критично: backup-команди потрібно тестувати на реальних розмірах даних і з реальним restore-процесом, а не лише записати в документацію.; Поширені помилки:

UPDATE accounts

Приклад:

name text,

істотно: JSONB не означає, що схема більше не потрібна.;

'''істотно:''' реплікація — не backup.;

Помилка: вважати, що PostgreSQL автономно вирішить усі проблеми з даними.; * зв’язку users і orders;

• захисту від “сирітських” записів;
• коректної моделі даних;
• каскадних операцій у частині сценаріїв;
• підтримки цілісності.; PostgreSQL називають object-relational database management system.; id bigserial PRIMARY KEY,

Приклад:

• time-series data;
• logs;
• events;
• billing data;
• великих таблиць;
• швидшого видалення старих даних;
• partition pruning;
• maintenance;
• архівування.;

  <div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
  Приклад ідеї:
  
  CREATE INDEX idx_users_email ON users (email);
  
  === Event logging ===
  
  </div>
  
  * входити в систему;
  * володіти об’єктами;
  * мати privileges;
  * бути членами інших roles;
  * мати обмеження;
  * використовуватися для application access;
  * розділяти admin і runtime permissions.; Він надає можливість зберігати напівструктуровані інформаційні дані й виконувати по них запити.; '''Практична роль:''' foreign key — це спосіб сказати базі: “цей order не має змогу існувати без реального user”.; CREATE INDEX idx_orders_customer_id ON orders (customer_id);
  === GIS-сервіс ===
  Приклад:
  
  <syntaxhighlight lang="sql">
  
  * PostgreSQL часто називають Postgres, і це нормальна коротка назва.;<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
  '''Найлюдяніший факт:''' PostgreSQL подобається розробникам не лише з цієї причини, що вона безкоштовна.;</div>
  <div style="background:#fef2f2; border-left:6px solid #ef4444; padding:12px; margin:12px 0;">
  '''BRIN''' індекси корисні для дуже великих таблиць, де інформаційні дані фізично приблизно впорядковані.;<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
  '''pgAdmin''' — популярний графічний інструмент для PostgreSQL.; JSONB корисний для:
  <div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
  '''Практична роль:''' PostgreSQL connections не безкоштовні.; \dt
  |-
  | ліцензійний пакет
  | Open source PostgreSQL License
  | Комерційна enterprise СКБД
  |-
  | Вартість
  | Без ліцензійної плати за community version
  | Комерційне ліцензування
  |-
  | Enterprise features
  | Багато можливостей + extensions/ecosystem
  | Дуже сильний enterprise stack
  |-
  | Міграція
  | Можлива, але потребує аналізу SQL, procedures і типів
  | Legacy enterprise-системи
  |}

GIN корисний для: Поширені підходи:

Див.; додатково

CREATE SCHEMA billing;

• self-managed VM;
• managed PostgreSQL;
• Amazon RDS;
• Amazon Aurora PostgreSQL-Compatible;
• Google Cloud SQL;
• AlloyDB;
• Azure Database for PostgreSQL;
• Kubernetes operators;
• Neon;
• Supabase;
• Crunchy Data;
• EDB;
• Aiven;
• Timescale Cloud.; CREATE TABLE users (

PostgreSQL підтримує роботу різні форми replication.; CREATE INDEX idx_events_payload ON events USING gin (payload);

PostgreSQL License надає можливість:

 id bigserial PRIMARY KEY,
 active boolean NOT NULL DEFAULT true,
|-
| Модель
| Relational + JSONB
| Document database
|-
| SQL
| Основна мова
| Не SQL-first
|-
| Schema
| Чітка схема плюс JSONB
| Гнучкі документи
|-
| Transactions
| Сильна SQL-транзакційність
| Транзакції є собою, але інша модель
|-
| Коли доречно
| Data integrity, joins, relational model, mixed structured data
| Document-first workloads, flexible document model
|}

</div>
== Roles і permissions ==
 created_at date NOT NULL,

 created_at timestamptz NOT NULL DEFAULT now()

Triggers використовують для: істотно: ACID — одна з причин, чому PostgreSQL часто обирають для фінансових, бізнесових і критичних застосунків.; created_at timestamptz NOT NULL DEFAULT now()

{| class="wikitable"

</div>

Це корисно для:

• перейти між major versions;
• зменшити downtime;
• не робити повний dump/restore у великих базах;
• зберегти data files у підтримуваному сценарії;
• прискорити upgrade.; Це зріла платформа для роботи з даними, яка винагороджує правильну архітектуру й уважне адміністрування.; Вона подобається з цієї причини, що часто поводиться чесно: якщо добре спроєктувати інформаційні дані й запити, вона відповідає стабільністю.; * виконувати SQL;
• переглядати таблиці;
• запускати scripts;
• перевіряти connection;
• використовувати meta-commands;
• експортувати результати;
• адмініструвати базу;
• працювати в terminal.; MVCC або Multi-Version Concurrency Control — механізм, завдяки наявності якому PostgreSQL надає можливість багатьом транзакціям працювати одночасно без грубого блокування читання.;== Replication ==

!;

• precomputed reports;
• dashboards;
• складних aggregates;
• read-heavy workloads;
• аналітики.; Критерій

Приклад: Schemas допомагають:

Хороші практики PostgreSQL

Приклад:

Row-Level Security або RLS надає можливість обмежувати доступ до рядків таблиці залежно від policy.;

PostgreSQL має змогу бути не найкращим вибором, якщо: !; Приклад:

created_at timestamptz NOT NULL DEFAULT now()

FROM orders

Основні переважні аспекти PostgreSQL:

• crash recovery;
• replication;
• point-in-time recovery;
• durability;
• backups;
• streaming replication;
• logical decoding;
• data safety.; Для деяких workloads краще спеціалізовані системи.; UPDATE accounts

Приклад:

Query planner

); |- | технічна архітектура | Server-based database | Embedded file-based database |- | Concurrency | Краще для багатьох користувачів і серверних застосунків | Чудова для локальних і невеликих сценаріїв |- | Адміністрування | Потрібен server process | Один файл бази |- | Типові задачі | Web apps, enterprise, SaaS, analytics | Mobile apps, desktop apps, local storage, tests |}

Практична порада: PostgreSQL часто є собою хорошим default choice для backend-бази, якщо немає чіткої причини обирати іншу систему.; WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'database index');

• Atomicity — транзакція виконується в цілому або не виконується;
• Consistency — інформаційні дані переходять між коректними станами;
• Isolation — паралельні транзакції не мають ламати одна одну;
• Durability — після commit інформаційні дані мають зберегтися навіть після збою.; Код можна обійти, а database constraint стоїть ближче до даних.;

• primary;
• standby replicas;
• streaming replication;
• failover manager;
• Patroni;
• etcd або Consul у частині сценаріїв;
• load balancer;
• PgBouncer;
• backup system;
• monitoring;
• alerting;
• runbooks.; * Реплікація — це не backup.; host appdb app_user 10.0.0.0/24 scram-sha-256

Приклади сценаріїв використання

Приклад:

!; pgAdmin корисний для:

WHERE active = true;

• PRIMARY KEY;
• FOREIGN KEY;
• UNIQUE;
• NOT NULL;
• CHECK;
• EXCLUDE constraints;
• default values;
• generated values.; CREATE TABLE events (

WHERE active = true

історичний розвиток PostgreSQL

Транзакції

• selective replication;
• міграцій;
• інтеграції систем;
• zero-downtime upgrade-підходів;
• data pipelines;
• cross-version scenarios;
• event-driven systems;
• CDC.; B-tree добре підходить для:

Практична роль: для невеликого й середнього пошуку PostgreSQL має змогу замінити окремий search engine, але для великих search-платформ можуть знадобитися спеціалізовані рішення для бізнесу.; істотно: індекс прискорює читання, але має змогу уповільнювати записи.; Least privilege у PostgreSQL — не прикраса, а базова безпека.; \x

JSONB

Таблиці — основа relational model у PostgreSQL.;

WAL

Практична роль: partitioning не робить базу магічно швидкою, але має змогу сильно допомогти, якщо інформаційні дані природно діляться за часом або діапазонами.;</syntaxhighlight>

• JSONB containment queries;
• array search;
• full-text search;
• document-like data;
• складних структур.; Цікавий факт: саме GIN + JSONB зробили PostgreSQL дуже привабливою для застосунків, де частина даних має document-like структуру.; PostgreSQL

• relational database;
• SQL;
• транзакції;
• data integrity;
• joins;
• JSONB разом із relational data;
• GIS через PostGIS;
• strong constraints;
• complex queries;
• reporting;
• web backend;
• SaaS;
• enterprise applications;
• open source database;
• extensibility;
• cloud portability;
• mature ecosystem.; * B-tree;
• Hash;
• GIN;
• GiST;
• SP-GiST;
• BRIN;
• expression indexes;
• partial indexes;
• multicolumn indexes;
• unique indexes.; Часто потрібні physical backups і WAL archiving.; ROLLBACK;

Транзакція — це група операцій, які виконуються як єдине ціле.; Пізніше платформа отримала SQL-підтримку й назву PostgreSQL.; PostGIS надає можливість шукати об’єкти поруч, будувати геозони й виконувати spatial queries.; Цікавий факт: MVCC — одна з причин, чому PostgreSQL має змогу одночасно обслуговувати читання й записи, не перетворюючи кожен запит на чергу очікування.; Він враховує:

RLS корисна для:

SQL — основна мова роботи з PostgreSQL.;

'''істотно:''' PostgreSQL універсальна, але не чарівна.; PostgreSQL має обмеження.;== Extensions ==
Вона застосовується; додатково реалізовано бізнес-систем, фінансових сервісів, аналітики, геоданих, SaaS-платформ, API, data engineering, backend-розробки, enterprise-інфраструктури й дослідницьких проєктів.;== Full-text search ==

== Schemas ==
== Коли варто використовувати PostgreSQL ==
</div>
</div>
);
== Цікавий факт ==
<syntaxhighlight lang="sql">
<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
SELECT *

'''Materialized view''' зберігає результат фізично й потребує refresh.; Критерій

<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">
<div style="background:#e8f8f5; border-left:6px solid #16a085; padding:12px; margin:12px 0;">
PostgreSQL має вбудовані функціональні можливості full-text search.;== PgBouncer ==
</div>

CREATE TABLE orders (
</div>
</div>

'''істотно:''' materialized view має змогу прискорити читання, але потрібно планувати, коли й як її оновлювати.;</div>
'''Критично:''' PostgreSQL не можна виставляти у відкритий інтернет без сильного захисту.; Якщо випадково видалити інформаційні дані на primary, видалення має змогу оперативно потрапити й на replica.; PostgreSQL цінують за передбачуваність, якість SQL-реалізації, сильну систему типів, extensibility, transactional integrity і активну ecosystem.;== High availability ==
 id bigserial PRIMARY KEY,
psql -d appdb

CREATE VIEW active_users AS

ON logs USING brin (created_at);

== pg_dump і pg_restore ==

</div>
'''Constraints''' захищають якість даних.; * потребує грамотного адміністрування;
* великі write-heavy workloads потребують tuning;
* MVCC створює bloat без правильного VACUUM;
* horizontal sharding не є собою “однією кнопкою”;
* складні major upgrades потребують плану;
* connection count потрібно контролювати;
* неправильні індекси можуть шкодити;
* великий JSONB без схеми має змогу стати хаосом;
* HA потребує додаткової архітектури;
* managed cloud не скасовує оптимізацію запитів;
* дуже великі analytics workloads можуть потребувати спеціалізованих систем.; '''Перевага:''' PostgreSQL надає можливість почати з простої таблиці користувачів, а потім поступово додавати індекси, JSONB, views, replication, partitioning, full-text search і extensions без зміни базової платформи.; * PostgreSQL має змогу бути маленькою базою для pet project і серйозною production-базою для великого бізнесу.; Безпека PostgreSQL охоплює:

PostgreSQL потрібно моніторити.; '''істотно:''' RLS потужна, але її потрібно ретельно тестувати.; '''Висновок:''' PostgreSQL часто обирають як open source альтернативу для частини Oracle-сценаріїв, але міграція складних enterprise-систем потребує ретельного аналізу.;<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
</div>

<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">

=== SaaS backend ===

'''Перевага:''' PostgreSQL License дуже business-friendly: компанії можуть використовувати PostgreSQL у продуктах без copyleft-вимоги відкривати власний код.; Водночас вона потребує дисципліни: schema design, indexes, backups, VACUUM, monitoring, security, migrations і upgrade planning мають бути продуманими.; Вона схожа на майстерню: спочатку здається великою, але потім виявляється, що майже для кожної складної задачі там уже є собою інструмент.; це потужна open source об’єктно-реляційна платформа керування базами даних виступає ключовою рисою вебзастосунків забезпечується через '''PostgreSQL''' або коротко '''Postgres'''.; * PostgreSQL Wiki.; * indexes;
* table statistics;
* row estimates;
* join order;
* join algorithms;
* filters;
* sort cost;
* parallel execution;
* work_mem;
* data distribution.;<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
== PostgreSQL 18 ==

Replication застосовується для:

== pg_upgrade ==

<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">

== Foreign data wrappers ==

</div>

Висновок: SQLite прекрасна для локальної бази, а PostgreSQL — для server-side систем із багатьма користувачами, транзакціями й складними запитами.; * connection type;

• database;
• user;
• address;
• authentication method.; істотно: constraints краще тримати в базі, а не лише в коді застосунку.; !; * time-series data;
• logs;
• append-only tables;
• telemetry;
• великих таблиць із timestamp;
• cheap indexing;
• data warehouses у частині сценаріїв.;

• event payloads;
• flexible metadata;
• external API responses;
• feature flags;
• audit records;
• document-like fields;
• mixed structured/unstructured data;
• прототипів зі змінною схемою.;

  ORDER BY created_at DESC
  <syntaxhighlight lang="sql">
  
  == Locks і deadlocks ==
  <syntaxhighlight lang="sql">
  
  </div>
  
  '''Головна перевага:''' PostgreSQL дає дуже сильну базу даних без ліцензійного бар’єра й з величезною екосистемою.; PostgreSQL
  
  * logical backup через `pg_dump`;
  * physical backup;
  * base backup;
  * WAL archiving;
  * point-in-time recovery;
  * managed cloud backups;
  * snapshots з database-aware підходом;
  * backup verification;
  * restore drills.; PostgreSQL поширюється під '''PostgreSQL License'''.; amount numeric(12,2) NOT NULL
  </div>
  == SQL ==
  
  PostgreSQL підтримує роботу принципи '''ACID''' для транзакцій.;<div style="background:#fef2f2; border-left:6px solid #ef4444; padding:12px; margin:12px 0;">
  $$;
  
  RETURNS int
  
  '''Критично:''' застосунок не має підключатися до production-бази під superuser.;== pgAdmin ==
  </div>
  
  == Таблиці ==
  '''Критично:''' “idle in transaction” у production має змогу тримати ресурси, заважати VACUUM і створювати дивні проблеми.; * створювати таблиці;
  * вставляти інформаційні дані;
  * читати інформаційні дані;
  * оновлювати записи;
  * видаляти записи;
  * створювати індекси;
  * об’єднувати таблиці;
  * писати аналітичні запити;
  * створювати views;
  * керувати транзакціями;
  * налаштовувати permissions;
  * викликати functions.; Можливі елементи:
  GRANT CONNECT ON DATABASE appdb TO app_user;
  !;

PostgreSQL підтримує роботу різні типи індексів:

<div style="background:#f0eaff; border-left:6px solid #8e44ad; padding:12px; margin:12px 0;">

</div>

LANGUAGE sql

* пошуку по статтях;
* пошуку по товарах;
* документації;
* blog search;
* internal search;
* ranking;
* language dictionaries;
* search vectors.; id bigserial PRIMARY KEY,

* multi-tenant SaaS;
* доступу користувачів лише до своїх даних;
* isolation;
* internal admin tools;
* security-sensitive applications;
* fine-grained permissions.; '''істотно:''' помилка в pg_hba.conf має змогу або заблокувати легальних користувачів, або відкрити доступ зайвим адресам.;=== Інтернет-магазин ===

'''Практична порада:''' великі schema migrations потрібно робити поступово: додати column, backfill, перевірити, перемкнути код, прибрати старе.; База має бути за firewall, private network або контрольованим доступом.;<syntaxhighlight lang="sql">
<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">
CREATE FUNCTION add_numbers(a int, b int)

== BRIN ==

</div>

PostgreSQL використовує '''roles''' для користувачів і груп.;

SELECT *

PostgreSQL доступний у багатьох cloud-сценаріях:

• карт;
• координат;
• геозон;
• distance queries;
• routes;
• spatial indexes;
• location-based services;
• urban planning;
• logistics;
• delivery apps;
• GIS analytics.;== Foreign keys ==

Коли PostgreSQL має змогу бути невдалим вибором

Підказка: якщо застосунок росте, PostgreSQL краще масштабувати поступово: індекси, pooling, query tuning, replicas, partitioning, а вже потім складні distributed-рішення.; PostgreSQL

name text NOT NULL,

;</syntaxhighlight> PostgreSQL поєднує класичну relational database model із розширюваністю: підтримує роботу SQL, транзакції, індекси, constraints, stored procedures, views, triggers, JSONB, extensions, replication, full-text search, foreign data wrappers і багато інших можливостей.; !; * EXPLAIN ANALYZE — один із найважливіших інструментів для оптимізації.; * контролю bloat; звільнення простору для reuse; коректної statistics maintenance; запобігання transaction ID wraparound; стабільної продуктивності; autovacuum.; PostgreSQL часто називають “найпросунутішою open source relational database” не через красивий слоган, а через поєднання речей, які рідко зустрічаються разом: сувора транзакційність, розширюваність, JSONB, PostGIS, складні індекси, SQL-функції, реплікація, foreign data wrappers і зріла спільнота.; PostgreSQL \d users Безпека PostgreSQL CREATE TABLE billing.invoices ( Він визначає: audit logs; автоматичних timestamps; denormalized counters; validation; change tracking; history tables; business rules у частині сценаріїв.;</syntaxhighlight> ); CREATE TABLE; ALTER TABLE; CREATE INDEX; backfill; data migration; constraint validation; column rename; table partitioning; rollback plan.; * PostgreSQL має власну permissive PostgreSQL License, схожу за духом до BSD/MIT.; SQLite </syntaxhighlight> WHERE id = 2; streaming replication; physical replication; logical replication; synchronous replication; asynchronous replication; hot standby; read replicas; cascading replication.; Мови можуть включати: читачі не блокують письменників у багатьох сценаріях; транзакція бачить consistent snapshot; старі версії рядків можуть існувати тимчасово; потрібен VACUUM для прибирання старих версій; isolation функціонує передбачуваніше; concurrency стає ефективнішою.; PostgreSQL застосовується для: FROM events проєктувати schema свідомо; використовувати constraints; створювати індекси під реальні запити; аналізувати EXPLAIN ANALYZE; тримати autovacuum увімкненим; налаштувати backups і restore drills; використовувати least privilege; не підключатися з застосунку під superuser; використовувати connection pooling; моніторити slow queries; тестувати migrations; стежити за replication lag; планувати major upgrades; перевіряти extensions перед upgrade; зберігати runbook для аварій; не плутати JSONB із заміною всієї relational model.; WHERE id = 1; офіційно затверджений сайт PostgreSQL.; {| class="wikitable" Критично: major upgrade PostgreSQL потрібно репетирувати на копії production.; Partitioning надає можливість розбивати велику таблицю на частини.;== Object-relational database == Monitoring істотно: PostgreSQL має довгу історію, але це не “стара база з минулого”.; * Документація щодо SQL, MVCC, WAL, replication, JSONB, indexes, extensions, PostGIS, backup, pg_dump, pg_restore, pg_upgrade, VACUUM, EXPLAIN і security.; * PostgreSQL походить від Berkeley POSTGRES.;</syntaxhighlight> ORDER BY created_at DESC Практична роль: якщо не знаєте, який індекс потрібен, найчастіше першим кандидатом буде B-tree.; Практична роль: psql — це інструмент, який варто знати навіть тим, хто зазвичай користується GUI.; Індекси потрібно створювати за реальними запитами, а не “про всяк випадок”.; AS $$

; Головна думка: PostgreSQL — це не без ускладнень “місце, куди складати інформаційні дані”.; PostgreSQL зберігає користувачів, підписки, платежі, permissions, audit logs і application data.;</syntaxhighlight> id bigserial PRIMARY KEY, Можливі проблеми: Performance tuning не створити індекс для частого WHERE; створити забагато індексів; підключатися до бази під superuser; не налаштувати backups; не тестувати restore; не читати EXPLAIN; тримати довгі транзакції; ігнорувати VACUUM; зберігати все в JSONB без схеми; робити schema migrations без плану; відкривати PostgreSQL у public internet; не використовувати connection pooling; плутати replica з backup; не моніторити disk usage; оновлювати major version без репетиції.; Практична роль: schemas — це як папки для database objects, але з власними правилами доступу й пошуку.; ) PARTITION BY RANGE (created_at); Критично: backup без перевіреного restore — це не backup, а припущення.; BEGIN; Приклад задачі: Trigger автономно виконує функцію при зміні даних.; Приклад: Row-Level Security перенесення баз; logical backups; вибіркового відновлення; міграцій; dev/test копій; архівування структури й даних.; PostgreSQL має потужну систему extensions.; CREATE TABLE orders ( Backups Найлюдяніший факт: PostgreSQL — це база даних, яка не намагається бути “простенькою”.; * PostGIS перетворює PostgreSQL на сильну GIS-платформу.; FDW можуть використовуватися для: Практична роль: правильно спроєктована таблиця часто важливіша за десятки оптимізацій пізніше.; Практична роль: database functions корисні, коли логіка має бути близько до даних, але надмірна бізнес-логіка в базі має змогу ускладнити підтримку.;<syntaxhighlight lang="sql"> SQL База даних Relational database Object-relational database PostgreSQL License MySQL MariaDB SQLite MongoDB Oracle Database Microsoft SQL Server PostGIS JSONB ACID MVCC WAL Replication Logical replication pg_dump pg_restore pg_upgrade VACUUM EXPLAIN PgBouncer pgAdmin Backup Логування Безпека застосунків Приватність даних PostgreSQL Postgres PostgreSQL 18 PostgreSQL 18.3 Open source database Relational database Object-relational database SQL ACID MVCC JSONB PostGIS PostgreSQL extensions WAL Replication Logical replication pg_dump pg_restore pg_upgrade VACUUM EXPLAIN PostgreSQL License Документація