PostgreSQL For System Design

Object Relational Database

PostgreSQL is “object-relational” because in addition to normal tables, it lets you use more advanced, object-like features such as custom types, table inheritance, and functions that behave like methods.

Atomicity

In Postgres, a transaction is “all or nothing”: either every statement in the transaction succeeds and is saved, or none of them are saved.

Consistency

The database should be left in a healthy (valid) state after the operation; if it can’t be, abort and revert to a previous healthy state.”

Isolation

Even when many users run queries at the same time, Postgres makes it feel like each transaction runs alone, by hiding other users’ in-progress changes.

Durability

means that once a transaction is committed, its effects will persist even if the database server or OS crashes immediately afterward.

Multiversion Concurrency Control (MVCC)

means that updates create new versions of rows instead of overwriting them, and each transaction reads from a snapshot that decides which version is visible. This lets reads and writes avoid conflicting locks.

Custom Data types

In addition to built-in types (integer, text, etc.), Postgres lets you define your own types (enums, composite types, ranges, domains) so columns better match your business concepts.

Arrays

A column that can store a list of values of the same type (e.g., text[], int[]). This is Postgres-specific and lets you keep small lists directly in a row without a separate table.

JSON

Postgres has json and jsonb column types for storing JSON documents. json keeps the original text; jsonb stores a binary representation that’s faster to search and index

HStore

key-value pairs; A Postgres extension/type that stores a set of text key-value pairs in one column, great for semi-structured data like user preferences or flexible attributes

CREATE statement 

CREATE TABLE users (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

INSERT Statement 

INSERT INTO users (name) VALUES ('Alice')

SELECT Statement

SELECT id, name FROM users;

JOIN Statement

SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id;

Primary Key

One or more columns whose value uniquely identifies each row in a table. In Postgres it also automatically creates a unique index for fast lookup.

Foreign Key

A column (or set of columns) that refers to the primary key of another table, enforcing a valid relationship between the two tables (e.g., orders.user_id references users.id)

Type Casting

Converting a value from one type to another, e.g., text to integer. In Postgres you can use ::type syntax:

SELECT '123'::int;

Indexing (Scaling Strategy)

creating a separate, quickly searchable structure that maps certain column values to their rows so the database can find data much faster. An index is a data structure (like a sorted lookup) that speeds up searches on a column (or columns). Postgres supports many index types (B-tree, GIN, etc.) and will use them to avoid scanning entire tables.

Materialized Views (Scaling Strategy)

A saved copy of the result of a query, stored on disk. Reads are fast because they don’t recompute the query each time, but the data can be a bit stale and must be refreshed.

Denormalization (Scaling Strategy)

Denormalization is intentionally duplicating data across tables to reduce joins and speed up reads, at the cost of extra storage and more complex updates.

Vertical Scaling (Scaling Strategy)

Making a single Postgres server more powerful (more CPU, RAM, faster disks) so it can handle more queries and data.

Caching (Scaling Strategy)

Storing frequently-used query results in memory (e.g., in the app layer or using tools like Redis) or relying on Postgres’s own buffer cache so repeated queries don’t hit disk every time.

Replication (Scaling Strategy)

Copying data from a primary Postgres server to one or more standby servers. Standbys can be used for read-only queries or failover if the primary goes down.

Sharding (Scaling Strategy)

Splitting your data across multiple Postgres instances (e.g., by user ID range or region) so no single server holds all rows. This can improve capacity and performance but adds complexity in your application and operations.