Database Study Flashcards

Multi-Version Concurrency Control (MVCC) ensures data consistency by granting each transaction a snapshot of the database, allowing concurrent reads and writes without locking.

WAL records changes to disk before data pages are updated, ensuring durability and enabling point-in-time recovery (PITR) and replication.

JSONB stores data in a parsed binary format that supports indexing (GIN/GiST) and faster processing, whereas JSON stores exact unparsed text.

EXPLAIN ANALYZE executes the query and provides the exact execution plan along with real execution time and row count statistics.

Autovacuum reclaims dead tuple storage left behind by UPDATEs and DELETEs, and updates planner statistics to maintain performance.

Generalized Inverted Index (GIN) is designed for composite items like JSONB documents, arrays, and full-text search documents.

RETURNING allows DML statements to return modified or inserted row columns directly without issuing a separate SELECT query.

FDW allows PostgreSQL to query external datasets (like Remote Postgres, MySQL, Redis, or CSVs) as if they were local tables.

pgvector is an open-source vector similarity search extension for storing and searching high-dimensional AI vector embeddings in Postgres.

STRING_AGG concatenates non-null text values from a set of rows into a single string separated by a specified delimiter.

InnoDB is the default storage engine, offering ACID transactions, foreign keys, and row-level locking.

InnoDB supports transactions, row locking, and foreign keys. MyISAM supports table-level locking only and has no transaction support.

When an INSERT statement causes a primary or unique key duplicate, MySQL executes an UPDATE on the existing row instead.

GROUP_CONCAT aggregates multiple non-null row values into a single concatenated string per group.

The Redo Log records page changes to ensure crash recovery (durability) before changes are written to tablespace data files.

EXPLAIN displays how MySQL executes a query, including key usage, join types (ALL, ref, eq_ref), and rows examined.

AUTO_INCREMENT automatically generates sequential unique numeric keys for new table rows.

A topology where writes to a Primary database are recorded in binary logs (binlog) and replayed on Secondary replica instances.

An index that contains all columns required by a query, enabling MySQL to return results directly from the index without reading table pages.

REPEATABLE READ is InnoDB's default isolation level, preventing non-repeatable reads using multi-versioning.

SQLite reads and writes directly to standard disk files without running a background server process daemon.

Write-Ahead Logging (WAL) allows concurrent readers while a write is occurring, improving concurrency and speed.

Data types are associated with the stored values themselves rather than the declared column types (manifest typing).

A database created using ':memory:' as the filename, residing entirely in RAM and deleted upon connection close.

It returns metadata about table columns, data types, nullability, default values, and primary key flags.

VACUUM rebuilds the database file to reclaim unused space and defragment data pages.

AUTOINCREMENT ensures primary key IDs are strictly monotonically increasing and never reused.

Full-Text Search 5 (FTS5) is a built-in virtual table module providing fast text search over text columns.

An object that presents an external data source (like CSV files or custom C code) as a SQLite table.

STRICT tables enforce rigid column data type checks, overriding default dynamic manifest typing.

System Global Area (SGA) is the shared memory region containing data buffer cache, shared pool, and redo log buffer.

Program Global Area (PGA) is private memory allocated for each server process executing SQL operations.

ROWNUM assigns row numbers during fetch, whereas FETCH FIRST N ROWS ONLY is standard ANSI pagination syntax.

NVL(expr1, expr2) replaces NULL with expr2 if expr1 evaluates to NULL.

Procedural Language/SQL (PL/SQL) is Oracle's extension providing procedural constructs like loops, variables, and exceptions.

A database object storing pre-computed query results that can be refreshed on demand or commit.

Enables querying table data state as it existed at a past timestamp or SCN number.

Logical storage unit grouping physical data files (.dbf) containing tables and indexes.

Hierarchical query clause used to traverse parent-child tree relationships in Oracle SQL.

Database object that generates sequential unique integers (using NEXTVAL and CURRVAL).

Transact-SQL (T-SQL) is Microsoft's extension to SQL adding procedural variables, string functions, and control flow.

TOP (N) limits the number of output rows returned by a SELECT query.

Single hash (#temp) is visible only to the current session. Double hash (##temp) is global and visible to all sessions.

CROSS APPLY invokes a table-valued function or correlated subquery for each row returned by an outer table query.

IDENTITY automatically generates auto-incrementing integer key values for new records.

A background service that executes scheduled administrative tasks, jobs, and automated backups.

ISNULL accepts two arguments and uses data type of first argument. COALESCE accepts multiple arguments and returns highest data type precedence.

Converts data to specified type and returns NULL if conversion fails instead of throwing a runtime error.

Defines the physical storage order of data rows on disk (only one clustered index per table).

Concatenates expression values with specified separator across dataset groups.

Binary JSON (BSON) is the binary serialization format used to store documents and extend JSON with data types like Date and ObjectId.

A 12-byte unique identifier generated automatically for document _id fields containing timestamp, machine, process, and counter bits.

A multi-stage framework ($match, $group, $lookup, $unwind) for transforming and summarizing documents sequentially.

It performs an equality left outer join to a collection in the same database to pull in related document arrays.

A cluster of MongoDB servers maintaining identical data copies to ensure high availability and automatic failover.

Horizontal scaling method partitioning document datasets across multiple server shards using a shard key.

An index supporting spatial queries over spherical geometry objects like points, lines, and polygons.

JSON Schema rules enforced during write operations to guarantee document structure integrity.

It deconstructs an array field from input documents to output a document for each element in the array.

An update operation that updates matching documents or creates a new document if no match is found.

Strings, Hashes, Lists, Sets, Sorted Sets (ZSet), Bitmaps, HyperLogLogs, Geospatial indexes, and Streams.

Time-To-Live (TTL) sets an automated expiration time after which Redis deletes the key from RAM.

Redis supports RDB (point-in-time snapshot files) and AOF (Append-Only File logging every write command).

A messaging pattern where publishers send messages to channels without knowing connected subscribers.

A collection of unique strings where each element is associated with a floating-point score used for ordering.

A system providing high availability, monitoring, notification, and automatic failover for Redis primary nodes.

Provides a way to run a Redis installation where data is automatically sharded across 16,384 hash slots.

Sending multiple commands to the server without waiting for individual replies, reducing round-trip time (RTT).

Least Recently Used (LRU) automatically evicts least recently accessed keys when RAM usage hits maxmemory limits.

Lua scripts run atomically on the Redis server, guaranteeing no other command executes concurrently during script run.

Separation of Storage, Multi-Cluster Compute (Virtual Warehouses), and Cloud Services layer.

An independent CPU/RAM compute cluster used to execute SQL queries and DML operations.

Automatic contiguous 50MB-150MB compressed storage blocks formatted in columnar layout.

QUALIFY filters window function results in a SELECT statement, eliminating subqueries.

Instantly creates a metadata copy of tables or databases without duplicating physical data storage.

Access historic table data state at any point within a configurable period (up to 90 days).

An automated continuous data ingestion service loading files as they land in S3/GCS stages.

A flexible semi-structured data type storing up to 16MB of JSON, Avro, ORC, or XML data.

Organizes micro-partitions along specified columns to maximize partition pruning speed.

Enables secure live data sharing across Snowflake accounts without copying physical files.

The default and most powerful table engine family, featuring primary key indexing, partition pruning, and background data merging.

Columnar storage reads only queried columns from disk, dramatically accelerating analytical aggregation queries over billions of rows.

Primary keys do not enforce row uniqueness; instead, they create a sparse index pointing to data mark ranges.

A table engine that asynchronously deduplicates rows matching the primary key in background merges.

A specialized table engine that automatically sums numeric columns sharing the same primary key during background merges.

Processing data in memory blocks (vectors) using SIMD CPU instructions for high query execution performance.

Stores transformed rows continuously as data is inserted into the source table, avoiding query-time computation.

It expands array elements into individual result output rows, similar to MongoDB's $unwind.

Skipping unneeded data partition files based on WHERE conditions matching the PARTITION BY expression.

A system catalog table providing metrics about physical MergeTree table parts, sizes, and row counts.

An embedded columnar analytical database engine (often called the SQLite for Analytics).

Queries process vectors of data values in CPU cache lines using SIMD instructions for maximum throughput.

Yes, DuckDB can execute SQL queries directly against local or remote Parquet and CSV files without importing them into tables.

DuckDB can query Pandas DataFrames, Polars DataFrames, and Arrow tables directly zero-copy.

Yes, DuckDB supports Snowflake-style QUALIFY clauses to filter window function outputs directly.

DuckDB automatically parallelizes query execution across all available CPU cores.

A function allowing direct HTTP/S3 querying of remote Parquet data lakes.

DuckDB gracefully spills intermediate data to disk when query memory usage exceeds RAM limits.

Exports query results directly into Parquet, CSV, or JSON files on disk.

Yes, DuckDB features full ACID transactional semantics using Multi-Version Concurrency Control (MVCC).