Selective Row-Level Filtering in PostgreSQL Logical Replication

By /

Imagine a scenario:

You are a DBA at a large-scale e-commerce company. Your primary database has
grown to 10 million rows of order data spanning for several years. The business team
now wants a dedicated reporting server for their analytics dashboards — but here is
the catch: their reports only query the last 50,000 active records.
Your first instinct is to set up standard logical replication — and it works. But soon you
realize you have just pushed all the 10 million records to the reporting server —
consuming enormous network bandwidth, filling up disk space rapidly, and making the
initial sync take hours. The reporting team only needed 50,000 rows. You transferred
10,000,000. That is 200x more data than needed.

Then, you start asking this right question to yourself: “Can PostgreSQL replicate ONLY
the rows I actually wanted?” The answer is Yes — and we can achieve this with Selective
Row Filtering. Instead of sending every single row across the replication stream, PostgreSQL
allows you to attach a WHERE clause directly to the publication definition — and only rows
matching that condition will ever leave the publisher. The remaining rows stay exactly where
they are.

In this blog, I will walk you through the exact steps I followed in my lab — from inserting
bulk data, setting up filtered publications, recreating subscriptions, and verifying that
only the right rows reached the subscriber. Every command and every output shown
here is tested in my lab environment.

What is Selective Row Filtering:

Selective Row Filtering in PostgreSQL Logical Replication (supported in Pg 16 or later) allows
you to replicate only a subset of rows from a table — instead of copying all rows. This is done by adding a WHERE clause to the publication definition.

Understanding the environment:

Before setting up row filtering, the following was already in place from the previous
logical replication setup:

The Publisher node(192.168.21.108) runs with PG-16 and the
subscriber(192.168.21.119) runs with PG-18 both fully support this feature.

ComponentsDescription
DatabaseCreate inventory_db (on both Publisher & Subscriber)
SchemasCreate warehouse, procurement schemas under inventory_db(on both Publisher & Subscriber)
Tables on (Publisher)Created 10 tables — 5  tables per each schema
Tables on (Subscriber)6 tables — 3 per each schema (replicated by previous logical replication)
Initial Data3 rows per each table already inserted with explicit IDs 1, 2, 3
Old Publicationinventory_pub — replicated all rows from the table(no filter)
Old Subscriptioninventory_sub — already created on Subscriber for previous logical replication

Part 1: On Publisher Node — Insert Bulk Data

All steps in this section run on the Publisher node (192.168.21.108) running
PostgreSQL 16.

Step 1 : Insert 100 Bulk Records into All 6 Tables

Use generate_series() to insert 100 records (IDs 4 to 103) into each of the 6 tables on
the publisher. This gives a total of 103 records per table (3 initial + 100 bulk), out of
which only the first 50 will be replicated.

Inserting the data in all six tables:

warehouse.products:

INSERT INTO warehouse.products (product_name, category, quantity, location)
SELECT
'Product_' || i,
CASE WHEN i % 3 = 0 THEN 'Electronics'
WHEN i % 3 = 1 THEN 'Furniture'
ELSE 'Clothing' END,
(i * 7) % 200,
'Zone-' || chr(65 + (i % 5))
FROM generate_series(4, 103) i;
-- Output: INSERT 0 100

warehouse.inventory

INSERT INTO warehouse.inventory (product_id, stock_level, last_updated,
warehouse_zone)
SELECT 
i,
(i * 13) % 300,
NOW(),
'Zone-' || chr(65 + (i % 5))
FROM generate_series(4, 103) i;
-- Output: INSERT 0 100

warehouse.shipments

INSERT INTO warehouse.shipments (product_id, quantity, shipment_date,
destination)
SELECT
i,
(i * 3) % 50,
CURRENT_DATE - (i % 30),
CASE WHEN i % 4 = 0 THEN 'Hyderabad'
WHEN i % 4 = 1 THEN 'Mumbai'
WHEN i % 4 = 2 THEN 'Delhi'
ELSE 'Chennai' END
FROM generate_series(4, 103) i;
-- Output: INSERT 0 100

procurement.suppliers

INSERT INTO procurement.suppliers (supplier_name, contact_email, phone,
country)
SELECT
'Supplier' || i,
'supplier' || i ||'@email.com',
'98765' || lpad(i::text, 5, '0'),
CASE WHEN i % 3 = 0 THEN 'India'
WHEN i % 3 = 1 THEN 'USA'
ELSE 'UK' END
FROM generate_series(4, 103) i;
-- Output: INSERT 0 100

procurement.purchase_orders

INSERT INTO procurement.purchase_orders
(supplier_id, order_date, delivery_date, total_amount)
SELECT
(i % 103) + 1,
CURRENT_DATE - (i % 60),
CURRENT_DATE + (i % 30),
(i * 1000.50) % 100000
FROM generate_series(4, 103) i;
-- Output: INSERT 0 100

procurement.order_items

INSERT INTO procurement.order_items
(order_id, product_name, quantity, unit_price)
SELECT
(i % 103) + 1,
Item'|| i,
(i % 50) + 1,
(i * 150.75) % 10000
FROM generate_series(4, 103) i;
-- Output: INSERT 0 100

Step 2: Verify Total Record Count on Publisher:

Confirm that all 6 tables now have 103 rows each before setting up selective replication.

postgres=# \c inventory_db
You are now connected to database "inventory_db" as user "postgres".
inventory_db=# SELECT COUNT(*) FROM warehouse.products;
 count
-------
   103
(1 row)

inventory_db=# SELECT COUNT(*) FROM warehouse.inventory;
 count
-------
   103
(1 row)

inventory_db=# SELECT COUNT(*) FROM warehouse.shipments;
 count
-------
   103
(1 row)

inventory_db=# SELECT COUNT(*) FROM procurement.suppliers;
 count
-------
   103
(1 row)

inventory_db=# SELECT COUNT(*) FROM procurement.purchase_orders;
 count
-------
   103
(1 row)

inventory_db=# SELECT COUNT(*) FROM procurement.order_items;
 count
-------
   103
(1 row)

PART 2: Publisher Node — Create Publication with Row Filter

Step 3: Drop Existing Publication:

The old publication (inventory_pub) replicated all rows without any filter. Drop it first so
we can recreate it with row-level WHERE conditions.

DROP PUBLICATION inventory_pub;
-- Output: DROP PUBLICATION

Step 4: Create New Publication with Row Filters:

Recreate the publication with a WHERE clause on each table. Only rows satisfying the
condition (primary key <= 50) will be replicated to any subscriber of this publication.

CREATE PUBLICATION inventory_pub FOR TABLE
warehouse.products WHERE (product_id <= 50),
warehouse.inventory WHERE (inventory_id <= 50),
warehouse.shipments WHERE (shipment_id <= 50),
procurement.suppliers WHERE (supplier_id <= 50),
procurement.purchase_orders WHERE (order_id <=  50),
procurement.order_items WHERE (item_id <= 50);
-- Output: CREATE PUBLICATION

Step 5: Verify Publication with Row Filters:

Use the \dRp+ meta-command to inspect the publication and confirm WHERE
conditions are correctly attached to each table.

\dRp+

-- Output:
Publication inventory_pub
Owner | All tables | Inserts | Updates | Deletes | Truncates
--------+------------+---------+---------+---------+----------
postgres| f | t | t | t | t

Tables:
procurement.order_items WHERE (item_id <= 50)
procurement.purchase_orders WHERE (order_id <= 50)
procurement.suppliers WHERE (supplier_id <= 50)
warehouse.inventory WHERE (inventory_id <= 50)
warehouse.products WHERE (product_id <= 50)
warehouse.shipments WHERE (shipment_id <= 50)

PART 3: Subscriber Node — Recreate Subscription

All steps in this section run on the Subscriber node (192.168.21.119) running with
PostgreSQL 18.

Step 6: Drop Existing Subscription on Subscriber:

The old subscription (inventory_sub) was synced with all 103 rows. Drop it first to start
fresh with the new filtered publication.

\c inventory_db
DROP SUBSCRIPTION inventory_sub;
-- Output:
NOTICE: dropped replication slot "inventory_sub" on publisher
DROP SUBSCRIPTION

Step 7: Truncate All Tables on Subscriber:

The subscriber tables still contain 103 rows from the previous full replication. Truncate
them to start with empty tables before creating the new filtered subscription.

TRUNCATE warehouse.products, warehouse.inventory, warehouse.shipments,
procurement.suppliers, procurement.purchase_orders,
procurement.order_items;
-- Output: TRUNCATE TABLE
inventory_db=# SELECT 'warehouse.products' AS table_name, COUNT(*) FROM warehouse.products
UNION ALL
SELECT 'warehouse.inventory', COUNT(*) FROM warehouse.inventory
UNION ALL
SELECT 'warehouse.shipments', COUNT(*) FROM warehouse.shipments
UNION ALL
SELECT 'procurement.suppliers', COUNT(*) FROM procurement.suppliers
UNION ALL
SELECT 'procurement.purchase_orders', COUNT(*) FROM procurement.purchase_orders
UNION ALL
SELECT 'procurement.order_items', COUNT(*) FROM procurement.order_items;
         table_name          | count
-----------------------------+-------
 warehouse.products          |     0
 warehouse.inventory         |     0
 warehouse.shipments         |     0
 procurement.suppliers       |     0
 procurement.purchase_orders |     0
 procurement.order_items     |     0
(6 rows)

Why, Truncate Before Re-subscribing? The subscriber already has 103 rows from the previous
subscription. If the entire data is not needed on the subscriber, truncating the table ensures starting from a clean slate so that only the filtered 50 rows are present after the new sync.

Step 8: Create New Subscription

Create a new subscription pointing to the updated publication (inventory_pub) which
now has row filters. PostgreSQL will automatically sync only the rows matching the
WHERE condition from each table.

CREATE SUBSCRIPTION inventory_sub
CONNECTION 'host=192.168.21.108 port=5432 dbname=inventory_db
user=replicator password=replicator123'
PUBLICATION inventory_pub;

-- Output:
NOTICE: created replication slot "inventory_sub" on publisher
CREATE SUBSCRIPTION

A new replication slot ‘inventory_sub’ is created on the publisher. PostgreSQL now starts the
initial table sync, copying only the filtered rows (id <= 50) from each table. The row filter is
enforced during both the initial sync AND all subsequent DML changes. If a new row is inserted
on the publisher with ID > 50, it will NOT be replicated.

PART 4: Verification — Confirm Only 50 Rows Replicated

Step 9: Verify Row Count on Subscriber:

Check the row count in all 6 tables on the subscriber. Each table should have exactly 50
rows — confirming that the WHERE filter worked correctly.

SELECT COUNT(*) FROM warehouse.products;
SELECT COUNT(*) FROM warehouse.inventory;
SELECT COUNT(*) FROM warehouse.shipments;
SELECT COUNT(*) FROM procurement.suppliers;
SELECT COUNT(*) FROM procurement.purchase_orders;
SELECT COUNT(*) FROM procurement.order_items;

-- Expected Output (each table): 50 rows
inventory_db=# SELECT COUNT(*) FROM warehouse.products;
 count
-------
    50
(1 row)

inventory_db=# SELECT COUNT(*) FROM warehouse.inventory;
 count
-------
    50
(1 row)

inventory_db=# SELECT COUNT(*) FROM warehouse.shipments;
 count
-------
    50
(1 row)

inventory_db=# SELECT COUNT(*) FROM procurement.suppliers;
 count
-------
    50
(1 row)

inventory_db=# SELECT COUNT(*) FROM procurement.purchase_orders;
 count
-------
    50
(1 row)

inventory_db=# SELECT COUNT(*) FROM procurement.order_items;
 count
-------
    50
(1 row)

The count of 50 (not 103) in every table confirms that row filtering is working correctly. The
publisher still has 103 rows — row filtering does NOT delete or modify data on the publisher. It
only controls what gets sent to the subscriber over the replication stream.

Conclusion:

Selective Row Filtering is (supported in Pg 16 and above) one of the most useful enhancements to PostgreSQL Logical Replication. By attaching WHERE clauses directly to publication definitions, DBAs can drastically reduce replication traffic, subscriber storage requirements, and synchronization times. In environments where reporting, analytics, or regional systems require only a subset of data, row filtering provides an efficient and scalable replication strategy without requiring additional ETL processes.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top