Definition
- B-tree index -- a sorted data structure that lets the database binary-search instead of scanning every row, placed on columns used in WHERE, JOIN, and ORDER BY.
- EXPLAIN ANALYZE -- a command that shows the actual query plan and execution time, proving whether an index is used.
- ACID -- Atomicity, Consistency, Isolation, Durability -- the guarantees that make a money transfer safe.
- Window function -- a function like ROW_NUMBER or SUM OVER that computes across a set of rows without collapsing them into groups.
- HikariCP -- a high-performance JDBC connection pool that reuses a small fixed set of database connections.
- Flyway -- a migration tool that applies versioned SQL scripts automatically and in order on application startup.
Picture the National Bank of India's giant ledger room. A table is one big ledger, and ACID is the rule that a money transfer either fully happens or does not happen at all, so Rajesh never loses money that Priya never received. An index is the alphabetical tab on the side of the ledger: without it, a clerk reads every page to find Rajesh's SIPs, but with it the clerk jumps straight to the right page. A window function is a clerk who, while reading each row, writes a running total in the margin without merging the rows together. HikariCP is a small set of shared pens at the counter that clerks borrow and return, rather than every clerk fetching a new pen from the store each time.
Understanding the Concepts
What is a B-tree index and why does it matter?
A B-tree index keeps column values sorted in a balanced tree structure so the database can binary-search instead of scanning every row. You place indexes on columns that appear in WHERE, JOIN, and ORDER BY clauses. Without an index, PostgreSQL performs a sequential scan over every row in the table. With a composite index on account_id and status, the database jumps straight to matching rows.
-- Create a composite index for the SIP dashboard query
CREATE INDEX idx_sip_account_status
ON sip_investments(account_id, status);
-- Query that benefits from this index
SELECT sip_id, fund_name, monthly_amount, status
FROM sip_investments
WHERE account_id = 'SAV2001'
AND status = 'ACTIVE';
How do you prove an index works with EXPLAIN ANALYZE?
EXPLAIN ANALYZE runs the query and shows the actual plan the database chose, including whether it used a sequential scan or an index scan and the real execution time. You should run it before and after adding an index to prove the improvement.
-- Before index: sequential scan
EXPLAIN ANALYZE
SELECT * FROM sip_investments WHERE account_id = 'SAV2001';
Seq Scan on sip_investments (cost=0.00..18234.00 rows=42 width=128)
Filter: (account_id = 'SAV2001')
Rows Removed by Filter: 999958
Actual time: 312.456..312.501 ms
-- After index: index scan
EXPLAIN ANALYZE
SELECT * FROM sip_investments WHERE account_id = 'SAV2001';
Index Scan using idx_sip_account_status on sip_investments
Index Cond: (account_id = 'SAV2001')
Actual time: 0.042..0.089 ms
The difference on a million-row table is the gap between 312 milliseconds and under one millisecond.
How do ACID transactions protect money?
ACID guarantees that a transfer either fully completes or fully rolls back. Atomicity means the debit and credit happen as one unit. Consistency means the database moves from one valid state to another. Isolation means concurrent transfers do not see each other's intermediate states. Durability means a committed transfer survives a crash.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class TransferService {
private final AccountRepository accountRepository;
public TransferService(AccountRepository accountRepository) {
this.accountRepository = accountRepository;
}
@Transactional
public void transfer(String fromAccountId, String toAccountId,
BigDecimal amount) {
var from = accountRepository.findByIdForUpdate(fromAccountId);
var to = accountRepository.findByIdForUpdate(toAccountId);
if (from.getBalance().compareTo(amount) < 0) {
throw new InsufficientFundsException(
"Account " + fromAccountId + " has insufficient balance");
}
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
accountRepository.save(from);
accountRepository.save(to);
}
}
Rajesh transfers 50000.00 from SAV2001 to SAV2002
SAV2001 balance: 150000.00 -> 100000.00
SAV2002 balance: 200000.00 -> 250000.00
Transaction committed atomically
How do window functions work for rankings and running totals?
Window functions compute a value across a set of related rows while keeping every row visible. Unlike GROUP BY, which collapses rows, a window function adds a computed column next to each row. ROW_NUMBER ranks rows within a partition. SUM OVER produces running totals. LAG fetches the previous row's value for month-over-month comparison.
-- Rank each customer's SIPs by return percentage
SELECT
account_id,
fund_name,
return_pct,
ROW_NUMBER() OVER (
PARTITION BY account_id
ORDER BY return_pct DESC
) AS rank
FROM sip_investments
WHERE account_id = 'SAV2001';
-- Running cumulative invested total
SELECT
txn_date,
amount,
SUM(amount) OVER (ORDER BY txn_date) AS cumulative_invested
FROM sip_transactions
WHERE account_id = 'SAV2001';
-- Month-over-month change using LAG
SELECT
txn_date,
amount,
LAG(amount) OVER (ORDER BY txn_date) AS prev_amount,
amount - LAG(amount) OVER (ORDER BY txn_date) AS change
FROM sip_transactions
WHERE account_id = 'SAV2001';
account_id | fund_name | return_pct | rank
SAV2001 | HDFC Equity | 14.20 | 1
SAV2001 | ICICI Bluechip | 11.80 | 2
SAV2001 | SBI Bond Fund | 7.50 | 3
How do you use CTEs to make complex queries readable?
A Common Table Expression, written with WITH, names an intermediate result so a complex report reads top to bottom instead of as nested subqueries. CTEs improve readability without affecting performance in most cases.
WITH monthly_totals AS (
SELECT
DATE_TRUNC('month', txn_date) AS month,
SUM(amount) AS total_invested
FROM sip_transactions
WHERE account_id = 'SAV2001'
GROUP BY DATE_TRUNC('month', txn_date)
)
SELECT
month,
total_invested,
LAG(total_invested) OVER (ORDER BY month) AS prev_month,
total_invested - LAG(total_invested) OVER (ORDER BY month) AS change
FROM monthly_totals
ORDER BY month;
How do you size the HikariCP connection pool correctly?
The classic mistake is setting the pool size equal to the Tomcat thread count. If Tomcat has 200 threads and the pool has 200 connections, you waste database resources because most threads are not doing database work at any given moment. The pool should match the real concurrent database work, which is usually 10 to 20 connections even with hundreds of threads, because each query is short.
# application.yml
spring:
datasource:
hikari:
maximum-pool-size: 15
minimum-idle: 5
connection-timeout: 3000
idle-timeout: 600000
max-lifetime: 1800000
package com.bank.config;
import org.springframework.boot.actuate.health.Health;
import org.springframework.boot.actuate.health.HealthIndicator;
import org.springframework.stereotype.Component;
import com.zaxxer.hikari.HikariDataSource;
@Component
public class PoolHealthIndicator implements HealthIndicator {
private final HikariDataSource dataSource;
public PoolHealthIndicator(HikariDataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public Health health() {
int active = dataSource.getHikariPoolMXBean().getActiveConnections();
int pending = dataSource.getHikariPoolMXBean().getThreadsAwaitingConnection();
if (pending > 0) {
return Health.down()
.withDetail("active", active)
.withDetail("pending", pending)
.build();
}
return Health.up().withDetail("active", active).build();
}
}
How do you prevent SQL injection?
You prevent SQL injection by using parameterized queries, never string concatenation. In Spring Data JPA, the @Query annotation with bound parameters is safe. Building a query by concatenating user input lets attackers read or destroy the ledger.
package com.bank.repository;
import com.bank.model.SipInvestment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface SipInvestmentRepository
extends JpaRepository<SipInvestment, Long> {
// CORRECT -- parameterized query, safe from injection
@Query("SELECT s FROM SipInvestment s WHERE s.accountId = :accountId")
List<SipInvestment> findByAccount(@Param("accountId") String accountId);
}
// WRONG -- string concatenation, vulnerable to injection
String sql = "SELECT * FROM sip_investments WHERE account_id = '"
+ userInput + "'";
// An attacker sends: ' OR 1=1; DROP TABLE sip_investments; --
// The query becomes:
// SELECT * FROM sip_investments WHERE account_id = '' OR 1=1;
// DROP TABLE sip_investments; --'
How does Flyway manage schema migrations?
Flyway applies versioned SQL scripts automatically and in order on application startup. Each migration file has a version prefix like V1, V2, and Flyway tracks which versions have been applied. The rule is that you never edit an applied migration -- you always add a new version.
-- V1__create_sip_tables.sql
CREATE TABLE sip_investments (
sip_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) NOT NULL,
fund_name VARCHAR(100) NOT NULL,
monthly_amount NUMERIC(15, 2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- V2__add_performance_indexes.sql
CREATE INDEX idx_sip_account_status
ON sip_investments(account_id, status);
CREATE INDEX idx_sip_created
ON sip_investments(created_at);
# application.yml
spring:
flyway:
enabled: true
locations: classpath:db/migration
baseline-on-migrate: true
Flyway Community Edition 9.22.0
Successfully applied 2 migrations to schema "public"
V1__create_sip_tables.sql
V2__add_performance_indexes.sql
What about read replicas and sharding?
Read replicas absorb read-heavy traffic. The primary handles writes, and one or more replicas serve reads like end-of-day reports and dashboard queries. Sharding splits a huge table across machines by a shard key such as account region. Sharding is used only at very large scale because it adds complexity to joins and transactions.
package com.bank.config;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
? "replica"
: "primary";
}
}
What are common mistakes?
The most common database mistakes in banking applications are using double instead of BigDecimal for money, concatenating user input into SQL, setting HikariCP pool size equal to the Tomcat thread count, and editing applied Flyway migrations.
// WRONG -- floating-point money
double balance = 100000.10;
double fee = 0.01;
System.out.println(balance - fee);
// Output: 100000.08999999999
// CORRECT -- BigDecimal money
import java.math.BigDecimal;
BigDecimal balance = new BigDecimal("100000.10");
BigDecimal fee = new BigDecimal("0.01");
System.out.println(balance.subtract(fee));
// Output: 100000.09
Visual Overview
Query Without Index Query With Index
────────────────── ─────────────────
┌──────────────────┐ ┌──────────────────┐
│ SELECT * │ │ SELECT * │
│ WHERE acct = │ │ WHERE acct = │
│ 'SAV2001' │ │ 'SAV2001' │
└────────┬─────────┘ └────────┬─────────┘
│ │
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ Seq Scan │ │ Index Scan │
│ Read ALL rows │ │ B-tree lookup │
│ 1,000,000 rows │ │ Jump to 42 rows │
└────────┬─────────┘ └────────┬─────────┘
│ │
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ 312 ms │ │ 0.08 ms │
└──────────────────┘ └──────────────────┘
Connection Pool (HikariCP):
┌────────────────────────────────────────────┐
│ Tomcat Threads (200) │
│ ┌───┐ ┌───┐ ┌───┐ ┌───┐ │
│ │ T1│ │ T2│ │ T3│ . . . │T200│ │
│ └─┬─┘ └─┬─┘ └─┬─┘ └───┘ │
│ │ │ │ most threads idle │
│ ▼ ▼ ▼ │
│ ┌────────────────────┐ │
│ │ HikariCP Pool (15) │ borrow / return │
│ │ ┌──┐┌──┐┌──┐... │ │
│ │ │C1││C2││C3│ │ │
│ │ └──┘└──┘└──┘ │ │
│ └─────────┬──────────┘ │
│ ▼ │
│ ┌────────────────────┐ │
│ │ PostgreSQL │ │
│ └────────────────────┘ │
└────────────────────────────────────────────┘
Key Takeaway
The biggest lever for database performance in a banking service is indexing. A B-tree index on the columns you filter and join on turns a sequential scan over every row into an index scan that jumps straight to the matching rows. On a million-row table that is the difference between 312 milliseconds and under one millisecond, and you prove it with EXPLAIN ANALYZE before and after. Indexes speed up reads but slow down writes, so you index deliberately, not everywhere.
For correctness, ACID transactions guarantee that a money transfer either fully completes or fully rolls back. Window functions like ROW_NUMBER, SUM OVER, and LAG compute rankings, running totals, and month-over-month comparisons without collapsing rows, and CTEs make complex reports readable. Every money figure uses BigDecimal because floating-point arithmetic introduces rounding errors that are unacceptable in banking.
For throughput and security, the HikariCP pool should be sized to real concurrent database work rather than the Tomcat thread count, parameterized queries prevent SQL injection by treating input as data rather than executable SQL, and Flyway applies versioned schema migrations automatically so the schema is always reproducible.
Remember one sentence: put B-tree indexes on the columns you filter and join on, prove the improvement with EXPLAIN ANALYZE, use BigDecimal for money, and always use parameterized queries to prevent SQL injection.
---