Quick Decision Guide

pglogical is a PostgreSQL extension that provides logical replication with more features than built-in logical replication — including DDL replication, conflict handling, and row/column filtering.

Architecture Overview

Installation pglogical

pglogical Setup — Step by Step

A replication set defines which tables and sequences to replicate:

-- Create a named replication set
SELECT pglogical.create_replication_set(
set_name := 'my_replication_set',
replicate_insert := true,
replicate_update := true,
replicate_delete := true,
replicate_truncate := true
);

-- Add specific tables to the set
SELECT pglogical.replication_set_add_table(
set_name := 'my_replication_set',
relation := 'public.orders',
synchronize_data := true -- Initial data copy
);

SELECT pglogical.replication_set_add_table('my_replication_set', 'public.customers', true);
SELECT pglogical.replication_set_add_table('my_replication_set', 'public.products', true);

-- Add ALL tables in a schema at once
SELECT pglogical.replication_set_add_all_tables(
set_name := 'my_replication_set',
schema_names := ARRAY['public'],
synchronize_data := true
);

-- Add sequences
SELECT pglogical.replication_set_add_sequence(
set_name := 'my_replication_set',
relation := 'public.orders_id_seq',
synchronize_data := true
);

-- Add ALL sequences
SELECT pglogical.replication_set_add_all_sequences('my_replication_set', ARRAY['public'], true);

Failover is the process of promoting a replica to become the new primary when the original primary fails. Here's a full breakdown covering manual, automated, and pglogical-aware failover.

Zero-RPO PostgreSQL Configuration — Complete Guide
RPO (Recovery Point Objective) = maximum acceptable data loss measured in time. Zero-RPO means zero data loss — every committed transaction must survive a primary failure.

The Zero-RPO Stack

Sequential Scans (Seq Scan): Reading the entire table row by row. This is generally inefficient for large tables, especially when filtering on specific columns. Indicates a lack of suitable indexes or that the planner determined an index scan would be more expensive.

Index Scans (Index Scan, Index Only Scan): Using an index to locate rows. Index Scan requires fetching the actual row data from the table after using the index, while Index Only Scan can retrieve all necessary data directly from the index itself (highly efficient).

Bitmap Scans (Bitmap Index Scan, Bitmap Heap Scan): Used when multiple indexes might apply to a query. The database creates bitmaps representing rows matching each index condition and then combines these bitmaps before fetching the rows from the table. Can be useful when combining multiple filter conditions.

Cost: The estimated cost of each operation. The planner uses a cost model to estimate the resources required to perform each operation. Higher costs usually indicate potential bottlenecks.

Rows: The estimated number of rows returned by each operation. Significant discrepancies between estimated and actual rows (observed with EXPLAIN ANALYZE) can indicate inaccurate statistics, leading the planner to choose a suboptimal plan.