Reference Architecture: 3-tier OLTP application

Classic business applications (orders, payments, inventory) run a large number of short, single-row, consistency-critical transactions (OLTP). This tree shows step by step, starting from identifying the load profile, when to introduce the primary relational database, a connection pooler, read replicas, a cache layer, backup + PITR and monitoring. The goal is not to 'add everything up front' but to add the right component as each bottleneck is proven.

What is your load profile: transaction mix and consistency requirements?

The first decision in an OLTP architecture is whether the workload is truly transactional (a short, ACID-requiring mix of writes and reads). Is the data naturally relational and is consistency critical (money, stock, orders), is the workload mostly reads, or is the load still very low?

• Short transactions, strong consistency (money/stock/orders)
• Reads heavily dominate, few writes, and traffic is growing
• Load is very low; is the simplest setup enough?

Have you chosen the primary database engine, and are you worried about scale?

A relational engine is the right foundation for the transactional core (PostgreSQL, MySQL/InnoDB, Oracle, SQL Server). Once the engine choice is clear, the next question is scaling: is a single instance enough for now, or are connection counts and read load increasing?

• Engine is clear; a single instance is enough for now
• Connection count has spiked, resources are running out
• Read load is growing and straining the primary write instance

How will you scale reads: replica or cache?

If read load is straining the primary instance, there are two complementary options. Reads that can tolerate slightly delayed data, such as reports/lists, can be routed to a read replica; if the same 'hot' data is requested over and over, a cache with microsecond latency (Redis) relieves the DB.

• Many report/list reads; slight lag is acceptable
• The same popular data is read again and again

Recommendation: Start with a single primary + pool + backup

Keep the architecture simple early on: a single relational primary instance, a connection pool in front of it and regular backups are enough. Add complexity only when a measured bottleneck forces it.

Recommendation: Add a connection pooler

In OLTP, when every application instance opens its own connections, the primary DB chokes on memory and scheduler overhead under thousands of connections. Putting a pooler in between shares a small number of real DB connections across many application requests.

Recommendation: Add a read replica

Writes stay on the primary while read-only traffic (reports, lists, search) is routed to one or more asynchronous replicas fed from the primary. This frees the primary for writes and consistency-critical reads.

Recommendation: Add a cache layer (Redis)

If the same popular data (sessions, catalog, profiles, page fragments) is read repeatedly, put an in-memory cache in front of the DB and serve most reads without ever reaching the DB. Latency drops and the primary DB is relieved.