Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
Aurora Serverless v2 for spiky traffic
Serverless v2 autoscales capacity in fine-grained ACU steps near-instantly, you pay per-ACU-second, and provisioned is fixed cost regardless of load. matching capacity model to traffic shape.
Replica lag and read-your-writes consistency
Stale reads come from async replica lag, the guarantee a user expects is read-your-writes, and you route that user's reads to the primary after a write. understanding replication lag and session consistency.
Read replica vs Multi-AZ in RDS
Multi-AZ is synchronous standby for failover, read replicas are async copies for read throughput, and the two solve different problems. separating high availability from read scaling. claiming a Multi-AZ standby serves reads.
Transaction isolation levels and their tradeoffs
Isolation levels control which concurrency anomalies (dirty/non-repeatable reads, phantoms) are allowed; higher levels mean stronger consistency but more blocking and less concurrency. isolation tradeoffs.
Pooled connection lifecycle and close() semantics
Borrow from pool, use, then close() returns it to the pool rather than tearing down the socket. pooled connection semantics. thinking close() physically severs the connection, or never closing and leaking connections.
Connection pools and the problem they solve
A pool reuses pre-opened connections so requests skip the expensive connect handshake; without one, every request pays setup latency and may overwhelm the database. connection reuse basics.
Purpose of database drivers (JDBC/ODBC)
A driver translates a standard API into each database's wire protocol, so app code stays portable across vendors. abstraction layers. thinking the app talks raw protocol or that drivers are interchangeable across databases.
Defense-in-depth against SQL injection
Beyond parameterization, apply least-privilege accounts, stored procedures, input allowlisting, and monitoring to shrink blast radius. layered SQLi mitigation. treating parameterized queries as the only and complete defense.
Diagnosing degradation with normal CPU and memory
When CPU and memory look fine, sessions are waiting, not computing; examine wait statistics, lock and latch contention, I/O waits, and buffer pool hit ratio. wait-based diagnosis. chasing CPU and memory dashboards alone.
RBAC vs direct user grants
Direct grants tie rights to individuals; RBAC groups rights into roles users inherit, so changes happen once per role. permission-management scalability.
Point-in-Time Recovery (PITR)
Restore a base backup then replay archived write-ahead logs up to a chosen moment, enabling recovery to just before an error. recovery design. thinking periodic backups alone allow recovery to any instant.
Connection pooling and its key parameters
Reuse open connections to skip costly handshakes; tune max pool size and connection timeout. pooling tradeoffs. setting max size huge, exhausting database connections, or treating the pool as free.
Least privilege for database service accounts
Grant each account only the minimum rights its job needs; for an app service account, scope grants to specific tables and verbs, never use the superuser. secure access design.
Full, differential, and incremental backups
Full copies everything; differential copies all changes since the last full; incremental copies changes since the last backup of any type. backup-strategy tradeoffs.
What is eventual consistency?
Replicas converge to the same value if writes stop, allowing temporary staleness for higher availability and lower latency. consistency-model tradeoffs. claiming it means data is wrong or never converges.
Leader-follower vs multi-leader replication
Single-writer leader-follower is simple but a write bottleneck; multi-leader accepts writes in many regions for latency and availability. replication topology tradeoffs. ignoring that multi-leader needs conflict resolution.
Range-based vs hash-based sharding trade-offs?
Range sharding keeps ordered keys together, great for range scans but prone to hot spots on sequential keys; hash sharding spreads keys evenly, avoiding hot spots but killing efficient range… choosing a shard strategy by query pattern.
What is sharding and why shard over vertical scaling?
Sharding splits one dataset across servers by a shard key so each holds a subset; you shard because vertical scaling hits hardware ceilings, gets costly, and remains a single point of failure. horizontal partitioning rationale.
What is database replication and why use it?
Replication keeps copies of data on multiple servers; primary benefits are high availability through failover and improved read scalability by spreading reads. basics of copying data across nodes.
What is a Type 2 slowly changing dimension?
An SCD handles dimension attributes that change over time; Type 2 inserts a new row per change with a surrogate key and validity dates, marking one current. preserving history in dimensional models.
How does columnar storage speed up analytics?
Columnar stores each column contiguously, so aggregations read only needed columns, scan far less data, and compress better with vectorized execution. physical storage layout versus query type.
What is a star schema?
A central fact table of measures and foreign keys surrounded by denormalized dimension tables of descriptive attributes, joined in one hop for fast, simple analytical queries. dimensional modeling fundamentals.
What is the difference between OLTP and OLAP?
OLTP handles many short read-write transactions on normalized current data; OLAP runs few large analytical scans over denormalized historical data. understanding two opposite workload profiles.
How do you choose between relational and NoSQL databases?
Relational gives schema, joins, and ACID for structured related data; document gives flexible schema and horizontal scale for varied or denormalized data. ability to match a data model to requirements.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.