PostgreSQL (Relational Database Tuning)

PostgreSQL is an open-source object-relational database system offering transactional ACID guarantees and spatial indices.

1. Core Architecture

• Write-Ahead Logging (WAL):

PostgreSQL writes modifications to the WAL on disk before updating actual data pages, ensuring durability in the event of crashes.

• Indexes:
• B-Tree: Default index for comparison queries (=, <, >).
• GIN (Generalized Inverted Index): Indexes composite values (JSONB arrays, document text search).
• GiST (Generalized Search Tree): Indexes spatial points (used by PostGIS extensions for geohash operations).

2. High-Availability & Replication

• Synchronous Replication:

Transactions wait for logs to be written to both the primary and standby replicas before returning success, guaranteeing consistency at the cost of write latency.

• Asynchronous Replication:

Transactions return success immediately after writing to the primary log, replicating changes to replicas with eventual consistency.

3. References & Tech Blogs

• PostgreSQL Documentation: Indexes and Performance
• Datadog Blog: Monitoring PostgreSQL Database Metrics