RadiatorDB PostgreSQL 10k TPS example

RadiatorDB PostgreSQL 10k TPS example

This example is a production starting point for a RadiatorDB deployment with:

• 10,000 document reads per second and 1,000 document writes per second in aggregate
• two Radiator processes and two writable PostgreSQL 18 nodes
• one million subscriber profiles and a small active write set
• a dedicated 16 vCPU, 16 GiB RAM Linux host for each PostgreSQL node
• low-latency SSD or NVMe storage
• durable local commits with synchronous-commit on

Apply the same PostgreSQL and operating system settings to both database nodes. Use the same RadiatorDB backend configuration on both Radiator hosts.

Radiator sizing measurements use two Radiators, two PostgreSQL nodes, one million profiles, and pools of up to 1,024 connections per Radiator and database node. The closest measured workload performs 10,000 reads and 10,000 latest-state writes per second in aggregate. It uses asynchronous commits and does not retain revision history for the frequently updated documents. The requested 1,000 write TPS is lower, but document size, indexes, revision retention, commit durability, storage latency, and hardware still affect the result.

Treat these files as a measured starting point, not as a capacity guarantee. Run the target workload and a one-node failure test before production use. See RadiatorDB sizing for the underlying measurements.

PostgreSQL configuration file

On Debian and Ubuntu, save the following file as /etc/postgresql/18/main/conf.d/20-radiatordb.conf. On other systems, add the settings to a file included by postgresql.conf.

Before you save this file, replace 192.168.1.20 with the address of the current PostgreSQL node:

# Network and connection capacity
listen_addresses = '192.168.1.20'
max_connections = 2200
superuser_reserved_connections = 10

# Memory for a dedicated 16 GiB database host
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 1MB
maintenance_work_mem = 512MB
huge_pages = try

# RadiatorDB logical replication
wal_level = logical
max_wal_senders = 50
max_replication_slots = 50
max_active_replication_origins = 50
max_logical_replication_workers = 50
max_worker_processes = 80
wal_retrieve_retry_interval = 200ms

# WAL and checkpoints
wal_writer_delay = 300ms
wal_writer_flush_after = 20MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 16GB
min_wal_size = 4GB

# Production diagnostics
track_io_timing = on
track_wal_io_timing = on
log_checkpoints = on
log_lock_waits = on
log_autovacuum_min_duration = 1s
log_min_duration_statement = 250ms

The 2,200 connection limit covers two Radiator processes that can each open up to 1,024 connections to this node, with a small reserve for administration, deployment, monitoring, and replication. PostgreSQL uses one process per client connection. Do not use this limit on a smaller host without measuring its memory and process overhead.

shared_buffers is 25% of the example host memory. effective_cache_size is a planner estimate, not allocated memory. The low work_mem value limits the worst-case memory multiplication across concurrent operations. Increase it only after query plans and temporary-file statistics show a need.

The 16 GiB WAL range reduces checkpoint frequency during write bursts and replica catch-up. Confirm that the database volume has enough free space for live data, retained revisions, WAL, replication backlog, maintenance, and the free-space reserve described in the sizing guide. This example does not set max_slot_wal_keep_size: an unavailable replica can therefore retain WAL until it returns. Monitor replication slots and available disk space.

The diagnostic settings record slow statements, checkpoint activity, autovacuum work, and I/O timing. Review their volume and storage cost during the soak test. Raise log_min_duration_statement if the logs are too busy.

See the PostgreSQL documentation for logical replication settings, memory settings, and WAL settings.

Linux sysctl file

Save the following file as /etc/sysctl.d/90-radiatordb.conf:

# Allow PostgreSQL to absorb connection bursts while Radiator pools reconnect.
net.core.somaxconn = 8192

Apply the file:

sudo sysctl --system

Do not copy generic vm.dirty_*, swap, or memory-overcommit tuning into this file. Those settings depend on the storage controller, filesystem, backup process, and host memory policy. PostgreSQL uses explicit huge pages when the host supports them and falls back to normal pages because huge_pages is try.

systemd service limits file

On Debian and Ubuntu, create the directory /etc/systemd/system/postgresql@18-main.service.d and save this file as limits.conf:

[Service]
LimitNOFILE=131072
LimitNPROC=8192
TasksMax=8192

The PostgreSQL unit name varies by operating system and package. For example, Red Hat based PostgreSQL 18 packages commonly use postgresql-18.service. Place the drop-in under the actual database service unit, not under a wrapper unit that only starts other services. Replace postgresql@18-main.service in the following commands with the actual unit name.

Apply the service limits and restart PostgreSQL on one node at a time:

sudo systemctl daemon-reload
sudo systemctl restart postgresql@18-main.service
sudo systemctl is-active postgresql@18-main.service

The final command must print active. Keep the other RadiatorDB node available while restarting a production node.

Matching Radiator connection pools

Use this connection capacity only with a matching Radiator configuration. This configuration keeps 128 warm connections and can grow each pool to 1,024 connections. With server-selection least-connections, Radiator sends each request to the usable PostgreSQL node with the fewest busy connections. This distributes database work even when clients send more requests to one Radiator process than the other. Before deployment, replace 192.168.1.20 and 192.168.1.21 with your PostgreSQL node addresses. Change the database and account names if they differ from radiatordb and radiatordb_admin. Deploy the same backend configuration to both Radiator processes:

backends {
    radiatordb "mydb" {
        server-selection least-connections;

        postgresql {
            synchronous-commit on;
        }

        server "node1" {
            host "192.168.1.20";
            port 5432;
            database "radiatordb";
            username "radiatordb_admin";
            password env.RADIATORDB_POSTGRES_PASSWORD;
            connections {
                min 128;
                max 1024;
                idle-timeout 30m;
            }
        }

        server "node2" {
            host "192.168.1.21";
            port 5432;
            database "radiatordb";
            username "radiatordb_admin";
            password env.RADIATORDB_POSTGRES_PASSWORD;
            connections {
                min 128;
                max 1024;
                idle-timeout 30m;
            }
        }
    }
}

The pool maximum is a limit, not the number of connections opened at startup. Reduce it after the soak test if peak concurrent database work is substantially lower. Increase the minimum only if connection creation causes latency during normal load.

Keep synchronous commit enabled for the first production test. Setting it to off can improve write throughput, but a database host crash can lose recently acknowledged local transactions. Logical replication between RadiatorDB nodes remains asynchronous in either mode.

Verify the settings

Check the PostgreSQL settings without reading or modifying RadiatorDB tables:

sudo -u postgres psql -X --dbname postgres --command="
SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN (
    'max_connections',
    'shared_buffers',
    'wal_level',
    'max_wal_senders',
    'max_replication_slots',
    'max_active_replication_origins',
    'max_logical_replication_workers',
    'max_worker_processes',
    'max_wal_size',
    'track_io_timing'
)
ORDER BY name;"

Every row must show pending_restart as f. Check the service limits. Replace postgresql@18-main.service with the actual PostgreSQL service unit:

systemctl show postgresql@18-main.service \
    --property=LimitNOFILE \
    --property=LimitNPROC \
    --property=TasksMax
sysctl net.core.somaxconn

Restart each Radiator process and verify a new Loading configuration done log entry as described in RadiatorDB installation.

Production acceptance test

Run the acceptance test with production document sizes, fields, indexes, revision retention, and commit policy. A short peak test is not enough. Use at least these phases:

1. Warm the database with the expected live data set.
2. Hold 10,000 read TPS and 1,000 write TPS for at least 30 minutes.
3. Confirm request latency, errors, pool use, CPU, memory, disk latency, WAL volume, and replication delay remain within the service objectives.
4. Stop one PostgreSQL node and repeat the steady load on the remaining node.
5. Restart the node and confirm replication catches up without exhausting disk, CPU, network, or the request latency budget.
6. Repeat the test with backup, garbage collection, and other scheduled production work enabled.

For one-node operation at 10,000 TPS, the documented latest-state workload requires 16 reference CPUs on the remaining database node. Compare the host CPU with the reference result of 4,750 sysbench cpu events per second per CPU.

Troubleshooting

Start with the Radiator log and statistics. Check request timeouts and errors, connection pool exhaustion, unavailable servers, retry counts, and PostgreSQL latency. These distinguish an undersized Radiator pool from a database that is not completing work quickly enough.

Use PostgreSQL system views rather than querying RadiatorDB data directly:

• pg_stat_activity shows connection counts, active queries, and wait events.
• pg_stat_database shows transactions, temporary files, deadlocks, and cache activity.
• pg_stat_io and pg_stat_checkpointer show storage and checkpoint pressure.
• pg_stat_wal shows WAL generation and write activity.
• pg_stat_subscription shows logical replication workers and progress.
• pg_replication_slots shows active slots and retained WAL.

See PostgreSQL monitoring and logical replication monitoring for the view definitions and fields.

Use operating system tools such as pidstat, iostat -xz 1, vmstat 1, and sar -n DEV 1 to correlate database latency with CPU saturation, storage queue depth, memory pressure, and network throughput.

Common patterns are:

• Pool exhaustion with low database CPU and latency indicates that the pool may be too small or connections are unevenly distributed.
• Pool exhaustion with high database latency indicates a database, storage, or query bottleneck. Increasing connection counts usually makes this worse.
• Frequent requested checkpoints or latency spikes during checkpoints indicate that WAL capacity and storage throughput need review.
• Growing retained WAL indicates a stopped or slow subscription. Check pg_stat_subscription, replication slots, peer connectivity, and PostgreSQL logs before disk space is exhausted.
• Increasing temporary files indicates insufficient memory for specific query operations. Inspect the slow operations before increasing work_mem because it applies independently to concurrent operations.
• Autovacuum falling behind indicates that write rate, revision retention, storage throughput, or per-table autovacuum behavior needs review.

If the workload misses its objective, preserve the Radiator statistics, PostgreSQL logs and system-view snapshots, operating system metrics, exact test mix, and configuration from the same time window. Those artifacts are enough to troubleshoot whether the first limit is Radiator connection scheduling, PostgreSQL CPU or locking, storage, checkpoints, replication, or the host.