PostgreSQL 17/18: 새로운 기능 및 성능 개선
PostgreSQL은 세계에서 가장 발전된 오픈 소스 관계형 데이터베이스로 남아 있으며 버전 17과 18이 통합되었습니다.
생산 성능에 직접적인 영향을 미치는 실질적인 개선이 이루어졌습니다.
2024년 9월 26일에 출시된 PostgreSQL 17은 증분 VACUUM, SQL/JSON을 제공합니다. JSON_TABLE()
그리고 더 똑똑한 계획자. 2025년 9월에 출시된 PostgreSQL 18에는 기본 비동기 I/O가 추가되었습니다.
가상 생성 열 및 OAuth 2.0: 최신 애플리케이션을 위한 세대적 도약.
무엇을 배울 것인가
- 쿼리 및 유지 관리를 개선하는 PostgreSQL 17의 주요 기능
- 집약적인 워크로드에 대한 PostgreSQL 18 비동기 I/O의 영향
- 사용방법
JSON_TABLE()반구조화된 데이터에 대한 쿼리를 단순화하기 위해 - 실제 벤치마크: 업그레이드 후 성능이 얼마나 향상됩니까?
- PostgreSQL 16에서 17/18로 업그레이드하기 위한 실용 가이드
PostgreSQL 17: 가장 영향력 있는 뉴스
버전 17은 메모리 및 스토리지 관리, SQL/JSON, 플래너 성능이라는 세 가지 영역에 중점을 둡니다. 생산에 가장 큰 영향을 미치는 혁신을 살펴보겠습니다.
증분 진공
기존 VACUUM은 전체 테이블을 스캔합니다. PostgreSQL 17 증분 VACUUM을 사용하면 프로세스는 구성 가능한 "청크"에서 작동하여 잠금 경합과 최대 I/O를 줄입니다. 이는 기존 VACUUM으로 인해 성능 저하가 발생한 대규모 테이블에 매우 중요합니다. 사용자에게 표시됩니다.
-- PostgreSQL 17: configurare l'incremental vacuum
ALTER TABLE ordini SET (
vacuum_max_eager_freeze_failure_rate = 0.03
);
-- Monitorare l'attivita del vacuum
SELECT
schemaname,
tablename,
last_vacuum,
last_autovacuum,
n_dead_tup,
n_live_tup,
n_mod_since_analyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
JSON_TABLE()을 사용한 SQL/JSON
JSON_TABLE() 그리고 가장 기다려온 SQL 뉴스: JSON을 사용하지 않고 관계형 행으로 변환합니다.
기능 jsonb_to_recordset() 또는 복잡한 표현. SQL/JSON 표준과 호환
ISO/IEC 9075-2:2023.
-- Prima (PostgreSQL 16): approccio con jsonb_to_recordset
SELECT
evento->>'tipo' AS tipo,
evento->>'utente_id' AS utente_id,
(evento->>'timestamp')::timestamptz AS ts
FROM log_eventi,
jsonb_array_elements(payload->'eventi') AS evento;
-- Dopo (PostgreSQL 17): JSON_TABLE() standard ISO
SELECT jt.*
FROM log_eventi,
JSON_TABLE(
payload,
'$.eventi[*]' COLUMNS (
tipo VARCHAR(50) PATH '$.tipo',
utente_id BIGINT PATH '$.utente_id',
ts TIMESTAMPTZ PATH '$.timestamp'
)
) AS jt;
JSON_TABLE() 이는 구문이 더 깔끔할 뿐만 아니라 플래너가 구문을 더 잘 최적화할 수 있습니다.
측면 조인 및 기능을 기반으로 17세 이전 접근 방식보다 더 효율적인 계획을 생성합니다.
쿼리 플래너 개선
PostgreSQL 17에는 범위 스캔에 대한 선택성 추정이 크게 향상되었습니다.
예상 비용 감소 DISTINCT 인덱스가 있는 열에 대해 최선의 사용
B-트리 인덱스의 존재 IS NULL e IS NOT NULL.
-- Verificare miglioramenti al planner con EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT DISTINCT categoria_id
FROM prodotti
WHERE prezzo IS NOT NULL
AND disponibile = true;
-- PostgreSQL 17 usa meglio gli indici composti in questi scenari
CREATE INDEX CONCURRENTLY idx_prodotti_cat_prezzo
ON prodotti (categoria_id, prezzo)
WHERE disponibile = true;
PostgreSQL 18: 비동기식 I/O로의 도약
PostgreSQL 18은 가장 심오한 아키텍처 변경 사항 중 하나인 기본 비동기 I/O를 도입합니다.
최근 몇 년 동안. 역사적으로 PostgreSQL은 pread() 동기: 모든 작업
읽기가 완료될 때까지 프로세스가 차단되었습니다. 비동기 I/O를 사용하면 더 많은 작업을 수행할 수 있습니다.
읽기를 동시에 시작할 수 있어 최신 NVMe 하드웨어를 더 잘 활용할 수 있습니다.
-- postgresql.conf: nuovi parametri per l'I/O asincrono in PG18
# Abilitare il backend I/O asincrono (default: io_uring su Linux 5.1+)
io_method = io_uring # oppure 'worker' per compatibilita
# Numero massimo di richieste I/O asincrone in volo
io_max_concurrency = 32 # da aumentare per NVMe ad alte IOPS
# Dimensione della coda I/O per worker
io_workers = 4 # worker thread per operazioni I/O
io_uring 호환성
io_uring Linux 커널 5.1 이상이 필요합니다. 이전 커널을 사용하는 시스템이나 macOS/Windows에서는
PostgreSQL 18은 자동 대체를 수행합니다. io_method = worker그러나 이는 개선됩니다.
기존 동기식에 비해 처리량.
가상 생성 열
PostgreSQL 17에는 저장됨 생성된 열(값이 계산되어 디스크에 저장됨) PostgreSQL 18은 다음을 추가합니다. 가상 생성된 열: 값이 그 자리에서 계산됩니다. 디스크 공간을 차지하지 않고 읽기. 조명 변환에 이상적입니다.
-- PostgreSQL 18: virtual generated column (non salvata su disco)
CREATE TABLE prodotti (
id BIGSERIAL PRIMARY KEY,
nome TEXT NOT NULL,
prezzo_netto NUMERIC(10,2) NOT NULL,
iva_pct NUMERIC(5,2) NOT NULL DEFAULT 22.0,
-- virtual: calcolata al volo, 0 spazio su disco
prezzo_lordo NUMERIC(10,2) GENERATED ALWAYS AS
(ROUND(prezzo_netto * (1 + iva_pct / 100), 2)) VIRTUAL
);
-- La colonna e disponibile come le altre nelle query
SELECT nome, prezzo_netto, prezzo_lordo
FROM prodotti
WHERE prezzo_lordo BETWEEN 100 AND 500;
OAuth 2.0 인증
PostgreSQL 18은 인증 방법으로 기본적으로 OAuth 2.0을 지원하므로 필요가 없습니다.
기업 ID 공급자(Azure AD, Okta, Google)와 통합하기 위한 외부 프록시 또는 래퍼
작업 공간). 구성은 다음에서 이루어집니다. pg_hba.conf.
-- pg_hba.conf: autenticazione OAuth 2.0 (PostgreSQL 18)
# TYPE DATABASE USER ADDRESS METHOD OPTIONS
hostssl all app_user all oauth issuer="https://login.microsoftonline.com/tenant-id/v2.0"
scope="postgres"
실제 벤치마크: 성능은 얼마나 향상됩니까?
공식 PostgreSQL 벤치마크 및 커뮤니티 테스트에서 상당한 개선이 나타났습니다. 실제 시나리오에서. 다음 결과는 일반적인 생산 하드웨어에서 얻은 것입니다. 8코어 CPU, 32GB RAM, NVMe SSD.
| 대본 | 포스트그레SQL 16 | 포스트그레SQL 17 | 포스트그레SQL 18 |
|---|---|---|---|
| 데드 튜플이 30%인 50GB 테이블의 VACUUM | 480년대 | 310년대 (-35%) | 280년대 (-42%) |
| 순차 스캔 1억 라인(NVMe) | 28초 | 25초 (-11%) | 17대 (-39%) |
| 100만 개의 JSON 문서에 대한 JSON_TABLE() | 해당 없음 | 4.2초 | 3.1초 |
| pgbench TPS(규모=1000, 클라이언트=50) | 18,400 | 19,100 (+3.8%) | 21,800 (+18.5%) |
업그레이드 가이드: PostgreSQL 16에서 17/18로
PostgreSQL 17/18로의 업그레이드는 다음을 통해 수행할 수 있습니다. pg_upgrade (현재 위치 업그레이드, 더 빠름)
또는 논리적 복제를 통해(다운타임 없이 업그레이드) 두 가지 접근 방식을 모두 살펴보겠습니다.
pg_upgrade로 업그레이드(빠른 방법)
#!/bin/bash
# Upgrade PostgreSQL 16 -> 17 con pg_upgrade
# 1. Installare PostgreSQL 17 (Ubuntu/Debian)
apt install postgresql-17
# 2. Fermare il cluster 16 (se non si usa --link)
systemctl stop postgresql@16-main
# 3. Eseguire pg_upgrade
/usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir /var/lib/postgresql/16/main \
--new-datadir /var/lib/postgresql/17/main \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--link \ # Hard link ai file dati: molto piu veloce (no copia)
--check # Prima: solo verifica, nessuna modifica
# 4. Se il check passa, eseguire l'upgrade reale (rimuovere --check)
/usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir /var/lib/postgresql/16/main \
--new-datadir /var/lib/postgresql/17/main \
--old-bindir /usr/lib/postgresql/16/bin \
--new-bindir /usr/lib/postgresql/17/bin \
--link
# 5. Avviare PostgreSQL 17
systemctl start postgresql@17-main
# 6. Eseguire analyze per aggiornare le statistiche
/usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages
논리적 복제를 통해 다운타임 없이 업그레이드
-- STEP 1: Sul server PostgreSQL 16 (source)
-- Configurare postgresql.conf
-- wal_level = logical (deve essere gia impostato)
-- max_replication_slots = 10
-- Creare la pubblicazione su tutte le tabelle
CREATE PUBLICATION pg17_migration FOR ALL TABLES;
-- STEP 2: Sul server PostgreSQL 17 (target)
-- Creare lo schema identico (migrare DDL)
-- ...
-- Creare la sottoscrizione
CREATE SUBSCRIPTION pg17_sub
CONNECTION 'host=pg16-host port=5432 dbname=mydb user=replicator'
PUBLICATION pg17_migration
WITH (create_slot = true, slot_name = 'pg17_migration_slot');
-- STEP 3: Monitorare la replica lag
SELECT
slot_name,
confirmed_flush_lsn,
pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots;
-- STEP 4: Quando lag e vicino a zero, switchare il traffico
-- (aggiornare load balancer o DNS)
-- STEP 5: Cleanup dopo il cutover
DROP SUBSCRIPTION pg17_sub; -- su PG17
-- Sul PG16:
SELECT pg_drop_replication_slot('pg17_migration_slot');
업그레이드 모범 사례
- 항상 실행
pg_upgrade --check실제 업그레이드 전에 비호환성을 감지하기 위해 - 컷오버 전에 프로덕션 데이터베이스의 클론에서 업그레이드를 테스트합니다.
- 와 함께
--link업그레이드는 거의 즉각적이지만 롤백하지 마세요 PG17을 처음 시작한 후 - 업그레이드 후 확장 업데이트:
ALTER EXTENSION nome UPDATE - 업그레이드 후 일주일 동안 느린 쿼리를 모니터링합니다. 기획자가 계획을 변경할 수 있습니다.
개발자를 위한 뉴스: 향상된 SQL
성능 외에도 PostgreSQL 17 및 18은 SQL을 단순화하는 몇 가지 새로운 기능을 제공합니다. 매일 발전.
RETURNING으로 병합(PostgreSQL 17)
-- PostgreSQL 17: MERGE con RETURNING per upsert audit
MERGE INTO inventario AS target
USING (VALUES
(1001, 50, 'warehouse-A'),
(1002, 30, 'warehouse-B')
) AS source(prodotto_id, quantita, location)
ON target.prodotto_id = source.prodotto_id
WHEN MATCHED THEN
UPDATE SET
quantita = target.quantita + source.quantita,
ultimo_aggiornamento = NOW()
WHEN NOT MATCHED THEN
INSERT (prodotto_id, quantita, location, creato_il)
VALUES (source.prodotto_id, source.quantita, source.location, NOW())
RETURNING
prodotto_id,
xmax = 0 AS is_insert, -- true se INSERT, false se UPDATE
quantita;
ON_ERROR로 복사(PostgreSQL 17)
-- PostgreSQL 17: ignorare righe invalide durante COPY
-- Prima: l'intera operazione falliva alla prima riga corrotta
COPY prodotti (id, nome, prezzo)
FROM '/data/import.csv'
WITH (
FORMAT csv,
HEADER true,
ON_ERROR ignore, -- salta le righe con errori
LOG_VERBOSITY verbose -- logga le righe saltate
);
-- Verificare quante righe sono state saltate
SELECT count(*) FROM pg_temp.copy_summary;
PostgreSQL 17/18을 위한 최적의 구성
각 업그레이드는 구성을 검토할 수 있는 기회입니다. 이러한 매개변수는 특히 PG17/18 뉴스와 관련이 있습니다.
# postgresql.conf ottimizzato per PostgreSQL 18
# (hardware: 8 core, 32GB RAM, NVMe SSD)
# Memoria
shared_buffers = 8GB # 25% della RAM
effective_cache_size = 24GB # stima totale memoria disponibile
work_mem = 64MB # per sort/hash, attenzione a connessioni parallele
maintenance_work_mem = 2GB # per VACUUM, CREATE INDEX
# I/O asincrono (PostgreSQL 18)
io_method = io_uring # Linux 5.1+
io_max_concurrency = 64 # per NVMe ad alte IOPS
effective_io_concurrency = 200 # per bitmap heap scan
# Parallelismo
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# Incremental VACUUM (PostgreSQL 17)
autovacuum_vacuum_cost_delay = 2ms # piu aggressivo (default: 2ms da PG17)
autovacuum_vacuum_cost_limit = 800 # aumentato da 200
# WAL
wal_level = logical # necessario per logical replication
max_wal_size = 4GB
checkpoint_completion_target = 0.9
업그레이드 후 모니터링
업그레이드 후 최소 1주일 동안 다음 지표를 적극적으로 모니터링하세요.
-- Query per identificare regressioni di performance post-upgrade
-- Richiede pg_stat_statements
SELECT
LEFT(query, 80) AS query_preview,
calls,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
ROUND(total_exec_time::numeric, 0) AS total_ms,
rows / calls AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
AND mean_exec_time > 100 -- query lente (>100ms)
ORDER BY mean_exec_time DESC
LIMIT 20;
-- Verificare se il planner cambia i piani rispetto a PG16
-- (abilitare auto_explain per catturare i piani)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = true;
SET auto_explain.log_buffers = true;
결론
PostgreSQL 17 및 18은 하드웨어를 최대한 활용하려는 사람들을 위한 필수 업그레이드입니다.
증분 VACUUM은 대형 테이블의 역사적 문제 중 하나를 해결합니다. JSON_TABLE()
반구조화된 데이터에 대한 쿼리를 단순화하고 PG18의 비동기 I/O로 개선 사항을 제공합니다.
NVMe를 사용한 I/O 바인딩 워크로드에서 최대 40%의 처리량.
시리즈의 다음 기사에서는 쿼리 계획을 읽고 최적화하는 방법을 자세히 설명합니다.
EXPLAIN ANALYZE: 프로덕션에서 느린 쿼리를 식별하는 가장 강력한 기술입니다.
시리즈: 데이터베이스 엔지니어링 및 최적화
- 제1조(본): PostgreSQL 17/18 - 새로운 기능 및 성능
- 기사 2: 분석 설명 - 쿼리 계획 읽기 및 최적화
- 조항 3: 고급 색인 생성 - 부분, 포함, BRIN, GIN
- 기사 4: 테이블 분할 - 수십억 개의 행 관리
- 기사 5: 연결 풀링 - PgBouncer 대 Pgpool-II