PostgreSQL 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.