How Enterprise System Manages Databases
Enterprise systems don't just "connect to a database"; they build an entire architecture around it to ensure high availability, scalability, security, and data integrity.
Let's start from first principles and progressively build an enterprise architecture.
Level 1: A simple application
A small application looks like this:
Users
│
▼
Backend API
│
▼
PostgreSQL
Every request goes directly to the database.
This works well for a few hundred users.
Level 2: Connection Pooling
Opening database connections is expensive.
Each application instance maintains a pool.
Connection Pool
│
Users → API ───┤
│
▼
PostgreSQL
Instead of creating a new connection every request, the application borrows one from the pool.
Level 3: Multiple API instances
As traffic grows, one API server isn't enough.
Load Balancer
/ | \
/ | \
API1 API2 API3
│ │ │
└─────────┼────────┘
│
PostgreSQL
Now there are multiple connection pools.
This introduces a new problem:
If every API has 50 connections,
API1 = 50
API2 = 50
API3 = 50
Total = 150
The database may become overloaded.
Level 4: Database Connection Pooler
Large PostgreSQL deployments often use PgBouncer.
API1
API2
API3
│
▼
PgBouncer
│
▼
PostgreSQL
PgBouncer:
reuses connections
limits database sessions
queues requests
protects PostgreSQL
Instead of 500 database sessions, PostgreSQL may only need 100.
Level 5: Read Replicas
Most applications perform far more reads than writes.
Example:
95% SELECT
5% INSERT/UPDATE
Enterprise systems split the workload.
Primary Database
(Writes)
/ \
/ \
Replica 1 Replica 2
Routing:
SELECT
↓
Replica
INSERT
UPDATE
DELETE
↓
Primary
Benefits:
more read throughput
reduced load on the primary
easier scaling
Level 6: Caching
Even replicas become overloaded if every request hits the database.
A cache is added.
Client
↓
API
↓
Redis
↓
PostgreSQL
Flow:
Request user
↓
Redis?
↓
Found
↓
Return immediately
Otherwise:
Redis miss
↓
PostgreSQL
↓
Store in Redis
↓
Return
This dramatically reduces database load.
Level 7: Database Transactions
Imagine transferring £100.
Without a transaction:
Subtract £100
Server crashes
Never add £100
Money disappears.
Instead:
BEGIN
Subtract
Add
COMMIT
If anything fails:
ROLLBACK
The database restores the previous state.
Transactions guarantee atomicity—either all operations succeed or none do.
Level 8: Optimistic and Pessimistic Locking
Suppose two users buy the last item simultaneously.
Without coordination:
Stock = 1
User A buys
User B buys
Stock = -1
To prevent this, databases use locking.
Pessimistic locking
Lock the row before updating.
User A
↓
Lock row
↓
Update
↓
Unlock
Other transactions wait.
Optimistic locking
Allow concurrent reads.
When updating:
UPDATE products
WHERE version = 5
If another transaction already changed it:
0 rows updated
The application retries or informs the user.
Level 9: Database Indexes
Without an index:
Find user id = 9
The database scans every row.
With an index:
B-tree
↓
Jump directly
↓
Return row
Indexes speed up reads but consume storage and slow down writes slightly because they also need updating.
Level 10: Partitioning
Imagine a table with 5 billion rows.
Searching becomes increasingly expensive.
Instead:
Orders
2024
2025
2026
Queries only scan the relevant partition.
Large organisations often partition by:
date
customer ID
geographic region
Level 11: Sharding
Eventually, a single database server is no longer enough.
Data is split across multiple databases.
Customer 1–1,000,000
↓
Database A
Customer 1,000,001–2,000,000
↓
Database B
Customer 2,000,001+
↓
Database C
This is horizontal scaling.
Sharding introduces challenges like cross-shard queries and distributed transactions, so it is usually adopted only at very large scale.
Level 12: High Availability
What if the primary database fails?
Enterprise systems keep a standby.
Primary
↓
Replication
↓
Standby
If the primary fails:
Standby
↓
Promoted
↓
Becomes Primary
Applications reconnect automatically, minimising downtime.
Level 13: Backups and Disaster Recovery
Enterprises assume failures will happen.
They maintain:
Regular full and incremental backups
Point-in-time recovery using transaction logs
Off-site or cross-region backup storage
Periodic restore tests to verify backups are usable
A backup that has never been restored is not yet proven to work.
Level 14: Monitoring
Databases are continuously monitored.
Common metrics include:
Active connections
Connection pool usage
Query latency
Slow queries
CPU utilisation
Memory usage
Disk I/O
Replication lag
Cache hit ratio
Deadlocks
Lock wait times
Monitoring enables teams to detect problems before users notice them.
Putting it all together
A typical enterprise architecture looks like this:
Internet
│
Load Balancer
│
┌──────────┴──────────┐
│ │
API 1 API 2
│ │
Connection Pool Connection Pool
└──────────┬──────────┘
│
PgBouncer
│
┌──────────┴──────────┐
│ │
Redis PostgreSQL Primary
│
┌──────────┴──────────┐
│ │
Read Replica Read Replica
Core principles
Enterprise systems follow a few consistent principles:
Protect the database by limiting concurrent connections.
Reduce unnecessary work using caches.
Scale reads with replicas.
Maintain consistency with transactions and appropriate locking.
Optimise performance through indexing and partitioning.
Ensure availability with replication and automatic failover.
Plan for recovery with tested backups and disaster recovery.
Observe everything through metrics, logs, and alerts.
These ideas form the foundation of how companies such as banks, cloud providers, e-commerce platforms, and SaaS companies operate relational databases in production.