0Pricing
PostgreSQL Performance & Query Optimization · 강의

읽기 및 분석 작업 오프로딩

기본 OLTP 지연 시간을 보호하도록 대규모 보고 트래픽을 논리적 복제본으로 전달하는 방법을 배웁니다.

읽기 및 분석 작업 오프로딩은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Offload Reads at All?

On a busy PostgreSQL primary, the same instance serves OLTP traffic (small, fast, latency-sensitive writes and point reads) and analytic traffic (large sequential scans, aggregations, reporting joins).

The problem: a single heavy reporting query can saturate shared buffers, evict hot OLTP pages, and inflate I/O queue depth. Your p99 checkout latency suddenly doubles because someone ran a quarterly revenue report.

  • Goal: keep the primary lean for transactional work.
  • Strategy: route heavy read and analytic queries to a replica that holds a copy of the data.

This lesson focuses on using logical replication to build purpose-shaped read replicas for analytics.

Physical vs Logical Replication

PostgreSQL offers two replication models, and choosing correctly is the key decision for read offloading.

  • Physical (streaming) replication: ships WAL byte-for-byte. The replica is an exact block-level clone — same schema, same indexes, same bloat. Great for HA and read scaling of identical queries.
  • Logical replication: decodes WAL into row-level changes (INSERT/UPDATE/DELETE) and replays them via SQL. The subscriber is an independent database — you can add different indexes, extra columns, materialized rollups, or even a different major version.

For analytic offload, logical replication shines: the analytics node can carry heavy reporting indexes the primary should never pay to maintain.

Setting Up a Publication

On the primary (the publisher), you must set wal_level = logical and create a publication — a named set of tables whose changes will be streamed.

You can publish all tables, a subset, or even filter rows and columns (PostgreSQL 15+). For an analytics offload you typically publish exactly the fact and dimension tables the reports need.

-- On the primary: declare what gets replicated
-- (requires wal_level = logical in postgresql.conf)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders, order_items, customers
  WITH (publish = 'insert, update, delete');

-- Inspect existing publications
SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete
FROM pg_publication;

Creating the Subscriber

On a separate host — your analytics replica — create a matching schema (at least the published tables) and then a subscription. The subscription connects to the publisher and starts an initial data copy followed by continuous streaming.

Because the subscriber is independent, give it the configuration analytics needs: more work_mem, more maintenance_work_mem, and parallel-friendly settings — without touching the OLTP primary.

-- On the analytics node: subscribe to the primary's publication
CREATE SUBSCRIPTION analytics_sub
  CONNECTION 'host=primary.db port=5432 dbname=shop user=repl password=secret'
  PUBLICATION analytics_pub
  WITH (copy_data = true, streaming = on);

-- Watch initial sync + streaming status
SELECT subname, srrelid::regclass AS rel, srsubstate
FROM pg_subscription_rel
JOIN pg_subscription ON oid = srsubid;

Add Analytic-Only Indexes on the Replica

Here is the payoff of logical replication. The primary keeps only the lean indexes OLTP needs. The analytics subscriber adds wide, expensive indexes that would slow every write on the primary but make reports fast on the replica.

  • BRIN indexes for huge append-only time-series fact tables.
  • Covering / partial indexes tailored to dashboard queries.
  • Expression indexes for report-specific predicates.

Maintaining these here costs the analytics node write amplification — but the primary never pays for it.

-- These indexes live ONLY on the analytics subscriber
CREATE INDEX brin_orders_created
  ON orders USING brin (created_at) WITH (pages_per_range = 32);

CREATE INDEX idx_orders_report
  ON orders (customer_id, created_at)
  INCLUDE (total_amount, status)
  WHERE status <> 'cancelled';

Routing Reads at the Application Layer

Offloading only helps if traffic actually reaches the replica. You route at the application or proxy layer:

  • Connection-level split: a read/write pool to the primary, a read-only pool to the analytics node.
  • Proxy-based: PgBouncer/Pgpool or a service mesh directs SELECT reporting traffic by user, schema, or query tag.

A clean pattern is a dedicated analytics_ro database role with statement timeouts and lower priority, so a runaway report can never threaten transactional SLAs.

-- Give reporting sessions a safety harness on the replica
ALTER ROLE analytics_ro SET statement_timeout = '120s';
ALTER ROLE analytics_ro SET work_mem = '256MB';
ALTER ROLE analytics_ro SET default_transaction_read_only = on;

-- Confirm where a session actually landed (run on each node)
SELECT pg_is_in_recovery() AS is_replica, current_setting('work_mem');

Pre-Aggregating with Materialized Views

The replica is the perfect home for materialized views that pre-compute expensive rollups. Dashboards then read a tiny summarized table instead of scanning millions of fact rows on every load.

Refresh them on the analytics node on a schedule. Using REFRESH MATERIALIZED VIEW CONCURRENTLY avoids locking readers out during the rebuild (it requires a unique index on the view).

-- Lives on the analytics replica only
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       count(*)            AS order_count,
       sum(total_amount)   AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX ON daily_revenue (day);

-- Scheduled refresh that does not block dashboard readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

Understanding Replication Lag

Logical replication is asynchronous by default: the subscriber trails the primary by some lag. For analytics this is almost always fine — a report on data that is two seconds old is acceptable. For "read-your-own-write" UI flows it is not.

Decision rule: send eventually-consistent, aggregate-heavy reads to the replica; keep read-after-write and exact-current-state reads on the primary.

Monitor lag from the publisher's replication slot — a stalled subscriber causes WAL to accumulate and can fill the primary's disk.

-- On the primary: how far behind is each logical slot?
SELECT slot_name,
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Filtering Down to What Analytics Needs

You rarely need every column or row on the analytics node. PostgreSQL 15+ lets a publication apply row filters and column lists, shrinking the replicated dataset and the WAL decoding cost.

  • Row filter: replicate only rows that matter to reporting (e.g. non-archived orders).
  • Column list: exclude wide or sensitive columns (PII, large JSON blobs) the reports never touch.

Smaller replicated footprint means faster initial sync and less network/decoding overhead.

-- Publish only relevant rows and a subset of columns (PG 15+)
CREATE PUBLICATION analytics_pub
  FOR TABLE orders (id, customer_id, created_at, total_amount, status)
    WHERE (status <> 'draft' AND created_at >= '2024-01-01');

Verifying the Plan Uses the Replica's Indexes

After building analytic indexes and materialized views on the subscriber, confirm the planner actually uses them. Run EXPLAIN (ANALYZE, BUFFERS) on the analytics node for your real reporting queries.

You want to see index scans / bitmap scans on the report-specific indexes and, ideally, the dashboard query hitting the materialized view instead of the raw fact table. Compare shared read buffers before and after — that drop is the I/O you removed from the primary.

-- Run on the analytics replica, not the primary
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT day, revenue
FROM daily_revenue
WHERE day >= current_date - interval '30 days'
ORDER BY day;

Operational Guardrails

A read-offload architecture is only as good as its failure handling. Build in guardrails:

  • Lag alerting: page when retained_wal or apply lag crosses a threshold — a dead subscriber can fill the primary's disk via the slot.
  • Sequences & DDL are not replicated: logical replication copies row data, not sequence values or schema changes. Manage DDL deliberately on both sides.
  • Conflict awareness: never let applications write to replicated tables on the subscriber, or apply will break.
  • Capacity: the analytics node can be smaller in connections but needs RAM and disk for its extra indexes and materialized views.

Quick Check: Choosing the Offload Target

A dashboard runs a 9-second aggregation over 200M order rows every time a manager opens it, and it is starving OLTP checkout latency on the primary. The report tolerates data that is a few seconds stale. What is the best move?

Recap: Offloading Reads with Logical Replication

You learned how to protect primary OLTP latency by routing heavy reporting traffic to a purpose-built logical replica.

  • Logical over physical when the analytics node needs its own indexes, columns, or rollups.
  • Publish exactly the tables (and rows/columns) reports need with a PUBLICATION; consume them with a SUBSCRIPTION.
  • Add analytic-only indexes and materialized views on the subscriber so the primary never pays for reporting structures.
  • Route reads via pools/proxies and a constrained read-only role with statement timeouts.
  • Send only stale-tolerant, aggregate reads to the replica; keep read-after-write on the primary.
  • Monitor replication slot lag — a stalled subscriber can fill the primary's disk.

The result: dashboards get fast, dedicated infrastructure, and your transactional SLAs stay safe.

자주 묻는 질문

“읽기 및 분석 작업 오프로딩” 강의는 무료인가요?

네 — “읽기 및 분석 작업 오프로딩” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“읽기 및 분석 작업 오프로딩”에서 뭘 배우나요?

기본 OLTP 지연 시간을 보호하도록 대규모 보고 트래픽을 논리적 복제본으로 전달하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“읽기 및 분석 작업 오프로딩” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 발행, 구독과 복제 식별자
  2. 읽기 및 분석 작업 오프로딩
  3. 다운타임에 가까운 수준으로 줄인 메이저 버전 업그레이드
  4. 복제 지연과 슬롯 팽창 모니터링
← PostgreSQL Performance & Query Optimization(으)로 돌아가기