The Unified PostgreSQL Architecture: Scaling Without Complexity

Your application does not need Kafka, Redis, multiple microservices, and several database systems just because those technologies appear in large-scale engineering stacks. Adding infrastructure before your workload demands it creates more configuration, monitoring, security, and failure points. PostgreSQL already provides concurrency controls, notifications, ephemeral tables, transactions, replication, and strong data types that can cover a large part of an application's backend.

The better approach is simple: **start with PostgreSQL, measure the workload, and introduce another system only when PostgreSQL becomes the actual bottleneck.**

Use PostgreSQL as a Queue Before Adding Kafka

A background-job system does not automatically require Kafka, RabbitMQ, or another dedicated message broker. PostgreSQL can implement a database-backed queue using row-level locking and `SKIP LOCKED`.

FOR UPDATE SKIP LOCKED

The key pattern is documented in the PostgreSQL SELECT reference:

SELECT *
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

`SKIP LOCKED` allows concurrent workers to skip rows that another transaction has already locked instead of waiting for those rows. PostgreSQL documents the feature specifically as a mechanism for skipping currently locked rows without waiting.

This works well for applications where jobs already belong to the same transactional data model as the rest of the application. A worker can claim a job, update its state, and process it without introducing another persistence system.

LISTEN and NOTIFY

You can also combine the queue with PostgreSQL's `LISTEN` and `NOTIFY`. A worker can listen for notifications and react when a producer signals that new work is available. PostgreSQL's NOTIFY documentation notes that `LISTEN` registers a session for notifications sent through a channel, while `NOTIFY` delivers those events to listening sessions.

When a dedicated broker is still the right next step

This does not mean PostgreSQL is a universal replacement for dedicated messaging infrastructure. If your workload requires very high message throughput, long retention, complex streaming semantics, or an event-stream architecture, evaluate dedicated tooling based on measured requirements.

Use Unlogged Tables for Data You Can Afford to Lose

Redis is useful, but not every cache, counter, or temporary state requires a separate Redis deployment.

Crash safety is the tradeoff

PostgreSQL supports unlogged tables for workloads where durability is not required. Unlike normal logged tables, changes to an unlogged table are not written to the Write-Ahead Log in the same way. The tradeoff is critical: unlogged data is not crash-safe and should not contain information that must survive a database failure.

That makes unlogged tables appropriate for selected ephemeral workloads, such as disposable staging data or certain rebuildable caches.

Do not treat this as a general-purpose replacement for durable application data. If losing the contents of a table after a crash would damage the application, an unlogged table is the wrong choice.

Know Where PostgreSQL Stops

Simplifying your architecture does not mean forcing PostgreSQL to do everything.

Keep blobs out of the relational core

PostgreSQL should not become your object-storage system. Large images, videos, backups, and other massive binary objects generally belong in dedicated object storage, while PostgreSQL stores the metadata or object identifier required to retrieve them. Compress and convert images before upload with the Image Optimizer; keep the database out of that path.

This separation keeps relational queries focused on relational data and avoids turning ordinary database operations into large-object management tasks.

The architectural principle is straightforward:

Use PostgreSQL for data that benefits from relational transactions, constraints, queries, and consistency. Use specialized infrastructure when the workload has fundamentally different storage requirements.

Tune GUCs to Find Lock Contention

When a PostgreSQL application becomes slow, adding hardware is not always the answer. Lock contention can cause requests to wait even when CPU and memory utilization appear acceptable.

PostgreSQL exposes configuration parameters known as Grand Unified Configuration (GUC) parameters. Two particularly useful settings for diagnosing lock problems are `log_lock_waits` and `log_lock_failures`.

log_lock_waits

`log_lock_waits` causes PostgreSQL to log when a session waits longer than `deadlock_timeout` to acquire a lock. In PostgreSQL 18, this setting defaults to `off`. PostgreSQL 19 changes that default to `on`.

log_lock_failures

`log_lock_failures` was introduced in PostgreSQL 18. It produces detailed logging when lock acquisition fails, currently covering failures caused by `SELECT ... NOWAIT`.

These settings provide visibility into problems that otherwise appear to the application as unexplained latency.

Leave deadlock_timeout alone until you have a reason

Do not blindly reduce `deadlock_timeout` simply to generate logs faster. Configuration changes should follow observed workload behavior rather than arbitrary tuning recipes.

Build High Availability Around the Database

High availability is not only about PostgreSQL. Your operating system, database process, replication layer, connection routing, and application all participate in the availability design.

Rolling OS patching on a real HA cluster

With a properly configured PostgreSQL HA environment, operating-system maintenance can follow a rolling process. Replica nodes can be patched and restarted first while another node continues serving as the primary. After the replicas have caught up, the cluster can perform a controlled switchover, allowing the previous primary to be patched afterward.

The exact procedure depends on the HA tooling and deployment architecture. Patroni can manage PostgreSQL cluster leadership, while a connection-routing layer can direct applications toward the current primary.

The critical requirement is that applications must not hardcode a database node that may cease to be the primary.

Test failover before you need it

A failover process that exists only in documentation is not enough. Test promotions, switchovers, recovery, application reconnect behavior, and monitoring before you need them during an incident.

Control Connection Growth With Pooling

PostgreSQL uses a process-based server architecture, so connection management matters as applications scale. PostgreSQL's documentation states that the server uses one process per connection. Treat connection growth the same way you treat API concurrency: plan the budget with a rate-limit planner instead of opening a new backend for every short-lived client.

One process per connection

This becomes important for applications that create large numbers of short-lived connections, particularly serverless and highly concurrent workloads.

Transaction pooling

A connection pooler such as PgBouncer can reduce the number of PostgreSQL backend connections required by allowing many client connections to share a smaller pool of database connections.

Transaction pooling is particularly useful when application requests do not need to hold the same database connection across multiple transactions.

The goal is not to maximize `max_connections`. The goal is to keep the number of active PostgreSQL backends appropriate for the server's CPU, memory, workload, and query patterns.

Treat session_replication_role as a Dangerous Tool

Bulk imports and migrations sometimes tempt developers to use:

SET session_replication_role = replica;

This is not a generic performance switch.

What replica mode actually disables

PostgreSQL documents `session_replication_role` as controlling the firing of replication-related triggers and rules for the current session. Because foreign keys are implemented using triggers, setting the role to `replica` also disables foreign-key checks. PostgreSQL explicitly warns that improper use can leave data inconsistent.

That means application logic depending on triggers can silently stop executing.

For example, if a trigger creates audit records, maintains derived data, or performs application-specific integrity work, disabling that trigger can leave the database in a state the application does not expect.

Use this setting only when you fully understand the data-loading process and its integrity implications. Do not use it as a shortcut for ordinary bulk inserts.

NOT ENFORCED constraints (PostgreSQL 18)

PostgreSQL 18 also introduced support for `NOT ENFORCED` constraints for foreign keys and `CHECK` constraints, providing a more explicit mechanism for cases where constraint enforcement needs to be controlled during schema operations. Spell that intent in schema and migration notes instead of hiding it in a session GUC.

Choose NUMERIC for Exact Financial Values

Data types are architectural decisions. Changing them later can require migrations, application changes, index work, and operational planning. Money columns belong in the same class of product decisions as SaaS unit economics: exact decimals, not approximations.

Exact numeric vs inexact float

For financial amounts and calculations where exact decimal results matter, PostgreSQL provides the `numeric` type. PostgreSQL describes `numeric` as an exact, selectable-precision type and specifically recommends it for monetary amounts and quantities where exactness is required.

Floating-point types such as `real` and `double precision` are inexact representations. They can be appropriate for scientific, statistical, or engineering workloads where their characteristics are acceptable, but they are not automatically the right choice for monetary values.

A practical schema decision is therefore:

CREATE TABLE invoices (
    id bigint PRIMARY KEY,
    total numeric(12, 2) NOT NULL
);

The appropriate precision and scale depend on the application's actual requirements. Do not copy a precision value blindly from an example.

Keep PostgreSQL Current Without Chasing Every Release

PostgreSQL continues to evolve, and version upgrades can introduce meaningful improvements in performance, security, observability, and developer capabilities. The same rule applies as with a runtime upgrade: ship the version you have tested, not the version that just appeared.

PostgreSQL 19 is still beta

As of September 2026, PostgreSQL 19 is in its beta cycle rather than being a release that should automatically be deployed to production. The PostgreSQL project explicitly advises against running beta versions in production and recommends testing them against representative workloads.

PostgreSQL 19 includes changes across performance, monitoring, security, replication, and SQL capabilities. It also enables `log_lock_waits` by default.

For production systems, the correct strategy is not to upgrade simply because a new major version exists. Evaluate compatibility, extensions, drivers, migration procedures, rollback options, and application behavior first.

A Simpler PostgreSQL Architecture Is Usually Easier to Operate

The strongest PostgreSQL architecture is not the one with the most infrastructure. It is the one that meets the workload's requirements without creating unnecessary operational complexity.

Use PostgreSQL's native locking features for appropriate job queues. Use `LISTEN` and `NOTIFY` when database notifications fit the workload. Use unlogged tables only for data that can safely disappear. Diagnose contention through GUCs instead of guessing. Use connection pooling when connection pressure becomes a problem. Design HA around tested failover and routing. Treat `session_replication_role` as a specialized administrative mechanism, not an everyday optimization. Choose exact numeric types when the business requires exact decimal arithmetic.

Then measure. Put the numbers on a page assistants can cite — GEO Brand Auditor scores whether those facts actually get mentioned — and keep a llms.txt hint pointed at the live pages, not at this file.

If PostgreSQL becomes the bottleneck, identify the specific bottleneck before replacing it. The right architecture is driven by workload characteristics, not by copying the infrastructure of companies operating at a completely different scale. If you want a second pair of eyes on that decision, contact OmniKit.