PG18 Hacktober: 31 Days of New Features: pg_logicalinspect Logical Replication Insights

By /

Welcome to Day 25 of the PG18 Hacktober

PostgreSQL continues to evolve with powerful extensions that help developers and DBAs gain deeper insights into database internals.
One such valuable addition is the pg_logicalinspection extension — introduced to simplify logical replication troubleshooting and inspection.

Contrib modules: Small extensions, big impact

PostgreSQL’s contrib ecosystem brings modular power to the core database. These official extensions are:

  • Well-vetted
  • Maintainable
  • Version-compatible
  • Feature-rich

pg_logicalinspect joins the ranks of pg_stat_statements, pg_buffercache, and pg_partman, making life easier for DBAs and replication engineers alike.

This extension provides functions that let you peek inside logical replication slots, inspect replication messages, and analyze the contents of WAL (Write-Ahead Log) in a user-friendly SQL interface.
It’s especially useful for diagnosing replication lag, testing logical decoding plugins, or simply understanding what kind of changes are being streamed through a slot, without actually consuming the changes.

Key features include:

  • Peek at changes in a logical replication slot without advancing the slot
  • Inspect transaction boundaries and catalog changes
  • Use multiple output plugins like test_decoding or pgoutput
  • Lightweight SQL interface for replication debugging

Understanding Logical Snapshots

A snapshot is PostgreSQL’s way of capturing a consistent view of all committed transactions at a point in time.

  • When you create a logical replication slot, PostgreSQL creates a snapshot and records the starting WAL position.
  • Snapshots ensure that decoding sees only committed, consistent changes.
  • The snapshot acts as a baseline for pg_logicalinspection, ensuring changes are decoded consistently.

You can inspect them using:

SELECT * FROM pg_ls_logicalsnapdir();
SELECT * FROM pg_get_logical_snapshot_meta('0-40796E18.snap');
SELECT * FROM pg_get_logical_snapshot_info('0-40796E18.snap');
kdb=# SELECT * FROM pg_ls_logicalsnapdir();
      name      | size |      modification
----------------+------+------------------------
 0-2022AA0.snap |  144 | 2025-10-24 12:12:25+00
 0-2029040.snap |  144 | 2025-10-25 07:06:23+00
 0-2029098.snap |  144 | 2025-10-25 07:07:32+00
 0-202F3B8.snap |  144 | 2025-10-25 07:07:35+00
 0-202F410.snap |  144 | 2025-10-25 07:12:32+00
 0-202F4C0.snap |  144 | 2025-10-25 07:12:41+00
(6 rows)

kdb=#
kdb=#
kdb=# SELECT * FROM pg_get_logical_snapshot_meta('0-2022AA0.snap');
   magic    | checksum | version
------------+----------+---------
 1369563137 | 55721347 |       6
(1 row)

kdb=#
kdb=# SELECT * FROM pg_get_logical_snapshot_info('0-2022AA0.snap');
-[ RECORD 1 ]------------+-----------
state                    | consistent
xmin                     | 782
xmax                     | 782
start_decoding_at        | 0/1C81C40
two_phase_at             | 0/0
initial_xmin_horizon     | 0
building_full_snapshot   | f
in_slot_creation         | f
last_serialized_snapshot | 0/20229F0
next_phase_at            | 0
committed_count          | 0
committed_xip            |
catchange_count          | 0
catchange_xip            |

kdb=#

Test Cases: Using pg_logicalinspection

1. Basic DML Changes

kdb=# CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department TEXT
);
CREATE TABLE
kdb=#
kdb=# SELECT * FROM pg_create_logical_replication_slot('emp_slot', 'test_decoding');
-[ RECORD 1 ]--------
slot_name | emp_slot
lsn       | 0/206A0A0

kdb=#
kdb=# INSERT INTO employees (name, department) VALUES ('John', 'HR');
INSERT 0 1
kdb=# UPDATE employees SET department = 'Finance' WHERE name = 'John';
UPDATE 1
kdb=# DELETE FROM employees WHERE name = 'John';
DELETE 1
kdb=#
kdb=# SELECT * FROM pg_logical_slot_peek_changes('emp_slot', NULL, NULL);
    lsn    | xid |                                            data
-----------+-----+--------------------------------------------------------------------------------------------
 0/206A0A0 | 784 | BEGIN 784
 0/206A140 | 784 | table public.employees: INSERT: id[integer]:1 name[text]:'John' department[text]:'HR'
 0/206A258 | 784 | COMMIT 784
 0/206A258 | 785 | BEGIN 785
 0/206A258 | 785 | table public.employees: UPDATE: id[integer]:1 name[text]:'John' department[text]:'Finance'
 0/206A2E0 | 785 | COMMIT 785
 0/206A318 | 786 | BEGIN 786
 0/206A318 | 786 | table public.employees: DELETE: id[integer]:1
 0/206A388 | 786 | COMMIT 786
(9 rows)

kdb=#

2. Multi-Transaction Batch

kdb=# BEGIN;
INSERT INTO employees (name, department) VALUES ('Alice', 'IT');
INSERT INTO employees (name, department) VALUES ('Bob', 'Marketing');
COMMIT;
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
kdb=#
kdb=# BEGIN;
UPDATE employees SET department = 'R&D' WHERE name = 'Alice';
DELETE FROM employees WHERE name = 'Bob';
COMMIT;
BEGIN
UPDATE 1
DELETE 1
COMMIT
kdb=# SELECT * FROM pg_logical_slot_peek_changes('emp_slot', NULL, NULL);
    lsn    | xid |                                            data
-----------+-----+---------------------------------------------------------------------------------------------
 0/206A0A0 | 784 | BEGIN 784
 0/206A140 | 784 | table public.employees: INSERT: id[integer]:1 name[text]:'John' department[text]:'HR'
 0/206A258 | 784 | COMMIT 784
 0/206A258 | 785 | BEGIN 785
 0/206A258 | 785 | table public.employees: UPDATE: id[integer]:1 name[text]:'John' department[text]:'Finance'
 0/206A2E0 | 785 | COMMIT 785
 0/206A318 | 786 | BEGIN 786
 0/206A318 | 786 | table public.employees: DELETE: id[integer]:1
 0/206A388 | 786 | COMMIT 786
 0/206A500 | 787 | BEGIN 787
 0/206A568 | 787 | table public.employees: INSERT: id[integer]:2 name[text]:'Alice' department[text]:'IT'
 0/206A6E0 | 787 | table public.employees: INSERT: id[integer]:3 name[text]:'Bob' department[text]:'Marketing'
 0/206A7A0 | 787 | COMMIT 787
 0/206A7D8 | 788 | BEGIN 788
 0/206A7D8 | 788 | table public.employees: UPDATE: id[integer]:2 name[text]:'Alice' department[text]:'R&D'
 0/206A828 | 788 | table public.employees: DELETE: id[integer]:3
 0/206A898 | 788 | COMMIT 788
(17 rows)

kdb=#

Multiple transactions are decoded while maintaining snapshot consistency.

Do
SELECT * FROM pg_logical_slot_get_changes('emp_slot', NULL, NULL);
get_changes moves it forward. It consumes changes

3. TRUNCATE and Sequence Events

kdb=# TRUNCATE TABLE employees;
TRUNCATE TABLE
kdb=#
kdb=# SELECT nextval('employees_id_seq');
 nextval
---------
       4
(1 row)
kdb=# SELECT * FROM pg_logical_slot_peek_changes('emp_slot', NULL, NULL);
    lsn    | xid |                     data
-----------+-----+----------------------------------------------
 0/206A9D8 | 789 | BEGIN 789
 0/2076390 | 789 | table public.employees: TRUNCATE: (no-flags)
 0/2076530 | 789 | COMMIT 789
(3 rows)

Logical decoding can detect TRUNCATE, sequence usage, and schema modifications depending on the plugin.

4. Snapshot Awareness

kdb=# SELECT ss.name, info.*
FROM pg_ls_logicalsnapdir() AS ss,
     pg_get_logical_snapshot_info(ss.name) AS info;
      name      |   state    | xmin | xmax | start_decoding_at | two_phase_at | initial_xmin_horizon | building_full_snapshot | in_slot_creation | last_seri
alized_snapshot | next_phase_at | committed_count | committed_xip | catchange_count | catchange_xip
----------------+------------+------+------+-------------------+--------------+----------------------+------------------------+------------------+----------
----------------+---------------+-----------------+---------------+-----------------+---------------
 0-206A418.snap | consistent |  787 |  784 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/206A3E0
                |             0 |               0 |               |               0 |
 0-206A4C8.snap | consistent |  787 |  784 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/206A418
                |             0 |               0 |               |               0 |
 0-206A8F0.snap | consistent |  789 |  784 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/206A898
                |             0 |               0 |               |               0 |
 0-206A9A0.snap | consistent |  789 |  784 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/206A8F0
                |             0 |               0 |               |               0 |
 0-2076530.snap | consistent |  789 |  790 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/206A9A0
                |             0 |               1 | {789}         |               0 |
 0-2076690.snap | consistent |  791 |  790 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/2076658
                |             0 |               0 |               |               0 |
 0-2076778.snap | consistent |  791 |  790 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/2076740
                |             0 |               0 |               |               0 |
 0-2076818.snap | consistent |  791 |  790 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/2076778
                |             0 |               0 |               |               0 |
 0-2076970.snap | consistent |  791 |  790 | 0/206A140         | 0/0          |                    0 | f                      | f                | 0/2076818
                |             0 |               0 |               |               0 |

Only transactions after the snapshot are decoded. Pre-existing data before slot creation is ignored.

Best Practices

  • Use non-production slots for testing to avoid affecting live replication.
  • Limit peeked changes on busy systems to reduce I/O overhead.
  • Understand plugin differences (test_decoding vs. pgoutput).
  • Combine with monitoring (pg_stat_replication) for holistic insight.
  • Check snapshot consistency when debugging replication or WAL issues.

Advantages

  • Non-destructive inspection of replication slots
  • Safe and SQL-accessible without custom clients
  • Supports multiple output plugins for flexibility
  • Deep insight into transactions and schema changes
  • Helps debug replication lag and slot issues

Snapshot and Logical Inspection Lifecycle

  1. Snapshot Creation – Occurs at slot creation. Freezes visible transactions.
  2. WAL Generation -Changes after snapshot are recorded.
  3. Inspection – pg_logicalinspection peeks at WAL changes without moving slot.
  4. Consumption (Optional) – Logical replication clients or get_changes advance slot pointer.

Conclusion

The pg_logicalinspection extension, together with PostgreSQL’s logical snapshots, empowers DBAs and developers to safely observe and debug logical replication streams without disrupting active replication.

By leveraging these tools, you gain the ability to:

  • Precisely track which transactions are visible at the start of decoding
  • Ensure replication slot consistency before consuming WAL changes
  • Diagnose replication issues end-to-end, from DML operations to slot advancement

What’s next?

PG18Hacktober will continue to throw light on one of PostgreSQL 18’s most underrated yet powerful extensions: pg_overexplain.

Don’t miss it, your EXPLAIN just leveled up!

Leave a Comment

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

Scroll to Top