1. Foundations of Database Systems

This chapter covers the mental model the rest of the guide depends on: what a database management system does, why the relational model became the standard, and where PostgreSQL fits among other databases. It then gets practical. By the end you will have a PostgreSQL server running, you will know how it is organized on disk and in memory, and you will have connected to it, created tables, and loaded real data to experiment with.

<a id="11-database-fundamentals"></a>

1.1 Database Fundamentals

This subchapter covers the core ideas that apply to every relational database: what problems a DBMS solves, how the relational model represents data, and the guarantees and workloads that shape how databases are designed.

What a Database Management System Does

Almost every application has to remember things. It needs to store them safely, share them with many users at once, keep them correct, and answer questions about them quickly. You could write all of that yourself on top of plain files. If you did, you would end up rebuilding something that took decades of research and engineering to get right: a database management system (DBMS).

Think of a large library. The books are the database, an organized collection of data. The librarians, catalog, lending rules, and fire-safe archive together are the DBMS, the software that manages that collection on behalf of everyone who uses it. Readers never walk into the stacks and rearrange shelves. They ask the librarian, who knows where everything is, makes sure two people cannot check out the same copy, and keeps records that survive a disaster.

A DBMS has a set of core responsibilities:

Responsibility	Question it answers	How PostgreSQL implements it
Storage management	Where and how do the bytes live on disk?	Heap files made of 8 KB pages, TOAST for large values
Query processing	What is the most efficient way to compute this answer?	Parser, rewriter, cost-based planner, executor
Transaction management	How do multi-step changes happen all-or-nothing?	Transactions, write-ahead log (WAL)
Concurrency control	How do thousands of users work at the same time without corrupting data?	Multiversion concurrency control (MVCC), locks
Recovery	What happens after a crash or power loss?	WAL replay at startup, backups, point-in-time recovery
Integrity enforcement	How is invalid data rejected?	Constraints: PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL, UNIQUE
Security	Who may see or change what?	Roles, privileges, row-level security
Metadata management	What tables, columns, and indexes exist?	System catalogs (pg_catalog)

Two further ideas explain why a DBMS is more than a storage library:

• Declarative access. You say what you want ("customers in Italy, sorted by name"), not how to find it. The DBMS chooses the algorithm: which index to use, which join method, in what order.
• Data independence. Physical data independence means you can change how data is stored (add an index, move a table to faster disks, partition it) without rewriting queries. Logical data independence means you can change the logical structure (split a table, add columns) while views keep older applications working.

Example: the layers inside a DBMS

   Application (psql, a Java service, a BI tool)
         |   SQL text sent over the network protocol
         v
 +--------------------------------------------------+
 |  Connection handling and authentication          |
 |  Parser -> Rewriter -> Planner -> Executor       |   query processing
 |  Transaction manager | Lock manager | MVCC       |   concurrency and atomicity
 |  Buffer manager (shared_buffers page cache)      |   memory
 |  Storage manager     | Write-Ahead Log (WAL)     |   persistence and recovery
 +--------------------------------------------------+
         |
         v
   Files on disk (the data directory)

Example: declarative querying and physical data independence

-- You describe WHAT you want...
SELECT name, email
FROM customers
WHERE country = 'IT'
ORDER BY name;

-- ...and the DBMS decides HOW. EXPLAIN shows the strategy it chose.
EXPLAIN SELECT name, email FROM customers WHERE country = 'IT' ORDER BY name;
--  Sort
--    Sort Key: name
--    ->  Seq Scan on customers            -- read every row, keep the matching ones
--          Filter: (country = 'IT'::text)

-- Change only the physical design...
CREATE INDEX customers_country_name_idx ON customers (country, name);

-- ...and the same query, unchanged, may now use a different strategy.
EXPLAIN SELECT name, email FROM customers WHERE country = 'IT' ORDER BY name;
--  Index Scan using customers_country_name_idx on customers
--    Index Cond: (country = 'IT'::text)  -- rows come out already sorted by name

The application code did not change at all. That is physical data independence in practice.

Key takeaways

• A database is the data. A DBMS is the software that stores it, queries it, protects it, and shares it.
• The core responsibilities are storage, query processing, transactions, concurrency, recovery, integrity, security, and metadata.
• SQL is declarative: you state the result you want, and the planner chooses the execution strategy.
• Data independence lets the physical and logical design change without breaking applications.

🧪 Practice

1. (Beginner) For an event-ticketing website, describe one concrete thing that would go wrong if the DBMS did not provide each of these: concurrency control, recovery, and integrity enforcement.
2. (Intermediate) Give one example each of physical and logical data independence for a table orders(id, customer_id, total, created_at). For the logical case, show the SQL (for example a view) that keeps an old query working after you change the table's structure.
3. (Interview) "Why not store everything as JSON files in object storage and skip the database?" Hint: Go through the responsibilities table and ask which ones your application code would then have to implement, and how hard each one is to get right under concurrency and crashes.

Files vs. Databases

Before DBMSs became common, applications stored data in their own files: fixed-width records, CSV, or binary formats. Each program read and wrote its own files. That works for one program and one user. It breaks down once data is shared, grows, or has to stay correct after failures.

Imagine a small shop that keeps customers.csv, orders.csv, and inventory.csv, with a separate script for each task. Over time these problems appear:

• Redundancy and inconsistency. The customer's address is copied into orders.csv for convenience. When the customer moves, one file gets updated and the other does not.
• Difficult ad-hoc access. "Which customers ordered more than three times last month?" needs a new program instead of a one-line query.
• Data isolation. Data is spread across files in different formats, so combining it is tedious.
• Integrity problems. Rules like "stock cannot go negative" live in application code, and every program has to remember to enforce them.
• Atomicity problems. If the process crashes halfway through rewriting a file, you are left with a half-written or empty file.
• Concurrent-access anomalies. Two processes read, modify, and write the same file at the same moment, and one update silently overwrites the other.
• Coarse security. File permissions give access to a whole file or none of it. You cannot hide one column or some of the rows.

Example: a lost update with a file

import json

def withdraw(path, account, amount):
    with open(path) as f:                 # 1. read the whole file into memory
        balances = json.load(f)
    if balances[account] < amount:        # 2. business rule enforced only here, in app code
        raise ValueError("insufficient funds")
    balances[account] -= amount           # 3. modify the in-memory copy
    with open(path, "w") as f:            # 4. truncate and rewrite the entire file
        json.dump(balances, f)            #    a crash here can leave an empty or partial file

Run two of these concurrently against a balance of 100:

 Time   Process A (withdraw 30)          Process B (withdraw 50)
 ----   ------------------------------   ------------------------------
  t1    read file -> balance = 100
  t2                                     read file -> balance = 100
  t3    write balance = 70
  t4                                     write balance = 50     <- A's withdrawal is lost
 Final balance: 50 (should be 20)

Example: the same operation in a database

CREATE TABLE accounts (
  id      text PRIMARY KEY,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)   -- rule enforced for every program, always
);

BEGIN;
UPDATE accounts
SET balance = balance - 30          -- read-modify-write happens inside one statement
WHERE id = 'alice';                 -- the row is locked: a concurrent writer waits its turn
COMMIT;                             -- once COMMIT returns, the change survives a crash

If B runs at the same time, it waits for A's row lock and then applies its update to the new value (70), so the result is 20. If B's withdrawal would push the balance below zero, the CHECK constraint rejects it, no matter which program sent it.

Concern	Flat files	DBMS
Concurrent writers	Application must implement locking	Row-level locking and MVCC built in
Crash in the middle of a write	Partial or corrupt files	Atomic transactions, WAL recovery
Integrity rules	Repeated in every program	Declared once as constraints
New questions	Write a new program	Write a query
Access control	Per file	Per table, column, or row
Performance at scale	Full scans unless you build indexes yourself	Indexes, cost-based planner, caching

Files are still the right tool for some jobs: append-only logs, large binary blobs (images, videos), configuration, and analytical files such as Parquet that query engines read in bulk. A common pattern is to store the blob in object storage and keep its metadata and path in the database.

Key takeaways

• File-based storage suffers from redundancy, inconsistency, lost updates, partial writes, and integrity rules scattered across programs.
• A DBMS centralizes concurrency control, atomicity, integrity, and access control so that every application gets them automatically.
• Doing the read-modify-write inside one SQL statement (or one transaction) closes the lost-update race.
• Files are still a good fit for blobs, logs, and bulk analytical formats, often alongside a database.

🧪 Practice

1. (Beginner) Name the file-system problem each scenario shows: (a) a customer's email is different in two files; (b) a power cut leaves orders.csv empty; (c) a script forgets to check that quantity is positive.
2. (Intermediate) Rewrite the Python withdraw function so that it uses PostgreSQL through a driver such as psycopg. Explain why two concurrent calls can no longer lose an update, and what happens when the balance is not enough.
3. (Interview) "When would you deliberately store data in files rather than in a database?" Hint: Think about the data's size, whether it is written once or updated, who reads it and how (bulk scans vs. point lookups), and whether it needs transactions.

The Relational Model

Before 1970, the main database models were hierarchical (IBM's IMS) and network (CODASYL). Programs navigated those databases by following physical pointers from record to record. A query was a loop that walked a data structure. If the structure changed, for example by adding a new path or reorganizing records, the programs broke.

In 1970, Edgar F. Codd at IBM published "A Relational Model of Data for Large Shared Data Banks". His proposal was to represent all data as relations (tables of values), to connect data by matching values instead of following pointers, and to ground querying in mathematics: set theory and first-order predicate logic. Applications would state what they wanted, and the system would work out how to get it.

 Navigational (network model)                Relational
 ---------------------------------           ---------------------------------
 FIND customer WHERE id = 42                 SELECT o.id, o.total
 LOOP:                                       FROM customers c
   FIND NEXT order WITHIN customer-orders    JOIN orders o ON o.customer_id = c.id
   IF end-of-set EXIT                        WHERE c.id = 42;
   GET order; PRINT order
 The program knows the physical access path. The program states the result it wants.

The relational model has three parts:

1. Structure: data lives in relations made of tuples and attributes (covered in the next topic).
2. Integrity: rules such as keys and referential integrity constrain which states are valid.
3. Manipulation: operators that take relations as input and produce relations as output.

The manipulation part is relational algebra. Because every operator returns a relation, operators can be combined freely. This property is called closure, and it is why SQL lets you nest queries inside queries.

Operator	Symbol	Meaning	SQL equivalent
Selection	σ	Keep tuples that satisfy a predicate	WHERE
Projection	π	Keep only some attributes	SELECT col1, col2
Cartesian product	×	Every combination of tuples	CROSS JOIN
Join	⋈	Product filtered by a matching condition	JOIN ... ON
Union	∪	Tuples in either relation	UNION
Difference	−	Tuples in the first but not the second	EXCEPT
Rename	ρ	Rename a relation or attribute	AS

Example: from algebra to SQL

"Names and order totals of Italian customers":

π name, total ( σ country = 'IT' (customers) ⋈ customers.id = orders.customer_id  orders )

SELECT c.name, o.total              -- projection (π)
FROM customers AS c                 -- rename (ρ) via alias
JOIN orders AS o                    -- join (⋈)
  ON o.customer_id = c.id
WHERE c.country = 'IT';             -- selection (σ)

SQL is not a pure implementation of the model. SQL was designed as a practical language, and it departs from Codd's model in ways you need to know:

Pure relational model	SQL / PostgreSQL
A relation is a set, so there are no duplicate tuples	Tables and query results are bags (multisets). Duplicates are allowed unless you use DISTINCT or a key
No notion of missing values	NULL and three-valued logic (TRUE, FALSE, UNKNOWN)
Attributes are unordered	Columns have a defined order (SELECT * uses it)
Tuples are unordered	Still unordered, unless you use ORDER BY
Every relation has a key	A table can exist without a primary key

PostgreSQL calls itself an object-relational DBMS. It implements the relational model through SQL and adds features such as user-defined types, table inheritance, and functions that make it extensible (see 1.2).

Key takeaways

• Codd's relational model (1970) replaced pointer navigation with value-based, declarative access to tables.
• The model has three parts: structure (relations), integrity (constraints), and manipulation (relational algebra).
• Closure (operators return relations) is what makes SQL queries composable.
• SQL departs from the pure model: duplicates, NULLs, and column order all exist in SQL.

🧪 Practice

1. (Beginner) Write the relational algebra expression and the SQL query for "the names of employees in department 10" using employees(id, name, dept_id, salary).
2. (Intermediate) Write a SQL query on orders(id, customer_id, total) that returns duplicate rows, which a pure relational projection could not. Then fix it with DISTINCT. Explain which relational algebra operator you were imitating and why the duplicates appeared.
3. (Interview) "What problem did the relational model solve that the hierarchical and network models could not?" Hint: Think about what happened to existing programs when the physical organization of the data changed.

Relations, Tuples, and Attributes

Precise terminology avoids confusion later, especially when reading documentation or design discussions. The formal vocabulary comes from mathematics. SQL and everyday usage have their own words for the same things.

• A domain is the set of values an attribute is allowed to take, such as "integers", "non-negative amounts with two decimals", or "ISO country codes".
• An attribute is a named column drawn from a domain, such as salary from the domain of non-negative amounts.
• A relation schema (or heading) is the relation's name plus its list of attributes: employees(id: int, name: text, dept: text, salary: numeric).
• A tuple is one element of the relation: one value for each attribute.
• A relation instance (or body) is the set of tuples present at a particular moment.
• Degree is the number of attributes. Cardinality is the number of tuples.

 Relation schema: employees(id: integer, name: text, dept: text, salary: numeric)

              attribute      attribute          attribute    attribute
                  |              |                  |            |
             +--------+--------------------+------------+-----------+
             |   id   |        name        |    dept    |  salary   |  <- heading (schema)
             +--------+--------------------+------------+-----------+
   tuple --> |    1   | Ada Lovelace       | R&D        |  95000.00 |
   tuple --> |    2   | Alan Turing        | R&D        |  98000.00 |  <- body (instance)
   tuple --> |    3   | Grace Hopper       | Platform   | 105000.00 |
             +--------+--------------------+------------+-----------+

   degree = 4 (fixed by the schema)      cardinality = 3 (changes as data changes)

Formal term	SQL term	File / spreadsheet term
Relation	Table	File / sheet
Tuple	Row	Record / line
Attribute	Column	Field
Domain	Data type (plus constraints)	Field format
Degree	Number of columns	Number of fields
Cardinality	Number of rows	Number of records

Formally, a relation has four properties:

1. No duplicate tuples. A relation is a set. In SQL, you enforce this with a primary key or unique constraint.
2. Tuples are unordered. There is no "first row". Any order you see without ORDER BY is incidental.
3. Attributes are unordered. They are identified by name, not position. SQL relaxes this.
4. Values are atomic. Each cell holds a single value from its domain. This is the basis of First Normal Form, covered in 2.3.

Example: domains, keys, and the absence of row order

CREATE DOMAIN salary_amount AS numeric(10,2)
  CHECK (VALUE >= 0);                       -- a domain: a base type plus a rule

CREATE TABLE employees (
  id     integer PRIMARY KEY,               -- key: no two tuples can be identical
  name   text          NOT NULL,
  dept   text          NOT NULL,
  salary salary_amount NOT NULL             -- attribute drawn from the custom domain
);

INSERT INTO employees VALUES
  (1, 'Ada Lovelace', 'R&D',      95000),
  (2, 'Alan Turing',  'R&D',      98000),
  (3, 'Grace Hopper', 'Platform', 105000);

UPDATE employees SET salary = salary * 1.05 WHERE id = 1;
-- PostgreSQL's MVCC writes a NEW version of row 1, possibly at the end of the table

SELECT * FROM employees;                    -- row 1 may now come back last
-- Only ORDER BY guarantees an order:
SELECT * FROM employees ORDER BY id;

INSERT INTO employees VALUES (4, 'Bad Data', 'Ops', -10);
-- ERROR:  value for domain salary_amount violates check constraint "salary_amount_check"

Key takeaways

• Relation = table, tuple = row, attribute = column, domain = allowed set of values (type plus constraints).
• Degree (column count) is fixed by the schema. Cardinality (row count) changes over time.
• Rows have no inherent order. Always use ORDER BY when order matters.
• Keys are what make a SQL table behave like a set of distinct tuples.

🧪 Practice

1. (Beginner) A products table has 6 columns and 1,200 rows. What are its degree and cardinality? Which one changes when you insert a row, and which one changes when you run ALTER TABLE ... ADD COLUMN?
2. (Intermediate) Write a relation schema for books and the matching CREATE TABLE. Include a domain isbn13 that accepts only strings of exactly 13 digits (use CHECK (VALUE ~ '^[0-9]{13}$')). Insert one valid and one invalid value and observe the result.
3. (Interview) "Why can the same SELECT without ORDER BY return rows in a different order from one run to the next?" Hint: Think about relations as sets, and about what can change underneath: row versions written by updates, parallel scans, synchronized sequential scans, and the planner choosing a different plan.

Schemas, Catalogs, and Namespaces

The word "schema" has three meanings in database work, and they get mixed up constantly:

1. The database schema (design): the overall logical structure, meaning all the tables, columns, and relationships. "Let's review the schema before launch."
2. A relation schema: one table's heading, as in the previous topic.
3. A schema object (namespace): a named container inside a database that holds tables, views, functions, and types. This is what CREATE SCHEMA creates.

This topic is about meaning 3, and its relationship to catalogs.

A namespace works like a directory in a file system. Two files can both be called report.txt if they are in different directories. In the same way, sales.people and hr.people can coexist. Schemas also let you group related objects, grant permissions on the whole group, and keep extensions or tenants separate from each other.

The SQL standard defines a hierarchy: cluster > catalog > schema > object. PostgreSQL maps it like this:

 Cluster: one server instance, one data directory, one port
 ├── Roles                (global: shared by every database)
 ├── Tablespaces          (global)
 ├── Database: postgres
 ├── Database: shop                         <- a "catalog" in SQL-standard terms
 │   ├── Schema: public
 │   │   ├── Table: products
 │   │   └── Table: orders
 │   ├── Schema: billing
 │   │   ├── Table: invoices
 │   │   └── Function: compute_tax(numeric)
 │   ├── Schema: pg_catalog                 <- the system catalogs (built in)
 │   └── Schema: information_schema         <- SQL-standard views over the catalogs
 ├── Database: template1
 └── Database: template0

A fully qualified name is database.schema.object. A PostgreSQL session is connected to exactly one database, though, so in practice you write schema.object. A reference to a different database fails.

The search path. When you write an unqualified name such as invoices, PostgreSQL searches the schemas listed in search_path in order. The default is "$user", public: first a schema with the same name as your role (if one exists), then public. pg_catalog is always searched first implicitly, which is how built-in functions like now() are found.

System catalogs. PostgreSQL stores its own metadata (the data dictionary) in ordinary tables in the pg_catalog schema:

Catalog	Describes
pg_namespace	Schemas
pg_class	Tables, indexes, sequences, views (any "relation")
pg_attribute	Columns of every relation
pg_type	Data types
pg_proc	Functions and procedures
pg_database	Databases in the cluster (shared across databases)
pg_roles	Roles (a view over pg_authid)

information_schema is the SQL-standard, portable set of views over the same information. It is easier to remember and works across databases, but it is slower and omits PostgreSQL-specific details.

Example: schemas, search path, and querying the catalog

CREATE SCHEMA billing;

CREATE TABLE billing.invoices (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL
);

CREATE TABLE public.invoices (id integer);   -- same name, different namespace: no conflict

SHOW search_path;                            -- "$user", public
SELECT * FROM invoices;                      -- resolves to public.invoices

SET search_path = billing, public;           -- affects this session only
SELECT * FROM invoices;                      -- now resolves to billing.invoices

-- Ask the system catalogs which user tables exist
SELECT n.nspname AS schema_name,
       c.relname AS table_name
FROM pg_catalog.pg_class     AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'                        -- 'r' = ordinary table ('v' view, 'i' index, ...)
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

-- The portable equivalent through information_schema
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

-- Cross-database references are rejected
SELECT * FROM otherdb.public.customers;
-- ERROR:  cross-database references are not implemented: "otherdb.public.customers"

In psql, \dn lists schemas and \dt billing.* lists the tables in one schema.

Security note. Before PostgreSQL 15, every role could create objects in public by default. A malicious user could then create a function with the same name as one other users call, and because public is on everyone's search path, it could intercept those calls. Since PostgreSQL 15, CREATE on public is no longer granted to all roles. A good habit is to keep untrusted schemas out of the search path of privileged roles.

The same words in other systems

Concept	PostgreSQL	MySQL	SQL Server	Oracle
Top-level container you connect to	Database	Server (instance)	Database	Database / pluggable database
Namespace for tables	Schema	Database (SCHEMA is a synonym)	Schema (default dbo)	Schema (tied to a user)
Cross-container queries	Not native (use postgres_fdw)	Yes, db.table	Yes, db.schema.table	Via database links

Key takeaways

• "Schema" can mean the overall design, a table's heading, or a namespace object. In PostgreSQL DDL it means the namespace.
• In PostgreSQL, cluster > database (catalog) > schema > object. Roles and tablespaces are cluster-wide.
• Unqualified names are resolved through search_path. pg_catalog is always searched first.
• Metadata lives in queryable tables (pg_catalog), with portable views in information_schema.
• A session connects to one database. Cross-database queries need extensions such as postgres_fdw.

🧪 Practice

1. (Beginner) Create schemas sales and hr, each with a table called people. Insert a different row into each, then query both tables using fully qualified names. Change search_path so that SELECT * FROM people reads from hr.
2. (Intermediate) Write a catalog query that lists the schema, table, column name, and data type of every column in your user tables, using pg_attribute (filter with attnum > 0 AND NOT attisdropped) and format_type(atttypid, atttypmod). Compare its output with information_schema.columns.
3. (Interview) "Why is it risky to let untrusted users create objects in a schema that appears early in other users' search_path?" Hint: Think about what happens when someone creates a function with the same name and argument types as one that a privileged user calls without schema qualification.

ACID Properties Overview

Think of moving money from one account to another. Two things have to happen: debit one account and credit the other. If only one of them happens, money is created or destroyed. If two transfers run at the same moment and interfere with each other, balances become wrong. If the server crashes just after confirming the transfer and the transfer is then lost, customers stop trusting the bank.

A transaction groups operations into one logical unit of work, and ACID names the four guarantees a transactional database gives about such units:

Property	Guarantee	Failure it prevents	PostgreSQL mechanism	Covered in depth
Atomicity	Either every change in the transaction happens, or none does	Half-finished transfers	WAL plus MVCC: row versions from aborted transactions never become visible	8.1
Consistency	A transaction moves the database from one valid state to another, according to its declared rules	Negative balances, orphaned orders	Constraints, foreign keys, triggers	2.2
Isolation	Concurrent transactions do not see each other's partial work, and the level controls how much they can interfere	Reading uncommitted data, lost updates	MVCC snapshots, locks, isolation levels (default: Read Committed)	8.2, 8.3
Durability	Once COMMIT returns, the change survives crashes and power loss	Losing confirmed transactions	WAL flushed to disk at commit, crash recovery	11.2

Some points to be precise about:

• Consistency is partly your responsibility. The database enforces only the rules you declare. If "a transfer must not exceed 10,000" is never expressed as a constraint, the database cannot enforce it.
• The C in ACID is not the C in the CAP theorem. ACID consistency means integrity rules hold. CAP consistency means all replicas return the same, latest value.
• Isolation comes in levels. Stronger levels prevent more anomalies but can make transactions wait or fail and need a retry.
• Durability depends on the hardware honoring flushes. Settings such as synchronous_commit = off deliberately trade a small durability window for speed.

Example: atomicity and consistency

CREATE TABLE accounts (
  id      text PRIMARY KEY,
  balance numeric(12,2) NOT NULL CHECK (balance >= 0)    -- a declared consistency rule
);
INSERT INTO accounts VALUES ('alice', 100), ('bob', 20);

BEGIN;                                                   -- start a transaction
UPDATE accounts SET balance = balance - 150 WHERE id = 'alice';
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
UPDATE accounts SET balance = balance + 150 WHERE id = 'bob';
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;                                                -- nothing from this transaction persists

SELECT * FROM accounts ORDER BY id;                      -- alice 100, bob 20: unchanged

BEGIN;                                                   -- a valid transfer
UPDATE accounts SET balance = balance - 50 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 50 WHERE id = 'bob';
COMMIT;                                                  -- both changes become visible together

Example: isolation between two sessions (Read Committed)

 Session A                                      Session B
 ---------------------------------------------  ---------------------------------------------
 BEGIN;
 UPDATE accounts SET balance = balance - 10
  WHERE id = 'alice';
 -- inside A, alice now has 40
                                                SELECT balance FROM accounts WHERE id = 'alice';
                                                -- 50: B cannot see A's uncommitted change
 COMMIT;
                                                SELECT balance FROM accounts WHERE id = 'alice';
                                                -- 40: each statement sees data committed before it began

For durability: if the machine loses power one millisecond after COMMIT returns to session A, PostgreSQL replays the WAL at the next startup and the debit is still there.

Key takeaways

• A transaction is an all-or-nothing unit of work. ACID describes the guarantees around it.
• Atomicity and durability come mainly from the write-ahead log. Isolation comes from MVCC and locks. Consistency comes from declared constraints.
• After an error inside a transaction block, PostgreSQL rejects further commands until ROLLBACK.
• PostgreSQL's default isolation level is Read Committed: each statement sees data committed before that statement started.

🧪 Practice

1. (Beginner) For a seat-booking system, describe what could go wrong if each of the four ACID properties were missing, one scenario per property.
2. (Intermediate) Open two psql sessions and reproduce the isolation timeline above. Then repeat it with session B starting BEGIN ISOLATION LEVEL REPEATABLE READ; before its first SELECT, and explain why the second SELECT result is different.
3. (Interview) "Is the C in ACID the same as the C in CAP?" Hint: One concerns integrity rules inside a single database. The other concerns agreement between replicas in a distributed system.

OLTP vs. OLAP Workloads

Databases serve two very different kinds of work. Picture a shop: the cashier handles a constant stream of small, fast transactions such as scanning an item, taking payment, and printing a receipt. At year end, the accountant asks big questions such as "revenue by month and category for the whole year", which read through all of the history but change nothing. Systems tuned for one of these jobs are often poor at the other.

• OLTP (Online Transaction Processing): many short, concurrent transactions that each read or write a few rows, usually by key. Examples: placing an order, updating a profile, booking a seat.
• OLAP (Online Analytical Processing): fewer, long-running, read-mostly queries that scan and aggregate large amounts of data. Examples: dashboards, reporting, trend analysis.

Aspect	OLTP	OLAP
Typical query	Point lookups and small updates by key	Scans, joins, and aggregations over millions of rows
Rows touched per query	Few	Many
Concurrency	Thousands of concurrent short transactions	A handful of heavy queries
Latency target	Milliseconds	Seconds to minutes
Write pattern	Frequent inserts, updates, deletes	Bulk loads, mostly reads
Schema style	Normalized (avoid redundancy)	Denormalized star or snowflake schemas (2.4)
Data freshness	Current state	Historical, often loaded periodically
Storage layout that fits best	Row-oriented	Column-oriented, compressed
Typical systems	PostgreSQL, MySQL, Oracle, SQL Server	Data warehouses and columnar engines

Row vs. column storage

 Row-oriented (PostgreSQL heap)                  Column-oriented (analytical engines)
 ----------------------------------------        ----------------------------------------
 page 1: [1|Ada|IT|95000] [2|Alan|UK|98000]      id:      [1][2][3][4]...
 page 2: [3|Grace|US|105000] [4|...]             name:    [Ada][Alan][Grace]...
                                                 country: [IT][UK][US]...
                                                 salary:  [95000][98000][105000]...

 Fetch one full row:   one page read             Fetch one full row:   touch every column
 SUM(salary) on all:   read every page,          SUM(salary) on all:   read only the
                       including unused columns                        (compressed) salary column

Example: the two workloads in SQL

-- OLTP: a checkout. Touches a few rows by key and finishes in milliseconds.
BEGIN;
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (98231, 17, 2, 24.50);
UPDATE products
SET in_stock = in_stock - 2
WHERE id = 17 AND in_stock >= 2;          -- primary-key lookup plus a guard
COMMIT;

-- OLAP: a monthly revenue report. Scans a year of history and aggregates it.
SELECT date_trunc('month', o.created_at)  AS month,
       p.category,
       sum(oi.quantity * oi.unit_price)   AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.created_at >= now() - interval '1 year'
GROUP BY 1, 2                             -- group by the 1st and 2nd select-list items
ORDER BY 1, 2;

Where PostgreSQL fits. PostgreSQL is a row-oriented database built first for OLTP. It also handles moderate analytical work well, thanks to parallel query, window functions, BRIN indexes, partitioning, and materialized views. Extensions add columnar storage and analytical engines. Running heavy analytics on the same primary that serves OLTP traffic has costs beyond CPU and I/O: a long-running query holds an old MVCC snapshot, which stops VACUUM from cleaning up dead rows (8.5). Common patterns are to run reports on a read replica, pre-aggregate with materialized views, or stream changes into a dedicated warehouse.

Key takeaways

• OLTP means many small, concurrent, latency-sensitive transactions. OLAP means few large, scan-heavy, read-mostly queries.
• Row storage favors fetching whole records. Column storage favors aggregating a few columns over many rows.
• PostgreSQL is OLTP-first but has strong analytical features, and extensions can add more.
• Mixing heavy OLAP into an OLTP primary can hurt it through resource contention and delayed vacuum cleanup.

🧪 Practice

1. (Beginner) Classify each as OLTP or OLAP: (a) log in a user by email; (b) average basket size per country over five years; (c) cancel an order; (d) top 100 products by revenue this quarter; (e) reserve the last seat on a flight.
2. (Intermediate) Explain why a two-hour analytical query on a PostgreSQL primary can slow down OLTP traffic even when the CPU is mostly idle. Propose two architectural changes to avoid it.
3. (Interview) "Product wants a live revenue dashboard that queries the production database directly. What do you consider?" Hint: How fresh does the data really need to be, how expensive is each refresh, how many viewers are there, and can replicas, materialized views, or pre-aggregated tables help?

<a id="12-introduction-to-postgresql"></a>

1.2 Introduction to PostgreSQL

This subchapter covers PostgreSQL itself: where it came from, who maintains it, how it compares with other databases, how its releases work, and why extensibility and a permissive license have produced such a large ecosystem.

History and Governance of PostgreSQL

Many of PostgreSQL's design choices only make sense in light of its history. Its extensibility, its MVCC heritage, and its "object-relational" label all go back to its origins. Its governance explains why no company can buy it or change its license.

Origins at Berkeley. In the 1970s, Michael Stonebraker and colleagues at UC Berkeley built Ingres, one of the first relational database implementations. In 1986 Stonebraker started a successor project called POSTGRES ("post-Ingres"). Its goal was to fix limitations of the systems of the time, most importantly that users could not add their own data types, operators, and access methods. POSTGRES originally used its own query language, POSTQUEL. In 1994, graduate students Andrew Yu and Jolly Chen replaced POSTQUEL with an SQL interpreter and released the result as Postgres95. In 1996 the project was renamed PostgreSQL to reflect SQL support, and development moved to a worldwide volunteer community. Stonebraker received the 2014 ACM Turing Award, partly for this work.

 1973-1979  Ingres project at UC Berkeley
 1986       POSTGRES project begins, led by Michael Stonebraker
 1989       POSTGRES version 1 released to a small group of external users (query language: POSTQUEL)
 1994       Andrew Yu and Jolly Chen add an SQL interpreter -> Postgres95
 1996       Renamed PostgreSQL; numbering restarts at 6.0; open community development begins
 2001       7.1: write-ahead logging (WAL)
 2005       8.0: native Windows support, savepoints, point-in-time recovery, tablespaces
 2010       9.0: built-in streaming replication and hot standby
 2014       9.4: jsonb binary JSON type
 2017       10:  declarative partitioning, logical replication, two-part version numbers
 2022       15:  MERGE statement
 2024       17:  incremental backups, JSON_TABLE
 2025       18:  asynchronous I/O subsystem, uuidv7(), OAuth authentication

Governance. PostgreSQL is developed by the PostgreSQL Global Development Group (PGDG), a community of individuals and companies. No single company owns the project or the code:

Group	Role
Core team	A small group that coordinates releases, handles security issues together with the security team, sets policy, and resolves disputes. By policy, no more than half of its members may work for the same company
Committers	Developers with write access to the main repository, who review and commit patches
Contributors	Anyone who writes patches, reviews, tests, documents, or translates
Security team	Receives vulnerability reports and coordinates fixes and disclosure
Code of Conduct Committee	Handles conduct reports across community spaces
Nonprofit organizations	Hold trademarks and domains and run conferences and outreach, for example the PostgreSQL Community Association, PostgreSQL Europe, and PgUS

Many contributors are employed by companies that depend on PostgreSQL. That funding is spread across many organizations, so the project does not depend on any one of them.

Development process. Patches are proposed and discussed in public on the pgsql-hackers mailing list and tracked in CommitFests, month-long review rounds held several times per development cycle:

  Jul      Sep      Nov      Jan      Mar        ~Apr           ~May          ~Sep/Oct
  CF ----- CF ----- CF ----- CF ----- CF ---->  feature  ---->  beta(s)  ---> release candidate
  (patches reviewed and committed to the master branch)  freeze                 -> general release

Key takeaways

• PostgreSQL descends from the Berkeley POSTGRES project (1986). It gained SQL in 1994 and its current name in 1996.
• Its original design goal, user-defined types, operators, and access methods, is why it is so extensible today.
• It is governed by a community (the PGDG and its core team), not owned by a company.
• Development happens in public on mailing lists and through CommitFests, with one major release per year.

🧪 Practice

1. (Beginner) Explain where the name "Postgres" comes from and why "SQL" was later added to it.
2. (Intermediate) Open the CommitFest application (commitfest.postgresql.org), pick one patch, and describe its status, how long it has been in review, and what the discussion on its mailing-list thread is about.
3. (Interview) "Why might a company prefer a community-governed database over an open-source database controlled by one vendor?" Hint: Look at database projects that changed their license in recent years, and think about what that did to their users' options.

PostgreSQL vs. Other Relational Databases

There is no universally "best" database. Each one makes trade-offs, and knowing them helps you choose the right tool and explain the choice.

Aspect	PostgreSQL	MySQL (InnoDB)	SQLite	Oracle Database	SQL Server
Deployment model	Client-server	Client-server	Embedded library (in-process)	Client-server	Client-server
Connection model	One OS process per connection	One thread per connection	Not applicable	Process or thread, depending on configuration	Threads
License	PostgreSQL License (permissive)	GPLv2 or commercial	Public domain	Proprietary	Proprietary
MVCC approach	Old row versions stay in the table, cleaned up by VACUUM	Old versions kept in undo logs	Single writer at a time	Undo tablespace	Locking by default, optional row versioning
Default isolation	Read Committed	Repeatable Read	Serializable	Read Committed	Read Committed
Transactional DDL	Yes	No (DDL commits implicitly)	Yes	No (DDL commits implicitly)	Mostly yes
Extensibility	Types, operators, index methods, languages, FDWs, extensions	Storage engines, plugins	C API for functions and virtual tables	PL/SQL, Java	.NET CLR integration
Built-in replication	Physical (streaming) and logical	Binary log based	None	Data Guard, GoldenGate (licensed)	Always On availability groups

Where PostgreSQL is strong

• Standards compliance and advanced SQL: CTEs, window functions, LATERAL, MERGE, FILTER, rich JSON support.
• A rich type system: arrays, ranges, jsonb, network types, geometric types, and user-defined types.
• Extensibility: features such as PostGIS and pgvector ship as extensions without forking the engine.
• Transactional DDL: schema migrations can be wrapped in a transaction and rolled back cleanly.
• A permissive license with no single-vendor control.

Where PostgreSQL needs extra care

• Connections: process-per-connection makes thousands of direct connections expensive, so a pooler such as PgBouncer is common (7.4).
• VACUUM: in-table MVCC leaves dead row versions that autovacuum must clean up (8.5).
• Update-heavy workloads: every update writes a new row version, which can amplify writes (HOT updates help).
• High availability: automatic failover needs external tools such as Patroni (12.3).
• Major upgrades: they need pg_upgrade, a dump and restore, or logical replication (11.4).

When another database may fit better

• SQLite: local or embedded storage (mobile apps, desktop apps, tests, edge devices) where a server is unnecessary.
• MySQL/MariaDB: existing team expertise or ecosystem tooling built around it.
• Oracle/SQL Server: existing enterprise investment, vendor support contracts, or features tied to that platform.

Example: transactional DDL

-- PostgreSQL: schema changes take part in the transaction
BEGIN;
CREATE TABLE audit_log (id bigint, message text);
ALTER TABLE products ADD COLUMN discontinued boolean NOT NULL DEFAULT false;
-- ...suppose the next migration step fails...
ROLLBACK;            -- audit_log and the new column are both gone: the schema is unchanged

In MySQL or Oracle, each CREATE and ALTER commits implicitly, so a migration that fails halfway leaves the schema partly changed.

Example: the same task in different dialects

Task	PostgreSQL	MySQL	SQL Server	Oracle
Auto-generated key	GENERATED ALWAYS AS IDENTITY	AUTO_INCREMENT	IDENTITY(1,1)	GENERATED ALWAYS AS IDENTITY (12c+)
First 10 rows	LIMIT 10 or FETCH FIRST 10 ROWS ONLY	LIMIT 10	TOP 10 or OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY	FETCH FIRST 10 ROWS ONLY (12c+)
Insert or update	INSERT ... ON CONFLICT DO UPDATE or MERGE	INSERT ... ON DUPLICATE KEY UPDATE	MERGE	MERGE
String concatenation	a || b	CONCAT(a, b)	a + b or CONCAT(a, b)	a || b
Current timestamp	now()	NOW()	SYSDATETIME()	SYSTIMESTAMP

Key takeaways

• PostgreSQL stands out for SQL standards support, a rich type system, extensibility, transactional DDL, and its permissive license.
• Its operational trade-offs are process-per-connection, VACUUM, update write amplification, external HA tooling, and major-version upgrades.
• SQLite is for embedded use. MySQL, Oracle, and SQL Server make other trade-offs in licensing, MVCC design, and features.
• Learn the dialect differences early. They are the main obstacle when migrating between systems.

🧪 Practice

1. (Beginner) A mobile note-taking app must work offline and store notes on the device. Which database from the table fits best, and why is a client-server database a poor fit here?
2. (Intermediate) Write a migration that adds a column status text NOT NULL DEFAULT 'active' to customers and creates an index on it, wrapped in a transaction. Explain what happens to the schema if the index creation fails in PostgreSQL, and what would happen in MySQL.
3. (Interview) "What are PostgreSQL's operational weak spots compared with MySQL?" Hint: Think about the connection model, how old row versions are cleaned up, the cost of updates, and how major versions are upgraded.

Release Cycle and Version Support

You will run PostgreSQL in production for years, so you need to know which versions get fixes, how upgrades differ, and when you are forced to upgrade.

The cycle

• Major releases add features and come out once a year, usually in September or October. They may change the format of the system catalogs and data files.
• Minor releases contain only bug and security fixes. They come out at least once a quarter (normally on the second Thursday of February, May, August, and November), with extra releases for urgent security issues.
• Support window: each major version gets minor releases for five years after its first release. After the final minor release it is end-of-life (EOL) and receives no further fixes, including security fixes.

Version numbers. Since PostgreSQL 10, a version has two parts. Before 10, the first two numbers formed the major version.

 PostgreSQL 10 and later:        17 . 6            PostgreSQL 9.6 and earlier:    9.6 . 24
                                 |    |                                            |      |
                             major   minor                                     major    minor

Minor vs. major upgrades

	Minor upgrade (e.g. 17.5 -> 17.6)	Major upgrade (e.g. 16.x -> 17.x)
What changes	Bug and security fixes only	New features, possibly a new catalog or storage format
On-disk compatibility	Same	May differ
Procedure	Install new binaries, restart the server	pg_upgrade, dump and restore, or logical replication
Downtime	Seconds (a restart)	Minutes to hours, or near zero with logical replication
Risk	Low, but read the release notes (some require extra steps such as a reindex)	Higher: test applications, extensions, and query plans
Recommendation	Always run the latest minor of your major	Plan ahead, well before EOL

Recent major versions. These dates follow the five-year policy. Check postgresql.org/support/versioning for the current list, because a new major version is added every autumn.

Major	First released	Final minor release (EOL)
13	September 2020	November 2025
14	September 2021	November 2026
15	October 2022	November 2027
16	September 2023	November 2028
17	September 2024	November 2029
18	September 2025	November 2030

Example: finding out what you are running

SELECT version();
-- PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc ..., 64-bit

SHOW server_version;          -- 17.6
SHOW server_version_num;      -- 170006  (major * 10000 + minor): easy to compare as a number

SELECT CASE
         WHEN current_setting('server_version_num')::int >= 150000 THEN 'MERGE is available'
         ELSE 'MERGE needs PostgreSQL 15 or later'
       END AS feature_check;

psql --version            # the CLIENT version, which may differ from the server version
pg_config --version       # version of the installed server development files

Key takeaways

• One major release per year, minor releases at least quarterly, and five years of support per major.
• Since version 10, the version is major.minor, so 17.6 means major 17, minor 6.
• Minor upgrades mean new binaries and a restart. Major upgrades need pg_upgrade, dump and restore, or logical replication.
• Running an EOL version means running without security fixes.

🧪 Practice

1. (Beginner) For 9.6.24, 12.3, and 16.0, identify the major and minor versions. Which of these needs pg_upgrade or a dump and restore: 16.0 -> 16.4 or 16.4 -> 17.0?
2. (Intermediate) Write a DO block that raises an exception with a clear message if the server is older than version 15, using current_setting('server_version_num'). Explain how you would use it at the start of a migration script.
3. (Interview) "Your company still runs PostgreSQL 13 in production. What is the risk, and how would you plan the upgrade?" Hint: Think about EOL and security exposure, the available upgrade methods and their downtime, extension compatibility, and testing against the new version.

Extensibility as a Design Principle

In the 1980s, adding a new kind of data to a database, such as geographic shapes, time series, or documents, meant changing the database engine's source code. POSTGRES was designed to remove that limit. In PostgreSQL, data types, functions, operators, aggregates, and index access methods are described by rows in the system catalogs, not hard-coded into the engine. Built-in types such as integer are registered the same way user-defined types are. That is why extensions get first-class treatment: the planner can estimate their costs and indexes can accelerate their operators.

An analogy is the difference between an old feature phone and a smartphone operating system. The phone ships with a fixed set of apps. The OS defines extension points, so third parties can add capabilities that behave like built-in ones.

What you can add	How	Example
Data types	CREATE TYPE, CREATE DOMAIN	Composite types, enums, ranges, base types written in C
Functions and procedures	CREATE FUNCTION, CREATE PROCEDURE	SQL, PL/pgSQL, PL/Python, C
Operators	CREATE OPERATOR	<-> distance operator for a custom type
Aggregates	CREATE AGGREGATE	A custom statistical aggregate
Index support	Operator classes for B-tree, GiST, GIN, SP-GiST, BRIN; CREATE ACCESS METHOD	Trigram indexes (pg_trgm), vector indexes (pgvector)
Procedural languages	CREATE LANGUAGE	PL/Python, PL/Perl, PL/v8
External data	Foreign data wrappers	postgres_fdw, file_fdw
Table storage	Table access methods (PostgreSQL 12+)	Columnar storage extensions
Server behavior	C modules loaded with shared_preload_libraries, using hooks and background workers	pg_stat_statements, pg_cron
Packaging	CREATE EXTENSION	Installs, versions, and removes a bundle of objects together

Example: adding a type, a function, and an operator

CREATE TYPE point2d AS (x float8, y float8);            -- a new composite type

CREATE FUNCTION point2d_distance(a point2d, b point2d)
RETURNS float8
LANGUAGE sql
IMMUTABLE STRICT                                         -- same inputs always give the same output; NULL in -> NULL out
AS $$
  SELECT sqrt((a.x - b.x) ^ 2 + (a.y - b.y) ^ 2)
$$;

CREATE OPERATOR <-> (                                    -- bind an operator symbol to the function
  LEFTARG    = point2d,
  RIGHTARG   = point2d,
  FUNCTION   = point2d_distance,
  COMMUTATOR = <->                                       -- a <-> b equals b <-> a
);

SELECT ROW(0, 0)::point2d <-> ROW(3, 4)::point2d;        -- 5

-- The new operator is a row in the catalog, next to the built-in operators with the same symbol
SELECT oprname, oprleft::regtype, oprright::regtype, oprcode
FROM pg_operator
WHERE oprname = '<->';

Example: installing a packaged extension

SELECT name, default_version, comment
FROM pg_available_extensions                     -- extensions installed on the server's disk
ORDER BY name;

CREATE EXTENSION IF NOT EXISTS pg_trgm;          -- ships with PostgreSQL as a contrib module

SELECT similarity('postgres', 'postgress');      -- 0.72727275: share of common 3-character sequences
CREATE INDEX products_name_trgm_idx
  ON products USING gin (name gin_trgm_ops);     -- now ILIKE '%keyb%' can use an index

Key takeaways

• Extensibility was a founding goal of POSTGRES, not something added later.
• Types, operators, functions, and index methods are catalog entries, so user-defined ones are treated the same as built-in ones.
• CREATE EXTENSION packages related objects into a versioned unit that can be installed and removed.
• Major capabilities such as spatial data, vector search, and time series ship as extensions instead of forks.

🧪 Practice

1. (Beginner) List the extensions available on your server with pg_available_extensions. Install citext, create a table with an email citext UNIQUE column, and show that 'Ada@Example.com' and 'ada@example.com' count as duplicates.
2. (Intermediate) Create a custom aggregate product(numeric) that multiplies all input values, using CREATE AGGREGATE with sfunc = numeric_mul, stype = numeric, and initcond = '1'. Test it with SELECT product(x) FROM generate_series(1, 5) AS x;.
3. (Interview) "How is PostgreSQL's extensibility different from a plugin system added to a database later?" Hint: Think about where built-in types and operators are defined, and whether the planner and the index machinery treat user-defined ones any differently.

Licensing and Ecosystem

A database's license decides what you can legally build with it. The ecosystem around it decides how much you have to build yourself.

The PostgreSQL License is a short, permissive, OSI-approved open-source license, similar to the BSD and MIT licenses. You may use, copy, modify, and distribute PostgreSQL, including in closed-source commercial products, at no cost. The main obligation is to keep the copyright notice and disclaimer. There is no copyleft: you do not have to publish your modifications.

License	Example	Use in a closed-source product?	Must publish modifications?	OSI-approved open source?
PostgreSQL License	PostgreSQL	Yes	No	Yes
Apache 2.0	Many infrastructure projects	Yes (with notice and patent terms)	No	Yes
GPLv2	MySQL Community Edition	Only if you do not distribute it, or you buy a commercial license	Yes, when distributing derived works	Yes
Source-available (SSPL, BSL, and similar)	Several databases that changed license	Restricted, especially for offering it as a service	Varies	No

This table is a simplification, not legal advice.

Because of this license, many companies have built products on PostgreSQL, and the ecosystem is large:

                               PostgreSQL core (community)
                                           |
      +-------------------+----------------+-----------------+----------------------+
      |                   |                |                 |                      |
  Extensions          Operations       Drivers         Managed services      Derived / compatible
  PostGIS             pgAdmin          libpq (C)       Amazon RDS / Aurora   Greenplum
  pgvector            PgBouncer        JDBC (Java)     Google Cloud SQL      EDB Postgres Advanced Server
  TimescaleDB         Patroni          psycopg (Py)    and AlloyDB           YugabyteDB (PG query layer)
  Citus               pgBackRest       node-postgres   Azure Database for    Systems that speak the PG
  pg_partman          pgBadger         Npgsql (.NET)   PostgreSQL            wire protocol
  pg_cron             pg_repack        pgx (Go)        Neon, Supabase, ...

Community resources

Resource	Use it for
Official documentation (postgresql.org/docs)	The authoritative reference, versioned per major release
Mailing lists (pgsql-general, pgsql-hackers, ...)	Questions, development discussion, announcements
PostgreSQL wiki	Guides, tips, and how-tos written by the community
PGXN	A registry of third-party extensions
Planet PostgreSQL	An aggregator of community blogs
Conferences (PGConf.dev, PGConf.EU, regional events)	Talks, training, and meeting contributors

"PostgreSQL-compatible" needs a closer look. Some products speak PostgreSQL's wire protocol or accept its SQL dialect but use a different engine underneath. Compatibility can stop at the protocol, the SQL syntax, the supported extensions, the isolation semantics, or the system catalogs.

Example: checking what your environment offers

-- Which extensions does this server (or managed service) let you install?
SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;

-- Which ones are installed in the current database?
SELECT extname, extversion FROM pg_extension;

Key takeaways

• The PostgreSQL License is permissive: commercial and closed-source use is allowed, and there is no copyleft.
• That license is why so many extensions, managed services, and derived products exist.
• The ecosystem covers extensions, operations tooling, drivers in every major language, and managed cloud services.
• "PostgreSQL-compatible" can mean many things. Check protocol, dialect, extensions, and semantics before relying on it.

🧪 Practice

1. (Beginner) In two sentences, explain what the PostgreSQL License allows a startup to do when building a commercial SaaS product on PostgreSQL, and what it requires.
2. (Intermediate) Compare three managed PostgreSQL services on these points: supported major versions, available extensions, superuser access, and how minor upgrades are applied. Present the result as a table.
3. (Interview) "A vendor says their database is 'PostgreSQL-compatible'. What do you ask before migrating?" Hint: Separate wire-protocol compatibility from SQL-dialect compatibility, extension support, transaction and isolation behavior, and catalog or tooling compatibility (for example pg_dump).

<a id="13-installation-and-environment-setup"></a>

1.3 Installation and Environment Setup

This subchapter takes you from nothing to a running PostgreSQL server. It covers installing it on each platform and in containers, and explains what a data directory, cluster, and server process are and how to manage them.

Installing PostgreSQL by Platform

Where you install PostgreSQL from decides which major versions you can get, how quickly minor (security) releases reach you, and where files end up on disk. Every major platform has a recommended route.

Platform	Recommended source	Server package	Typical data directory	Service name
Debian / Ubuntu	PGDG apt repository (apt.postgresql.org)	postgresql-17	/var/lib/postgresql/17/main	postgresql@17-main
RHEL / Rocky / Alma / Fedora	PGDG yum/dnf repository	postgresql17-server	/var/lib/pgsql/17/data	postgresql-17
macOS	Homebrew or Postgres.app	postgresql@17	/opt/homebrew/var/postgresql@17 (Apple Silicon)	brew services
Windows	Interactive installer linked from postgresql.org, or WSL2 with the Ubuntu steps	Installer bundle	C:\Program Files\PostgreSQL\17\data	Windows service
Any	Docker image postgres	postgres:17	Volume inside the container	Container

The PGDG repositories are run by the PostgreSQL community. They provide every supported major version and publish minor releases on release day. Distribution repositories often carry only one, sometimes older, major version.

Debian / Ubuntu

# Add the community (PGDG) apt repository
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh   # configures apt.postgresql.org

# Install a specific major version (server + client); a cluster called "main" is created and started
sudo apt install -y postgresql-17

# Debian's wrapper tools can run several clusters and versions side by side
pg_lsclusters
# Ver Cluster Port Status Owner    Data directory              Log file
# 17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

sudo -u postgres psql          # connect as the postgres OS user (peer authentication)

RHEL family

# Install the PGDG repository package (choose the URL for your EL version on postgresql.org/download)
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql            # stop the distro module from hiding PGDG packages
sudo dnf install -y postgresql17-server

# Unlike Debian, RHEL packages do NOT create a cluster automatically
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17

macOS (Homebrew)

brew install postgresql@17
brew services start postgresql@17                 # run as a background service

# Versioned formulae are "keg-only": add their binaries to PATH yourself
echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

psql postgres                                     # Homebrew creates a superuser named after your macOS user

Windows. The installer linked from postgresql.org sets up the server as a Windows service, asks for a password for the postgres superuser, and can also install pgAdmin and command-line tools. Many developers use WSL2 with the Ubuntu instructions instead, so their environment matches Linux production servers.

Verifying the installation

psql --version                                    # client version
pg_isready                                        # e.g. "/var/run/postgresql:5432 - accepting connections"
sudo -u postgres psql -c "SELECT version();"      # server version

Common pitfalls:

• Client vs. server version mismatch. An old psql on your PATH can connect to a newer server, but some meta-commands may not work fully. Run which psql to see which one you are using.
• Two installations at once, for example Homebrew plus Postgres.app, or distro packages plus PGDG packages. They fight over port 5432 and use different socket directories.
• Installing from the distro repository and only later finding it carries an older major version than you need.

Key takeaways

• Use the PGDG repositories on Linux to get any supported major version and timely minor releases.
• Debian-based packages create and start a cluster automatically. RHEL-based packages need an explicit initdb step.
• On macOS, Homebrew's versioned formulae are keg-only, so you must add their bin directory to PATH.
• Verify the installation with pg_isready, psql --version, and SELECT version(), and watch for client/server mismatches.

🧪 Practice

1. (Beginner) Install PostgreSQL on your platform. Confirm the server is running with pg_isready, then connect and run SELECT version();.
2. (Intermediate) On Debian or Ubuntu, install two major versions side by side (for example postgresql-16 and postgresql-17). Use pg_lsclusters to find the port each one listens on, and connect to each with psql -p <port>.
3. (Interview) "Why would you install PostgreSQL from the PGDG repository instead of your Linux distribution's default packages?" Hint: Compare which major versions each source offers and how quickly security releases arrive.

Running PostgreSQL in Docker

Containers give you a disposable, reproducible PostgreSQL in seconds. You can pin an exact version, run several versions side by side, and use the same setup on every developer's laptop and in CI. This is usually the fastest way to get a learning environment.

Three Docker concepts matter here:

• Image: the packaged software, for example postgres:17. Always pin at least the major version. Never rely on latest for a database, because a new major version will not start on an old data directory.
• Container: a running instance of the image. It is disposable.
• Volume: persistent storage that outlives the container. The data directory must live on a volume, or removing the container deletes your data.

Quick start

docker run -d --name pg17 \
  -e POSTGRES_PASSWORD=devpassword \
  -p 5432:5432 \
  -v pgdata17:/var/lib/postgresql/data \
  postgres:17

psql "postgresql://postgres:devpassword@localhost:5432/postgres"   # from the host
docker exec -it pg17 psql -U postgres                             # or from inside the container

The flags mean the following: -e POSTGRES_PASSWORD sets the superuser password, which the image requires. -p 5432:5432 publishes container port 5432 on host port 5432. -v pgdata17:... mounts a named volume at the data directory.

A reproducible setup with Docker Compose

services:
  db:
    image: postgres:17                           # pin the major version
    container_name: pg17
    environment:
      POSTGRES_USER: app                         # superuser created on first start (default: postgres)
      POSTGRES_PASSWORD: devpassword             # required unless authentication is set to trust
      POSTGRES_DB: appdb                         # database created on first start (default: same as user)
    command: ["postgres", "-c", "log_min_duration_statement=200"]   # pass server settings with -c
    ports:
      - "5432:5432"                              # host:container
    volumes:
      - pgdata:/var/lib/postgresql/data          # data directory for images up to 17 (see note below)
      - ./initdb:/docker-entrypoint-initdb.d:ro  # *.sql, *.sql.gz, *.sh run ONCE, on an empty data dir
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 5s
      timeout: 3s
      retries: 10

volumes:
  pgdata:

docker compose up -d                 # start in the background
docker compose logs -f db            # follow the server log
docker compose down                  # stop and remove containers (named volumes are kept)
docker compose down -v               # also delete volumes: a full reset, init scripts run again

Note on PostgreSQL 18 and later images. The official image changed its default data directory layout to include the major version (under /var/lib/postgresql/<major>/). For those images, mount the volume at /var/lib/postgresql instead. Check the image documentation for the tag you use.

Initialization happens only once. The POSTGRES_* variables and the scripts in /docker-entrypoint-initdb.d are used only when the container starts with an empty data directory. On later starts the existing data is used as-is, so changing POSTGRES_PASSWORD in the compose file has no effect after the first start.

Pitfall	Symptom	Fix
No volume	Data disappears when the container is removed	Mount a named volume at the data directory
image: postgres:latest	Container fails to start after a pull: data directory was initialized by an older major version	Pin postgres:<major> and upgrade deliberately
Changing env vars later	New password or database not applied	Change it with SQL (ALTER ROLE ... PASSWORD), or reset with down -v
Port already in use	bind: address already in use	A local PostgreSQL already uses 5432. Map another host port ("5433:5432")
Connecting to localhost from another container	Connection refused	Use the service name (db) as the host inside the Compose network

Key takeaways

• Docker makes PostgreSQL disposable and reproducible. Pin the major version and always use a volume for data.
• POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB, and init scripts apply only on first initialization.
• Pass server settings with -c name=value in the container command.
• Inside a Compose network, other containers reach the database by service name, not localhost.

🧪 Practice

1. (Beginner) Start a postgres:17 container with a named volume, create a table and insert a row, remove the container (docker rm -f pg17), start a new container with the same volume, and confirm the row is still there.
2. (Intermediate) Write a Compose file with an initdb/01-schema.sql script that creates a schema and seed data. Start it, confirm the data exists, edit the script, restart, and observe that it does not run again. Then reset the environment so it does.
3. (Interview) "You changed POSTGRES_PASSWORD in docker-compose.yml, but the old password still works. Why?" Hint: When does the image's entrypoint run its initialization logic, and where is the password actually stored?

Initializing a Data Directory with initdb

PostgreSQL cannot start without a data directory: a folder holding the system catalogs, the databases, the write-ahead log, and the configuration files. initdb creates it. Packages and Docker images usually run initdb for you, but you need to understand it when you set up custom environments, and because some choices made at initdb time are hard or impossible to change later.

What initdb does:

1. Creates the directory structure and the system catalogs.
2. Creates three databases: template1 (copied by every CREATE DATABASE), template0 (a pristine, unmodifiable copy), and postgres (a default database to connect to).
3. Creates the bootstrap superuser role, named after the OS user that runs initdb unless you pass -U.
4. Sets default encoding and locale, and writes postgresql.conf, pg_hba.conf, and pg_ident.conf.

Example

# Run as the unprivileged OS user that will own the server process (never as root)
initdb \
  -D /srv/pgdata/17 \
  -U postgres \
  -W \
  --encoding=UTF8 \
  --locale=en_US.UTF-8 \
  --auth-local=peer \
  --auth-host=scram-sha-256 \
  --data-checksums

Option	Meaning
-D /srv/pgdata/17	Where to create the data directory (can also come from the PGDATA environment variable)
-U postgres	Name of the bootstrap superuser
-W	Prompt for the superuser's password
--encoding=UTF8	Default character encoding for new databases. Use UTF8 unless you have a specific reason not to
--locale=en_US.UTF-8	Default locale (sorting and character classification rules). --locale-provider=icu with --icu-locale selects ICU collations
--auth-local=peer	Local (Unix-socket) connections authenticate by matching the OS user name
--auth-host=scram-sha-256	TCP connections require a password using SCRAM
--data-checksums	Detect corruption in data pages. This is the default since PostgreSQL 18 (--no-data-checksums turns it off)

What gets created

/srv/pgdata/17/
├── PG_VERSION              major version that created this directory
├── postgresql.conf         main server configuration
├── postgresql.auto.conf    settings written by ALTER SYSTEM (do not edit by hand)
├── pg_hba.conf             client authentication rules (who may connect, from where, how)
├── pg_ident.conf           mappings from OS user names to database role names
├── base/                   one subdirectory per database, named by the database's OID
├── global/                 cluster-wide tables (pg_database, pg_authid, ...)
├── pg_wal/                 write-ahead log segment files
├── pg_xact/                commit status of transactions
├── pg_stat/                statistics kept across clean restarts
└── postmaster.pid          present only while the server runs (PID, port, socket dir)

Template databases in action

SELECT datname,
       pg_encoding_to_char(encoding) AS encoding,
       datcollate,
       datistemplate,                      -- may be used as a template by anyone with CREATEDB
       datallowconn                        -- template0 does not accept connections
FROM pg_database;

-- CREATE DATABASE copies template1 by default, including anything you added to it.
-- To use a different encoding or locale, copy the pristine template0 instead:
CREATE DATABASE legacy_app
  TEMPLATE template0
  ENCODING 'LATIN1'
  LOCALE 'C';

Choices that are hard to change later

Setting	Can it be changed later?
Default encoding and locale	Per database at CREATE DATABASE time (from template0). Changing an existing database means dump and restore
Data checksums	Only offline, with pg_checksums --enable/--disable while the server is stopped
WAL segment size (--wal-segsize)	Only with the pg_resetwal tool, offline
Data directory location	Yes, by stopping the server and moving it, but it must belong to the server's OS user

Key takeaways

• initdb creates the data directory: catalogs, template0, template1, postgres, and config files.
• Run it as the unprivileged OS user that will run the server, never as root.
• Choose encoding (UTF8), locale, authentication methods, and checksums carefully, because they are hard to change later.
• CREATE DATABASE copies template1. Use template0 when you need a different encoding or locale.

🧪 Practice

1. (Beginner) Run initdb -D ~/pgsandbox as your normal user, list the directory's contents, and identify which file controls client authentication and which records the major version.
2. (Intermediate) Start a server on the sandbox directory on port 5499 (pg_ctl -D ~/pgsandbox -o "-p 5499" -l ~/pgsandbox.log start). Try CREATE DATABASE legacy TEMPLATE template1 ENCODING 'LATIN1' LOCALE 'C';, read the error, and explain why template0 works where template1 fails.
3. (Interview) "Why enable data checksums, and what do they not protect you from?" Hint: Checksums detect corruption when a data page is read back from disk. Think about what is not a data page, about corruption that happens before the checksum is computed, and about the difference between detecting damage and repairing it.

Clusters, Databases, and Connections

In PostgreSQL, "cluster" does not mean a group of machines. A database cluster is one PostgreSQL server instance and the collection of databases it manages: one data directory, one port, one set of roles. Confusing the two meanings is a classic mistake, so keep this definition in mind.

Process architecture. The server starts a main process (historically called the postmaster). It listens for connections and starts a separate backend process for each client connection. All processes share a block of memory containing the page cache (shared_buffers), WAL buffers, and lock tables. Background processes take care of housekeeping.

                               clients
          psql             app server (pool of 10)         pgAdmin
            |                        |                        |
            +--------- TCP :5432 or Unix-domain socket -------+
                                     |
                             +---------------+
                             |  postmaster   |  accepts connections, starts one backend per client
                             +---------------+
          +-----------------+--------+---------+-----------------------------+
          |                 |                  |                             |
    backend (pid 2210)  backend (pid 2211)    ...             background processes:
    db = shop           db = analytics                        checkpointer, background writer,
    user = alice        user = report                         WAL writer, autovacuum launcher,
          \                 |                                 logical replication launcher, ...
           +----------------+----------- shared memory -------------+
                             | shared_buffers | WAL buffers | locks |
                             +--------------------------------------+
                                             |
                                 data directory (one per cluster)

Rules to remember

• A cluster holds many databases, all with one data directory, one port, and one set of roles.
• A connection is bound to exactly one database. \c otherdb in psql opens a new connection. To query across databases you need postgres_fdw or dblink.
• Roles and tablespaces belong to the whole cluster. Schemas, tables, functions, and extensions belong to one database.
• Every connection is an OS process using a few megabytes of memory or more. max_connections defaults to 100. Applications that need many connections put a pooler in front of the server (7.4).

Example: inspecting the cluster from SQL

SELECT current_database(),          -- the one database this connection is bound to
       current_user,
       inet_server_port(),          -- NULL when connected through a Unix socket
       pg_backend_pid();            -- the OS process serving this connection

SELECT datname FROM pg_database;    -- every database in the cluster

SELECT pid, datname, usename, application_name, client_addr, state, backend_type
FROM pg_stat_activity               -- one row per server process, including background ones
ORDER BY backend_type, pid;

SHOW max_connections;               -- 100 by default (changing it requires a restart)

SELECT pg_terminate_backend(2211);  -- end another session (needs appropriate privileges)

Example: the same picture from the operating system

ps -o pid,cmd -u postgres
#   PID CMD
#  1021 /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -c config_file=...
#  1023 postgres: 17/main: checkpointer
#  1024 postgres: 17/main: background writer
#  1026 postgres: 17/main: walwriter
#  1027 postgres: 17/main: autovacuum launcher
#  1028 postgres: 17/main: logical replication launcher
#  2210 postgres: 17/main: alice shop [local] idle        <- a client backend: user, database, source, state

Separate databases or separate schemas?

	Separate databases	Separate schemas in one database
Joins across them	Not possible directly	Yes
Isolation	Strong: separate catalogs and extensions	Logical only, enforced by privileges
Backups	Independent pg_dump per database	One dump, or select schemas with -n
Connections	A pool per database	One pool can serve all schemas
Typical use	Unrelated applications on one server	Modules of one application, tenants that share code

Key takeaways

• A PostgreSQL "cluster" is one server instance with one data directory, not a multi-node setup.
• One process per connection. Shared memory holds the buffer cache, WAL buffers, and locks.
• A connection works with one database. Roles are shared across the cluster, schemas are not.
• pg_stat_activity shows every session and background process.

🧪 Practice

1. (Beginner) Connect to the postgres database, create databases alpha and beta, list them with \l, switch between them with \c, and run SELECT pg_backend_pid(); after each switch. Explain why the PID changes.
2. (Intermediate) Open three psql sessions to different databases. From a fourth session, use pg_stat_activity to identify each one by application_name or pid, list the background processes, and end one session with pg_terminate_backend. What does the terminated session see on its next command?
3. (Interview) "Why does PostgreSQL usually need a connection pooler at a scale where some other databases do not?" Hint: Think about the cost of a process per connection: memory, startup time, and the per-transaction work that grows with the number of open connections.

Starting and Stopping a Server

Stopping the server the wrong way can turn a five-second restart into a long crash recovery, or interrupt users without warning. You also need to know which configuration changes take effect with a reload and which need a restart.

How the server is controlled

Environment	Tool
Packaged Linux install	systemctl (plus pg_ctlcluster on Debian/Ubuntu)
Manual or custom setup	pg_ctl
macOS Homebrew	brew services
Docker	docker start/stop, docker compose up/down

Shutdown modes

Mode	pg_ctl -m	Signal to postmaster	What happens	On next start
Smart	smart	SIGTERM	Refuses new connections and waits for existing sessions to disconnect	Normal start
Fast (default)	fast	SIGINT	Rolls back open transactions, disconnects clients, writes a checkpoint, exits	Normal start
Immediate	immediate	SIGQUIT	Exits at once without a checkpoint	Crash recovery (WAL replay)

Reload vs. restart. Every setting has a context that says when a change takes effect:

context in pg_settings	When a change takes effect	Examples
postmaster	Only after a full restart	shared_buffers, max_connections, listen_addresses, port
sighup	After a reload (no disconnects)	log_line_prefix, autovacuum_naptime
superuser	Reload, or SET by a superuser in a session	log_min_duration_statement
user	Reload, or SET by any user in a session	work_mem, search_path

Example: shell commands

# Manual control with pg_ctl (run as the OS user that owns the data directory)
pg_ctl -D /srv/pgdata/17 -l /srv/pgdata/17/server.log start   # -l: write the server log to a file
pg_ctl -D /srv/pgdata/17 status
pg_ctl -D /srv/pgdata/17 reload                                # re-read configuration files (SIGHUP)
pg_ctl -D /srv/pgdata/17 restart -m fast
pg_ctl -D /srv/pgdata/17 stop                                  # fast mode by default
pg_ctl -D /srv/pgdata/17 stop -m smart                         # wait for clients to disconnect

# Debian/Ubuntu packaged clusters
sudo systemctl restart postgresql@17-main                      # one specific cluster
sudo pg_ctlcluster 17 main reload
tail -f /var/log/postgresql/postgresql-17-main.log

# RHEL PGDG packages
sudo systemctl restart postgresql-17

Example: reloading and checking pending changes from SQL

SELECT pg_reload_conf();                     -- same as pg_ctl reload (superuser or granted role)

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'work_mem', 'log_min_duration_statement');
-- pending_restart = true means the file was changed but the setting needs a restart

SELECT pg_postmaster_start_time();           -- when the server was last started

Rules of thumb

• Use fast mode for routine restarts. Use immediate only in emergencies.
• Never kill -9 the postmaster, and never delete postmaster.pid while the server may be running. Doing so risks two servers writing to the same data directory.
• After editing postgresql.conf, reload first, then check pending_restart to see whether a restart is still needed.
• Read the server log when it does not start. It almost always states the reason, for example "could not bind IPv4 address" or "permission denied".

Key takeaways

• Smart waits for clients, fast (the default) rolls back and disconnects cleanly, immediate skips the checkpoint and triggers crash recovery.
• Each setting's context tells you whether it needs a restart, a reload, or can be set per session.
• Use pg_reload_conf() or pg_ctl reload to apply most changes without disconnecting anyone.
• Never force-kill the postmaster or remove postmaster.pid by hand.

🧪 Practice

1. (Beginner) Start and stop your server once in each of the three modes. After the immediate stop, start it again and find the log lines showing that recovery ran.
2. (Intermediate) Change work_mem in postgresql.conf, reload, and confirm with SHOW work_mem. Then change shared_buffers, reload, and query pending_restart. Restart and confirm the new value.
3. (Interview) "What happens if a single backend process (not the postmaster) is killed with SIGKILL?" Hint: The postmaster cannot know whether that process left shared memory in a consistent state. Think about what it must do to all other sessions to be safe.

Default Ports and Socket Files

"Connection refused" and "No such file or directory" are the two most common first errors with PostgreSQL. Both come down to where the server listens and where the client looks.

The server can accept connections in two ways:

1. TCP/IP on a port, 5432 by default. The listen_addresses setting controls which network interfaces it binds to. The default is 'localhost', so out of the box remote machines cannot connect until you change it and allow them in pg_hba.conf.
2. Unix-domain sockets on Linux and macOS: special files in the directories listed in unix_socket_directories. The socket file is named .s.PGSQL.<port>. Socket connections are local only, slightly faster than TCP, and allow peer authentication. The default directory is /tmp for source builds, while Debian and Red Hat packages use /var/run/postgresql (/run/postgresql).

Clients built on libpq (psql, pg_dump, and many drivers) use a Unix socket when no host is given. A host value starting with / is treated as a socket directory.

 Client command                Path                                 Server requirement
 ---------------------------   ----------------------------------   -------------------------------------
 psql                          Unix socket in the default dir       socket in unix_socket_directories
 psql -h /tmp                  Unix socket /tmp/.s.PGSQL.5432       '/tmp' in unix_socket_directories
 psql -h localhost             TCP 127.0.0.1:5432 (or ::1)          listen_addresses includes localhost
 psql -h 10.0.0.5              TCP 10.0.0.5:5432                    listen_addresses = '*' or '10.0.0.5'
                                                                    AND a matching "host" line in pg_hba.conf
                                                                    AND no firewall blocking 5432

Example: server configuration

# postgresql.conf
listen_addresses = 'localhost,10.0.0.5'          # comma-separated; '*' = all interfaces (restart required)
port = 5432                                      # also decides the socket file name (restart required)
unix_socket_directories = '/var/run/postgresql'  # comma-separated list of directories

Example: inspecting and connecting

ls -la /var/run/postgresql/
# srwxrwxrwx 1 postgres postgres  0 ... .s.PGSQL.5432        <- the socket (file type 's')
# -rw------- 1 postgres postgres 70 ... .s.PGSQL.5432.lock   <- lock file next to it

psql -h /var/run/postgresql -p 5432 -U postgres       # explicit socket directory
psql -h localhost -p 5432 -U postgres                 # TCP over loopback
ss -ltnp | grep 5432                                  # which addresses is the server listening on?

SHOW port;
SHOW listen_addresses;
SHOW unix_socket_directories;

SELECT inet_server_addr(), inet_server_port();    -- both NULL when connected through a Unix socket
-- In psql, \conninfo prints how the current session is connected

Diagnosing the two classic errors

Error message	Likely cause	Check
connection to server at "10.0.0.5", port 5432 failed: Connection refused	Server not running, not listening on that interface, wrong port, or firewall	pg_isready -h 10.0.0.5, SHOW listen_addresses, ss -ltnp
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory	Client looks in a different socket directory or for a different port than the server uses	Compare unix_socket_directories and port with the client's defaults; use -h <dir>
no pg_hba.conf entry for host ...	The network path works but authentication rules reject the client	pg_hba.conf (10.1)

Never expose port 5432 directly to the public internet. Use firewalls, private networks, strict pg_hba.conf rules, and TLS (chapter 10).

Key takeaways

• Default TCP port 5432. listen_addresses = 'localhost' by default, so remote access must be enabled explicitly.
• Unix sockets are files named .s.PGSQL.<port> in unix_socket_directories. Packages and source builds use different directories.
• A libpq host starting with / is a socket directory. No host means "use the default socket".
• Remote access needs listen_addresses, a pg_hba.conf rule, and an open network path, all three.

🧪 Practice

1. (Beginner) Find your server's port and socket directory. Connect once through the socket and once through TCP, and compare the output of \conninfo for each.
2. (Intermediate) Move your sandbox server to port 5433. Predict the new socket file name, verify it, and update a connection command and a .pg_service.conf entry (see 1.4) so they still work.
3. (Interview) "A developer gets No such file or directory for /tmp/.s.PGSQL.5432, but the server is definitely running. How do you diagnose it?" Hint: Compare where this client looks for the socket with where the server creates it, and consider whether two different PostgreSQL installations are on the machine.

<a id="14-client-tools-and-first-steps"></a>

1.4 Client Tools and First Steps

This subchapter covers the tools you will use every day to talk to PostgreSQL, from the psql terminal and connection strings to graphical clients, and ends with creating your first tables and loading realistic data.

The psql Interactive Terminal

psql ships with every PostgreSQL installation, works the same on every platform, can be scripted, and supports every server feature. Graphical tools come and go, but psql is always available, including over SSH on a production server at 3 a.m. Time spent learning it pays off for years.

psql handles two kinds of input:

• SQL statements are sent to the server. They can span several lines and are executed when psql sees a terminating semicolon ;.
• Meta-commands start with a backslash (\) and are handled by psql itself, for example to list tables or change output format. They end at the end of the line and need no semicolon.

Connecting

psql -h localhost -p 5432 -U alice -d shop          # explicit host, port, user, database
psql shop                                           # defaults: Unix socket, OS user name as role
psql "postgresql://alice@localhost:5432/shop"       # URI form (see Connection Strings)
psql -d shop -c "SELECT count(*) FROM products"     # run one command and exit
psql -d shop -v ON_ERROR_STOP=1 -f migrate.sql      # run a script, stop at the first error
psql -X -d shop                                     # -X: ignore ~/.psqlrc (clean, predictable sessions)

Reading the prompt

shop=> SELECT name, price
shop->   FROM products            -- "->" : the statement continues (no semicolon yet)
shop->  WHERE price > 10;
     name      | price
---------------+--------
 Keyboard      |  49.90
 Monitor       | 189.00
(2 rows)

shop=> \q

Prompt	Meaning
shop=>	Connected to shop as a normal role
shop=#	Connected as a superuser, so be careful
shop->	Continuation line: waiting for more of the statement
shop(>	Inside an open parenthesis
shop'>	Inside an unterminated string literal
shop=*>	Inside an open transaction block (BEGIN without COMMIT/ROLLBACK)

If you are stuck at a '> or (> prompt, press Ctrl+C to cancel the current input.

Productivity features

• Up and down arrows recall history. Ctrl+R searches it.
• Tab completes keywords, table names, and column names.
• \e opens the current query in $EDITOR. When you save and quit, psql runs it.
• \g re-runs the last query. \timing on prints how long each statement took.

Autocommit. By default, psql commits every statement immediately unless you start a transaction with BEGIN. Two variables change this behavior:

• \set AUTOCOMMIT off makes psql start a transaction implicitly, so changes wait for COMMIT.
• \set ON_ERROR_ROLLBACK interactive places a savepoint before each statement in interactive sessions. A typo then does not abort your whole transaction.

A starter ~/.psqlrc

-- ~/.psqlrc runs at the start of every interactive psql session (skip it with -X).
-- Comments go on their own lines: text after a meta-command is parsed as arguments.
\set QUIET 1
-- show NULL explicitly instead of as an empty cell
\pset null '(null)'
-- switch to vertical output automatically when rows are too wide
\x auto
-- print execution time after every statement
\timing on
-- a typo inside BEGIN ... COMMIT does not abort the whole transaction (interactive only)
\set ON_ERROR_ROLLBACK interactive
\set HISTSIZE 10000
-- one history file per database
\set HISTFILE ~/.psql_history-:DBNAME
\unset QUIET

Scripts and exit codes

Exit code	Meaning
0	Finished normally. Without ON_ERROR_STOP, this is returned even if some SQL statements failed
1	Fatal error in psql itself (for example, out of memory or file not found)
2	Connection to the server was lost or could not be established
3	A script error occurred and ON_ERROR_STOP was set

Key takeaways

• SQL statements end with ; and go to the server. Backslash meta-commands are handled by psql.
• The prompt shows your database, superuser status (#), and incomplete input (-, (, ', *).
• Use ~/.psqlrc for interactive comfort, and -X for scripts so they behave the same everywhere.
• Always run scripts with -v ON_ERROR_STOP=1. Without it, failed statements do not cause a non-zero exit code.

🧪 Practice

1. (Beginner) Connect with psql, run SELECT now(), current_user, current_database();, recall it with the up arrow, change it to add version(), run it again, and quit with \q.
2. (Intermediate) Write bad.sql that creates a table, inserts a valid row, inserts a row with a syntax error, and inserts another valid row. Run it with and without -v ON_ERROR_STOP=1, check echo $? each time, and count how many rows were inserted in each case.
3. (Interview) "In CI, the psql migration step reports success even though one statement failed. Why, and how do you fix it?" Hint: Look at psql's default behavior when a statement in a script fails, and at its exit codes.

Meta-Commands and Output Formatting

Meta-commands are psql's built-in tools for exploring a database without remembering catalog queries, and for controlling how results look. They matter most when you work with an unfamiliar database.

Essential meta-commands

Command	Purpose
\?	Help for all meta-commands
\h [command]	SQL syntax help, e.g. \h CREATE INDEX
\conninfo	Show how you are connected
\l[+]	List databases (+ adds size and description)
\c dbname [user]	Connect to another database (opens a new connection)
\dn[+]	List schemas
\dt[+] [pattern]	List tables, e.g. \dt sales.*
\d name	Describe a table, view, index, or sequence
\d+ name	Describe with storage details, comments, and the view definition
\di, \dv, \ds, \df, \du, \dx	Indexes, views, sequences, functions, roles, extensions
\x [on|off|auto]	Expanded (vertical) output
\timing [on|off]	Show statement execution time
\i file.sql	Execute commands from a file
\o file.txt	Send query output to a file (\o alone stops)
\copy ...	Import or export data using a file on the client machine
\watch [seconds]	Re-run the current query periodically
\gx	Run the query once with expanded output
\gset	Store the result's columns in psql variables
\gexec	Execute every cell of the result as a SQL statement
\errverbose	Show the most recent error with full detail
\! command	Run a shell command

Patterns use shell-style wildcards: \dt public.order*, \df *date*.

See what psql runs behind the scenes. \set ECHO_HIDDEN on makes psql print the catalog queries behind each \d command. This is a good way to learn the system catalogs.

Output formats. \pset format takes aligned (default), unaligned, wrapped, csv, html, asciidoc, latex, or troff-ms. Other useful options include \pset border 2, \pset linestyle unicode, and \pset null '(null)'.

Example: interactive formatting and variables

\x auto
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();
-- wide rows are printed vertically:
-- -[ RECORD 1 ]----+-----------------------------
-- datid            | 16384
-- datname          | shop
-- ...

\pset format csv
SELECT id, name, price FROM products ORDER BY id LIMIT 2;
-- id,name,price
-- 1,Keyboard,49.90
-- 2,Monitor,189.00
\pset format aligned

-- \gset replaces the semicolon: the column "n" becomes the variable :n
SELECT count(*) AS n FROM products \gset
\echo There are :n products

-- \gexec: generate statements with a query, then run each one
SELECT format('ANALYZE %I.%I', schemaname, tablename)   -- %I quotes identifiers safely
FROM pg_tables
WHERE schemaname = 'public' \gexec

-- \watch: re-run every 2 seconds until Ctrl+C (handy for monitoring)
SELECT now(), count(*) AS sessions FROM pg_stat_activity \watch 2

Example: clean output for shell scripts

# -X ignore .psqlrc, -A unaligned, -t tuples only (no headers or footers)
count=$(psql -X -A -t -d shop -c "SELECT count(*) FROM products")
echo "products: $count"

# --csv: CSV output with a header row
psql -X --csv -d shop -c "SELECT id, name, price FROM products" > products.csv

# \copy runs on the client, so the file is written to YOUR machine, even if the server is remote
psql -X -d shop -c "\copy (SELECT * FROM products WHERE price > 100) TO 'expensive.csv' WITH (FORMAT csv, HEADER)"

Key takeaways

• \d and its variants (\dt, \di, \df, \du, \dx, \dn) are the fastest way to explore a database. Add + for more detail.
• \x auto, \pset, and \timing control how output is shown. -A -t and --csv produce clean output for scripts.
• \gset, \gexec, and \watch make psql a small scripting and monitoring tool.
• \set ECHO_HIDDEN on shows the catalog queries behind each meta-command.

🧪 Practice

1. (Beginner) List all tables in the public schema, describe one with \d+, list installed extensions with \dx, and show your roles with \du.
2. (Intermediate) Turn on ECHO_HIDDEN, run \dt, and copy the catalog query psql prints. Change it so it lists only tables whose estimated row count (pg_class.reltuples) is above 1,000, sorted by size using pg_total_relation_size.
3. (Interview) "How do you export a query result to a CSV file on your laptop when the database server is remote and you have no shell access to it?" Hint: Compare where COPY ... TO 'file' and \copy ... TO 'file' read and write files, and which privileges each one needs.

Connection Strings and Service Files

Every tool and application has to know where the server is, which database and user to use, and how to authenticate and encrypt. PostgreSQL's client library, libpq, defines a shared set of conventions for this: connection strings, environment variables, a password file, and a service file. Learn them once and they work in psql, pg_dump, and every libpq-based driver.

Two connection string formats

Keyword/value:
  host=db.internal port=5432 dbname=shop user=app sslmode=verify-full application_name=checkout

URI:
  postgresql://app:s3cr%40t@db.internal:5432/shop?sslmode=verify-full&application_name=checkout

URI parts:

• postgresql:// (or postgres://): the scheme
• app:s3cr%40t@: user and, optionally, password. Special characters must be percent-encoded (@ becomes %40, / becomes %2F, : becomes %3A)
• db.internal:5432: host and port
• /shop: database name
• ?sslmode=...&application_name=...: any other parameters

Common parameters

Parameter	Purpose
host, port	Server address or socket directory, and port
dbname, user	Database and role
password	Password (prefer a password file over putting it in the string)
sslmode	disable, allow, prefer (default), require, verify-ca, verify-full
sslrootcert	CA certificate used to verify the server
connect_timeout	Seconds to wait for a connection
application_name	Shows in pg_stat_activity and logs, which makes sessions easy to identify
options	Server settings for the session, e.g. options='-c search_path=app -c statement_timeout=5s'
target_session_attrs	With several hosts, which kind of server to accept: read-write, read-only, primary, standby, ...
service	Name of an entry in a service file

Listing several hosts gives simple client-side failover:

postgresql://app@db1:5432,db2:5432/shop?target_session_attrs=read-write

The client tries the hosts in order and keeps the first one that accepts writes, which is the primary.

Environment variables. PGHOST, PGPORT, PGDATABASE, PGUSER, PGSSLMODE, PGAPPNAME, PGSERVICE, and others supply defaults. PGPASSWORD exists but is discouraged, because environment variables can be visible to other processes.

The password file ~/.pgpass (on Windows, %APPDATA%\postgresql\pgpass.conf):

# hostname:port:database:username:password    (* matches anything; the first matching line wins)
db.internal:5432:shop:app:s3cr@t
localhost:*:*:postgres:devpassword

chmod 600 ~/.pgpass     # required on Unix: libpq ignores the file if others can read it

The service file ~/.pg_service.conf gives a connection a name:

# Each section is a named connection; any libpq parameter can appear in it
[shop-prod]
host=db.internal
port=5432
dbname=shop
user=app
sslmode=verify-full
application_name=ops-console

[shop-local]
host=localhost
dbname=shop
user=postgres

psql service=shop-local                 # or: PGSERVICE=shop-local psql
pg_dump "service=shop-prod" -Fc -f shop.dump

A system-wide service file can also live in the directory reported by pg_config --sysconfdir, or in the one set by PGSYSCONFDIR. PGSERVICEFILE points to a custom per-user file.

Precedence. When the same parameter comes from several places, libpq uses: values given explicitly in the connection string or command-line options, then the service file entry, then environment variables, then built-in defaults.

Example: the same conventions in application code

import psycopg  # psycopg 3 uses libpq, so service files, .pgpass and PG* variables all apply

with psycopg.connect("service=shop-local application_name=report-job") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database(), current_user, current_setting('application_name')")
        print(cur.fetchone())   # ('shop', 'postgres', 'report-job')

Some drivers do not use libpq. The JDBC driver for Java (jdbc:postgresql://host:5432/shop?sslmode=verify-full) and several pure-JavaScript, Go, and .NET drivers are examples. Check their documentation for which of these conventions they support.

Key takeaways

• libpq accepts keyword/value strings and URIs. Percent-encode special characters in URI passwords.
• Keep passwords in ~/.pgpass (mode 600), not in command lines, scripts, or PGPASSWORD.
• Service files give connections short names and work in every libpq-based tool and driver.
• Set application_name so every session can be identified in pg_stat_activity and the logs.

🧪 Practice

1. (Beginner) Convert host=db.example.com port=6543 dbname=sales user=report password=p@ss/word sslmode=require to URI form, encoding the password correctly. Then convert it back.
2. (Intermediate) Create ~/.pgpass and ~/.pg_service.conf with two services (local and Docker). Connect to each with psql service=... without typing a password, and confirm the target and application_name with \conninfo and SHOW application_name;.
3. (Interview) "Why is it discouraged to pass the database password in PGPASSWORD or inside a URI on the command line?" Hint: Think about who else on the machine can read process arguments and environment variables, and about shell history and CI logs.

pgAdmin and GUI Clients

Graphical clients make some tasks easier: browsing an unfamiliar schema, reading a complex execution plan as a diagram, drawing an entity-relationship diagram, or editing a few rows. They do not replace psql, but they are useful alongside it, especially while learning.

pgAdmin 4 is the open-source administration tool from the PostgreSQL community. It is a web application written in Python that runs either in desktop mode (single user) or server mode (shared, behind a web server). Main features:

• An object browser tree that mirrors the cluster > database > schema > object hierarchy
• A query tool with syntax highlighting, history, and a graphical EXPLAIN view
• An ERD tool to design or reverse-engineer schemas
• Dialogs for backup and restore that wrap pg_dump and pg_restore
• Server activity dashboards and a schema diff tool

Servers
└── local-dev (PostgreSQL 17)
    ├── Databases
    │   └── shop
    │       ├── Schemas
    │       │   └── public
    │       │       ├── Tables
    │       │       ├── Views
    │       │       ├── Functions
    │       │       └── Sequences
    │       └── Extensions
    ├── Login/Group Roles          <- cluster-wide objects
    └── Tablespaces                <- cluster-wide objects

Other clients

Client	Type	License	Notes
pgAdmin 4	Desktop or web	Open source	PostgreSQL-specific, full administration features
DBeaver	Desktop	Open source (Community) plus commercial editions	Works with many databases, strong data editor and ER diagrams
DataGrip	Desktop	Commercial (JetBrains)	Works with many databases, advanced SQL completion and refactoring
Beekeeper Studio	Desktop	Open source plus commercial edition	Simple, modern interface
TablePlus	Desktop	Commercial	Lightweight, works with many databases
Postico	Desktop (macOS)	Commercial	PostgreSQL-focused, simple
pgcli	Terminal	Open source	A psql alternative with smarter autocompletion and syntax highlighting

Example: pgAdmin next to PostgreSQL in Docker Compose

services:
  db:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: devpassword
    volumes:
      - pgdata:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@example.com     # login for the pgAdmin web UI
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "8080:80"                                  # open http://localhost:8080
    depends_on:
      - db

volumes:
  pgdata:

When you register the server in pgAdmin, use host db (the Compose service name), port 5432, user postgres. localhost would point to the pgAdmin container itself.

Pitfalls

• Sessions left "idle in transaction". Some GUI clients run in manual-commit mode or open a transaction implicitly. A forgotten open transaction holds its locks and can keep VACUUM from cleaning up dead rows. Know your client's auto-commit setting.
• Editing data in a grid in production. It is easy to change the wrong row. Prefer reviewed SQL scripts.
• Opaque SQL. Note what SQL the GUI generates (most clients can show it), so you learn the underlying commands.

Key takeaways

• pgAdmin 4 is the community's PostgreSQL-specific GUI, and its tree mirrors the cluster, database, and schema hierarchy.
• GUI clients help with browsing, ER diagrams, and plan visualization. psql stays essential for scripting and servers.
• In Docker Compose, connect to the database by service name, not localhost.
• Watch for GUI sessions left "idle in transaction", which hold locks and can block cleanup.

🧪 Practice

1. (Beginner) Register your local server in pgAdmin or DBeaver, browse to a table, and view its generated DDL. Compare it with the output of \d+ for the same table in psql.
2. (Intermediate) Run pgAdmin and PostgreSQL together with the Compose file above. Register the server, first trying localhost and then db, and explain why only one of them works.
3. (Interview) "A DBA finds a GUI session that has been 'idle in transaction' for six hours. Why is that a problem even though it is doing nothing?" Hint: Think about the locks the transaction may hold and whether its transaction ID or snapshot keeps VACUUM from removing dead rows.

Creating a First Database and Table

This topic walks through the full first-steps workflow: create a role and a database, create a table with sensible types and constraints, then insert, query, change, and inspect data. Each step introduces an idea that later chapters explain in depth.

Step 1: a role and a database (as a superuser)

-- Connect as a superuser first, e.g.: sudo -u postgres psql
CREATE ROLE shop_owner LOGIN PASSWORD 'change-me';   -- a role with LOGIN is what people call a "user"
CREATE DATABASE shop OWNER shop_owner;               -- the owner controls the database
                                                     -- (and, since PG 15, its public schema)
\c shop shop_owner

Applications should not connect as a superuser. A common pattern is an owner role that runs migrations and a separate, less privileged role the application uses at runtime (10.2).

Step 2: a table

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- auto-generated surrogate key
  sku         text          NOT NULL UNIQUE,                    -- business identifier, no duplicates
  name        text          NOT NULL,
  price       numeric(10,2) NOT NULL CHECK (price >= 0),        -- exact decimal: never float for money
  in_stock    integer       NOT NULL DEFAULT 0,
  created_at  timestamptz   NOT NULL DEFAULT now()              -- time-zone-aware timestamp
);

Choice	Why
bigint GENERATED ALWAYS AS IDENTITY	SQL-standard auto-numbering. ALWAYS rejects manual values by accident. Preferred over the older serial
text	No arbitrary length limit and no performance penalty compared with varchar(n) in PostgreSQL
numeric(10,2)	Exact arithmetic for money, where float would introduce rounding errors
timestamptz	Stores an absolute point in time, the right default for event times (3.2)
NOT NULL, UNIQUE, CHECK	The database rejects invalid data, whichever application sends it

Step 3: insert, query, update, delete

INSERT INTO products (sku, name, price, in_stock)
VALUES ('KB-001', 'Mechanical Keyboard',  89.90,  25),
       ('MS-002', 'Wireless Mouse',       24.50, 140),
       ('MN-003', '27" Monitor',         229.00,   8)
RETURNING id, sku;                       -- returns the generated ids without a second query

SELECT sku, name, price
FROM products
WHERE price < 100
ORDER BY price DESC;

UPDATE products
SET in_stock = in_stock - 1
WHERE sku = 'MN-003'
RETURNING sku, in_stock;                 -- shows the new value

DELETE FROM products WHERE sku = 'MS-002';

Step 4: watch the constraints work

INSERT INTO products (sku, name, price) VALUES ('KB-001', 'Duplicate', 10);
-- ERROR:  duplicate key value violates unique constraint "products_sku_key"
-- DETAIL:  Key (sku)=(KB-001) already exists.

INSERT INTO products (sku, name, price) VALUES ('XX-999', 'Broken', -5);
-- ERROR:  new row for relation "products" violates check constraint "products_price_check"

INSERT INTO products (id, sku, name, price) VALUES (999, 'ID-999', 'Manual id', 1);
-- ERROR:  cannot insert a non-DEFAULT value into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

Step 5: inspect what you built

shop=> \d products
                                     Table "public.products"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 sku        | text                     |           | not null |
 name       | text                     |           | not null |
 price      | numeric(10,2)            |           | not null |
 in_stock   | integer                  |           | not null | 0
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "products_sku_key" UNIQUE CONSTRAINT, btree (sku)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)

PRIMARY KEY and UNIQUE automatically created B-tree indexes, which is how PostgreSQL enforces uniqueness efficiently.

Key takeaways

• Create a dedicated role and database for each application. Do not run applications as a superuser.
• Good defaults: bigint identity keys, text, numeric for money, timestamptz for event times.
• Declare constraints (NOT NULL, UNIQUE, CHECK) so the database enforces correctness.
• RETURNING gives you generated or changed values without a second round trip.

🧪 Practice

1. (Beginner) Create a customers table with an identity id, a unique, required email, a required full_name, and created_at defaulting to now(). Insert three customers and query them ordered by created_at.
2. (Intermediate) Add an orders table with customer_id bigint NOT NULL REFERENCES customers(id), total numeric(12,2), and placed_at timestamptz. Try to insert an order for a customer that does not exist, then try to delete a customer who has orders. Explain both errors.
3. (Interview) "Why is GENERATED ALWAYS AS IDENTITY preferred over serial for new tables?" Hint: Compare standards compliance, how the underlying sequence's ownership and permissions are handled, and what happens when someone inserts an explicit id value.

Loading Sample Datasets

Learning SQL on a three-row table hides almost everything interesting. Joins, indexes, and query plans only behave realistically with realistic data volumes and relationships. Before moving on to the next chapters, load at least one sample dataset and one large synthetic table.

Popular sample databases

Dataset	Domain	Size	Format	Good for
Pagila	DVD rental store (a port of MySQL's Sakila)	Small	Plain SQL scripts	Joins, views, functions, general SQL practice
dvdrental	DVD rental (a variant used by many tutorials)	Small	pg_restore archive (.tar)	Following tutorials
Chinook	Digital music store	Small	SQL scripts for several databases	Cross-database comparisons
Northwind	Trading company orders	Small	SQL scripts	Classic reporting queries
Airlines demo (Postgres Professional)	Flight bookings	Small, medium, and large variants	SQL dump	Realistic volumes, time-based queries
Public data dumps (e.g. Stack Exchange, IMDb)	Real-world content	Large	XML, TSV, or CSV that needs converting	Performance and indexing practice

Loading methods by file type

You have	Use
Plain .sql script	psql -f file.sql
Custom-format (-Fc), tar, or directory dump	pg_restore
CSV / TSV files	\copy (file on the client) or COPY (file on the server)
Nothing: you want volume	generate_series() and random()

Example: a script-based dataset and an archive-based dataset

# Pagila: plain SQL scripts, run with psql
git clone https://github.com/devrimgunduz/pagila.git
createdb pagila
psql -X -d pagila -v ON_ERROR_STOP=1 -f pagila/pagila-schema.sql   # tables, views, functions
psql -X -d pagila -v ON_ERROR_STOP=1 -f pagila/pagila-data.sql     # rows
# Note: the scripts assign ownership to a role named "postgres"; create it first if it does not exist

# An archive dump: psql cannot read it, pg_restore can
createdb dvdrental
pg_restore --no-owner --dbname=dvdrental dvdrental.tar   # --no-owner: objects belong to the connecting role

Example: CSV files

CREATE TABLE cities (
  name       text    NOT NULL,
  country    text    NOT NULL,
  population integer
);

-- \copy reads the file on the CLIENT and streams it over the connection (no special privileges)
\copy cities FROM 'cities.csv' WITH (FORMAT csv, HEADER true)

-- COPY (no backslash) reads a file on the SERVER's file system: superuser or pg_read_server_files
COPY cities FROM '/srv/import/cities.csv' WITH (FORMAT csv, HEADER true);

Example: generating a million rows of synthetic data

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    integer     NOT NULL,
  kind       text        NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO events (user_id, kind, created_at)
SELECT 1 + floor(random() * 10000)::int,                                -- user ids 1..10000
       (ARRAY['view', 'click', 'purchase'])[1 + floor(random() * 3)::int], -- one of three kinds
       now() - random() * interval '365 days'                           -- spread over the last year
FROM generate_series(1, 1000000);                                        -- one row per generated number

ANALYZE events;                                    -- refresh planner statistics after a bulk load

SELECT kind, count(*) FROM events GROUP BY kind;   -- roughly 333,000 rows per kind

Example: checking what you loaded

SELECT relname AS table_name, n_live_tup AS approx_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;
SELECT pg_size_pretty(pg_total_relation_size('events'))     AS events_size;   -- table + indexes + TOAST

Tips for loading large datasets

• Prefer COPY/\copy to many single-row INSERTs. It is often an order of magnitude faster.
• Load into tables without secondary indexes, then create the indexes afterwards.
• Run ANALYZE after a bulk load so the planner has accurate statistics.
• Load into a scratch database you can drop and recreate freely.

Key takeaways

• Realistic data is essential to learn joins, indexing, and query plans.
• Plain SQL dumps load with psql -f. Archive dumps load with pg_restore.
• \copy uses files on the client. COPY uses files on the server and needs extra privileges.
• generate_series() with random() creates millions of rows in seconds. Run ANALYZE afterwards.

🧪 Practice

1. (Beginner) Load Pagila, list its tables with \dt, and count the rows in film, customer, and rental.
2. (Intermediate) Create the one-million-row events table. With \timing on, run SELECT count(*) FROM events WHERE user_id = 42; and look at its EXPLAIN plan. Then create an index on user_id, run it again, and compare the timing and the plan.
3. (Interview) "What would you do to load a 50 GB CSV file into a new PostgreSQL table as fast as possible?" Hint: Think about COPY versus INSERT, when to create indexes and constraints, transaction size, and what to run once the load finishes.

<a id="2-relational-design-and-data-modeling"></a>