Database Design for Banking in 2026: Core Schemas and Sharding
A practical guide to core banking database design: ledger schemas, double-entry bookkeeping, ACID consistency, event sourcing, and sharding strategies for high-volume transaction processing.
Core banking database design principles
Every fintech product is only as reliable as its database. The card payment, the SEPA credit transfer, the interest accrual that runs overnight - all of them write to and read from a central store of record. Getting that store wrong is not a performance problem you tune away later; it is a correctness problem that surfaces as reconciliation breaks, regulatory findings, or customer money that cannot be located.
Core banking database design rests on a handful of principles that have survived every generation of technology change: from mainframe VSAM files through relational databases to today's distributed cloud-native systems. The names evolve but the underlying requirements do not.
Immutability
Posted transactions are never updated or deleted. Corrections arrive as new, reversing entries. The ledger only grows forward.
Double-entry bookkeeping
Every money movement debits one account and credits another for the same amount. The sum of all entries is always zero.
Event sourcing
The authoritative record is the ordered log of events, not a mutable balance field. Balances are derived by replaying that log.
Practically, these principles mean: use DECIMAL or NUMERIC for every monetary column (never FLOAT), enforce foreign-key constraints at the database level not just the application layer, and treat the transaction table as append-only from day one. Decisions made at schema design time are expensive to reverse once production data exists.
Let's discuss your project and see how we can launch your digital banking product together
Request demoSchema design for accounts, postings and products
A minimal but complete banking schema needs three core domain entities and clean separation between them. The exact column names vary by vendor or team, but the conceptual structure below reflects how production systems at fintechs are organized in 2026.
| Entity | Key columns | Design notes |
|---|---|---|
| Customer | customer_id, status, KYC tier, created_at | PII stored encrypted (AES-256). A separate KYC event table holds AML check results with immutable audit log. |
| Account | account_id, customer_id, product_id, currency, status, current_balance, available_balance | Balance columns are derived aggregates updated transactionally; stored for read speed. Product_id links to the product catalogue, not hard-coded logic. |
| Posting / Ledger entry | posting_id, account_id, transaction_id, amount (DECIMAL), direction (DR/CR), value_date, booking_date, status | Append-only. transaction_id groups the two legs of a double-entry pair. Balances are reconciled by summing postings. |
| Transaction | transaction_id, reference, channel, initiated_at, settled_at, narrative | Parent record linking one or more posting pairs. Stores external references (SWIFT UETR, SEPA end-to-end ID). |
| Product | product_id, type, interest_rate_config, fee_config, currency_rules | Product definitions as data - a current account, savings account, and loan differ by configuration not code. Enables new products without schema changes. |
A pattern many cloud-native cores adopt is separating the General Ledger (GL) from the customer-facing account layer. The GL enforces double-entry at the system level; the account layer aggregates and presents balances to customers and APIs. Reporting runs against a separate read replica or a data warehouse fed by Change Data Capture (CDC), which prevents analytics queries from contending with real-time transaction writes.
Consistency, ACID and the ledger
Banking is one of the few domains where the full ACID guarantee - Atomicity, Consistency, Isolation, Durability - is non-negotiable. A payment debit that succeeds without its corresponding credit is not a performance degradation; it is missing money.
| Property | What it means for banking | Practical enforcement |
|---|---|---|
| Atomicity | The debit and credit of a transfer either both commit or both roll back. No partial states. | Single database transaction wrapping both posting rows. Saga pattern for distributed ledgers. |
| Consistency | Sum of all DR minus sum of all CR must equal zero after every transaction. | Database CHECK constraints + application-level assertion at posting time. |
| Isolation | A balance check during a concurrent transfer must see either the pre- or post-transfer state, never a halfway view. | Serializable or REPEATABLE READ isolation level; row-level locking for balance updates. |
| Durability | Once a payment is confirmed, it must survive a server crash or power failure. | WAL (write-ahead log) in PostgreSQL/Oracle; synchronous replication to at least one standby. |
Event sourcing complements ACID by treating the posting log as the canonical record. Rather than storing a mutable balance field as the source of truth, the system stores every event (debit, credit, fee, interest accrual) and derives the current balance by summing them. This creates a natural audit trail, makes time-travel queries trivial ("what was the balance at 09:15:00 on 3 March?"), and simplifies the path to regulatory reporting. The trade-off is that balance reads require aggregation unless a materialized view or a CQRS read model is maintained alongside the event store.
PostgreSQL is the most common open-source engine for this pattern in 2026: it supports serializable isolation, row-level locking, logical replication for CDC, partitioning by range or hash, and the NUMERIC type with arbitrary precision. Oracle and DB2 remain in production at tier-one banks for legacy reasons. Cloud-managed variants (AWS Aurora, Google AlloyDB, Azure Database for PostgreSQL) add managed failover and storage autoscaling on top of the same wire protocol.
Sharding strategies for high-volume transaction processing
A single PostgreSQL node can handle roughly 10,000-30,000 transactions per second under good conditions. Many fintech platforms hit that ceiling within a few years of launch. Once vertical scaling (adding RAM and CPU to one server) reaches its cost and physical limit, the next step is horizontal scaling through sharding - distributing rows across multiple autonomous database nodes so that each node holds only a fraction of the data and traffic.
A shard map (sometimes called a routing table or directory) tracks which data lives on which node. Every query passes through a routing layer that consults the shard map and directs the request to the right node.
| Strategy | How it works | Banking fit | Watch out for |
|---|---|---|---|
| Hash-based | Hash function on the shard key (e.g., account_id) routes to a fixed shard. Consistent hashing minimises data movement when nodes are added. | Excellent for write distribution across accounts. Even load by design. | Range queries across many accounts require fan-out to all shards. |
| Range-based | Sequential ranges of the key (e.g., account IDs 0-999999 on shard 1, 1000000-1999999 on shard 2). | Good for time-series data and date-range reporting. | New accounts or recent transactions concentrate on the latest shard - classic hot-spot problem. |
| Geo-sharding | Data routed by customer geography. EU customers on EU shards, US customers on US shards. | Reduces latency; supports data-residency requirements (GDPR, local regulation). | Cross-border transfers require cross-shard coordination. |
| Directory-based | Lookup table maps each key to its shard explicitly. Maximum flexibility in placement. | Useful for VIP/enterprise accounts that need dedicated shards. | The directory itself becomes a critical single point of failure if not replicated. |
Scaling reads and writes, partitioning keys, and hot-spot avoidance
Choosing a shard key is the most consequential decision in a sharded banking architecture. A poor key choice creates hot spots - shards that receive a disproportionate fraction of traffic while other shards sit idle. The classic mistake is sharding by a low-cardinality field (currency, account status, country) rather than a high-cardinality identifier (account ID, customer ID, transaction ID). With millions of transactions, a low-cardinality key collapses the data onto a handful of shards regardless of how many nodes you add.
Practical guidelines for shard key selection in banking:
- Use account_id or customer_id for ledger data - all postings for one account stay on the same shard, making single-account queries fast and single-shard.
- Avoid sequential IDs from a single sequence as the shard key in hash mode - consistent hashing handles this, but auto-increment integers from one generator still concentrate writes momentarily. Use UUIDs v4 or snowflake IDs instead.
- Pre-split shards if you know your initial load - start with more shards than you need rather than resharding later. Resharding a live ledger without downtime is one of the hardest operational tasks in fintech engineering.
- Maintain referential integrity at the application layer - traditional foreign keys do not cross shard boundaries. The application (or a saga coordinator) must enforce consistency between postings on different shards for cross-account transfers.
Read scaling is handled separately from write sharding. Read replicas on each shard serve reporting, balance lookups, and statement generation without contending with write traffic. A CQRS (Command Query Responsibility Segregation) pattern is common: writes go to the sharded OLTP cluster; reads for reporting and analytics go to a separate read model or a data warehouse (BigQuery, Redshift, Snowflake) populated via CDC pipelines.
For cross-shard queries (e.g., "show me all transactions above £10,000 across all accounts for AML review"), the options are: fan-out the query to all shards and merge results in the application layer, maintain a cross-shard materialized index updated via event streaming, or route such queries to the data warehouse. Most production systems use all three depending on query type and latency tolerance.
Backup, audit and regulatory data retention
Financial regulators in every jurisdiction require that transaction records be retained for a defined minimum period - typically five to seven years for standard transaction data, and up to ten years for AML-related records. The database design must make this retention operationally feasible and auditable without degrading production performance.
| Requirement | Implementation approach |
|---|---|
| Immutable audit trail | Append-only ledger tables. Database triggers or application logic prevent UPDATE/DELETE on posted rows. Write audit events to a separate append-only audit log (can be a separate schema or a streaming sink like Kafka). |
| Regulatory retention (5-10 years) | Partition transaction tables by month or quarter. Older partitions are moved to cheaper cold storage (object store, archival tier) while remaining queryable via partitioned views or federated query engines. |
| Backup and recovery | Point-in-time recovery (PITR) via WAL archiving. At minimum: daily full backup + continuous WAL streaming. Test restores quarterly. RTO and RPO targets documented in the business continuity plan. |
| Data masking and PII protection | Encrypt PII columns at the application layer (AES-256) before writing to DB. Mask data in non-production environments. Column-level encryption for particularly sensitive fields (IBAN, card numbers). |
| Access control | Row-level security (PostgreSQL RLS) ensures application roles only access their own tenant's data. Separate read-only roles for reporting. DBA access logged and time-limited. |
The immutability rule also simplifies compliance audits. When a regulator or internal audit team asks "show me every transaction that touched account X between date A and date B", the answer is a straightforward query against the posting table - no reconstruction from logs, no worry that rows were edited. That simplicity is worth the design discipline of treating the ledger as append-only from the start.
Disaster recovery for a banking database goes beyond backup frequency. The recovery time objective (RTO) and recovery point objective (RPO) need to be defined before launch, not after an incident. A typical production setup uses synchronous replication to a hot standby in a second availability zone (RPO near zero), plus asynchronous replication to a third region (RPO a few minutes) for catastrophic failures. These targets feed directly into the business continuity plan required by most banking regulators.
Where Crassula fits
Crassula's white-label banking platform is built on these principles. The ledger is append-only, double-entry, and ACID-compliant from the ground up. Account balances are maintained transactionally and reconciled against the posting log. Audit trails are written to an immutable event store and available for regulatory inspection on request.
For clients launching new banking or payment products, Crassula removes the need to design and build this infrastructure from scratch. The data layer, the reconciliation engine, the partitioning and retention policies - all are included in the platform. Teams focus on the customer experience and product configuration rather than database engineering.
If you are evaluating database architecture for a core banking build, or assessing whether a white-label platform is the right fit for your transaction volumes and compliance requirements, the Crassula team is available to walk through the technical specifics.
FAQ
Start with the ledger as the single source of truth. Define your core entities - Customer, Account, Posting (ledger entry), Transaction, and Product - with clean separation between them. Use DECIMAL or NUMERIC for all monetary columns. Make the posting table append-only from day one: corrections arrive as new reversing entries, never as updates to existing rows. Enforce ACID transactions at the database level for every funds movement. Separate your write path (OLTP) from your reporting/analytics path (read replica or data warehouse fed via CDC). Build retention partitioning in from the start so that older data can be archived without schema changes.
Double-entry bookkeeping means every money movement produces two ledger entries - a debit on one account and a credit on another for the same amount. The sum of all entries is always zero, which makes it impossible for money to appear or disappear silently. Any imbalance is immediately detectable.
Event sourcing extends this by storing the ordered log of events (each posting) as the authoritative record, with balances derived by summing that log rather than stored as mutable fields. The combination gives you a natural audit trail, time-travel queries ("what was the balance at time T?"), and a foundation for regulatory reporting - without any additional audit infrastructure.
Sharding is horizontal partitioning: distributing rows across multiple independent database nodes so that each node holds only a fraction of the data. A routing layer (shard map) directs each query to the correct node.
In banking you need sharding when a single database node can no longer handle your peak write throughput or when your data volume makes single-node storage impractical. A well-tuned PostgreSQL instance handles roughly 10,000-30,000 transactions per second. At higher volumes, or when you need geo-distributed data residency for regulatory compliance, sharding becomes the standard solution. Many mid-size fintechs reach this threshold within two to three years of launch.
The shard key must have high cardinality - enough distinct values to spread data evenly across all shards. For a banking ledger, account_id or customer_id are the natural choices: all postings for one account land on the same shard, making single-account queries fast and requiring no cross-shard joins.
Avoid sharding by low-cardinality fields (currency, country, status) - they create hot spots where one shard holds the majority of data. Avoid sequential auto-increment integers as shard keys if new accounts are being created at high volume; use UUIDs v4 or snowflake IDs instead. Plan your shard count generously at launch - resharding a live ledger without downtime is one of the most painful operations in fintech infrastructure.
Within a single shard, standard ACID database transactions maintain consistency. The hard problem is cross-shard transactions - for example, a transfer between two accounts that sit on different shards.
The standard approaches are: the saga pattern (a sequence of local transactions coordinated by a saga orchestrator or choreography via events, with compensating transactions for rollback), and two-phase commit (2PC) across nodes (rarely used in high-throughput systems due to latency and coordinator failure risk). Most production fintech ledgers use sagas, with an idempotency key on every operation so that retries after a partial failure are safe. The event store that records each step of the saga also serves as the audit trail for cross-shard operations.
