Databases, Redis, MyBatis

SQL and relational design

What are ACID properties?

Atomicity (all or nothing), Consistency (constraints hold after each transaction), Isolation (concurrent transactions don't see each other's partial work, to the chosen level), Durability (committed data survives a crash, via the write-ahead log).

Explain isolation levels and the anomalies they prevent.

Level	Dirty read	Non-repeatable read	Phantom
Read uncommitted	possible	possible	possible
Read committed (Postgres default)	no	possible	possible
Repeatable read (MySQL default)	no	no	possible (not in Postgres's snapshot implementation)
Serializable	no	no	no

Also know lost update (two read-modify-write transactions overwrite each other): fix with SELECT ... FOR UPDATE, optimistic versioning, or an atomic UPDATE ... SET x = x + 1.

How does an index work? When does it not help?

A B-tree index keeps keys sorted with pointers to rows, so lookups and range scans are O(log n). It doesn't help for: functions on the column (WHERE lower(email) = ... unless you index the expression), leading-wildcard LIKE '%x', low-selectivity columns, or a composite index whose leading column isn't in the query. Indexes slow writes and use space. A covering index includes all needed columns so the table isn't read.

Composite index (a, b, c): which queries use it?

WHERE a = ?, a = ? AND b = ?, a = ? AND b = ? AND c = ?, a = ? ORDER BY b, and a = ? AND b > ?. Not WHERE b = ? alone (leftmost-prefix rule). Put equality columns first, then the range/sort column.

Clustered vs. non-clustered index?

Clustered: the table rows are stored in index order (one per table; InnoDB's primary key). Non-clustered: a separate structure pointing to rows. Postgres tables are heaps; all indexes are secondary (CLUSTER reorders once).

Normalisation vs. denormalisation?

Normalise (1NF atomic values, 2NF no partial dependency on a composite key, 3NF no transitive dependency) to avoid update anomalies and duplication. Denormalise deliberately for read performance (reporting tables, materialised views), accepting the cost of keeping copies in sync.

Work link: the Index Research schema redesign. Fill in: was it normalising duplicated per-client data, or adding a reporting structure?

Write SQL: second highest salary; top earner per department; duplicates.

-- Second highest salary
SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

-- Top earner per department (window function)
SELECT * FROM (
  SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employee e
) t WHERE r = 1;

-- Duplicate emails
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- Running total per account
SELECT account_id, trade_time, qty,
       SUM(qty) OVER (PARTITION BY account_id ORDER BY trade_time) AS position
FROM fills;

Know ROW_NUMBER (unique), RANK (gaps on ties), DENSE_RANK (no gaps), LAG/LEAD, and WHERE vs. HAVING.

Inner vs. left vs. full join?

Inner: matching rows only. Left: all left rows, nulls where the right has no match (find "models with no funds": LEFT JOIN ... WHERE f.id IS NULL). Full: all rows from both. Cross: every combination.

How do you read an execution plan?

EXPLAIN ANALYZE shows each node (Seq Scan, Index Scan, Nested Loop, Hash Join, Sort), estimated vs. actual rows and time. Look for sequential scans on big tables, big gaps between estimated and actual rows (stale statistics: ANALYZE), sorts spilling to disk, and nested loops over large inputs.

How do you scale a relational database?

In order: fix queries and indexes → cache reads (Redis) → read replicas (replication lag!) → connection pooling (HikariCP, PgBouncer) → partitioning large tables (by date) → vertical scaling → sharding by a key (account id), which costs cross-shard queries and transactions.

MyBatis

How does MyBatis work?

Mapper interfaces bound to SQL in XML or annotations; SqlSession executes them; result maps map columns to objects (including nested association/collection for joins); dynamic SQL with <if>, <choose>, <where>, <foreach>; #{} binds a parameter safely (prepared statement) while ${} substitutes text (SQL injection risk, only for trusted identifiers like column names in ORDER BY). Integrates with Spring via mybatis-spring and @MapperScan; first-level cache per session, optional second-level cache.

What changed from iBatis to MyBatis?

iBatis 2 used SqlMapClient and sqlMap XML with #param# syntax; MyBatis 3 uses mapper interfaces, #{param}, namespaces tied to the interface, annotations, better dynamic SQL and Spring integration. Migration means converting XML maps, replacing DAO code that called SqlMapClient with mapper interfaces, and re-testing result mappings.

Redis

What is Redis and what data types does it have?

An in-memory data store, single-threaded command execution (so each command is atomic), optional persistence. Types: strings (counters, cached JSON), hashes (objects), lists (queues), sets, sorted sets (leaderboards, ranked by score), streams, bitmaps, HyperLogLog (approximate counts), geo.

Prayog link: the leaderboard is a sorted set: ZADD to update a score, ZREVRANGE 0 99 for the top 100, ZREVRANK for "my rank", all O(log n). Redis is treated as a cache: if it's lost, it's rebuilt from Postgres and swapped in atomically.

Caching patterns: cache-aside, write-through, write-behind?

Cache-aside (most common): read from cache; on miss, read DB and populate with a TTL; on write, update DB then delete the cache key. Write-through: write to cache and DB together. Write-behind: write to cache, flush to DB asynchronously (fast, risk of loss). Spring: @Cacheable, @CacheEvict, @CachePut.

Why delete the key on update instead of updating it?

Two concurrent writers can update the cache in the opposite order of the DB, leaving stale data. Deleting means the next read loads the current value. A short TTL bounds any remaining staleness.

What are cache stampede, penetration and avalanche?

Stampede: a hot key expires and many requests hit the DB at once; fix with a lock/single-flight so one request reloads, or refresh early. Penetration: requests for keys that don't exist always miss; cache the "not found" briefly or use a Bloom filter. Avalanche: many keys expire together; add random jitter to TTLs.

Eviction policies?

When maxmemory is reached: noeviction (errors), allkeys-lru, volatile-lru (only keys with a TTL), allkeys-lfu, volatile-ttl, random. For a pure cache, allkeys-lru or allkeys-lfu.

Redis persistence and HA?

RDB snapshots (compact, can lose recent writes), AOF (append every write; everysec fsync is common), or both. HA with replicas + Sentinel (failover) or Redis Cluster (sharding over 16,384 hash slots). AWS: ElastiCache.

How do you implement a distributed lock or rate limiter with Redis?

Lock: SET lock:job <uuid> NX PX 30000; release only if the value is still yours (Lua script). Locks can expire mid-work, so use fencing tokens for correctness-critical work. Rate limiter: INCR a per-window key with EXPIRE (fixed window), or a sorted set of timestamps (sliding window), or a token bucket in Lua.

DynamoDB

How does DynamoDB work? Partition key vs. sort key?

A managed key-value/document store. Each item has a partition key (hashed to pick the storage partition) and optionally a sort key (items with the same partition key are stored sorted, so you can query ranges: PK = model#123 AND SK begins_with "version#"). Single-digit-ms latency at any scale if access is spread evenly.

What are GSIs and LSIs?

Global secondary index: a different partition + sort key over the same data, eventually consistent, its own capacity; add any time. Local secondary index: same partition key, different sort key, strongly consistent option, must be defined at table creation, 10 GB per partition key limit.

What is a hot partition and how do you avoid it?

Too much traffic on one partition key (a popular model id, today's date as a key) throttles that partition. Use high-cardinality keys, add a random or calculated suffix (write sharding), and cache hot reads (DAX/Redis).

When DynamoDB vs. PostgreSQL?

DynamoDB when access patterns are known up front, scale is large or spiky, and you need predictable latency without managing servers. Postgres when you need ad-hoc queries, joins, complex transactions and constraints. DynamoDB modelling starts from the queries ("single-table design"); relational starts from the entities.

Consistency and transactions in DynamoDB?

Reads are eventually consistent by default; strongly consistent reads are optional (not on GSIs). Conditional writes (attribute_not_exists, version checks) give optimistic locking. TransactWriteItems gives ACID across up to 100 items.

PostgreSQL specifics

What is MVCC?

Multi-version concurrency control: an update writes a new row version; readers see the snapshot valid at their start, so readers don't block writers and writers don't block readers. Old versions are cleaned by VACUUM (autovacuum); neglecting it bloats tables.

Why does Prayog's ledger use one transaction per batch?

A batch of fills either applies completely (fills, positions, P&L, applied event ids) or not at all. On failure, the whole batch retries in a new transaction; because applied ids are recorded in the same transaction, redelivery is a no-op. That gives effectively-once results from an at-least-once Kafka stream.