A set of properties that guarantee database transactions are processed reliably: Atomicity (all operations succeed or fail together), Consistency (data integrity is maintained), Isolation (concurrent transactions don't interfere), and Durability (committed changes persist).
Example: PostgreSQL ensures ACID compliance, making it suitable for financial applications where data integrity is critical.
The property that ensures all operations within a transaction are completed successfully, or none are applied at all.
Example: If transferring money between accounts fails halfway through, atomicity ensures no partial changes are saved.
Continuous shipping of WAL segments to durable storage for backups and PITR. Controlled by archive_mode and archive_command.
Example: archive_mode = on and archive_command = "cp %p /mnt/wal-archive/%f" enable WAL archiving to external storage.
Configuration parameter that specifies the shell command to execute to archive a completed WAL segment.
Example: archive_command = "rsync -a %p backup@example:/wal/%f" ships WAL files to a remote backup server.
Configuration parameter to enable WAL archiving. Values: off | on | always (for standbys).
Example: Set archive_mode = on on the primary to allow archive_command to run for each completed WAL segment.
A deployment model where software runs on the customer's own cloud infrastructure rather than the vendor's managed service.
Example: Vela's BYOC approach lets you deploy on AWS, GCP, Azure, or on-premises while maintaining full control.
A tree data structure that maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time.
Example: PostgreSQL uses B-tree indexes by default for most column types to speed up WHERE clause queries.
Logical backups dump SQL/data (pg_dump), while physical backups copy data files/WAL (pg_basebackup, file system snapshots).
Example: Use pg_dump for schema-only export; use pg_basebackup + WAL archiving for full cluster physical backups.
A consistent physical copy of a PostgreSQL cluster, typically taken with pg_basebackup, used with archived WAL for PITR.
Example: pg_basebackup -D /backups/base -Ft -z -X stream creates a compressed tar backup with WAL streamed.
A resource management technique where data is shared until one process needs to modify it, then a copy is created.
Example: Vela's copy-on-write cloning allows instant database copies that only use additional storage when data diverges.
A technique to maintain a cache of database connections that can be reused across multiple requests.
Example: PgBouncer provides connection pooling to reduce the overhead of establishing new PostgreSQL connections.
A point in the WAL where all dirty data pages have been written to disk, providing a consistent recovery point.
Example: PostgreSQL automatically performs checkpoints to ensure data durability and limit recovery time.
A writable copy of a database or cluster, often created instantly using storage-level snapshots or COW.
Example: Create a clone from last night's backup to test migrations without touching production.
A version control approach for databases that allows creating isolated copies for development, similar to Git branches.
Example: Vela enables database branching so developers can work on features with real production data without affecting the main database.
The ACID property ensuring that once a transaction is committed, it remains so even in the event of system failure.
Example: PostgreSQL's WAL (Write-Ahead Logging) ensures durability by persisting all changes before acknowledging commits.
A PostgreSQL command that shows the execution plan of a query along with actual runtime statistics.
Example: EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user at example.com' shows index usage and execution time.
A constraint that establishes a link between data in two tables by referencing the primary key of another table.
Example: A user_id foreign key in an orders table ensures every order belongs to a valid user in the users table.
A technique for searching text data that goes beyond simple pattern matching to include linguistic analysis.
Example: PostgreSQL's full-text search can find documents containing "running" when searching for "run" using stemming.
Generalized Inverted Index - an index type in PostgreSQL optimized for indexing composite values like arrays and full-text search.
Example: CREATE INDEX idx_tags ON posts USING GIN(tags) speeds up queries on array columns.
A standby server that can accept read-only queries while continuously applying WAL records from the primary.
Example: PostgreSQL hot standby allows read replicas to serve read traffic while staying synchronized with the primary.
An approach that enables real-time analytics on transactional data without separate ETL processes.
Example: Vela's HTAP capabilities allow running OLAP queries directly on live OLTP data without data movement.
A data structure that improves the speed of data retrieval operations on a database table.
Example: CREATE INDEX idx_user_email ON users(email) speeds up login queries that search by email address.
The ACID property that ensures concurrent transactions don't interfere with each other.
Example: PostgreSQL's MVCC provides isolation by showing each transaction a consistent snapshot of the database.
PostgreSQL data types for storing JSON documents. JSONB is a binary format that supports indexing and efficient operations.
Example: JSONB columns allow storing flexible document data while maintaining the benefits of relational constraints.
A mechanism to control concurrent access to database resources to maintain data consistency.
Example: PostgreSQL uses various lock types to ensure that UPDATE operations don't conflict with each other.
A method of replicating data changes based on their logical representation rather than physical storage.
Example: Logical replication allows selective replication of specific tables or schemas to different PostgreSQL versions.
A byte position in the WAL stream that uniquely identifies a point in time for recovery or replication.
Example: pg_last_wal_replay_lsn() shows the last replayed LSN on a standby; useful for monitoring lag.
A technique that allows multiple transactions to access the same data simultaneously without blocking.
Example: PostgreSQL's MVCC lets readers and writers operate concurrently without locks by maintaining multiple data versions.
A database object that contains the result of a query and is physically stored, unlike a regular view.
Example: A materialized view can cache complex aggregations for faster reporting queries that don't need real-time data.
A category of database processing that facilitates complex analytical queries over large datasets.
Example: Vela's OLAP capabilities enable real-time business intelligence directly on operational PostgreSQL data.
A category of database processing that manages transaction-oriented applications with high concurrency.
Example: E-commerce order processing is a typical OLTP workload requiring fast, consistent transactions.
PostgreSQL's built-in benchmarking tool for running performance tests with simulated workloads.
Example: pgbench -c 10 -j 2 -T 60 mydb runs a 60-second benchmark with 10 clients and 2 worker threads.
A logical backup utility that exports a database's schema and/or data to a portable format (SQL, custom, directory).
Example: pg_dump -Fc -d mydb -f backup.dump creates a compressed custom-format backup for parallel restore.
A utility to restore backups created by pg_dump (custom, directory, tar formats), supporting parallelism and selective restore.
Example: pg_restore -j 4 -d mydb backup.dump restores using 4 jobs; use -t table to restore specific tables.
A tool to take a physical base backup of a running PostgreSQL cluster over the replication protocol.
Example: pg_basebackup -h primary -D /data/backup -R -X stream -C -S slot1 creates a base backup and configures a replica.
Recovery of a cluster to an exact point in time or LSN using a base backup plus archived WAL and a recovery target.
Example: Set restore_command and recovery_target_time = "2025-01-01 12:00:00 UTC" to recover to a timestamp.
Physical backups copy data files/WAL (cluster-level); logical backups export SQL or data at the database/table level.
Example: Use physical backups for disaster recovery; use logical backups for migrating a single database between clusters.
A constraint that uniquely identifies each row in a table and cannot contain NULL values.
Example: An auto-incrementing id column is commonly used as a primary key to ensure each record is unique.
A technique to split large tables into smaller, more manageable pieces based on specific criteria.
Example: Range partitioning by date allows efficient querying and maintenance of large time-series tables.
The component of PostgreSQL that determines the most efficient way to execute a given SQL query.
Example: The query planner chooses between index scans and sequential scans based on table statistics and query conditions.
The process of copying and maintaining database objects in multiple database environments.
Example: Streaming replication keeps standby servers synchronized with the primary for high availability.
Process of rebuilding a database or cluster from backups. Logical restore uses pg_restore; physical restore replays WAL.
Example: To restore physically, provision a data directory from base backup and set restore_command to replay archived WAL.
Configuration parameter that specifies how the server retrieves archived WAL files during recovery.
Example: restore_command = "cp /mnt/wal-archive/%f %p" tells PostgreSQL how to fetch WAL segments for replay.
A recovery stop point defined by time, XID, name, or LSN to which PostgreSQL will recover during PITR.
Example: Use recovery_target_time or recovery_target_lsn to land just before an accidental DROP TABLE.
A mechanism that retains WAL required by a subscriber or backup stream to prevent premature recycling.
Example: Create a physical slot for pg_basebackup with -C -S to avoid WAL loss during long-running backups.
The ability to analyze data and provide insights immediately as data is created or updated.
Example: Vela enables real-time analytics on live transactional data without traditional ETL delays.
A standardized language for managing and manipulating relational databases.
Example: SELECT name FROM users WHERE created_at > '2023-01-01' retrieves users created this year.
An open-source backend platform that provides PostgreSQL database, authentication, and real-time subscriptions.
Example: Vela builds on the Supabase ecosystem, extending it with enterprise features and performance optimizations.
A standby.signal file in the data directory triggers standby mode in v12+, replacing recovery.conf.
Example: pg_basebackup -R creates standby.signal and primary_conninfo for a new replica automatically.
A key performance metric measuring the number of database transactions completed per second.
Example: Vela achieves 290,000 TPS for read workloads, significantly outperforming traditional cloud databases.
A sequence of database operations that are executed as a single logical unit of work.
Example: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
Data organized by time order, typically used for metrics, logs, and sensor data.
Example: PostgreSQL with TimescaleDB extension efficiently handles time-series data for IoT applications.
A sequence identifier for WAL history used during recovery and replication; increments on divergence (e.g., PITR).
Example: After PITR, the cluster may switch to a new timeline; pg_waldump -p shows timeline history.
A single database system that can handle multiple workload types (OLTP, OLAP, search, etc.) efficiently.
Example: Vela's unified approach eliminates the need for separate systems for transactional and analytical workloads.
An operation that inserts a new row or updates an existing row if a conflict occurs.
Example: INSERT INTO users (id, name) VALUES (1, 'John') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
A PostgreSQL maintenance operation that reclaims storage and updates statistics for optimal performance.
Example: VACUUM ANALYZE users; reclaims space from deleted rows and updates table statistics for the query planner.
A search technique that finds similar items based on vector embeddings, commonly used for AI applications.
Example: PostgreSQL with pgvector extension enables semantic search and recommendation systems using vector similarity.
A high-performance PostgreSQL platform with instant cloning, Git-like branching, and unified OLTP/OLAP capabilities.
Example: Vela provides 6x better performance than AWS RDS while offering modern development workflows developers love.
A logging mechanism where changes are written to a log before being applied to the database files.
Example: WAL ensures data durability and enables point-in-time recovery and streaming replication in PostgreSQL.
A SQL clause used to filter records based on specified conditions.
Example: SELECT * FROM products WHERE price > 100 AND category = 'electronics' filters products by price and category.
Explore additional resources to deepen your PostgreSQL knowledge