Lessons

PostgreSQL Replication and Performance

Level: Advanced Module: Database Services 11 min read Lesson 44 of 66

Overview

  • What you’ll learn: PostgreSQL streaming replication for high availability, logical replication for selective table replication, connection pooling with PgBouncer, query optimization using EXPLAIN ANALYZE, index strategies, and performance monitoring tools.
  • Prerequisites: Lesson 43 (PostgreSQL Installation and Administration), familiarity with pg_hba.conf and PostgreSQL roles
  • Estimated reading time: 25 minutes

Introduction

A well-configured PostgreSQL installation can handle remarkable workloads, but as data volumes grow and availability requirements tighten, a single server is no longer sufficient. Replication provides data redundancy and read scalability, while performance tuning ensures that queries execute efficiently even as datasets expand.

PostgreSQL offers two primary replication mechanisms: streaming replication for byte-level WAL shipping that creates an exact standby copy, and logical replication for publishing specific tables to subscribers. On the performance side, understanding the query planner through EXPLAIN ANALYZE, choosing appropriate indexes, and managing connections with a pooler like PgBouncer are essential skills for any database administrator.

In this lesson you will configure both replication types, set up connection pooling, and learn systematic approaches to identifying and resolving performance bottlenecks.

Streaming Replication

Streaming replication sends Write-Ahead Log (WAL) records from a primary to one or more standby servers in real time. The standby maintains an exact byte-for-byte copy of the primary’s data directory.

Configuring the Primary

# /etc/postgresql/16/main/postgresql.conf (primary)
wal_level = replica
max_wal_senders = 5                  # Max number of standby connections
wal_keep_size = 1GB                  # WAL segments retained for standbys
hot_standby = on
# /etc/postgresql/16/main/pg_hba.conf (primary)
# Allow replication connections from standby
host    replication    repl_user    10.0.1.0/24    scram-sha-256
# Create the replication role
$ sudo -u postgres psql
postgres=# CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'R3pl_P@ss!';

# Reload configuration
$ sudo systemctl reload postgresql

Configuring the Standby

# Stop PostgreSQL on the standby
$ sudo systemctl stop postgresql

# Remove existing data directory
$ sudo rm -rf /var/lib/postgresql/16/main/*

# Create a base backup from the primary
$ sudo -u postgres pg_basebackup 
    -h 10.0.1.10 
    -U repl_user 
    -D /var/lib/postgresql/16/main 
    -Fp -Xs -P -R

# The -R flag creates standby.signal and connection info in postgresql.auto.conf

# Start PostgreSQL on the standby
$ sudo systemctl start postgresql

Verifying Replication

# On the primary — check connected standbys
postgres=# SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
           FROM pg_stat_replication;

# On the standby — verify it is in recovery mode
postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t

# Check replication lag
postgres=# SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Synchronous Replication

By default, streaming replication is asynchronous. For stronger durability guarantees, enable synchronous replication:

# On the primary — postgresql.conf
synchronous_standby_names = 'standby1'

# On the standby — postgresql.auto.conf (set by pg_basebackup -R)
primary_conninfo = 'host=10.0.1.10 user=repl_user password=R3pl_P@ss! application_name=standby1'

With synchronous replication, the primary waits for the standby to confirm that WAL records have been flushed to disk before committing the transaction. This eliminates data loss risk but adds latency to writes.

Logical Replication

Logical replication publishes changes from specific tables rather than replicating the entire WAL stream. This allows selective replication, cross-version replication, and replication to different schemas.

Setting Up a Publisher

# /etc/postgresql/16/main/postgresql.conf (publisher)
wal_level = logical
# Restart PostgreSQL
$ sudo systemctl restart postgresql

# Create a publication
postgres=# CREATE PUBLICATION my_pub FOR TABLE users, orders;

# Or publish all tables
postgres=# CREATE PUBLICATION all_tables_pub FOR ALL TABLES;

Setting Up a Subscriber

# On the subscriber — create the same table structure
subscriber_db=# CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    email VARCHAR(255) NOT NULL
);

# Create the subscription
subscriber_db=# CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=10.0.1.10 dbname=myapp_db user=repl_user password=R3pl_P@ss!'
    PUBLICATION my_pub;

# Check subscription status
subscriber_db=# SELECT * FROM pg_stat_subscription;

Connection Pooling with PgBouncer

Each PostgreSQL connection consumes a backend process and significant memory. For applications with many short-lived connections, PgBouncer provides lightweight connection pooling that dramatically reduces resource usage.

# Install PgBouncer
$ sudo apt install pgbouncer -y

Configuring PgBouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # transaction, session, or statement
default_pool_size = 20
max_client_conn = 200
max_db_connections = 50
# /etc/pgbouncer/userlist.txt
"appuser" "SCRAM-SHA-256$4096:salt$stored_key:server_key"
# Generate the password hash from PostgreSQL
postgres=# SELECT concat('"', usename, '" "', passwd, '"')
           FROM pg_shadow WHERE usename = 'appuser';

# Start PgBouncer
$ sudo systemctl start pgbouncer
$ sudo systemctl enable pgbouncer

# Connect through PgBouncer
$ psql -h 127.0.0.1 -p 6432 -U appuser myapp_db

Pool Modes

  • session: A server connection is assigned for the full duration of the client session. Safest but least efficient.
  • transaction: A server connection is assigned only for the duration of a transaction. Recommended for most applications.
  • statement: A server connection is assigned per statement. Only works for simple, autocommit queries.

Query Optimization with EXPLAIN

The EXPLAIN command shows the execution plan the query planner chooses. EXPLAIN ANALYZE actually runs the query and shows real execution times.

-- Basic explain
postgres=# EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on users  (cost=0.00..25.00 rows=1 width=128)
   Filter: (email = 'alice@example.com'::text)

-- With actual timing
postgres=# EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
           SELECT * FROM users WHERE email = 'alice@example.com';

-- After creating an index
postgres=# CREATE INDEX idx_users_email ON users (email);
postgres=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
                        QUERY PLAN
----------------------------------------------------------
 Index Scan using idx_users_email on users  (cost=0.28..8.30 rows=1 width=128)
   Index Cond: (email = 'alice@example.com'::text)

Reading Execution Plans

Key elements to look for in execution plans:

  • Seq Scan: Full table scan — acceptable for small tables, problematic for large ones.
  • Index Scan: Uses an index to locate rows — efficient for selective queries.
  • Bitmap Index Scan: Scans the index to create a bitmap of matching pages, then fetches them — efficient for medium-selectivity queries.
  • Nested Loop / Hash Join / Merge Join: Different join strategies with varying cost characteristics.
  • cost: Estimated startup and total cost (in arbitrary units).
  • rows: Estimated number of rows returned.
  • actual time: Real execution time in milliseconds (only with ANALYZE).

Index Strategies

-- B-tree index (default, good for equality and range queries)
CREATE INDEX idx_orders_date ON orders (created_at);

-- Partial index (only indexes rows matching a condition)
CREATE INDEX idx_active_users ON users (email) WHERE active = true;

-- Composite index (covers multiple columns)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN index (for full-text search and JSONB)
CREATE INDEX idx_docs_content ON documents USING gin (to_tsvector('english', content));
CREATE INDEX idx_data_jsonb ON events USING gin (metadata jsonb_path_ops);

-- Check index usage statistics
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Performance Monitoring

-- Top queries by total execution time (requires pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Table statistics: dead tuples, sequential vs index scans
SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Cache hit ratio (should be > 99% for production)
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

Key Takeaways

  • Streaming replication creates an exact standby using WAL shipping; use pg_basebackup with the -R flag to bootstrap a standby server.
  • Synchronous replication eliminates data loss risk at the cost of write latency — asynchronous is sufficient for most read-heavy workloads.
  • Logical replication enables selective table publication across servers, versions, or schemas.
  • PgBouncer reduces PostgreSQL connection overhead; use transaction pool mode for most applications.
  • EXPLAIN ANALYZE reveals actual execution plans and timings — look for sequential scans on large tables as optimization targets.
  • Choose the right index type: B-tree for equality/range, GIN for full-text/JSONB, partial indexes for common filter conditions.
  • Monitor cache hit ratio (target > 99%) and use pg_stat_statements to identify the most expensive queries.

What’s Next

In Lesson 45, you will learn comprehensive database backup strategies for both MySQL and PostgreSQL, including logical and physical backups, point-in-time recovery, automated backup scheduling, and offsite backup best practices.

繁體中文

概覽

  • 您將學到：PostgreSQL 串流複寫用於高可用性、邏輯複寫用於選擇性表複寫、使用 PgBouncer 的連線池、使用 EXPLAIN ANALYZE 的查詢最佳化、索引策略和效能監控工具。
  • 先決條件：第 43 課（PostgreSQL 安裝和管理），熟悉 pg_hba.conf 和 PostgreSQL 角色
  • 預估閱讀時間：25 分鐘

簡介

配置良好的 PostgreSQL 安裝可以處理可觀的工作負載，但隨著資料量增長和可用性要求提高，單一伺服器已不再足夠。複寫提供資料冗餘和讀取擴展性，而效能調校確保查詢即使在資料集擴展時也能高效執行。

PostgreSQL 提供兩種主要複寫機制：串流複寫用於位元組級 WAL 傳輸以建立精確的備用副本，邏輯複寫用於將特定表發布給訂閱者。

串流複寫

串流複寫將預寫日誌（WAL）記錄從主伺服器即時發送到一台或多台備用伺服器。備用伺服器維護主伺服器資料目錄的精確副本。

設定主伺服器

# postgresql.conf（主伺服器）
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on
# 建立複寫角色
postgres=# CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'R3pl_P@ss!';

設定備用伺服器

# 停止備用伺服器上的 PostgreSQL
$ sudo systemctl stop postgresql

# 從主伺服器建立基礎備份
$ sudo -u postgres pg_basebackup 
    -h 10.0.1.10 -U repl_user 
    -D /var/lib/postgresql/16/main 
    -Fp -Xs -P -R

# 啟動備用伺服器上的 PostgreSQL
$ sudo systemctl start postgresql

驗證複寫

# 在主伺服器上檢查連線的備用伺服器
postgres=# SELECT client_addr, state, sent_lsn, replay_lsn
           FROM pg_stat_replication;

# 在備用伺服器上驗證恢復模式
postgres=# SELECT pg_is_in_recovery();

# 檢查複寫延遲
postgres=# SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

邏輯複寫

邏輯複寫從特定表發布變更，而非複寫整個 WAL 串流。這允許選擇性複寫和跨版本複寫。

# 在發布者上設定
postgres=# CREATE PUBLICATION my_pub FOR TABLE users, orders;

# 在訂閱者上建立訂閱
subscriber_db=# CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=10.0.1.10 dbname=myapp_db user=repl_user password=R3pl_P@ss!'
    PUBLICATION my_pub;

使用 PgBouncer 的連線池

每個 PostgreSQL 連線消耗一個後端程序和大量記憶體。PgBouncer 提供輕量級連線池，大幅減少資源使用。

# 安裝 PgBouncer
$ sudo apt install pgbouncer -y
# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 200

連線池模式

  • session：伺服器連線在整個用戶端會話期間分配。最安全但效率最低。
  • transaction：伺服器連線僅在交易期間分配。推薦用於大多數應用程式。
  • statement：每條語句分配一個連線。僅適用於簡單的自動提交查詢。

使用 EXPLAIN 的查詢最佳化

-- 基本 explain
postgres=# EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 帶實際計時
postgres=# EXPLAIN (ANALYZE, BUFFERS)
           SELECT * FROM users WHERE email = 'alice@example.com';

-- 建立索引後
postgres=# CREATE INDEX idx_users_email ON users (email);
postgres=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

索引策略

-- B-tree 索引（預設，適合等值和範圍查詢）
CREATE INDEX idx_orders_date ON orders (created_at);

-- 部分索引
CREATE INDEX idx_active_users ON users (email) WHERE active = true;

-- 複合索引
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN 索引（用於全文搜尋和 JSONB）
CREATE INDEX idx_docs_content ON documents USING gin (to_tsvector('english', content));

效能監控

-- 按總執行時間排列的最耗資源查詢
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- 快取命中率（生產環境應 > 99%）
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

重點回顧

  • 串流複寫使用 WAL 傳輸建立精確備用；使用帶 -R 標誌的 pg_basebackup 引導備用伺服器。
  • 同步複寫以寫入延遲為代價消除資料遺失風險——非同步複寫對大多數讀取密集型工作負載已足夠。
  • 邏輯複寫支援跨伺服器、版本或結構描述的選擇性表發布。
  • PgBouncer 減少 PostgreSQL 連線開銷；大多數應用程式使用 transaction 連線池模式。
  • EXPLAIN ANALYZE 顯示實際執行計畫和計時——尋找大表上的順序掃描作為最佳化目標。
  • 選擇正確的索引類型：等值/範圍用 B-tree，全文/JSONB 用 GIN，常見篩選條件用部分索引。
  • 監控快取命中率（目標 > 99%）並使用 pg_stat_statements 識別最昂貴的查詢。

下一步

在第 45 課中，您將學習 MySQL 和 PostgreSQL 的全面資料庫備份策略，包括邏輯和物理備份、時間點恢復、自動備份排程和異地備份最佳實踐。

日本語

概要

  • 学習内容：高可用性のための PostgreSQL ストリーミングレプリケーション、選択的テーブルレプリケーションのためのロジカルレプリケーション、PgBouncer によるコネクションプーリング、EXPLAIN ANALYZE を使用したクエリ最適化、インデックス戦略、パフォーマンス監視ツール。
  • 前提条件：レッスン 43（PostgreSQL インストールと管理）、pg_hba.conf と PostgreSQL ロールの知識
  • 推定読了時間：25 分

はじめに

適切に設定された PostgreSQL インストールは驚くべきワークロードを処理できますが、データ量が増加し可用性要件が厳しくなると、単一サーバーでは不十分になります。レプリケーションはデータの冗長性と読み取りスケーラビリティを提供し、パフォーマンスチューニングはデータセットが拡大してもクエリが効率的に実行されることを保証します。

PostgreSQL は2つの主要なレプリケーションメカニズムを提供します：正確なスタンバイコピーを作成するバイトレベルの WAL シッピングのためのストリーミングレプリケーションと、特定のテーブルをサブスクライバーにパブリッシュするためのロジカルレプリケーションです。

ストリーミングレプリケーション

ストリーミングレプリケーションは、先行書き込みログ（WAL）レコードをプライマリから1つ以上のスタンバイサーバーにリアルタイムで送信します。

プライマリの設定

# postgresql.conf（プライマリ）
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
hot_standby = on
# レプリケーションロールの作成
postgres=# CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'R3pl_P@ss!';

スタンバイの設定

# スタンバイの PostgreSQL を停止
$ sudo systemctl stop postgresql

# プライマリからベースバックアップを作成
$ sudo -u postgres pg_basebackup 
    -h 10.0.1.10 -U repl_user 
    -D /var/lib/postgresql/16/main 
    -Fp -Xs -P -R

# スタンバイの PostgreSQL を開始
$ sudo systemctl start postgresql

レプリケーションの確認

# プライマリで接続されたスタンバイを確認
postgres=# SELECT client_addr, state, sent_lsn, replay_lsn
           FROM pg_stat_replication;

# スタンバイでリカバリモードを確認
postgres=# SELECT pg_is_in_recovery();

# レプリケーション遅延の確認
postgres=# SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

ロジカルレプリケーション

ロジカルレプリケーションは WAL ストリーム全体をレプリケーションするのではなく、特定のテーブルからの変更をパブリッシュします。

# パブリッシャーで設定
postgres=# CREATE PUBLICATION my_pub FOR TABLE users, orders;

# サブスクライバーでサブスクリプションを作成
subscriber_db=# CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=10.0.1.10 dbname=myapp_db user=repl_user password=R3pl_P@ss!'
    PUBLICATION my_pub;

PgBouncer によるコネクションプーリング

各 PostgreSQL 接続はバックエンドプロセスと相当なメモリを消費します。PgBouncer は軽量なコネクションプーリングを提供し、リソース使用量を大幅に削減します。

# PgBouncer のインストール
$ sudo apt install pgbouncer -y
# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 200

プールモード

  • session：クライアントセッション全体にサーバー接続を割り当て。最も安全だが効率は最低。
  • transaction：トランザクション期間のみサーバー接続を割り当て。ほとんどのアプリケーションに推奨。
  • statement：ステートメントごとにサーバー接続を割り当て。シンプルな自動コミットクエリのみ対応。

EXPLAIN によるクエリ最適化

-- 基本的な explain
postgres=# EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 実際のタイミング付き
postgres=# EXPLAIN (ANALYZE, BUFFERS)
           SELECT * FROM users WHERE email = 'alice@example.com';

-- インデックス作成後
postgres=# CREATE INDEX idx_users_email ON users (email);
postgres=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

インデックス戦略

-- B-tree インデックス（デフォルト、等値・範囲クエリに適用）
CREATE INDEX idx_orders_date ON orders (created_at);

-- 部分インデックス
CREATE INDEX idx_active_users ON users (email) WHERE active = true;

-- 複合インデックス
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN インデックス（全文検索と JSONB 用）
CREATE INDEX idx_docs_content ON documents USING gin (to_tsvector('english', content));

パフォーマンス監視

-- 総実行時間順のトップクエリ
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- キャッシュヒット率（本番環境では > 99% が目標）
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

重要ポイント

  • ストリーミングレプリケーションは WAL シッピングを使用して正確なスタンバイを作成。pg_basebackup の -R フラグでスタンバイサーバーをブートストラップ。
  • 同期レプリケーションは書き込みレイテンシと引き換えにデータ損失リスクを排除——非同期は読み取り重視のワークロードに十分。
  • ロジカルレプリケーションはサーバー、バージョン、スキーマ間の選択的テーブルパブリケーションを可能にする。
  • PgBouncer は PostgreSQL の接続オーバーヘッドを削減。ほとんどのアプリケーションには transaction プールモードを使用。
  • EXPLAIN ANALYZE は実際の実行計画とタイミングを表示——大きなテーブルでのシーケンシャルスキャンを最適化対象として探す。
  • 適切なインデックスタイプを選択：等値/範囲には B-tree、全文/JSONB には GIN、一般的なフィルタ条件には部分インデックス。
  • キャッシュヒット率を監視（目標 > 99%）し、pg_stat_statements で最もコストの高いクエリを特定する。

次のステップ

レッスン 45 では、MySQL と PostgreSQL の包括的なデータベースバックアップ戦略を学びます。論理的・物理的バックアップ、ポイントインタイムリカバリ、自動バックアップスケジューリング、オフサイトバックアップのベストプラクティスを学びます。

You Missed