Database Proxies: A Manager for Your Database Traffic
A database proxy is a manager between your app and database, handling requests to improve performance and security. It pools connections, caches queries, and balances load, preventing any single server from being overwhelmed.
SQL Query Builders: Write SQL Without Writing SQL
An SQL query builder is a translator for your database, converting visual clicks or chained code methods into raw SQL. It's used to write safer, database-agnostic code or to let non-technical users build queries. The footgun is generating inefficient queries.
ODBC: The Universal Translator for Databases
ODBC acts as a universal translator, letting one application speak to many different relational databases. Your app uses the standard ODBC interface, and a specific "driver" handles the unique protocol for each database. The footgun is performance overhead.
SQL Injection: When User Input Becomes a Command
SQL injection tricks a database into running unintended commands by sneaking them into user input. It's a common attack on websites where user data is directly stitched into SQL queries. The footgun is trusting input; always use prepared statements instead.
How Database Indexes Rot and How to Fix Them
Your database indexes rot over time, making queries slower. Frequent writes cause fragmentation (disordered pages) and low page density (half-empty pages), forcing more disk I/O.
Two-Phase Commit (2PC): All or Nothing, Together
Two-Phase Commit (2PC) ensures a distributed transaction is atomic: all participants either commit or abort together. A coordinator first asks all nodes to prepare (vote), then issues a final commit or abort.
Read Replicas: Scale Out Your Database Reads
A read replica is a read-only copy of your database that handles query traffic. Use it for read-heavy apps to prevent your primary DB from becoming a bottleneck. The footgun: replication is asynchronous, so reads from a replica can return slightly stale data.
Fact Table: The Numbers in Your Data Warehouse
A fact table is the ledger of business events, recording what happened and how much. It's the core of a data warehouse, holding sales figures or page views. The footgun is storing descriptive text here; that belongs in linked dimension tables.
BASE: Trading Consistency for Availability
BASE is a database design philosophy that prioritizes availability over strict consistency, the opposite of ACID. It's used in large-scale systems like social media where uptime is key and slightly stale data is okay.
PostgreSQL's Free Space Map: Finding Room to Write
A Free Space Map is a table of contents for empty space in a Postgres table. It lets Postgres quickly find a page with enough room for a new row or index entry, avoiding a slow scan. It's used on every INSERT/UPDATE.
Database Checkpoints: Faster Recovery After a Crash
A database checkpoint creates a known good point for faster crash recovery. Instead of writing every change to disk, it periodically flushes modified data from memory, reducing the amount of log data to process.
Slotted Page Structure: Stable Pointers on Disk
A slotted page organizes data by growing records from the end of the page and pointers from the beginning. This allows databases to handle variable-sized records without costly reshuffling, keeping pointers stable.
Heap File Organization: Fast Writes, Slow Reads
Heap file organization is like tossing records into a box in no particular order. It's great for bulk-loading data quickly, but searching requires a full table scan. The footgun is using it for frequently queried tables, which kills performance.
Database Pages: The Building Blocks of Your Data
A database page is the fundamental 8KB block for all storage. The database engine reads and writes entire pages, not single rows, for user data, indexes, and metadata. The key footgun: the physical order of rows on a page is not guaranteed.
Predicate Pushdown: Filter Data at the Source
Predicate pushdown tells the database to filter data at the source, not after fetching it. This speeds up queries in data warehouses and lakehouses by reducing network traffic. The main footgun: not all data sources can execute all types of filters.
Database Statistics: The Query Optimizer's Internal Map
Database statistics are the raw data the query optimizer uses to guess the cheapest way to run your query. It uses stats like row counts and value distribution to decide between a full table scan and an index seek.
Row Mode vs. Batch Mode Execution in SQL Server
Row mode processes data one row at a time, like a checklist. Batch mode processes chunks of rows together for vectorized speed. Row mode is classic for OLTP, while batch mode shines in data warehousing for large scans.
MVCC: Read and Write Data Without Blocking Each Other
MVCC avoids slow, traditional locks by giving each transaction its own consistent data snapshot. This allows readers and writers to work at the same time without blocking each other, boosting performance in databases like PostgreSQL.
Shared & Exclusive Locks: The Read vs. Write Rule
A Shared (S) lock is like many people reading a library book at once; an Exclusive (X) lock is one person writing in it alone. Databases use S/X locks to manage concurrency, preventing writes from corrupting reads.
Top 10 FastAPI interview questions for senior roles
From dependency injection internals to how Pydantic v2 validation differs from v1 — these are the interview questions that separate juniors from staff.