Unit
- Shared memory architecture: PostgreSQL uses a shared memory segment for buffer cache, WAL buffers, and lock tables. This allows multiple backend processes to communicate efficiently without passing messages through the operating system. The shared memory size is configured via
shared_buffersin postgresql.conf. - Process model: Unlike thread-based databases, PostgreSQL uses a multi-process model where each client connection gets a dedicated backend process. The postmaster parent process manages these children, listening on the port and forking new backends. This improves isolation but increases memory overhead per connection.
- WAL (Write-Ahead Log): PostgreSQL ensures durability by writing changes to a sequential WAL before applying them to data files. The WAL is stored in the
pg_walsubdirectory and can be archived for point-in-time recovery. This design allows crash recovery by replaying WAL records. - Buffer cache management: The buffer manager maintains a pool of pages in shared memory. A clock-sweep algorithm decides which pages to evict when free buffers are needed. The
pg_buffercacheextension provides visibility into buffer usage for tuning. - Query execution pipeline: Each query passes through parser, rewriter, planner/optimizer, and executor stages. The planner uses cost-based optimization to generate the most efficient execution plan. Statistics collected by
ANALYZEinform the optimizer about table sizes and data distribution. - Concurrency control with MVCC: PostgreSQL implements multiversion concurrency control (MVCC) where each transaction sees a snapshot of data as of the start of the transaction. Readers never block writers and vice versa. Dead rows are cleaned up by the autovacuum daemon.
- Replication and streaming: Built-in streaming replication allows a standby server to receive WAL changes in real time. This supports high availability setups with automatic failover. Logical replication enables selective replication of tables to other PostgreSQL instances.
- Extension ecosystem: PostgreSQL supports extensions that add functionality without changing core code. Popular extensions include
PostGISfor geospatial,pg_stat_statementsfor performance, andpg_partmanfor automatic partitioning.
System Architecture
Pattern: PostgreSQL Process Architecture
Components:
- Postmaster: Parent process that manages shared memory, listens for connections, and forks backend processes.
- Backend processes: One per client connection, handling queries and transactions.
- WAL writer: Background writer that flushes WAL buffers to disk for durability.
- Autovacuum workers: Cleanup dead tuples and update statistics automatically.
- Checkpointer: Writes dirty buffers from shared memory to data files.
- Archiver: Copies WAL segments to archival storage for backup.
Security Consideration
HIGH RISK
Vulnerability: Default superuser password and network exposure.
Mitigation: Set a strong password for the postgres user immediately after installation. Configure pg_hba.conf to restrict connections to trusted networks. Disable remote connections if not needed by setting listen_addresses = 'localhost'.
Reference: OWASP Database Security Cheat Sheet
Code Example
-- Connect to database using psql
psql -h localhost -U postgres -d mydb
-- Display current database name
SELECT current_database();
-- Create a sample table
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary NUMERIC(10,2)
);
-- Insert a row
INSERT INTO employees (name, salary) VALUES ('Alice', 75000.00);This code connects to a PostgreSQL database, creates a simple employees table with an auto-incrementing primary key, and inserts a sample row. The psql command-line tool is used for interaction.
Quick Revision
- PostgreSQL uses a multi-process, shared-memory architecture with a separate backend process per connection.
- Write-Ahead Logging (WAL) ensures durability and enables point-in-time recovery.
- MVCC allows concurrent reads and writes without locking conflicts.
- The autovacuum daemon cleans up dead tuples and maintains statistics.
- Extensions like PostGIS and pg_stat_statements extend functionality.
- Security best practices include strong passwords and network restrictions.