Перейти до вмісту

PostgreSQL

Матеріал з K2 ERP Wiki

PostgreSQL 18

</div>

</div>
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
== Індекси ==

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

SELECT *

CREATE INDEX idx_logs_created_brin

Небезпека: PostgreSQL здатна довго пробачати помилки, але потім одна погана міграція, відсутній backup або заповнений диск стають великою аварією. FDW можуть використовуватися для: WHERE payload @> '{"type": "signup"}'; UPDATE accounts

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

Цікавий факт: PostgreSQL здатна не лише зберігати інформаційні дані, а й “дивитися” в інші системи через foreign data wrappers. Погана схема й погані запити можуть зламати навіть дуже хорошу базу. * Реплікація — це не backup. Потрібні backup, план rollback, перевірка extensions і тест застосунку.

PostgreSQL цінують за передбачуваність, якість SQL-реалізації, сильну систему типів, extensibility, transactional integrity і активну ecosystem. * JSONB дає можливість зберігати document-like інформаційні дані, але при цьому залишатися в SQL-світі.

Приклад:

Internal analytics

BRIN індекси корисні для дуже великих таблиць, де інформаційні дані фізично приблизно впорядковані.== Full-text search ==

</syntaxhighlight>

pg_upgrade

pg_dump -Fc -d appdb -f appdb.dump

BRIN

Приклад:

Критично: backup без перевіреного restore — це не backup, а припущення. * precomputed reports;

  • dashboards;
  • складних aggregates;
  • read-heavy workloads;
  • аналітики. Пізніше платформа отримала SQL-підтримку й назву PostgreSQL. Це означає, що вона не обмежується мінімальним набором relational database-функцій. ! PostgreSQL поширюється під PostgreSQL License. B-tree — стандартний і найпоширеніший тип індексу в PostgreSQL. База має бути за firewall, private network або контрольованим доступом.== Stored procedures і functions ==
критично: constraints краще тримати в базі, а не лише в коді застосунку. Цікавий факт: саме GIN + JSONB зробили PostgreSQL дуже привабливою для застосунків, де частина даних має document-like структуру.

RLS корисна для: </syntaxhighlight>

);

JSONB

Приклад:

SELECT *

  • зв’язку users і orders;
  • захисту від “сирітських” записів;
  • коректної моделі даних;
  • каскадних операцій у частині сценаріїв;
  • підтримки цілісності.
  • SQL-запити;
  • індекси;
  • schema design;
  • statistics;
  • VACUUM;
  • memory settings;
  • connection pooling;
  • disk I/O;
  • WAL settings;
  • partitioning;
  • hardware;
  • application behavior;
  • transactions length;
  • locks;
  • caching. PostgreSQL — це одна з найсильніших open source баз даних для сучасних застосунків. MongoDB

Критично: high availability не замінює backup. інформаційні дані, які мають чітку структуру й часто використовуються в JOIN, краще зберігати в нормальних columns.</syntaxhighlight>

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

);

MVCC означає:

VACUUM ANALYZE users;

FROM articles Потрібно враховувати: Він визначає: 

name text NOT NULL,
  • JSONB containment queries;
  • array search;
  • full-text search;
  • document-like data;
  • складних структур. SELECT id, email
  • audit logs;
  • автоматичних timestamps;
  • denormalized counters;
  • validation;
  • change tracking;
  • history tables;
  • business rules у частині сценаріїв. * 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. Приклад:

Загальний SEO-опис

id bigserial PRIMARY KEY,

</syntaxhighlight> SET balance = balance + 100 

id bigserial PRIMARY KEY,

Trigger автоматизовано виконує функцію при зміні даних. FROM users

SQL

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

amount numeric(12,2) NOT NULL

Критично: “idle in transaction” у production здатна тримати ресурси, заважати VACUUM і створювати дивні проблеми.

) PARTITION BY RANGE (created_at);

Транзакції

id bigserial PRIMARY KEY,

</syntaxhighlight>

Найлюдяніший факт: PostgreSQL подобається розробникам не лише тому, що вона безкоштовна.

Schemas

<syntaxhighlight lang="sql">

PostgreSQL здатна бути не найкращим вибором, якщо:
<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

<syntaxhighlight lang="sql">

<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
'''Практична роль:''' навіть проста схема вже показує сильні сторони PostgreSQL: primary key, foreign key, check constraint, defaults і indexes. email text NOT NULL UNIQUE,
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
== Locks і deadlocks ==
<syntaxhighlight lang="sql">
</div>

</div>

== Хороші практики PostgreSQL ==
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
Приклад:
'''Практична роль:''' PostgreSQL 18 варто розглядати для нових проєктів і планових оновлень, але production upgrade потрібно тестувати на копії даних. * PostgreSQL License.</div>

* 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. name text NOT NULL,

</div>
</div>
'''Висновок:''' PostgreSQL часто кращий, коли потрібні SQL, joins і цілісність даних, а MongoDB — коли вся модель справді document-first. );

* '''Atomicity''' — транзакція виконується на 100% або не виконується;
* '''Consistency''' — інформаційні дані переходять між коректними станами;
* '''Isolation''' — паралельні транзакції не мають ламати одна одну;
* '''Durability''' — після commit інформаційні дані мають зберегтися навіть після збою.<syntaxhighlight lang="sql">

High availability для PostgreSQL зазвичай будується з кількох компонентів.

Replication застосовується для для:
'''Практична роль:''' SQL у PostgreSQL дає можливість описувати не “як пройти по даних”, а “який результат потрібен”. * іншої PostgreSQL-бази;
* CSV/files;
* зовнішніх SQL-систем;
* data integration;
* migration;
* federation;
* reporting;
* ETL-процесів.<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
== PgBouncer ==
== PostgreSQL і MongoDB ==
LIMIT 20;
<div style="background:#f0eaff; border-left:6px solid #8e44ad; padding:12px; margin:12px 0;">
<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

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

 payload jsonb NOT NULL,

'''Практична роль:''' PostgreSQL зазвичай попереджає про проблеми метриками задовго до повної аварії. ! '''Практична роль:''' partitioning не робить базу магічно швидкою, але здатна сильно допомогти, якщо інформаційні дані природно діляться за часом або діапазонами. pg_dump -Fc -d appdb -f appdb.dump

== Backups ==

* карт;
* координат;
* геозон;
* distance queries;
* routes;
* spatial indexes;
* location-based services;
* urban planning;
* logistics;
* delivery apps;
* GIS analytics. Помилка в policy здатна або заблокувати потрібні інформаційні дані, або відкрити зайві. * 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.</div>
PostgreSQL називають '''object-relational database management system'''. * 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-сервісів.<div style="background:#f0eaff; border-left:6px solid #8e44ad; padding:12px; margin:12px 0;">
</div>

'''Query planner''' вирішує, як виконати SQL-запит. '''Практична роль:''' database functions корисні, коли логіка має бути близько до даних, але надмірна бізнес-логіка в базі здатна ускладнити підтримку.== Типові помилки початківців ==

pg_restore -d appdb_restore appdb.dump

Вона застосовується для; так само реалізовано бізнес-систем, фінансових сервісів, аналітики, геоданих, SaaS-платформ, API, data engineering, backend-розробки, enterprise-інфраструктури й дослідницьких проєктів. ! Через SQL можна:

High availability

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

PostgreSQL часто називають “найпросунутішою open source relational database” не через красивий слоган, а через поєднання речей, які рідко зустрічаються разом: сувора транзакційність, розширюваність, JSONB, PostGIS, складні індекси, SQL-функції, реплікація, foreign data wrappers і зріла спільнота.
  • SQL;
  • PL/pgSQL;
  • PL/Python у відповідних сценаріях;
  • інші procedural languages через extensions. PgBouncer часто рятує системи, де застосунок створює забагато підключень. Якщо щось пішло не так, можна зробити:

MVCC

У PostgreSQL tuning передбачено багато рівнів. * створювати таблиці;

  • вставляти інформаційні дані;
  • читати інформаційні дані;
  • оновлювати записи;
  • видаляти записи;
  • створювати індекси;
  • об’єднувати таблиці;
  • писати аналітичні запити;
  • створювати views;
  • керувати транзакціями;
  • налаштовувати permissions;
  • викликати functions.== EXPLAIN ==

Constraints

Приклад:

  • проєктувати 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.</syntaxhighlight>

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

psql дає можливість:

COMMIT;

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

GRANT CONNECT ON DATABASE appdb TO app_user;

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

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

=== Інтернет-магазин ===
PostgreSQL поєднує класичну relational database model із розширюваністю: підтримує SQL, транзакції, індекси, constraints, stored procedures, views, triggers, JSONB, extensions, replication, full-text search, foreign data wrappers і багато інших можливостей. Materialized view корисна для:
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
pgAdmin корисний для:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user; CREATE TABLE orders (

B-tree добре підходить для: </syntaxhighlight> 

<syntaxhighlight lang="sql">

'''Головна думка:''' PostgreSQL — це не без зусиль “місце, куди складати інформаційні дані”. MySQL
<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">
</div>

AS $$
</div>
 id bigserial PRIMARY KEY,

</div>

* офіційний сайт PostgreSQL. * Документація щодо SQL, MVCC, WAL, replication, JSONB, indexes, extensions, PostGIS, backup, pg_dump, pg_restore, pg_upgrade, VACUUM, EXPLAIN і security. customer_id bigint NOT NULL REFERENCES customers(id),
<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

== pgAdmin ==

'''Constraints''' захищають якість даних.== WAL ==

'''Цікавий факт:''' через PostGIS PostgreSQL здатна бути не без зусиль базою даних, а справжнім GIS-двигуном для картографії й просторової аналітики. * CREATE TABLE;
* ALTER TABLE;
* CREATE INDEX;
* backfill;
* data migration;
* constraint validation;
* column rename;
* table partitioning;
* rollback plan. Roles можуть:
== Logical replication ==
'''критично:''' RLS потужна, але її потрібно ретельно тестувати. WHERE id = 2;
{| class="wikitable"

!== Цікавий факт ==

* читачі не блокують письменників у багатьох сценаріях;
* транзакція бачить consistent snapshot;
* старі версії рядків можуть існувати тимчасово;
* потрібен VACUUM для прибирання старих версій;
* isolation функціонує передбачуваніше;
* concurrency стає ефективнішою. Він дає можливість зберігати напівструктуровані інформаційні дані й виконувати по них запити.<syntaxhighlight lang="sql">

! '''критично:''' JSONB не означає, що схема більше не потрібна.== Query planner ==

* PostgreSQL часто називають Postgres, і це нормальна коротка назва.<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">

== Див. так само ==

Поширені помилки:

== конкурентні переваги PostgreSQL ==

<syntaxhighlight lang="sql">

Він враховує:

!== Міграції schema ==

<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
</div>
EXPLAIN оптимізує зрозуміти:

<div style="background:#e8f8f5; border-left:6px solid #16a085; padding:12px; margin:12px 0;">
через '''Foreign key''' зв’язує таблиці й користувачі можуть зберігати referential integrity. * time-series data;
* logs;
* events;
* billing data;
* великих таблиць;
* швидшого видалення старих даних;
* partition pruning;
* maintenance;
* архівування. '''Перевага:''' PostgreSQL License дуже business-friendly: компанії можуть використовувати PostgreSQL у продуктах без copyleft-вимоги відкривати власний код. PostgreSQL
psql -d appdb
'''Критично:''' major upgrade PostgreSQL потрібно репетирувати на копії production.</div>

Приклад ідеї:

* довгі транзакції;
* migrations у peak time;
* неправильний порядок оновлень;
* unindexed foreign keys;
* manual locks;
* DDL у production;
* idle in transaction;
* deadlocks. * Матеріали щодо relational databases, open source databases, cloud PostgreSQL, high availability, monitoring і database performance tuning. '''Практична роль:''' EXPLAIN — це рентген для SQL-запиту. * PostgreSQL Wiki. );
'''Помилка:''' вважати, що PostgreSQL автоматизовано вирішить усі проблеми з даними.<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
Мови можуть включати:
pg_restore -d appdb_restore appdb.dump

'''MVCC''' або '''Multi-Version Concurrency Control''' — механізм, через якому PostgreSQL дає можливість багатьом транзакціям працювати одночасно без грубого блокування читання. Відновлення потрібно тестувати. EXPLAIN ANALYZE
<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
<div style="background:#f0eaff; border-left:6px solid #8e44ad; padding:12px; margin:12px 0;">

'''Головне правило:''' PostgreSQL найкраще функціонує, коли база — це не без зусиль “місце для зберігання”, а добре спроєктована частина архітектури.</div>
\x
'''Практична роль:''' BRIN здатна бути маленьким і ефективним там, де B-tree став би занадто великим.</div>
'''критично:''' materialized view здатна прискорити читання, але потрібно планувати, коли й як її оновлювати. PostgreSQL

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

== pg_dump і pg_restore ==

createdb appdb_restore

* пошуку по статтях;
* пошуку по товарах;
* документації;
* blog search;
* internal search;
* ranking;
* language dictionaries;
* search vectors. HA захищає від простою, але не від випадкового DELETE, помилки міграції або ransomware.</div>

* selective replication;
* міграцій;
* інтеграції систем;
* zero-downtime upgrade-підходів;
* data pipelines;
* cross-version scenarios;
* event-driven systems;
* CDC.</div>
== PostgreSQL License ==
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">

== Безпека PostgreSQL ==

 created_at timestamptz NOT NULL DEFAULT now()

== Приклад backup-команд ==

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

== Performance tuning ==

критично: ACID — одна з причин, чому PostgreSQL часто обирають для фінансових, бізнесових і критичних застосунків.

</div>

 active boolean NOT NULL DEFAULT true,

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

* connection type;
* database;
* user;
* address;
* authentication method. `pg_upgrade` застосовують, коли потрібно для major version upgrade PostgreSQL. Приклад:
WHERE user_id = 42
 created_at timestamptz NOT NULL DEFAULT now()

FROM events

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

'''критично:''' індекс прискорює читання, але здатна уповільнювати записи. ORDER BY created_at DESC

* equality search;
* range queries;
* ORDER BY;
* primary keys;
* unique constraints;
* timestamp filtering;
* numeric ranges;
* text ordering. '''критично:''' triggers можуть бути дуже корисними, але прихована логіка в базі іноді ускладнює debugging. |-
| технічна архітектура
| Server-based database
| Embedded file-based database
|-
| Concurrency
| Краще для багатьох користувачів і серверних застосунків
| Чудова для локальних і невеликих сценаріїв
|-
| Адміністрування
| Потрібен server process
| Один файл бази
|-
| Типові задачі
| Web apps, enterprise, SaaS, analytics
| Mobile apps, desktop apps, local storage, tests
|}

'''Практична роль:''' якщо не знаєте, який індекс потрібен, найчастіше першим кандидатом буде B-tree. '''Row-Level Security''' або '''RLS''' дає можливість обмежувати доступ до рядків таблиці залежно від policy. * PostgreSQL Release Notes.<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

PostgreSQL License дає можливість:
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
'''Практична роль:''' logical replication дає більше гнучкості, ніж проста фізична копія всього кластера. \d users
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">

'''View''' — збережений SQL-запит, який поводиться як віртуальна таблиця.== Views і materialized views ==

PostgreSQL потребує надійного backup-плану. '''Висновок:''' SQLite прекрасна для локальної бази, а PostgreSQL — для server-side систем із багатьма користувачами, транзакціями й складними запитами.</div>

Важливі метрики:

PostgreSQL добре підходить, якщо потрібно:

* перегляду databases;
* виконання SQL;
* адміністрування ролей;
* перегляду схем;
* роботи з таблицями;
* backup/restore у частині сценаріїв;
* візуального аналізу об’єктів;
* навчання. Критерій

 payload jsonb
VACUUM важливий для:
== Джерела ==
</div>
</div>

ROLLBACK;
<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
== Приклад базової схеми ==

'''VACUUM''' прибирає старі row versions, які виникають через MVCC.<syntaxhighlight lang="sql">

== B-tree ==

LANGUAGE sql

* входити в систему;
* володіти об’єктами;
* мати privileges;
* бути членами інших roles;
* мати обмеження;
* використовуватися для application access;
* розділяти admin і runtime permissions. '''PgBouncer''' — connection pooler для PostgreSQL. '''WAL''' або '''Write-Ahead Log''' — журнал змін PostgreSQL. Приклад:

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

* crash recovery;
* replication;
* point-in-time recovery;
* durability;
* backups;
* streaming replication;
* logical decoding;
* data safety.<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">

PostgreSQL добре підходить для web backend, SaaS, enterprise applications, GIS, аналітики, фінансових систем, internal tools і багатьох cloud-сценаріїв. це потужна open source об’єктно-реляційна платформа керування базами даних виступає ключовою рисою вебзастосунків забезпечується через '''PostgreSQL''' або коротко '''Postgres'''. Приклад ідеї:
<syntaxhighlight lang="text">

PostgreSQL має потужну систему '''extensions'''. name text,

<div style="background:#f0eaff; border-left:6px solid #8e44ad; padding:12px; margin:12px 0;">
Приклад для JSONB:
</div>

PostgreSQL потрібно моніторити.</div>
|-
| ліцензійний пакет
| Open source PostgreSQL License
| Комерційна enterprise СКБД
|-
| Вартість
| Без ліцензійної плати за community version
| Комерційне ліцензування
|-
| Enterprise features
| Багато можливостей + extensions/ecosystem
| Дуже сильний enterprise stack
|-
| Міграція
| Можлива, але потребує аналізу SQL, procedures і типів
| Legacy enterprise-системи
|}

`pg_hba.conf` керує тим, хто й як здатна підключатися до PostgreSQL. '''psql''' — стандартний command-line client для PostgreSQL. price numeric(12,2) NOT NULL CHECK (price >= 0)
'''Практична порада:''' PostgreSQL часто виступає як хорошим default choice для backend-бази, якщо немає чіткої причини обирати іншу систему.<div style="background:#fdecea; border-left:6px solid #e74c3c; padding:12px; margin:12px 0;">
'''PostgreSQL 18''' — сучасна основна реліз системи PostgreSQL. * перенесення баз;
* logical backups;
* вибіркового відновлення;
* міграцій;
* dev/test копій;
* архівування структури й даних. '''Критично:''' backup-команди потрібно тестувати на реальних розмірах даних і з реальним restore-процесом, а не лише записати в документацію. Це permissive open source-ліцензія, подібна за духом до BSD або MIT License. '''Практична роль:''' managed PostgreSQL зменшує частину адміністративної роботи, але не скасовує потребу в schema design, індексах, backup-політиці й query tuning.</div>
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">
</div>

<syntaxhighlight lang="sql">
'''Практична роль:''' PostgreSQL connections не безкоштовні. Вона поєднує SQL, ACID, MVCC, constraints, advanced indexes, JSONB, PostGIS, extensions, replication, backup-інструменти, security-модель і permissive PostgreSQL License. Це зріла платформа для роботи з даними, яка винагороджує правильну архітектуру й уважне адміністрування. Partitioning корисний для:
<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">
 created_at timestamptz NOT NULL DEFAULT now()

WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'database index');

</div>

{| class="wikitable"

* performance improvements;
* нової I/O-архітектури;
* оптимізації upgrade;
* покращення planner;
* розвитку SQL-функцій;
* production-середовищ;
* cloud deployments;
* extension ecosystem;
* довгострокового планування оновлень.
  • 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. Безпека PostgreSQL містить:

критично: вимкнути autovacuum без дуже вагомої причини — один із найшвидших способів отримати проблеми в PostgreSQL.

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

Приклад: критично: для великих production-баз одного `pg_dump` здатна бути недостатньо. * групувати таблиці;

  • розділяти modules;
  • ізолювати об’єкти;
  • керувати permissions;
  • підтримувати multi-tenant design у частині сценаріїв;
  • уникати конфліктів імен. PostgreSQL використовує roles для користувачів і груп.== Таблиці ==
SELECT a + b;

|- | Модель | 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 |}

CREATE TABLE events ( !== Triggers == Цікавий факт: MVCC — одна з причин, чому PostgreSQL здатна одночасно обслуговувати читання й записи, не перетворюючи кожен запит на чергу очікування. PostgreSQL

Foreign data wrappers

PostGIS — одне з найвідоміших PostgreSQL-розширень для геопросторових даних. SQLite 

created_at timestamptz NOT NULL DEFAULT now()

історія продукту PostgreSQL

Materialized views, aggregates і read replica допомагають робити звіти без надмірного навантаження на primary. JSONB — binary JSON-тип у PostgreSQL. Підказка: якщо застосунок росте, PostgreSQL краще масштабувати поступово: індекси, pooling, query tuning, replicas, partitioning, а вже потім складні distributed-рішення. критично: реплікація — не backup. Код можна обійти, а database constraint стоїть ближче до даних. * PostgreSQL має власну permissive PostgreSQL License, схожу за духом до BSD/MIT. WAL потрібен для: критично: GUI зручний, але для production-адміністрування все одно потрібно розуміти SQL, permissions, backups і logs. Висновок: MySQL часто простіший для старту в класичних web-сценаріях, а PostgreSQL сильніший у складному SQL, data integrity, extensibility і GIS.</syntaxhighlight> Проста аналогія: транзакція — це як переказ грошей: не можна списати з одного рахунку й “забути” зарахувати на інший. Типові варіанти: CREATE TABLE billing.invoices (

CREATE TABLE customers (

WHERE active = true;

  • primary;
  • standby replicas;
  • streaming replication;
  • failover manager;
  • Patroni;
  • etcd або Consul у частині сценаріїв;
  • load balancer;
  • PgBouncer;
  • backup system;
  • monitoring;
  • alerting;
  • runbooks.
    <syntaxhighlight lang="sql">

PostgreSQL дає можливість писати functions і procedures.<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">

`pg_dump` створює logical backup бази, а `pg_restore` відновлює dump у custom format.<syntaxhighlight lang="sql">
<div style="background:#e7f3ff; border-left:6px solid #2b7cff; padding:12px; margin:12px 0;">

FROM users
  • не створити індекс для частого WHERE;
  • створити забагато індексів;
  • підключатися до бази під superuser;
  • не налаштувати backups;
  • не тестувати restore;
  • не читати EXPLAIN;
  • тримати довгі транзакції;
  • ігнорувати VACUUM;
  • зберігати все в JSONB без схеми;
  • робити schema migrations без плану;
  • відкривати PostgreSQL у public internet;
  • не використовувати connection pooling;
  • плутати replica з backup;
  • не моніторити disk usage;
  • оновлювати major version без репетиції. Materialized view зберігає результат фізично й потребує refresh.
<syntaxhighlight lang="sql">

GIN

pg_hba.conf

  • контролю bloat;
  • звільнення простору для reuse;
  • коректної statistics maintenance;
  • запобігання transaction ID wraparound;
  • стабільної продуктивності;
  • autovacuum. Основні етапи:
CREATE TABLE users (

'''EXPLAIN''' показує query plan. UPDATE accounts

 id bigserial PRIMARY KEY,

</div>

Приклад:

* перейти між major versions;
* зменшити downtime;
* не робити повний dump/restore у великих базах;
* зберегти data files у підтримуваному сценарії;
* прискорити upgrade.== PostgreSQL у хмарі ==
'''Практична роль:''' foreign key — це спосіб сказати базі: “цей order не здатна існувати без реального user”. '''Основна ідея:''' PostgreSQL — це база даних для випадків, коли потрібні надійність, SQL, транзакції, гнучкість і можливість рости від маленького застосунку до серйозної production-системи. id bigserial PRIMARY KEY,

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

'''критично:''' якщо statistics застарілі, planner здатна вибрати поганий план навіть для правильно написаного запиту. '''Проста аналогія:''' WAL — це чорновий журнал, у який PostgreSQL спочатку записує, що має статися, щоб після збою знати, як відновитися. Приклад:

created_at timestamptz NOT NULL DEFAULT now()

Приклад:

CREATE INDEX idx_users_email ON users (email);

  • нові типи даних;
  • функції;
  • індекси;
  • foreign data wrappers;
  • GIS;
  • text search;
  • cryptography;
  • statistics;
  • monitoring;
  • scheduling;
  • vector search у відповідних extensions;
  • audit;
  • replication helpers. Практична роль: schemas — це як папки для database objects, але з власними правилами доступу й пошуку. Водночас вона потребує дисципліни: schema design, indexes, backups, VACUUM, monitoring, security, migrations і upgrade planning мають бути продуманими. * EXPLAIN ANALYZE — один із найважливіших інструментів для оптимізації. Критерій
  • multi-tenant SaaS;
  • доступу користувачів лише до своїх даних;
  • isolation;
  • internal admin tools;
  • security-sensitive applications;
  • fine-grained permissions.

CREATE INDEX idx_events_payload ON events USING gin (payload);

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

CREATE TABLE products (

критично: PostgreSQL універсальна, але не чарівна.</syntaxhighlight>

ACID

CREATE ROLE app_user LOGIN PASSWORD 'change_me';

ORDER BY created_at DESC

PostgreSQL підтримує:

ACID означає: Foreign data wrappers або FDW дозволяють PostgreSQL працювати з зовнішніми джерелами даних як із таблицями. JSONB корисний для: Приклад:

PostgreSQL походить від проєкту POSTGRES, який розроблявся в University of California at Berkeley. * Багато сучасних “database products” фактично будуються навколо PostgreSQL або його extensions. PostgreSQL має вбудовані функції ERP full-text search. Якщо випадково видалити інформаційні дані на primary, видалення здатна оперативно потрапити й на replica. * PostgreSQL здатна бути маленькою базою для pet project і серйозною production-базою для великого бізнесу. * PostgreSQL Versioning Policy. PostgreSQL використовує locks для захисту даних і schema changes.== VACUUM ==

  • indexes;
  • table statistics;
  • row estimates;
  • join order;
  • join algorithms;
  • filters;
  • sort cost;
  • parallel execution;
  • work_mem;
  • data distribution. PostGIS дає можливість шукати об’єкти поруч, будувати геозони й виконувати spatial queries. RETURNS int

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

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

Triggers використовують для:

Monitoring

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

PostGIS

</syntaxhighlight> CREATE SCHEMA billing; CREATE INDEX idx_orders_created_at ON orders (created_at); 

email text NOT NULL UNIQUE,

PostgreSQL і Oracle Database

Schema migrations змінюють структуру бази.== Висновок ==

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

psql

Практична роль: правильно спроєктована таблиця часто важливіша за десятки оптимізацій пізніше. Приклад:

PostgreSQL застосовується для для: </syntaxhighlight> GRANT USAGE ON SCHEMA public TO app_user;

SaaS backend

Обмеження PostgreSQL

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

  • чи застосовується для індекс;
  • де full scan;
  • скільки рядків читається;
  • які join algorithms;
  • де bottleneck;
  • чи правильна statistics;
  • чому запит повільний. Критерій

Практична порада: великі schema migrations потрібно робити поступово: додати column, backfill, перевірити, перемкнути код, прибрати старе.== Коли варто використовувати PostgreSQL ==

Object-relational database

критично: помилка в pg_hba.conf здатна або заблокувати легальних користувачів, або відкрити доступ зайвим адресам. * user-defined types;

  • custom functions;
  • operators;
  • extensions;
  • inheritance у частині сценаріїв;
  • composite types;
  • arrays;
  • JSONB;
  • range types;
  • domains;
  • custom index behavior через extensions;
  • procedural languages. total numeric(12,2) NOT NULL CHECK (total >= 0),

критично: PostgreSQL має довгу історію, але це не “стара база з минулого”. * 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.

Приклад:

  • time-series data;
  • logs;
  • append-only tables;
  • telemetry;
  • великих таблиць із timestamp;
  • cheap indexing;
  • data warehouses у частині сценаріїв.</syntaxhighlight>

Event logging

BEGIN;

PostgreSQL підтримує різні форми replication.

Перевага: PostgreSQL дає можливість почати з простої таблиці користувачів, а потім поступово додавати індекси, JSONB, views, replication, partitioning, full-text search і extensions без зміни базової платформи. PostgreSQL

Extensions

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

-- Знайти об'єкти поруч із заданою точкою

PostgreSQL і MySQL

Проста аналогія: PostgreSQL — це не тільки таблиці й рядки. Вона подобається тому, що часто поводиться чесно: якщо добре спроєктувати інформаційні дані й запити, вона відповідає стабільністю. SQL — основна мова роботи з PostgreSQL. * 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.== Replication ==
created_at date NOT NULL,

PgBouncer оптимізує:

SELECT *

! Вона принесла покращення продуктивності, нову I/O-підсистему, поліпшення upgrade-процесу, кращу роботу з індексами й інші зміни. \dt $$; Він оптимізує:

LIMIT 10;

SEO title: PostgreSQL — open source об’єктно-реляційна база даних для застосунків, аналітики, GIS, JSON і enterprise-систем

SEO 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 

</noinclude>
 {{SEO
Шаблон для службового SEO-опису сторінки.

}}


Schema у PostgreSQL — це namespace всередині database. Least privilege у PostgreSQL — не прикраса, а базова безпека. Oracle Database 

status text NOT NULL DEFAULT 'new',

Найцікавіше, що PostgreSQL здатна поводитися і як класична SQL-база, і як частково document-friendly платформа через JSONB, і як геопросторова база через PostGIS, і як платформа для розширень.== Partitioning ==

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Таблиця має:

Головна перевага: extensions — одна з причин, чому PostgreSQL часто називають не без зусиль базою, а платформою для даних. * PostGIS перетворює PostgreSQL на сильну GIS-платформу.

CREATE INDEX idx_orders_created_at ON orders (created_at); Практична роль: psql — це інструмент, який варто знати навіть тим, хто зазвичай користується GUI. Транзакція — це група операцій, які виконуються як єдине ціле.== Row-Level Security ==

PostgreSQL підтримує: SELECT id, email ! * зменшити кількість backend connections;

  • покращити latency;
  • захистити PostgreSQL від connection storm;
  • краще використовувати resources;
  • працювати з web apps;
  • масштабувати application servers. Можливі проблеми:

Можливі елементи:

! Вона схожа на майстерню: спочатку здається великою, але потім виявляється, що майже для кожної складної задачі там уже виступає як інструмент. Для деяких workloads краще спеціалізовані системи. Таблиці products, orders, users, payments і inventory працюють із constraints, transactions і indexes.

Критично: PostgreSQL не можна виставляти у відкритий інтернет без сильного захисту. pgAdmin — популярний графічний інструмент для PostgreSQL. Поширені підходи:

Приклад задачі: SET balance = balance - 100 Schemas допомагають:

PostgreSQL має обмеження. PostgreSQL зберігає користувачів, підписки, платежі, permissions, audit logs і application data. ON logs USING brin (created_at); CREATE TABLE orders (

Індекси прискорюють пошук, сортування й фільтрацію даних.

* streaming replication;
* physical replication;
* logical replication;
* synchronous replication;
* asynchronous replication;
* hot standby;
* read replicas;
* cascading replication. Приклад:

<syntaxhighlight lang="bash">
'''критично:''' найкращий performance tuning часто починається не з конфігурації сервера, а з правильного індексу або переписаного SQL-запиту. Критерій
<syntaxhighlight lang="sql">

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

<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">
Таблиці — основа relational model у PostgreSQL. ! user_id bigint NOT NULL REFERENCES users(id),
<div style="background:#eafaf1; border-left:6px solid #2ecc71; padding:12px; margin:12px 0;">
'''Logical replication''' передає зміни на рівні таблиць і logical changes. Часто потрібні physical backups і WAL archiving. CREATE TABLE events (

'''Критично:''' застосунок не має підключатися до production-бази під superuser. * PostgreSQL Documentation. Рекомендовано:
</div>
CREATE FUNCTION add_numbers(a int, b int)

'''Найлюдяніший факт:''' PostgreSQL — це база даних, яка не намагається бути “простенькою”. Індекси потрібно створювати за реальними запитами, а не “про всяк випадок”. );

=== GIS-сервіс ===

 id bigserial PRIMARY KEY,

* виконувати SQL;
* переглядати таблиці;
* запускати scripts;
* перевіряти connection;
* використовувати meta-commands;
* експортувати результати;
* адмініструвати базу;
* працювати в terminal. Розширення можуть додавати:

* high availability;
* read scaling;
* disaster recovery;
* migration;
* reporting replicas;
* zero/low downtime upgrades у частині сценаріїв;
* data distribution. Він корисний, коли застосунок відкриває багато database connections.<div style="background:#fff4e5; border-left:6px solid #f39c12; padding:12px; margin:12px 0;">

</div>
PostgreSQL підтримує принципи '''ACID''' для транзакцій.</div>

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

CREATE VIEW active_users AS </syntaxhighlight>

Вони можуть включати: </syntaxhighlight> Partitioning дає можливість розбивати велику таблицю на частини. ! Вона корисна для:

WHERE id = 1; Висновок: PostgreSQL часто обирають як open source альтернативу для частини Oracle-сценаріїв, але міграція складних enterprise-систем потребує ретельного аналізу. Вона активно розвивається й регулярно отримує нові функції ERP. host appdb app_user 10.0.0.0/24 scram-sha-256

Foreign keys

</syntaxhighlight>

  • B-tree;
  • Hash;
  • GIN;
  • GiST;
  • SP-GiST;
  • BRIN;
  • expression indexes;
  • partial indexes;
  • multicolumn indexes;
  • unique indexes. Без нього оптимізація часто перетворюється на вгадування. id bigint,

Приклад:

Приклади:

  • потребує грамотного адміністрування;
  • великі write-heavy workloads потребують tuning;
  • MVCC створює bloat без правильного VACUUM;
  • horizontal sharding не виступає як “однією кнопкою”;
  • складні major upgrades потребують плану;
  • connection count потрібно контролювати;
  • неправильні індекси можуть шкодити;
  • великий JSONB без схеми здатна стати хаосом;
  • HA потребує додаткової архітектури;
  • managed cloud не скасовує оптимізацію запитів;
  • дуже великі analytics workloads можуть потребувати спеціалізованих систем. Це ще платформа, яку можна розширювати під складні типи даних і поведінку. * MVCC дає PostgreSQL сильну concurrency-модель, але потребує VACUUM.

CREATE PUBLICATION app_pub FOR TABLE users, orders; Головна перевага: PostgreSQL дає дуже сильну базу даних без ліцензійного бар’єра й з величезною екосистемою. );

FROM orders

</syntaxhighlight>

Приклад:

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

); WHERE active = true

Запит:

Приклад ідеї:

  • logical backup через `pg_dump`;
  • physical backup;
  • base backup;
  • WAL archiving;
  • point-in-time recovery;
  • managed cloud backups;
  • snapshots з database-aware підходом;
  • backup verification;
  • restore drills.</syntaxhighlight>
  • PRIMARY KEY;
  • FOREIGN KEY;
  • UNIQUE;
  • NOT NULL;
  • CHECK;
  • EXCLUDE constraints;
  • default values;
  • generated values.</syntaxhighlight>
Отримано з https://wiki.kyiv.ua/index.php?title=PostgreSQL&oldid=1352