Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
Downsampling: Trading Precision for Storage in Time Series Data
Downsampling trades precision for storage in aging time series data. It's like summarizing old notes: you keep key trends but discard granular details. This is vital for observability systems that need recent precision but only coarse historical views.
Faceted Search: Guided Drill-Down for Large Datasets
Faceted search turns a massive result list into an interactive drill-down experience, like the filters on a shopping site. It's used in e-commerce and document libraries where items have structured attributes.
Full-Text Search: Beyond Simple String Matching
Full-text search isn't just string matching; it's a search engine for your data that understands language. Use it for e-commerce search or log analysis. The footgun is thinking a simple `LIKE` query is a substitute for a real search engine like Elasticsearch.
Cache-Aside Pattern: Your App Owns the Cache
The Cache-Aside pattern makes your application the gatekeeper for the cache. On a read, your code checks the cache first; on a miss, it fetches from the database and writes to the cache. This speeds up read-heavy apps. The key footgun is stale data.
Inverted Index: How Search Engines Find Your Keywords
An inverted index is like a book's index: it maps keywords to the documents containing them. This is the core of full-text search in search engines and databases, allowing instant lookups.
Schema Evolution: Changing a Live Database Without Outages
Schema evolution is like renovating a house while you live in it: you must change your database's structure without breaking the live application. This is critical when adding or renaming columns.
CockroachDB: A SQL Database That Survives Disasters
CockroachDB is a distributed SQL database designed to be unkillable. Use it for global apps needing strong consistency and high availability, like financial ledgers or identity systems. The footgun: ignoring network latency between nodes can kill performance.
Google Cloud Spanner: A Globally Distributed SQL Database
Spanner is a globally distributed SQL database that scales like NoSQL but keeps the strong consistency of a relational database. Use it for global applications like financial ledgers that need ACID transactions across continents.
NewSQL: SQL Scalability Without Sacrificing ACID
NewSQL databases aim for NoSQL's horizontal scaling with the ACID guarantees of a traditional relational database. They suit high-throughput OLTP systems, like e-commerce, that must scale out. The footgun is assuming they are a simple drop-in replacement.
AWS DMS: Your Managed Database Migration Engine
AWS DMS is a managed service for migrating databases. It acts like a replication server you point at a source and target, handling the data transfer. It's used for one-time migrations to AWS or for continuous replication.
Amazon Aurora: AWS's Proprietary Relational Database
Amazon Aurora is a proprietary relational database from AWS, offered as part of the Amazon Relational Database Service (RDS). It provides a managed database solution within the AWS cloud ecosystem, available since October 2014.
Compute & Storage Separation: Scale One Without the Other
This architecture treats your data warehouse (cheap storage) and query engine (expensive compute) as separate services. You can scale compute for peak demand without overprovisioning storage.
Amazon RDS: Managed Relational Databases in the Cloud
Amazon RDS is like hiring a DBA to manage your database's plumbing. It's for when you need a SQL database like PostgreSQL or MySQL without the hassle of patching and backups. The footgun is assuming it's 'serverless'—you still manage cost and performance.
Active Record: Your Object is the Database Row
The Active Record pattern treats an object as a self-managing database row, bundling data with persistence logic. It's great for simple CRUD apps, but tightly couples your business logic to your database schema, making complex refactors difficult.
Database Cursors: Row-by-Row Result Processing
A database cursor is an iterator for a query's results, letting you process a large dataset one row at a time. It's for batch jobs on huge record sets that would otherwise crash your app.
The Object-Relational Impedance Mismatch
The Object-Relational Impedance Mismatch is the friction between how SQL databases see data (tables, rows) and how OO code sees it (objects, inheritance). It's the core problem ORMs solve. The footgun is thinking an ORM makes the database disappear.
Database Parameter Tuning: Beyond the Defaults
Database defaults are a compromise. Parameter tuning tailors the database to your specific workload, hardware, and reliability needs. It's used to optimize memory, WAL settings, or query planning.
Database Disaster Recovery: Planning for Total Failure
Database Disaster Recovery (DR) assumes your primary site is gone for good, focusing on restoring service at a secondary location. It's for critical systems where regional outages are unacceptable. The footgun is confusing DR with High Availability (HA).
Database High Availability: Surviving Server Failure
High Availability (HA) means having a hot standby database ready to take over instantly upon failure. It's essential for critical systems like payment gateways where downtime is unacceptable.
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.
Point-in-Time Recovery: Rewind Your Database to a Specific Second
Point-in-Time Recovery (PITR) is a database time machine, restoring data to a specific second, not just the last snapshot. It's crucial for reversing application-level errors.
Connection Pooling: Don't Re-Open, Reuse
A connection pool is a valet service for database access. Instead of creating a new connection for every request, you borrow a ready-made one and return it. This avoids costly setup/teardown in web apps.
Role-Based Access Control (RBAC) in Databases
RBAC bundles permissions into roles, like 'analyst' or 'admin', instead of assigning them to individuals. This simplifies managing who can read or write data in a database. The footgun is creating too many roles, making it as complex as individual permissions.
Differential Backups: Faster Backups, Simpler Restores
A differential backup saves all changes since the last full backup, making daily backups faster. To restore, you only need the full backup and the latest differential file. The footgun: each differential file grows larger until the next full backup is made.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.