DEV Community: Alex Zhdankov

The Hidden Complexity of PostgreSQL Logical Replication

Alex Zhdankov — Thu, 23 Jul 2026 11:31:27 +0000

We were building a PostgreSQL Logical Replication monitoring system — topology visualization, lag alerts, health checks — across PostgreSQL 10 through 17. The problem we kept running into wasn't missing documentation. It was that logical replication metadata is fundamentally decentralized: the publisher knows its publications, the subscriber knows its subscriptions, and neither side has the full picture. Everything we needed to build — topology, lag monitoring, health classification — had to be assembled from fragments spread across machines that don't know about each other.

The rest of this article explores ten consequences of that architectural split.

This article assumes familiarity with PostgreSQL logical replication basics: publications, subscriptions, and replication slots.

Architecture: who knows what

This diagram is the foundation for everything that follows. The architectural lessons that follow are consequences of this split.

PUBLISHER                          SUBSCRIBER
─────────────────────────────      ─────────────────────────────
pg_publication                     pg_subscription
  └─ what is published               └─ where to connect
                                       └─ what to subscribe to

pg_replication_slots               pg_subscription_rel
  └─ how much WAL is retained        └─ sync progress per table

pg_stat_replication                pg_stat_subscription
  └─ write_lag                       └─ apply worker pid
  └─ flush_lag                       └─ last_msg_receipt_time
  └─ replay_lag                      └─ (no lag fields)
  └─ who is connected right now

pg_stat_replication                (no equivalent on subscriber)
  └─ subscriber is visible only
     while connected

Lag lives only on the publisher. The subscriber list is stored nowhere on the publisher. WAL slot metrics are unavailable on the subscriber. These facts only surface when you try to build something on top of replication.

Part I — Architectural consequences

These aren't quirks. They're deliberate design decisions with implications that only become visible when you're building on top of replication.

1. The publisher doesn't know its subscribers — and that's intentional

It seems natural to ask the publisher "who is subscribed to you." But pg_publication contains no information about subscribers. pg_stat_replication only shows actively connected WAL senders — disabled and offline subscriptions never appear there.

This is a direct consequence of how PostgreSQL models replication. A subscription is a subscriber-side object. A publication is a publisher-side object. The publisher streams WAL to whoever connects, but maintains no registry of subscribers — the publisher must remain independent of the number and composition of its subscribers. Scalability is achieved precisely through this asymmetry.

To display the subscriber list for a publication, you have to query every other instance:

-- Run on EACH subscriber:
SELECT subname FROM pg_subscription WHERE 'pub_name' = ANY(subpublications)

In one production installation, a single publication had up to 12 subscribers across different hosts. This required a two-tier strategy: live fan-out for instances managed by the same agent, plus a heartbeat JSONB cache for cross-host subscribers.

Once you realize this, you stop looking for "the query" that returns the topology. There isn't one.

2. Replication lag exists only on the publisher

pg_stat_subscription on the subscriber has no write_lag, flush_lag, or replay_lag fields. All of that lives exclusively in pg_stat_replication on the publisher — as shown in the diagram above.

The reason is architectural: only the WAL sender knows two timestamps — when WAL was sent, and when confirmation arrived from the receiver. The subscriber physically cannot compute its own lag.

If the subscriber is offline, pg_stat_replication is empty and lag is None. You cannot distinguish "subscriber is disconnected" from "lag is critical" using only subscriber-side data.

There's also a parsing problem: depending on the psycopg2 version and interval_style setting, lag intervals arrive as timedelta, float, or a "HH:MM:SS.fff" string. A normalizer that handles all three formats is mandatory.

Alert thresholds that worked in production:

| lag_ms          | Slot wal_status | Subscription state (debounced) | Correlated condition | Alert level                     |
|-----------------|-----------------|--------------------------------|----------------------|---------------------------------|
| < 10 000        | reserved        | running                        | —                    | healthy                         |
| 10 000 – 59 999 | —               | —                              | —                    | warning                         |
| ≥ 60 000        | —               | —                              | —                    | critical                        |
| None            | —               | no_worker < 15s                | —                    | unstable (debounce)             |
| None            | —               | no_worker >= 15s               | —                    | critical (debounce confirmed)   |
| None            | —               | disabled                       | —                    | paused (user action)            |
| None            | active          | disconnected / unstable        | retained_wal >= 5Gb  | critical (disk exhaustion risk) |
| None            | active          | disconnected / unstable        | retained_wal >= 1Gb  | warning (monitor disk)          |
| None            | active          | disconnected / unstable        | retained_wal < 1Gb   | warning (worker down)           |
| None            | lost            | —                              | —                    | critical (blocking)             |

wal_status = lost is not recoverable by restarting anything — see lesson 5. The 30-second grace period on no_worker prevents false alerts during subscription creation, when a worker that's starting and one that's crashed look identical.

3. system_identifier: the only reliable guard against circular replication

PostgreSQL does not prevent CREATE SUBSCRIPTION sub CONNECTION 'host=localhost ...' PUBLICATION pub. The command will succeed and create an infinite loop — WAL will grow exponentially.

Our first instinct was to check the hostname. It didn't work — one instance can have multiple interfaces, IPv4 and IPv6, hostname vs IP, or a VIP that moves during failover.

PostgreSQL already exposes something stronger: pg_control_system() returns system_identifier — a 64-bit fingerprint generated at initdb and immutable for the lifetime of the cluster:

if publisher_sysid == subscriber_sysid:
    raise HTTPError(400, "Cannot subscribe to self")

This only guards against the direct cycle A→A. A multi-hop cycle A→B→C→A requires full graph analysis — we chose to protect only the obvious case. A promoted physical replica inherits the primary's system_identifier, so the check has boundaries in high-availability setups.

4. DROP SUBSCRIPTION hangs when the publisher is unreachable

DROP SUBSCRIPTION automatically calls pg_drop_replication_slot() on the publisher. If the publisher is unreachable, the command hangs waiting for a connect timeout. A user cannot remove a broken subscription at exactly the moment they need to most — during an outage.

The reason is the same philosophy as lesson 5: an abandoned slot accumulates WAL indefinitely, and PostgreSQL avoids making destructive decisions automatically.

The recovery procedure has two steps:

ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET (slot_name = NONE)  -- detach slot reference
DROP SUBSCRIPTION IF EXISTS name                -- no publisher contact needed

The slot becomes an orphan intentionally. We return an explicit instruction: run SELECT pg_drop_replication_slot('slot_name') on the publisher when it comes back.

5. wal_status = lost: the slot is dead, but PostgreSQL won't drop it

The progression: reserved → extended (WAL exceeded max_slot_wal_keep_size) → lost (WAL deleted at the next checkpoint). The transition happens during a normal checkpoint with no default log warning.

Once a slot reaches lost, the apply worker fails on every retry. The slot isn't dropped automatically — PostgreSQL leaves destructive decisions to the DBA.

On PG < 13, wal_status doesn't exist. Detecting WAL loss without manually comparing LSN values is impossible on older versions.

Once a slot becomes lost, monitoring should stop treating it as a performance problem and start treating it as a recovery problem: drop the slot, recreate the subscription with copy_data=true, and accept the data resync.

Part II — Non-obvious catalog behavior

6. pg_stat_subscription has one row per worker, not per subscription

Everything looked correct in testing. Then one customer initialized a subscription with 180 tables — and suddenly our topology page showed every subscription 180 times.

When a subscription is created with copy_data=true, PostgreSQL parallelizes the initial sync by launching a separate tablesync worker per table. All of them appear in pg_stat_subscription alongside the main apply worker:

Apply worker        ← relid IS NULL  (coordinator)
├── TableSync #1   ← relid = 'orders'::regclass
├── TableSync #2   ← relid = 'events'::regclass
└── ...            (one per table)

The bug only reproduced during active sync — by the time we ran tests, the 8-minute initial copy had finished.

The fix is one predicate:

LEFT JOIN pg_stat_subscription srss ON s.oid = srss.subid AND srss.relid IS NULL

Without AND srss.relid IS NULL, any query against subscriptions is semantically incorrect during initial sync.

7. pg_subscription has no state field — you have to compute it

SELECT state FROM pg_subscription — this field doesn't exist in any version of PostgreSQL. It appeared in pg_stat_subscription only in PG 14. On PG 10–13, there's no explicit subscription status anywhere.

The only solution compatible with PG 10+:

CASE
    WHEN NOT s.subenabled   THEN 'disabled'
    WHEN srss.pid IS NULL   THEN 'no_worker'
    ELSE 'running'
END

This can't distinguish "worker is starting" from "worker has crashed" — both produce pid IS NULL, which is why the grace period from lesson 2 matters.

8. Version compatibility isn't a maintenance problem — it's an architectural constraint

Practically every major PostgreSQL version doesn't just add new fields. It changes existing ones. Passing an unknown argument to CREATE SUBSCRIPTION WITH (...) on an older version is an error, not a no-op. The substream column changed from bool to char between PG 15 and PG 16 — same column name, same query, different Python type on the other side. A parser treating the result as boolean silently misreads every value on PG 16+.

Supporting multiple PostgreSQL versions becomes a code generation problem rather than a SQL problem:

def get_subscriptions_query(server_version: int) -> str:
    if server_version >= 160000:
        streaming_expr = "CASE s.substream WHEN 'f' THEN 'off' WHEN 't' THEN 'on' WHEN 'p' THEN 'parallel' END"
    elif server_version >= 140000:
        streaming_expr = "CASE WHEN s.substream THEN 'on' ELSE 'off' END"
    else:
        streaming_expr = "'off'"
    # ... rest of query

def get_create_logical_slot_query(server_version: int) -> str:
    if server_version >= 170000: return "... %(failover)s)"
    if server_version >= 150000: return "... %(twophase)s)"
    return "... %(temporary)s)"

The same pattern applies to subscription DDL, slot DDL, and pg_subscription SELECT. One codebase, PG 10–17.

Part III — Where our assumptions broke

The previous lessons describe PostgreSQL's architecture. The next two describe our own mistakes — both caused by misreading that architecture.

9. Every subscription showed disconnected

All subscriptions in the topology graph showed a disconnected icon. Replication was working normally. Tests were green..

Inside build_topology(), we were classifying subscriptions using classify_lag_status(lag_ms). But as the diagram at the top shows, lag fields live in pg_stat_replication, not pg_stat_subscription. For subscriptions, lag_ms is always None. The function returned disconnected for any None input.

In tests, we verified topology against static fixtures with manually set lag values. In production, data came from real PostgreSQL.

The fix: for subscriptions, use classify_subscription_state(state)["status"] — the source of truth is pid in pg_stat_subscription, not the WAL sender. Tests against static fixtures can't catch this kind of bug — it only surfaces with a real PostgreSQL connection.

10. Fallback code needs its own tests

In check_prerequisites, we use a PL/pgSQL batch function. If unavailable, a Python fallback runs. The fallback referenced t.get('has_pk', False), but the actual field name is has_primary_key. Result: every table got a "no primary key" warning.

The fallback triggered on the very first prerequisites run, before the PL/pgSQL function existed. The fix was one word.

The interesting part isn't the bug — it's that fallback branches don't break the main flow, so they pass code review and test suites. They're needed in exactly the scenarios where nobody is watching. Any degraded path requires its own test with explicit mocking of the primary path.

Conclusion

Monitoring logical replication turned out to be less about querying PostgreSQL and more about accepting a fundamental constraint: the metadata you need is decentralized by design. Lag is on the publisher because only the WAL sender knows when data was sent. Subscribers aren't tracked on the publisher because tracking them would create coupling. wal_status = lost doesn't auto-delete because PostgreSQL doesn't make destructive decisions unilaterally.

Once you accept that the picture has to be assembled rather than retrieved, PostgreSQL's behavior stops looking inconsistent. Every piece of metadata lives exactly where it belongs. The monitor's job is simply to put the pieces back together.

And yes — test your fallback paths.

Why we built a durable task runtime without a broker

Alex Zhdankov — Tue, 09 Jun 2026 13:26:45 +0000

Background tasks look trivial - until they must survive process restarts, support delayed execution, and run without external infrastructure.

We needed scheduled maintenance inside a PostgreSQL management agent: VACUUM jobs, health checks, heartbeat tasks, retention cleanup, delayed operations.

Celery was the obvious answer. We didn't use it.

Not because Celery is bad. Because the operational model was wrong for our constraints.

The agent is installed directly on database hosts. Sometimes inside isolated customer environments. Without Kubernetes. Without managed infrastructure. Without external services we control.

At that point, background jobs stopped being a helper library problem. They became persistence, supervision, and lifecycle management inside a single Python process.

The constraint that changed everything

The agent is a single Python process running on the database host.

We already used Redis elsewhere in the platform for the browser‑based psql terminal. Using it as a task broker sounded convenient.

Operationally, it was the wrong fit.

Terminal traffic optimises for low‑latency streaming. Task scheduling optimises for durable persistence. Mixing them would create contention.
Worse: Redis persistence is optional. If Redis restarts, queued tasks disappear. That trade‑off is acceptable for transient terminal streams. It is much harder to justify for scheduled maintenance.

RabbitMQ was even harder to justify - a dedicated distributed broker whose only purpose was “run tasks on the same machine that already runs the agent”.

The requirements became clear:

runs fully in‑process
survives restarts with task state intact
supports scheduled future + periodic execution
supports cancellation and recovery
zero external infrastructure

The incident that changed the design

The original implementation used an in‑memory queue. It worked perfectly in development.

Then one agent restarted during a maintenance window. The worker process disappeared. Queued follow‑up tasks disappeared with it. A VACUUM scheduled for 3 AM simply never ran. No error. No log. Just silence.

At that point: task execution and task state cannot live only in memory.

Scheduled operations needed to survive agent restarts, package upgrades, partial failures, unexpected termination. The scheduler became persistence‑first.

Why not cron? Why not systemd timers?

Cron solves static scheduling. We needed runtime orchestration: tasks created dynamically via API, deduplication, cancellation, rescheduling, execution status tracking, reattachment after restart. Cron has no concept of task ownership. Coordinating “run VACUUM every 6 hours unless one is already running” becomes awkward fast.

Systemd timers solve some lifecycle problems but split orchestration across two independent runtimes (the application and the OS). The scheduler needed direct access to database metadata, agent runtime state, worker registration, internal APIs. One ownership boundary: the agent owns the tasks.

The architecture

Three independent processes. Two queues. One Unix socket.

API caller
    │
    │ Unix socket
    ▼
Scheduler process
    │
    ├── task_queue ──► WorkerPool process
    │
    └── event_queue ◄─ WorkerPool process

The important framing line is this:

The scheduler is really two independent systems:
a durable state machine and a subprocess supervisor.

Task state and task execution are intentionally separated.

Scheduler owns persistence, timing, deduplication, lifecycle state.
WorkerPool owns subprocess execution, signals, supervision, cancellation.

They never share memory - only queues and socket messages.

That separation turned out to matter operationally: if the WorkerPool crashes, the Scheduler continues running. If the Scheduler restarts, tasks survive on disk.

Task lifecycle

The state machine is explicit:
DEFAULT → SCHEDULED → QUEUED → DOING → DONE, with branches to FAILED, ABORTED, CANCELED.

Status is a bitmask. Recovery logic collapsed into a single SQLite predicate:

WHERE status & (DOING | QUEUED)

One integer column. No joins. No OR chains. That’s not clever implementation — that’s operational simplicity when you have to reconstruct state after a crash.

How the scheduler actually works

The Scheduler runs a select() loop over two file descriptors: the Unix socket (incoming requests) and the WorkerPool event queue (status updates). Every second, it also runs schedule(): find runnable tasks, reschedule periodic ones, purge expired history.

The key insight: the Scheduler never executes work. It only orchestrates lifecycle transitions. Execution happens inside isolated subprocesses.

Process isolation gave us a clean guarantee: if a worker misbehaves (hangs, leaks memory, deadlocks), the scheduler can terminate it unconditionally. Threads would have shared the failure domain. That guarantee mattered more than raw efficiency.

Persistence and recovery (the SQLite moment)

Task state lives entirely in SQLite. On startup, recovery runs:

Any task that was DOING or QUEUED during shutdown becomes ABORTED. Future scheduled tasks remain intact.

A VACUUM scheduled for 3 AM still runs after a midnight restart.

SQLite here is not a database. It is a durable state machine log - exactly the same pattern we used for our metrics buffer. That realisation unified our mental model across the entire agent.

Deterministic deduplication

Duplicate submissions (retries, double‑clicks, race conditions) should not create duplicate execution.

Task IDs are generated deterministically from the operation signature: database:schema:table:hour:operation_type. The second submission resolves to the same identifier and is rejected immediately. One simple mechanism eliminated an entire class of coordination bugs.

What this system intentionally does not have

This is not Celery. No distributed routing, no broker federation, no result backend, no retry orchestration, no monitoring UI, no horizontal scaling.

For a single‑host agent, what we needed instead was:

durable local scheduling
subprocess isolation
restart recovery
periodic execution
deterministic deduplication
bounded orchestration complexity

The entire scheduler fits in roughly 400 lines of standard library Python.

The mental model

Unix socket = scheduler API boundary
Scheduler = durable task state machine
WorkerPool = subprocess supervisor
SQLite = local orchestration persistence

You could call it a local control plane - a miniature of what the larger platform does, but inside a single host boundary.

The interesting realisation was this:

Once tasks must survive crashes and restarts, background jobs stop being a threading problem. They become a persistence problem, a lifecycle problem, and a process supervision problem.

Celery solves distributed execution. We needed crash‑safe orchestration inside a single host boundary.

Those are very different systems.

SQLite as an offline metrics buffer in a monitoring agent

Alex Zhdankov — Thu, 28 May 2026 14:53:32 +0000

Reliable monitoring becomes surprisingly hard once you assume the network will eventually fail.

Monitoring systems quietly depend on one dangerous assumption:

the monitoring server will always be reachable

In production, that assumption breaks constantly.

And once it breaks,
you discover that metrics pipelines are really distributed systems.

The problem

Our PostgreSQL monitoring agent periodically collects metrics and sends them to the Control Plane.

Under normal conditions:

Agent
   │
   └──► metrics ───► Control Plane ───► storage

Simple enough.

But then production networks happen:

VPN reconnects
firewall reloads
DNS failures
routing instability
Control Plane maintenance windows

Once connectivity drops,
the entire delivery pipeline becomes unreliable.

The obvious answer sounds simple:

“just retry later”

That idea turned out to be much harder than expected.

Questions immediately appeared:

how long should metrics stay in memory?
what happens during process restart?
how do we avoid unbounded memory growth?
how do we recover after long outages?
how do we know delivery is degraded?

At that point,
this stopped being “metrics collection”.

It became a buffering and reliability problem.

The incident that changed the design

One outage lasted almost six hours after a network partition between monitoring agents and the Control Plane.

The agents themselves stayed healthy.
PostgreSQL stayed healthy.

Only the network path disappeared.

The original implementation buffered metrics in memory.

At 4:12 AM, one agent restarted during a package update.

Six hours of buffered monitoring data vanished instantly.

The dashboards looked like this:
timeline ─────── gap ─────── timeline

No visibility.
No recovery.
No idea what happened during the outage.

That incident permanently killed the idea of in-memory buffering.

Metrics had to survive process restarts.

The architecture

The final design became intentionally simple:

                PostgreSQL
                     │
                     ▼
              metrics collector
                     │
                     ▼
          +----------------------+
          |      SQLite WAL      |
          |----------------------|
          | durable local queue  |
          | bounded retention    |
          +----------------------+
                     │
                     ▼
             batch delivery loop
                     │
                     ▼
               Control Plane

The key architectural shift:

the Control Plane is no longer in the critical path of collection

Metrics are always written locally first.

Network delivery became asynchronous.

That single separation changed the reliability model completely.

The network could fail for hours,
and the agent would continue operating normally.

Why SQLite

The interesting part here is not SQL.

It's what SQLite fundamentally is:

a single file
atomic writes
crash-safe persistence
zero infrastructure
included in Python itself

We evaluated alternatives.

In-memory queue

Failed because:

metrics disappear on restart
memory usage becomes unbounded during long outages

Plain append-only files

Failed because:

manual locking
manual serialization
corruption handling
cleanup complexity

Redis

We already use Redis elsewhere in the platform (for the psql terminal).

But for the metrics buffer, Redis was the wrong fit:

Redis is in-memory by default. Persistence (RDB/AOF) is optional and adds latency.
If Redis restarts, the buffer disappears — even if the agent is healthy.
Mixing low-latency terminal traffic with background batch writes would create contention.

SQLite gives us durability on disk, survives agent restarts, and has zero dependencies outside the agent process.

Redis is great for real-time coordination. SQLite is better for durable local buffering.

The schema is intentionally tiny

CREATE TABLE IF NOT EXISTS metrics_queue (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    instance_id TEXT    NOT NULL,
    timestamp   TEXT    NOT NULL,
    metric_type TEXT    NOT NULL,
    data        TEXT    NOT NULL,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
)

One row per metric type per collection cycle.

Payloads are JSON-encoded.

This is not relational modeling.

This is durable buffering.

Concurrency: why SQLite didn't become a bottleneck

One obvious question:

“doesn't SQLite lock the whole database?”

Yes.
And that matters.

The reason it worked here:

exactly one writer exists
the collector loop owns all inserts
delivery workers only read/delete in batches

We also enabled WAL mode:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

WAL mode mattered for one specific reason:

the collector continuously appends metrics,
while the delivery loop simultaneously reads and deletes batches.

Without WAL,
those operations would frequently block each other.

WAL allows readers to continue while writes are happening.

We also use:

PRAGMA synchronous=NORMAL;

This reduces fsync frequency while still preserving crash consistency,
which was a better latency/durability tradeoff for monitoring data.

For our workload:

collection every 10–30 seconds
batches of 100–500 rows
occasional degraded-network bursts

SQLite contention was negligible.

If we needed hundreds of concurrent writers,
we would likely move to PostgreSQL instead.

For this system,
operational simplicity mattered more than horizontal scalability.

The buffer state machine

The buffer itself behaves like a bounded queue:

COLLECT
   │
   ├── buffer healthy ──► INSERT
   │
   └── limits exceeded
              │
              ▼
         DROP + WARN

Two limits control growth:

maximum file size
maximum record age

Those constraints turned out to be operationally critical.

Why age-based cleanup matters

Imagine a six-hour outage.

Without retention limits,
the moment connectivity returns,
the agent floods the Control Plane with six hours of historical metrics.

On dashboards, that looks like:

timeline ─── gap ─── MASSIVE SPIKE ─── normal

Operationally, that's worse than losing data.

The spike is misleading.

So we intentionally discard stale records.

If buffered metrics exceed max_age_hours,
they are silently removed during cleanup.

The result becomes:

timeline ─── gap ─── normal

The outage remains visible.
But the monitoring data stays trustworthy.

That tradeoff mattered more than perfect retention.

Batch delivery

The delivery loop periodically reconstructs batches from SQLite:

SELECT id, instance_id, metric_type, data
FROM metrics_queue
ORDER BY created_at ASC
LIMIT ?

Rows are grouped back into metric payloads and sent to the Control Plane.
Importantly:

rows are deleted only after successful delivery

if send_to_control_plane(batch):
    cursor.execute(
        f"""
        DELETE FROM metrics_queue
        WHERE id IN ({','.join('?' * len(ids))})
        """,
        ids,
    )
    conn.commit()

This creates a very simple delivery guarantee:
INSERT → SEND → DELETE
If the process crashes before DELETE,
the metrics remain in SQLite and are retried later.

That makes delivery effectively at-least-once.

This effectively turns SQLite into a durable local write-ahead queue.

The second production incident

Several months later,
another issue appeared.

Monitoring dashboards showed healthy agents,
but metrics were arriving hours late.

The agents were buffering correctly.

The problem was:
the SQLite files had silently grown to hundreds of megabytes after a prolonged outage.

Delivery throughput could not drain the backlog fast enough.

At that point we realized:

the buffer itself needed observability

Buffer observability

Every heartbeat sent to the Control Plane now includes buffer state:

{
    'buffer_size_bytes': size,
    'oldest_record_age': age,
    'buffer_utilization': utilization,
}

Operationally, the most useful metrics became:

Metric             | Meaning                  | Operational action
───────────────────+──────────────────────────+────────────────────────
buffer_size_bytes  | Current SQLite file size | Alert if >100 MB
oldest_record_age  | Delivery lag             | Alert if >1 hour
buffer_utilization | Current size / max size  | Scale retention policy
delivery_failures  | Consecutive failed sends | Investigate connectivity

Without these metrics,
network degradation looked identical to healthy operation.

The buffer itself became another production subsystem
that required monitoring.

Production numbers

Typical production values:

collection interval: 10–30 seconds
average batch size: 100–500 rows
SQLite buffer limit: 128 MB
longest outage survived: ~6 hours

Under normal degraded-network conditions,
the SQLite file rarely exceeded 10–15 MB.

CPU overhead was effectively negligible.

Failure modes we actually cared about

A. Agent restart during outage

Metrics survive because the buffer lives on disk.
Delivery resumes automatically after restart.

B. Control Plane recovery after long outage

Fresh metrics resume immediately.
Stale buffered records expire automatically.
No misleading spikes.

C. Disk pressure

Once the buffer exceeds the configured size limit:

new writes stop
warnings are emitted
collection pauses temporarily

Delivery resumes automatically once backlog drains.

D. SQLite corruption

Rare, but possible.

We intentionally kept handling simple:

log the error
recreate the buffer
continue operation

For a monitoring system,
temporary metric loss is acceptable.

Operational complexity was not worth stronger guarantees.

Bounded loss over unbounded growth

One important design decision:

the system intentionally prefers bounded data loss over unbounded resource growth

Old metrics eventually expire.

The SQLite file has a hard size limit.

That tradeoff was deliberate.

For operational monitoring,
partial visibility is better than:

a dead agent
an exhausted disk
or an overloaded recovery pipeline

Perfect durability was never the goal.

Operational survivability was.

The mental model

If you understand only one thing:

collect
   ▼
SQLite
   ▼
deliver
   ▼
delete

SQLite here is not acting as a database.

It's:

a durable queue
with bounded retention
bounded size
atomic writes
and zero infrastructure dependencies

That's the entire design.

Final thought

The interesting realization was this:

monitoring reliability problems are mostly buffering problems

Once network delivery became unreliable,
the architecture naturally evolved toward:

local durability
asynchronous delivery
bounded retention
explicit degradation handling

Not because SQLite is magical.

Because a single durable file solved the exact failure mode we actually had.

Why your SSH scripts will fail in production

Alex Zhdankov — Mon, 18 May 2026 15:41:40 +0000

Remote command execution looks trivial — until unstable networks, retries, long-running commands, and half-open connections turn it into a reliability problem.

We use Paramiko with a thin supervision layer on top.
The same operational problems apply to AsyncSSH, Fabric, or plain OpenSSH subprocesses.

At first, the implementation looked completely straightforward:

client = paramiko.SSHClient()
client.connect(hostname=host, username=user)

stdin, stdout, stderr = client.exec_command(
    "systemctl restart postgres"
)

output = stdout.read().decode()

In development, this worked perfectly.

Then production happened.

Hundreds of hosts.
Unstable networks.
Long-running commands.
Frozen sessions.
Half-open connections.
Retries.
Partial execution.

At that point this stopped being “SSH scripting”.

It became a distributed systems problem.

SSH is deceptively simple

Most developers intuitively model SSH like this:
local subprocess, but remote

But production SSH execution is actually:

network transport
+ stateful session
+ interactive channel
+ remote process lifecycle
+ unreliable infrastructure
+ partial execution visibility

And failures can happen independently at every layer.

Application
    ↓
SSH Client
    ↓
TCP transport        ← packets can vanish
    ↓
SSH session          ← can hang without closing
    ↓
Remote shell         ← can ignore commands
    ↓
Process execution    ← may continue after disconnect
    ↓
stdout/stderr        ← can block forever

This distinction changes everything.

Failure mode #1 — execution uncertainty

This was the first major production lesson.

If the SSH transport dies, you do not know whether the command:

succeeded
failed
partially executed
is still running remotely

That uncertainty completely changes retry semantics.

For example:

systemctl restart postgres

If the connection drops immediately after sending the command:

did restart begin?
is postgres still restarting?
did it already succeed?
is the service now dead?

You no longer have execution certainty.

This is not a “Paramiko problem”.

This is a distributed systems problem.

Retry is dangerous

Retries sound harmless until commands become stateful.

Some operations are naturally idempotent:

cat /proc/meminfo
ls -la /etc
systemctl status postgres

Others are not:

useradd deploy
rm -rf /some/path
systemctl restart postgres

A failed transport does not imply failed execution.

That means naive retry logic can create destructive side effects.

This forced us to separate failures into two categories:

transport uncertainty
command failure

Those are fundamentally different operational states.

Timeouts are not one thing

One of the most common mistakes in SSH automation is treating timeout as a single concept.

Production systems usually need several independent timeout layers:

TCP connect timeout
SSH handshake timeout
authentication timeout
command execution timeout
idle/read timeout

Each failure means something different operationally.

client.connect(
    hostname=host,
    username=username,
    timeout=10,
    banner_timeout=15,
    auth_timeout=15
)

But even that is insufficient.

A command may still hang forever while the socket technically remains alive.

That distinction matters a lot in production.

Half-open connections are nasty

This became one of the hardest reliability problems.

Sometimes:

TCP stays alive
SSH transport stays alive
but the remote process is effectively dead

Or:

packets silently disappear
the remote kernel freezes
stdout stops forever
but the socket never closes

From the application perspective:
everything looks connected

while the operation is permanently stalled.

This is the classic half-open connection problem.

Blocking reads break automation

This code looks innocent:

stdout.read()

But under real workloads it becomes dangerous.

If:

the command hangs
stdout stops producing data
the socket remains alive

then:
the thread blocks forever

We eventually moved to streaming execution instead of buffered reads.

Streaming changes the execution model

Long-running commands fundamentally change how remote execution must be handled.

Operations like:

pg_dump
VACUUM
package upgrades
log exports

can run for minutes or hours.

Buffering all output in memory is unreliable.
Blocking until completion destroys observability.

Instead we switched to chunked streaming:

while not channel.exit_status_ready():
    if channel.recv_ready():
        data = channel.recv(4096)
        callback(data)

This solved several production problems simultaneously:

realtime progress visibility
lower memory usage
cancellation support
dead session detection

Streaming ended up being much more operationally stable than buffered execution.

Security becomes infrastructure, not validation

Another important lesson:

SSH automation is remote code execution infrastructure.

That means command construction rules matter enormously.

This is catastrophic:

cmd = f"rm -rf {user_input}"

Because eventually someone passes:

/home/user; rm -rf /

We ended up treating all remote commands as infrastructure-sensitive operations.

Input validation alone was insufficient.

Every dynamic argument had to be:

validated
escaped
constrained

safe_value = shlex.quote(user_input)

Even simple automation eventually becomes security-critical.

Resource cleanup matters more than expected

SSH resources leak surprisingly easily.

Channels.
Sockets.
Transports.
PTY buffers.

Under load, forgotten cleanup accumulates fast.

We eventually standardized all operations around explicit lifecycle management:

with ssh_operation(...) as ssh:
    ssh.execute(...)

The important part was not aesthetics.

It was guaranteeing cleanup under:

exceptions
timeouts
partial failures
interrupted execution

Production automation lives or dies on cleanup guarantees.

The architecture we ended up with

Over time the system evolved into several independent layers:

Connection management
    ↓
Retry classification
    ↓
Execution supervision
    ↓
Streaming transport
    ↓
Resource cleanup
    ↓
Observability

The important realization was:

remote execution is not a helper function

It is infrastructure.

Final insight

The happy path is trivial.

Production architecture begins where execution certainty ends.

SSH automation fails when treated like scripting.

Because it is not scripting.

It is:

remote process orchestration
over unreliable transport
with partial execution visibility
inside a distributed system

And once you accept that,
the architecture changes completely.

We built a real psql terminal in the browser. Here’s what made it unexpectedly hard.

Alex Zhdankov — Wed, 13 May 2026 20:53:07 +0000

A PTY-backed PostgreSQL console running in the browser using reverse WebSockets, Redis Streams, and xterm.js — designed around centralized control-plane constraints and production failure modes.

We needed a real PostgreSQL terminal inside the browser.

Not a SQL editor.
Not a query API.
A real psql session with full terminal semantics.

That requirement immediately forced several architectural constraints:

a real PTY
a long-lived stateful process
bidirectional streaming
terminal resize handling
signal forwarding (Ctrl+C)
native psql behavior

And then the infrastructure constraints made things significantly more interesting:

agents live in internal networks
all traffic must go through the Control Plane
xterm.js only supports WebSocket transport
we could not emulate psql

At that point, this stopped being a “web feature”.
It became a distributed terminal runtime problem.

High-level architecture

This system only makes sense if you read it as a dataflow graph, not as isolated services.

Browser (xterm.js)
    │
    │ WebSocket (terminal I/O)
    ▼
Control Plane
    │
    │ session management + auth
    ▼
Redis Streams (output buffer)
    │
    │ coordination + async delivery
    ▼
Agent WebSocket channel
    │
    │ PTY stdin/stdout bridge
    ▼
PTY → real psql process

The critical architectural decision:

the browser never connects to the agent directly.

The Control Plane is the only public entrypoint in the entire system.
Everything flows through it.

Why the architecture looks “backwards”

The surprising part is that the agent initiates the terminal transport.

Not because NAT traversal was impossible.

But because the system was intentionally designed around a centralized Control Plane.

Agents sit in internal networks.
The browser has no direct visibility into them.

So instead of:

Browser → Agent

the architecture becomes:

Browser → Control Plane ← Agent

The Control Plane acts as:

session coordinator
auth boundary
transport router
lifecycle owner

Once that decision is made, reverse WebSockets become the natural transport model.

Session establishment

The session lifecycle happens in multiple stages.

Importantly:

the PTY process does not exist when the browser first connects

Only a logical session exists.

Step 1 — Browser creates a logical session

Browser
  │
  │ WebSocket connect
  ▼
Control Plane
  ├── creates session_id
  ├── registers browser handler
  └── starts auth timeout

At this point:

no PTY exists
no psql exists
no database connection exists

The Control Plane only knows:

“a browser wants a terminal session”

Step 2 — Control Plane signals the agent

The Control Plane sends a lightweight HTTP request:
POST /terminal?session_id=<uuid>

This is intentionally the only HTTP hop in the entire terminal lifecycle.

The request does not carry terminal traffic.

It only means:

“establish terminal transport for this session”

Step 3 — Agent opens reverse WebSocket

Agent
  │
  │ outbound WebSocket
  ▼
Control Plane

Now the system has two independent transport channels:

Browser WS → Control Plane
Agent WS → Control Plane

But they are still disconnected.

The system is in a half-connected state.

Session stitching

This is the moment where the architecture becomes interesting.

Browser Handler ───────┐
                       ├── session binding
Agent Handler ─────────┘

At this point:

the Control Plane stops being a transport endpoint and becomes a message router

It now forwards:

browser input → agent
agent output → browser

But critically:

not directly

All terminal output passes through an asynchronous buffering layer.

That layer ended up being one of the most important production decisions in the system.

PTY process creation

Once the session is fully initialized, the agent forks a real PTY:

(child_pid, fd) = pty.fork()

if child_pid == 0:
    subprocess.run([
        "psql",
        "-U", user,
        "-d", dbname
    ])

At this point the architecture fundamentally changes.

This is no longer “web infrastructure”.

It becomes:

PTY supervision
file descriptor management
process lifecycle handling
signal propagation
backpressure management

Most complexity appeared after this step.

Not before it.

The real data pipeline

This is the most important flow in the system.

Browser
  │
  │ keystroke
  ▼
Control Plane
  │
  ▼
Agent WS handler
  │
  │ write(fd)
  ▼
PTY → psql
  │
  │ stdout
  ▼
PTY reader thread
  │
  │ Redis XADD
  ▼
Redis Streams
  │
  │ async consumer
  ▼
Control Plane
  │
  │ WS push
  ▼
Browser

The most important line in the entire architecture is this:
PTY reader → Redis XADD → async consumer → WebSocket
That line is the system’s stability boundary.

Why Redis Streams became mandatory

The original implementation directly forwarded PTY output into WebSocket writes:
PTY → WebSocket

It worked in development.

It failed in production.
The issue was subtle:

PTY reads are synchronous
WebSocket writes can block
backpressure propagates backwards

The resulting failure mode was catastrophic for terminal UX:

slow network
    ↓
blocked WS writes
    ↓
frozen PTY reader
    ↓
terminal stalls

The terminal looked dead while psql was still running underneath.

Redis Streams solved this by introducing a decoupling boundary.

Now:

PTY reads stay non-blocking
network latency becomes isolated
consumers can temporarily lag
output survives reconnects

The additional latency was negligible.

The operational stability improvement was enormous.

The architecture is actually two independent loops

This is the part most terminal architectures hide.

Input loop
Browser → Control Plane → Agent → PTY

Output loop
PTY → Redis → Control Plane → Browser

These loops are intentionally independent.

That separation is what allows the system to survive partial failures.

Why we split browser and agent handlers

We intentionally kept browser-facing and agent-facing handlers separate.

Because they solve fundamentally different problems.

Browser Handler:

auth
user session ownership
browser disconnect semantics
user errors

Agent Handler:

PTY lifecycle
process supervision
reconnect semantics
infrastructure errors

Trying to merge them created tightly coupled failure modes and significantly more lifecycle complexity.

Separating them made the system dramatically easier to reason about.

Failure modes that mattered in production

The hardest problems were not PostgreSQL problems.

They were long-lived process problems.

A. Redis failure

Impact:

output pipeline breaks
PTY continues running

Mitigation:

memory limits
retention limits
monitoring
bounded stream lifetime

B. Agent disconnect

Impact:

transport disappears
PTY may still be alive

Mitigation:

reconnect window
session reattachment
delayed teardown

C. Process explosion

Impact:

memory exhaustion
PostgreSQL connection storms

Mitigation:
BoundedSemaphore(max_sessions=10)
This was one of the simplest and most effective safeguards in the system.

D. xterm resize storms
xterm.js emits resize events aggressively during browser resizing.

Impact:
Each resize triggers:
ioctl(TIOCSWINSZ)

Mitigation:

Without throttling, the PTY spent significant time processing resize events instead of actual terminal traffic.
Simple debounce logic completely fixed the issue.

Scaling reality

The system does not scale like a normal WebSocket service.

Each session includes:

a real psql process
a PTY
multiple threads
Redis streams
two WebSocket channels
a database connection

The scaling bottleneck is not Redis.

It is not CPU.

It is not WebSockets.

It is:

how many real PostgreSQL sessions the infrastructure can sustain

Why HTTP and SSE were rejected

We evaluated both.

HTTP
Failed because:

stateless
no streaming terminal semantics
no signal handling
no persistent shell state

SSE
Failed because:

one-directional transport
incompatible with terminal interaction patterns
xterm.js expects bidirectional communication

At the end, terminals naturally map onto WebSockets.

Trying to avoid that only complicates the architecture.

What this system actually is

If you remove all abstractions:

this is a distributed process supervisor for a PTY running psql

Everything else is transport, routing, buffering, and failure handling around that core idea.

Final architecture insight

The system is ultimately defined by three separations.

Connection separation
The Control Plane isolates browsers from agents.
Process separation
PTY isolates PostgreSQL from the web layer.
Flow separation
Redis isolates terminal I/O from network I/O.

Final mental model

If you understand only one thing, understand this:

Browser ↔ Control Plane ↔ Agent ↔ PTY ↔ psql
                     ↑
              Redis is the buffer

Everything else is lifecycle management around this chain.

Final thought

We did not build a “web UI for PostgreSQL”.

We built a distributed, fault-tolerant runtime for a stateful terminal process.

PostgreSQL just happened to be the process attached to it.