Databases

• SQL (Relational): Structured data in tables with a fixed schema. Uses SQL for queries. Strong ACID guarantees. Examples: PostgreSQL, MySQL, SQLite.
• NoSQL: Flexible/dynamic schemas. Designed for scale and specific data models. Examples: MongoDB (document), Redis (key-value), Cassandra (wide-column), Neo4j (graph).

Choose SQL when: data is relational, consistency is critical, complex queries are needed.

Choose NoSQL when: schema is dynamic, you need horizontal scale, or data fits a non-tabular model.

• Atomicity: A transaction is all-or-nothing. If any part fails, the whole transaction is rolled back.
• Consistency: A transaction brings the database from one valid state to another. Constraints are never violated.
• Isolation: Concurrent transactions execute as if they were serial. Changes are not visible to others until committed.
• Durability: Once committed, the transaction persists even after a crash (written to disk / WAL).

• Normalisation: Organising columns and tables to reduce redundancy and improve integrity.
• 1NF: Each column holds atomic values; no repeating groups.
• 2NF: 1NF + every non-key column is fully dependent on the entire primary key.
• 3NF: 2NF + no transitive dependencies (non-key columns not dependent on other non-key columns).
• BCNF: Stronger form of 3NF; every determinant is a candidate key.

Denormalisation (trading redundancy for read speed) is often used in read-heavy or analytics workloads.

• An index is a data structure (usually a B-tree or hash) that speeds up data retrieval without scanning every row.
• B-tree index: Default in most RDBMS. Good for range queries and equality.
• Hash index: O(1) lookup for exact equality; does not support range queries.
• Composite index: Index over multiple columns. The order of columns matters (leftmost prefix rule).
• Covering index: Index contains all columns needed by a query, avoiding a table lookup.

Trade-off: Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must update the index).

• INNER JOIN: Returns rows where there is a match in both tables.
• LEFT JOIN: All rows from the left table; NULLs for non-matching right rows.
• RIGHT JOIN: All rows from the right table; NULLs for non-matching left rows.
• FULL OUTER JOIN: All rows from both tables; NULLs where there's no match.
• CROSS JOIN: Cartesian product — every row from left × every row from right.
• SELF JOIN: A table joined with itself (e.g., employee → manager hierarchy).

• A transaction is a unit of work that is executed atomically.
• Controlled with BEGIN, COMMIT, and ROLLBACK.
• Isolation levels (from lowest to strictest): Read Uncommitted → Read Committed → Repeatable Read → Serializable.
• Higher isolation prevents anomalies (dirty reads, non-repeatable reads, phantom reads) at the cost of concurrency.

• A view is a virtual table based on a stored SQL query. It does not store data itself.
• Benefits: simplifies complex queries, provides a security layer (hide columns), encapsulates business logic.
• Materialised view: A cached snapshot of a query result stored on disk. Must be refreshed. Used for expensive aggregations.

• Precompiled SQL routines stored in the database, executed with a single call.
• Pros: Reduced network round-trips, reusable logic, can enforce business rules at DB level.
• Cons: Harder to version-control, tightly coupled to DB vendor, can obscure business logic from app code.
• Modern preference often favours keeping logic in the application layer (ORM/query builders) for better testability.

• Occurs when you fetch a list of N items, then issue 1 additional query per item to fetch related data — resulting in N+1 total queries.
• Example: Load 10 posts, then load comments for each post = 11 queries instead of 2.
• Fix: Use JOINs or eager loading (include/with in ORMs).
• Detectable via query logging (e.g., Django Debug Toolbar, Bullet gem, Prisma query logs).

• Use EXPLAIN / EXPLAIN ANALYZE to inspect the query execution plan.
• Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses.
• Avoid SELECT * — fetch only needed columns.
• Avoid functions on indexed columns in WHERE (defeats the index).
• Use pagination (LIMIT / OFFSET) or keyset pagination for large result sets.
• Consider caching (Redis/Memcached) for frequently read, rarely changed data.
• Denormalise or use materialised views for read-heavy analytics queries.

• States that a distributed system can guarantee at most 2 of 3:
• Consistency (C): Every read receives the most recent write.
• Availability (A): Every request gets a response (not necessarily the latest data).
• Partition Tolerance (P): The system continues to operate despite network partitions.

Since network partitions are unavoidable, the real choice is between CP (e.g., HBase, MongoDB) and AP (e.g., Cassandra, CouchDB).

• Sharding splits a dataset across multiple database instances (shards), each holding a subset of the data.
• Range sharding: Rows split by value range (e.g., users A–M on shard 1).
• Hash sharding: Row assigned to shard via a hash function. Distributes evenly but makes range queries harder.
• Directory-based: A lookup table maps records to shards. Flexible but adds overhead.
• Challenges: Cross-shard queries, re-sharding, hot spots, no JOIN across shards.

• Replication copies data from a primary node to one or more replicas.
• Primary-Replica (Master-Slave): Writes go to primary; reads can be distributed to replicas. Replicas may lag.
• Multi-Primary (Multi-Master): Writes accepted on multiple nodes. Increases write availability but requires conflict resolution.
• Synchronous replication: Primary waits for replica to confirm write — strong consistency, higher latency.
• Asynchronous replication: Primary doesn't wait — lower latency, potential data loss on failure.

• Stores data as self-contained documents (JSON/BSON). Schema-flexible — each document can have different fields.
• Examples: MongoDB, CouchDB, Firestore.
• Best for: Content management, catalogues, user profiles, event data — where data shapes vary per record.
• Avoid when: You need complex multi-document transactions or heavily relational data.

• Simplest NoSQL model — stores data as key → value pairs.
• Examples: Redis, Memcached, DynamoDB (also supports document model).
• Best for: Caching, sessions, rate limiting, leaderboards, pub/sub messaging.
• Redis supports richer data structures: strings, lists, sets, sorted sets, hashes, streams.

• Stores data in tables with rows and dynamic columns, optimised for writes and range scans by row key.
• Examples: Apache Cassandra, HBase, Google Bigtable.
• Best for: Time-series data, analytics, IoT, event logs — massive write throughput at scale.
• Data is modelled around query patterns, not entities (denormalise for each query).

• Stores data as nodes (entities) and edges (relationships), both of which can have properties.
• Examples: Neo4j, Amazon Neptune, ArangoDB.
• Best for: Social networks, recommendation engines, fraud detection, knowledge graphs — where relationships between entities are the primary concern.
• Graph queries (e.g., Cypher, Gremlin) are far more expressive than SQL JOINs for deeply connected data.

• Optimised for storing and querying data points indexed by time.
• Examples: InfluxDB, TimescaleDB (PostgreSQL extension), Prometheus.
• Features: automatic data retention policies, downsampling, efficient compression, fast time-range queries.
• Best for: Metrics, monitoring, IoT sensor data, financial tick data.

• Full-text search uses an inverted index — maps each word to the documents containing it.
• PostgreSQL: Built-in full-text search via tsvector / tsquery.
• Elasticsearch / OpenSearch: Dedicated search engines built on Lucene. Better for complex relevance scoring, facets, and large scale.
• Features: stemming, stop words, fuzzy matching, relevance scoring (TF-IDF, BM25).
• Syncing data from a primary DB to Elasticsearch is a common architecture (CDC or dual writes).

• Cache-aside (lazy loading): App checks cache first; on miss, reads from DB and populates cache.
• Write-through: Writes go to cache and DB simultaneously. Cache always up to date. Slower writes.
• Write-behind (write-back): Write to cache immediately, flush to DB asynchronously. Fast writes, risk of data loss.
• Read-through: Cache sits in front of DB; cache handles DB reads on miss.
• TTL (Time-to-live): Entries expire automatically to prevent stale data.

• Maintains a pool of pre-established DB connections that are reused rather than opened/closed per request.
• Opening a DB connection is expensive (handshake, auth, memory). Pooling amortises this cost.
• Tools: PgBouncer (PostgreSQL), HikariCP (Java), Sequelize/Prisma built-in pooling.
• Key settings: min/max pool size, connection timeout, idle timeout.
• Without pooling, high-traffic apps can exhaust DB connection limits.

• Migrations are versioned scripts that apply incremental schema changes to a database.
• Managed by tools like Flyway, Liquibase, Prisma Migrate, Alembic, or framework-specific tools (Rails, Django).
• Best practices: Never edit a committed migration; always write rollback migrations; test in staging before production.
• Zero-downtime migrations: Add column as nullable first → backfill → add constraint → remove old column.

• An ORM (Object-Relational Mapper) maps database tables to objects in code, abstracting raw SQL.
• Examples: Prisma, Sequelize, TypeORM (JS/TS), SQLAlchemy (Python), Hibernate (Java).
• Pros: Reduces boilerplate, type safety, automatic migrations, portability across DBs.
• Cons: Can generate inefficient queries, hides complexity, harder to optimise, "magic" behaviour.
• For complex queries, mixing raw SQL via the ORM's escape hatch is common.

• SQL Injection prevention: Always use parameterised queries / prepared statements. Never string-interpolate user input into SQL.
• Principle of least privilege: Each app user should only have the permissions it needs (e.g., read-only for reporting).
• Encryption: Encrypt data at rest (TDE) and in transit (TLS/SSL).
• Audit logging: Log all access and DDL changes.
• Secrets management: Never hardcode credentials; use environment variables or secret managers (Vault, AWS Secrets Manager).

• Full backup: Complete snapshot of the database. Slowest to produce, simplest to restore.
• Incremental backup: Only changes since the last backup. Faster, less storage, more complex to restore.
• WAL archiving (PostgreSQL): Archive Write-Ahead Log files for point-in-time recovery (PITR).
• RTO (Recovery Time Objective): How long until the system is back online.
• RPO (Recovery Point Objective): How much data loss is acceptable.
• Test your backups — a backup never tested is not a backup.

• A consistency model where replicas are not guaranteed to be identical at any given moment, but will converge to the same state given enough time with no new updates.
• Common in AP systems (e.g., Cassandra, DynamoDB, CouchDB).
• Use cases: DNS, social media likes/counts, shopping carts — where temporary inconsistency is acceptable.
• Strong consistency: All reads reflect the latest write. Harder to achieve in distributed systems.

• Optimistic locking: Assumes conflicts are rare. Read data with a version number; on update, check the version hasn't changed. Retry if it has. No locks held.
• Pessimistic locking: Assumes conflicts are likely. Acquires a lock before reading (SELECT FOR UPDATE). Blocks other writers. Prevents conflicts but reduces throughput.
• Optimistic is better for low-contention scenarios; pessimistic for high-contention (e.g., inventory reservations).

• Partitioning divides a single large table into smaller, more manageable pieces, usually within the same DB instance (unlike sharding, which is across instances).
• Range partitioning: By date range (e.g., one partition per month for logs).
• List partitioning: By discrete values (e.g., by country code).
• Hash partitioning: By hash of a column for even distribution.
• Benefits: faster queries (partition pruning), easier archiving, smaller index sizes.

• Cloud-managed databases remove the operational burden of provisioning, patching, backups, and failover.
• SQL: Amazon RDS/Aurora, Google Cloud SQL, Azure Database, Supabase (PostgreSQL).
• NoSQL/Document: MongoDB Atlas, Firebase Firestore, Amazon DynamoDB.
• Cache: Amazon ElastiCache, Redis Cloud, Upstash.
• Trade-off: Convenience and scalability vs vendor lock-in and cost at scale.

• Data model: Relational? Document? Graph? Time-series? Key-value?
• Consistency requirements: Strong ACID? Eventual consistency acceptable?
• Scale: Read-heavy? Write-heavy? Need horizontal sharding?
• Query patterns: Complex joins? Aggregations? Full-text search? Simple lookups?
• Operational maturity: Team experience, managed service availability, cost.
• Often the right answer is multiple databases (polyglot persistence): e.g., PostgreSQL for primary data + Redis for caching + Elasticsearch for search.