Course contents
On this page
Application data architecture — extended reference
Original detailed coverage and citations accompanying the focused application data architecture lesson.
This reference preserves the earlier detailed chapter and its citations. Start with the guided explanation, then use these sections for additional mechanisms and qualifications. References to the original lesson sequence are historical.
Part 7: Building and operating applications on these engines
Prerequisites: Lesson 36 and the preceding foundations.
Learning outcomes
- Choose application transaction boundaries from business invariants and external effects.
- Explain pooling, N+1 requests, outbox publication, and retry identity through complete request paths.
- Identify where service separation creates additional consistency and recovery protocols.
A transaction can finish before the workflow is complete
An order service commits a new order, then publishes an order-created message. It crashes between those steps. The database is correct, but the shipping service never receives the event. Reversing the steps creates another failure: the message can be delivered even though the order transaction later aborts.
Application architecture must connect engine guarantees to the full workflow. A local atomic transaction does not automatically cover message publication, payment providers, caches, or another service’s database. The design should identify authoritative facts and every boundary where an outcome can become uncertain.
Choose transaction boundaries around invariants
A transaction should include the reads and changes required to preserve its invariant. A transfer needs both account changes under the chosen contract. A purchase may need stock, order identity, and payment-state recording. Splitting those into services can create cross-system coordination even if each service’s local database is transactional.
A boundary that is too large can retain locks, snapshots, memory, and participant state for too long. A boundary that is too small can expose partial business state. The useful unit follows the invariant and expected failure behavior rather than a rule that every API call should map to exactly one transaction.
Never keep a database transaction open while waiting for user interaction. External calls inside a transaction also require scrutiny because their delay and side effects do not share the database’s rollback semantics. A workflow can instead represent pending states and reconcile them explicitly.
Access patterns and N+1 requests
An ORM or convenient object API can conceal a network request for each related object. One parent query followed by one hundred dependent child queries at an illustrative 1-millisecond RTT spends roughly 101 milliseconds on communication before other work. The code can look like a simple loop while implementing an expensive distributed algorithm.
Joining, batching identifiers, or using a suitable prefetch can reduce exchanges. The replacement query must preserve multiplicity, permissions, and result size limits. Fetching every relationship eagerly can create a different problem by loading far more data than the endpoint needs.
A good endpoint has an explicit access budget: expected queries, rows examined, result bytes, and transaction duration. Tracing should expose actual remote calls rather than only the top-level handler time. Optimizing database internals cannot compensate indefinitely for unnecessary application exchanges.
Connection pools as admission boundaries
Pools amortize setup and bound simultaneous database conversations. Their waiting time belongs in endpoint latency. Increasing pool size can move queueing into the engine and increase retained state without increasing sustainable completions.
A pool borrower must receive a clean session with correct transaction and security state. On errors, the connection should be reset or discarded according to the driver’s contract. Returning an aborted or open transaction to another request can create confusing failures or leaks.
Per-service pools can multiply total connections across replicas and autoscaled instances. A configuration of fifty connections per process becomes five thousand across one hundred processes. The engine’s capacity and the aggregate admission model must account for that multiplication.
The transactional outbox
An outbox records the intended downstream event in the same transaction as the business state. A separate publisher reads committed outbox rows and delivers them. This removes the gap between committing the order and recording the obligation to publish.
BEGIN;
INSERT INTO orders (order_id, customer_id, status)
VALUES (901, 17, 'accepted');
INSERT INTO outbox (event_id, aggregate_id, event_type, payload)
VALUES (7001, 901, 'order_accepted', 'order=901');
COMMIT;The SQL is illustrative and assumes tables and uniqueness constraints appropriate to the application. A real payload format should be versioned and validated. The event identifier must remain stable across publisher retries.
The publisher can crash after delivery but before marking progress, causing redelivery. Therefore the consumer needs idempotency or another safe replay protocol. The outbox establishes a durable publication obligation; it does not guarantee that every downstream side effect happens exactly once without further design.
Consumer state and duplicate suppression
A consumer can transactionally record an event identifier alongside the resulting local change. On redelivery, the identifier prevents repeating the business effect. Its uniqueness scope, retention, and payload consistency need definition.
Ordering may be per aggregate rather than global. Two events for the same order can require sequence handling, while independent orders can progress in parallel. A poison event that always fails should not block unrelated work indefinitely. The system needs explicit quarantine, alerting, and reconciliation behavior.
If the consumer calls an external provider, local deduplication alone may not cover a crash between the provider action and local recording. The provider’s idempotency key or status API can help. If no such capability exists, the workflow must admit uncertainty and provide reconciliation rather than claim a guarantee it cannot enforce.
Service boundaries and ownership
A service should own a coherent set of facts and transitions. Sharing tables freely across services can undermine independent evolution and access control. Splitting one tight invariant across separately deployed stores can create unnecessary distributed transactions or compensation.
There is no universal rule that fewer services or more services are always better. Independent workloads can benefit from separate scaling and failure boundaries. Tightly coupled transactional logic can benefit from colocation. The architecture should explain which coordination it removes and which it introduces.
A database per service also changes reporting and joins. Analytical copies or query services can combine data while exposing freshness differences. An application should not assume that a materialized cross-service report is the authoritative source for a current exclusive decision.
A precise order workflow
A hypothetical shop separates three facts: an order was accepted, payment authorization is pending, and shipment is allowed. Treating them as one boolean conceals the fact that an external payment provider does not share the order database’s rollback mechanism.
The local transaction can create an order in a pending-payment state, reserve available stock under its invariant, and record a durable obligation to request payment. A worker submits that request with a stable provider idempotency key. It records the observed provider outcome in a later local transaction. Shipment begins only after the application reaches the required state.
This workflow may expose intermediate states, and that is intentional. “Pending” is a real durable state with an owner, deadline, and reconciliation procedure. It is more accurate than pretending a database transaction can atomically cover an arbitrary network provider.
accepted locally -> payment requested -> payment confirmed -> shipment allowed
| |
+-> expires/cancels +-> uncertain, reconcile provider statusFigure: A simplified business state machine. An uncertain external outcome remains distinct from definitive failure.
If the payment succeeds and the worker crashes before recording success, retrying with the same provider key can recover the result under the provider’s contract. If the provider has no suitable identity or status mechanism, the application must admit an uncertain state and resolve it operationally. A local transaction cannot manufacture missing guarantees in another system.
The outbox publisher’s crash windows
The transactional outbox closes one gap by placing state and publication intent in the same commit. It introduces a publisher with its own progress state. Consider three crash points: before delivery, after delivery but before recording progress, and after recording progress.
Before delivery, the event remains pending and can be retried. After delivery but before progress, the event can be delivered again. After progress, it is normally not selected again under that publisher’s protocol. Therefore the consumer must tolerate duplicates unless a stronger end-to-end protocol explicitly covers the delivery and effect.
Marking progress before delivery avoids duplicates by creating loss: a crash after marking but before sending makes the event disappear from future publisher work. This is the dual of the original order-and-publish problem. Rearranging two independent writes does not make them atomic.
A durable broker acknowledgment also has a scope. It may mean the broker retained the message under its policy, not that a consumer applied the business effect. The system should track publication, consumer application, and external effects separately if the workflow needs those distinctions.
Idempotent consumption requires protected state
A consumer receiving event E can transactionally check whether E was already processed, apply its local effect, and insert E into a processed-event table. A unique identifier prevents concurrent consumers from both treating the event as new.
begin transaction with scoped abort/cleanup on any failed step:
if processed(event_id):
require stored payload digest matches this event
saved_outcome = copy(prior outcome)
end read-only transaction and release protection
return saved_outcome
validate event schema and business preconditions
apply local state change
insert processed(event_id, payload_digest, outcome)
commit
return outcomeConcurrent claims use a uniqueness constraint in this same transaction; a losing claim rolls back its local effect before retrying or returning the stored result. The payload digest or equivalent consistency check detects accidental reuse of an identity for different content. The exact hash and representation are implementation choices, but the principle is to avoid returning an old result for a different logical event.
The completed-result branch ends its read-only transaction before returning. Any failed requirement or uniqueness conflict invokes the scoped abort and cleanup path; no branch leaves a transaction open on a reusable connection.
Retention must match replay. If processed identities are deleted after seven days but the broker can replay a month’s events, an old event may be applied again. Either retain deduplication state long enough, use a durable aggregate sequence, or restrict replay with a documented procedure. “Idempotent consumer” is not a timeless property unless its state lifetime supports it.
Idempotency can also be semantic rather than identifier-based. Setting a document to version 42 can reject older versions. Incrementing a balance by 10 requires stronger duplicate handling because applying it twice changes the result.
Ordering follows the business dependency
Global ordering can be expensive and unnecessary. An order’s “accepted” event should precede its “shipped” event, but independent orders can often proceed concurrently. Partitioning events by aggregate identity can preserve useful local order while allowing scale.
A consumer must still define what happens when it sees a gap. If sequence 7 arrives before 6, it can wait, request replay, or process only if the semantics allow it. Silently treating arrival order as authoritative can cause incorrect transitions. A shipment event received before payment confirmation should not bypass the shipment rule.
Poison events add another issue. One malformed or unsupported event can block a partition indefinitely. Quarantine with alerting and retained evidence can protect unrelated work, but skipping the event may leave downstream state incomplete. The product needs a reconciliation policy rather than merely moving the message to a different queue and declaring success.
Schema evolution belongs to this protocol. Consumers may run older code while producers emit newer events. Backward-compatible fields, versioned event types, and explicit required semantics prevent a new producer from breaking a long-retained replay history.
Pools are queues with session state
A connection pool protects the database only if aggregate concurrency remains bounded. One hundred application processes with 40 connections each can expose 4,000 simultaneous sessions. Adding application replicas can therefore increase database pressure even when each process keeps the same setting.
A useful pool limit follows measured service capacity and request duration, not the number of incoming users. If the engine completes 5,000 transactions/s with 10 ms mean connection occupancy, Little’s Law suggests about 50 occupied connections in that stable illustrative regime. This is not a recommended universal pool size; variance, parallel work, and the latency objective still matter.
Connections carry state: active transactions, temporary objects, session variables, prepared statements, and security context. A request must return a clean session or discard it. Reusing a connection with an open transaction can retain locks or expose another request’s settings. Pool resets are part of the isolation boundary.
Waiting in the pool should have a deadline and metric. Increasing the pool until its queue disappears can move waiting into the database, where every queued request may retain more expensive state. The goal is useful completions with bounded latency, not an empty queue at one chosen layer.
Batching the right relationships
An endpoint returning 100 orders can generate one query for orders and 100 more for customers. At 1 ms dependent communication per query, that is roughly 101 ms before execution. A join or batched customer lookup can remove most dependent exchanges.
The replacement must preserve semantics. A join can duplicate order rows when the relationship is one-to-many. Joining several collections can multiply rows dramatically. An eager-loading fix can trade N+1 calls for a huge intermediate result and more client decoding.
A bounded two-step approach is often clear: fetch the page of order identifiers and required fields, collect the distinct customer identifiers, then fetch those customers in one bounded query. It uses two exchanges and explicit result size. Whether it is preferable to a join depends on the engine, access pattern, and required snapshot consistency.
If the two queries must reflect one coherent state, they need an appropriate transaction or version contract. Removing a transaction for speed can allow inconsistent combinations. Performance changes should name any altered freshness or isolation behavior.
Service boundaries can create distributed invariants
Suppose inventory and orders are split into independent services. Accepting an order requires stock allocation and a durable order record. If each service commits independently, the workflow needs reservation states, compensation, or a distributed transaction supported by the actual architecture.
Splitting services may improve team ownership or independent scaling, but it creates a coordination boundary. That can be worthwhile when the domains are genuinely independent or intermediate states are acceptable. It is expensive when every request crosses the same tight invariant.
The opposite mistake is one giant transaction spanning unrelated concerns. Holding stock locks while sending email, generating a PDF, and waiting for analytics increases contention without improving the stock invariant. Durable obligations can move those downstream effects outside the critical transaction.
A good boundary keeps exact local invariants together and makes asynchronous consequences explicit. It does not follow a universal rule that all business actions belong in one transaction or every table deserves its own service.
Read models need an authority label
A service can maintain a denormalized order summary for fast display. That summary may lag the authoritative order and payment records. It is suitable for a dashboard under an explicit freshness contract, but not necessarily for deciding whether a refund is allowed.
The application should label which representation owns each decision. A local cache can display available stock while the purchase transaction checks the exact authoritative count. A search index can find products while authorization and current visibility are enforced before returning private content.
Derived representations need rebuild procedures. If the outbox or change log is retained for only a day, a week-long outage may require a new snapshot instead of replay. The snapshot and stream must align just as they do for client subscriptions. CQRS changes the organization of reads and writes; it does not eliminate synchronization.
Debezium’s outbox event router is one concrete integration for committed outbox records. Its role should be understood within the surrounding consumer and retention contracts. Outbox event router
External side effects and compensation
Compensation is a new action that attempts to offset an earlier one. Refunding a payment does not erase the fact that the payment occurred, and returning stock may fail if the business has already shipped the item. A saga must define these realities rather than describe compensation as ordinary rollback.
A hypothetical booking workflow reserves a room, charges a card, and sends a confirmation. If charging fails definitively, releasing the room is a useful compensation. If charging is uncertain, immediately releasing the room and retrying payment as a new operation can create a paid but unbooked customer. The state machine must first resolve the uncertain payment or define an explicit customer remediation path.
Every step needs an owner, identity, retry policy, timeout meaning, and evidence. Background reconciliation can compare local pending states with external provider status. Human escalation is appropriate when the system lacks enough evidence to automate safely; it should be a designed outcome, not an invisible abandoned row.
A complete latency-and-correctness budget
Assume an illustrative purchase endpoint spends 3 ms in a connection pool, 2 ms executing its transaction, 4 ms at durable commit, and 1 ms constructing the reply. It acknowledges in about 10 ms before client network delay. Payment may complete asynchronously in 300 ms, while shipment remains prohibited until confirmation.
This endpoint is fast because its success message means “order accepted and durable,” not “every external effect completed.” That is a legitimate product contract if communicated accurately. Calling it a completed purchase when payment is still uncertain would be misleading.
An alternative synchronous endpoint can wait for the external result, but should avoid holding unnecessary database locks during that wait. It can use durable pending state, call the provider, and finalize locally. The user waits longer while the database’s critical transaction remains short.
This distinction connects UX to systems design. A precise status model can improve responsiveness without weakening exact ownership or hiding failure. The application exposes progress honestly while preserving recoverable obligations behind the scenes.
A publication sequence with distinct outcomes
order service database publisher consumer store
| | | |
|-- order+outbox --->| | |
|<-- durable commit-| | |
| |-- event E ----->| |
| | |-- E ------------->|
| | | effect+dedup commit
| | |<-- acknowledgment-|
| |<-- progress ----| |Figure: A conceptual outbox path. A crash after consumer commit but before publisher progress can redeliver E, so the consumer’s deduplication belongs with its effect.
The diagram distinguishes three durable facts: the order and publication obligation exist, the consumer has applied the local effect, and the publisher has recorded progress. They can occur at different times without violating the workflow if retries and deduplication are designed accordingly.
A network acknowledgment between components has meaning only under its protocol. If the consumer acknowledges before committing its local effect, the publisher can advance while a subsequent crash loses the effect. If acknowledgment follows commit, a lost acknowledgment causes safe repetition when deduplication remains available.
This sequence is deliberately smaller than a universal event architecture. It supplies the minimum mechanism needed to avoid the order-and-publish gap. Additional brokers, schemas, and orchestration should be introduced for concrete scaling or ownership requirements, while preserving the same identities and transition meanings.
The lesson’s conclusion is that application reliability comes from connecting local atomic facts through explicit recoverable obligations. That connection is also a performance tool: it lets expensive or remote consequences leave the critical transaction without becoming invisible abandoned work.
This boundary discipline also makes debugging practical. A request can be traced from durable order identity to outbox event and consumer outcome, instead of inferring workflow success from one HTTP status. The identities connect evidence across the asynchronous stages.
Case connection
PostgreSQL’s transaction tutorial establishes a local atomic boundary. Debezium’s outbox event router documents a concrete integration around committed outbox records. The general analysis is that durable intent plus idempotent consumption is a way to connect that local boundary to asynchronous workflows, with ordering, retention, and external-provider semantics still explicit.
A production application should make transaction scope, remote calls, pool limits, publication obligations, and retry identities visible in its design. These boundaries remain important when workflows cross geographic distance.
Architectural conclusion
The database supplies a boundary with defined isolation and durability. The application composes that boundary with remote calls, queues, caches, and user-visible states. Correct composition requires explicit authority, stable identities, bounded waiting, and recoverable workflow transitions.
Performance and correctness often improve together when unnecessary remote exchanges and oversized transactions are removed. They diverge when an optimization silently weakens acknowledgment, freshness, or replay guarantees. A clear design makes that choice visible before production failures expose it.