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.
- 02BASEBasically Available, Soft state, Eventually consistent2BASE describes distributed databases that favor availability over immediate consistency: they keep answering and let copies of data briefly disagree.
- 03CAP TheoremConsistency, Availability, Partition Tolerance3The 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.
- 04Connection Pool4A 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.
- 05Data Lake5A 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.
- 06Data Warehouse6A data warehouse is a central database built for analytics that collects historical data from many sources so teams can run large reporting queries quickly.
- 07Database7A database is an organized collection of data stored on a computer, managed by software that lets applications save, search, and update it efficiently.
- 08Database Index8A 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.
- 09Database Migration9A 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.
- 10Database Normalization10Database normalization is the process of organizing tables so each fact is stored only once, reducing duplicate data and preventing inconsistent updates.
- 11Database Replication11Database replication is the continuous copying of data from one database server to others, so several servers hold the same data for reliability and scale.
- 12Database Schema12A database schema is the blueprint of a database that defines its tables, columns, data types, relationships, and the rules that stored data must follow.
- 13Database Trigger13A 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.
- 14Database View14A 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.
- 15Denormalization15Denormalization is the deliberate duplication of data across tables or documents so that frequent reads need fewer joins, at the cost of more complex writes.
- 16Document Database16A 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.
- 17DynamoDBAmazon DynamoDB17Amazon DynamoDB is a fully managed NoSQL database on AWS that stores items by key, scales automatically and answers lookups in single-digit milliseconds.
- 18Elasticsearch18Elasticsearch is a distributed search and analytics engine that indexes JSON documents for fast full-text search, filtering and aggregations over large data.
- 19ELTExtract, Load, Transform19ELT is a data integration approach that extracts data from sources, loads it raw into a data warehouse, and then transforms it there, usually with SQL.
- 20ETLExtract, Transform, Load20ETL 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.
- 21Eventual Consistency21Eventual 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.
- 22Firebase22Firebase is Google's platform that gives web and mobile apps a hosted database, authentication, storage, hosting and functions without managing a backend.
- 23Foreign Key23A 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.
- 24Full-Text Search24Full-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.
- 25Graph Database25A 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.
- 26Isolation Level26An isolation level is a database setting that controls how much concurrent transactions can see of each other's changes, trading strictness for speed.
- 27Key-Value Store27A 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.
- 28Memcached28Memcached is an open-source, in-memory key-value cache that keeps small pieces of data in RAM across servers to take load off databases and speed up apps.
- 29MongoDB29MongoDB 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.
- 30MySQL30MySQL is a popular open-source relational database queried with SQL, long known as the database behind WordPress and the classic LAMP web stack.
- 31N+1 Query Problem31The 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.
- 32NoSQLNot Only SQL32NoSQL is a family of databases that store data in models other than relational tables, such as documents, key-value pairs, wide columns, or graphs.
- 33OLAPOnline Analytical Processing33OLAP (online analytical processing) describes systems built to answer complex analytical questions over large amounts of historical data quickly.
- 34OLTPOnline Transaction Processing34OLTP (online transaction processing) describes databases built for many small, fast reads and writes from everyday operations, such as placing orders.
- 35Optimistic Locking35Optimistic locking is a concurrency technique that lets transactions proceed without holding locks and checks a version number at save time to detect conflicts.
- 36ORMObject-Relational Mapping36An 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.
- 37Partitioning37Partitioning 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.
- 38PostgreSQL38PostgreSQL is a free, open-source relational database known for reliability, strict standards support and extensions, and widely used for web applications.
- 39Primary Key39A 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.
- 40RedisRemote Dictionary Server40Redis 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.
- 41Relational Database41A 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.
- 42Sharding42Sharding 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.
- 43SQLStructured Query Language43SQL 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.
- 44SQL JOIN44A 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.
- 45SQL ServerMicrosoft SQL Server45Microsoft 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.
- 46SQLite46SQLite 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.
- 47Stored Procedure47A 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.
- 48Supabase48Supabase is an open-source backend platform built on PostgreSQL that gives an app a database, authentication, storage, real-time updates and instant APIs.
- 49Time-Series Database49A time-series database is a database optimized for storing and querying timestamped measurements, such as sensor readings or server metrics, in time order.
- 50Transaction50A 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.