Book 04
Databases
How data is stored, queried and kept consistent: SQL, NoSQL, indexes and transactions.
Contents
- 01ACIDAtomicity, Consistency, Isolation, Durability1ACID is a set of four guarantees, atomicity, consistency, isolation, and durability, that keep database transactions reliable even when errors or crashes occur.
- 02CAP TheoremConsistency, Availability, Partition Tolerance2The CAP theorem says that if a network failure splits a distributed database, the system must choose between consistency and availability; it can't have both.
- 03Connection Pool3A connection pool is a cache of open database connections that an application reuses across requests, avoiding the cost of opening a new connection every time.
- 04Data Lake4A data lake is a central storage repository that holds large amounts of raw data in its original format, structured or not, until someone needs to analyze it.
- 05Data Warehouse5A data warehouse is a central database built for analytics that collects historical data from many sources so teams can run large reporting queries quickly.
- 06Database6A database is an organized collection of data stored on a computer, managed by software that lets applications save, search, and update it efficiently.
- 07Database Index7A database index is a data structure that helps a database find rows quickly without scanning a whole table, much like the index at the back of a book.
- 08Database Migration8A database migration is a versioned script that changes a database's schema, such as adding a column, so every environment applies the same changes in order.
- 09Database Normalization9Database normalization is the process of organizing tables so each fact is stored only once, reducing duplicate data and preventing inconsistent updates.
- 10Database Replication10Database replication is the continuous copying of data from one database server to others, so several servers hold the same data for reliability and scale.
- 11Database Schema11A database schema is the blueprint of a database that defines its tables, columns, data types, relationships, and the rules that stored data must follow.
- 12Database Trigger12A database trigger is code stored in the database that runs automatically when a chosen event, such as an insert, update, or delete, happens on a table.
- 13Database View13A database view is a saved SQL query that behaves like a virtual table, so you can select from it by name instead of repeating the underlying query each time.
- 14Denormalization14Denormalization is the deliberate duplication of data across tables or documents so that frequent reads need fewer joins, at the cost of more complex writes.
- 15Document Database15A document database is a NoSQL database that stores each record as a self-contained document, usually JSON-like, whose fields can differ from record to record.
- 16DynamoDBAmazon DynamoDB16Amazon DynamoDB is a fully managed NoSQL database on AWS that stores items by key, scales automatically and answers lookups in single-digit milliseconds.
- 17Elasticsearch17Elasticsearch is a distributed search and analytics engine that indexes JSON documents for fast full-text search, filtering and aggregations over large data.
- 18ETLExtract, Transform, Load18ETL is a data integration process that extracts data from source systems, transforms it into a clean, consistent shape, and loads it into a target store.
- 19Eventual Consistency19Eventual consistency is a guarantee that, if no new updates are made, all copies of a piece of data in a distributed system will become identical over time.
- 20Firebase20Firebase is Google's platform that gives web and mobile apps a hosted database, authentication, storage, hosting and functions without managing a backend.
- 21Foreign Key21A foreign key is a column in one database table that refers to the primary key of another table, linking related rows and keeping those references valid.
- 22Full-Text Search22Full-text search is a technique that finds documents containing given words or phrases by looking them up in a text index, then ranks the results by relevance.
- 23Graph Database23A graph database stores data as nodes connected by relationships, which makes it fast to follow links such as friends of friends or dependencies between items.
- 24Isolation Level24An isolation level is a database setting that controls how much concurrent transactions can see of each other's changes, trading strictness for speed.
- 25Key-Value Store25A key-value store is a NoSQL database that saves each piece of data under a unique key, so an application can read or write it by that key very quickly.
- 26MongoDB26MongoDB is a document database that stores data as flexible JSON-like documents instead of table rows, so records in one collection can have different fields.
- 27MySQL27MySQL is a popular open-source relational database queried with SQL, long known as the database behind WordPress and the classic LAMP web stack.
- 28N+1 Query Problem28The N+1 query problem is a performance bug where code runs one query to load a list and then one extra query per item, instead of fetching it all at once.
- 29NoSQLNot Only SQL29NoSQL is a family of databases that store data in models other than relational tables, such as documents, key-value pairs, wide columns, or graphs.
- 30OLAPOnline Analytical Processing30OLAP (online analytical processing) describes systems built to answer complex analytical questions over large amounts of historical data quickly.
- 31OLTPOnline Transaction Processing31OLTP (online transaction processing) describes databases built for many small, fast reads and writes from everyday operations, such as placing orders.
- 32Optimistic Locking32Optimistic locking is a concurrency technique that lets transactions proceed without holding locks and checks a version number at save time to detect conflicts.
- 33ORMObject-Relational Mapping33An ORM is a library that maps database tables to objects in your programming language, letting you read and write data with code instead of raw SQL.
- 34Partitioning34Partitioning splits a large table into smaller partitions by a rule such as date ranges, so queries can skip irrelevant data and old data is easy to remove.
- 35PostgreSQL35PostgreSQL is a free, open-source relational database known for reliability, strict standards support and extensions, and widely used for web applications.
- 36Primary Key36A primary key is a column, or set of columns, whose value uniquely identifies each row in a database table and can never be empty or duplicated.
- 37RedisRemote Dictionary Server37Redis is an in-memory key-value store that reads and writes in well under a millisecond, which makes it a popular cache, session store and message broker.
- 38Relational Database38A relational database stores data in tables of rows and columns, links those tables through keys, and lets you query and combine the data with SQL.
- 39Sharding39Sharding is a way of scaling a database by splitting its data across several servers, called shards, so each one stores and handles only part of the total.
- 40SQLStructured Query Language40SQL is the standard language for working with relational databases, used to create tables and to insert, query, update, and delete the data stored in them.
- 41SQL JOIN41A SQL JOIN is a query operation that combines rows from two or more tables into one result, matching them on related columns such as a foreign key.
- 42SQL ServerMicrosoft SQL Server42Microsoft SQL Server is Microsoft's relational database, queried with the T-SQL dialect of SQL and widely used for business apps alongside .NET and Windows.
- 43SQLite43SQLite is a small SQL database engine that runs inside your application and stores a whole database in a single file, with no separate server to manage.
- 44Stored Procedure44A stored procedure is a named set of SQL statements saved inside the database, which applications can run with a single call instead of sending each query.
- 45Supabase45Supabase is an open-source backend platform built on PostgreSQL that gives an app a database, authentication, storage, real-time updates and instant APIs.
- 46Time-Series Database46A time-series database is a database optimized for storing and querying timestamped measurements, such as sensor readings or server metrics, in time order.
- 47Transaction47A transaction is a group of database operations that succeed or fail as a single unit, so the data is never left in a half-finished, inconsistent state.