Lesson 01 beginner

Introduction to PostgreSQL

What a relational database is, why PostgreSQL became the default choice for new projects, how its release cycle works, how a Postgres server is organised into clusters, databases, schemas and tables, and the Orbit database you will build in this course.

Almost every application you use keeps its important data in a database: accounts, orders, messages, settings. PostgreSQL (Postgres for short) is the database more and more teams pick for that job. This lesson explains what it is, why it is so popular and how a Postgres server is organised, so the commands in the next lessons make sense.

What a relational database is

A relational database stores data in tables. A table has named, typed columns and any number of rows. Tables are linked by values they share: a note row stores the id of the user who wrote it.

idemailname
1ana@orbit.devAna
2ben@orbit.devBen

You talk to the database in SQL (Structured Query Language). SQL is declarative: you describe the result you want and the database works out how to get it.

SELECT name FROM users WHERE email = 'ana@orbit.dev';

You did not say “open the file, read every line, compare the email”. The database’s planner picks the fastest way, for example using an index, and that choice can change as your data grows without any change to your query.

A database also gives you guarantees that files on disk never could:

  • Transactions: a group of changes happens completely or not at all, even if the server crashes halfway.
  • Constraints: rules such as “every note belongs to an existing user” are enforced for every program that writes data.
  • Concurrency: hundreds of clients can read and write at the same time without corrupting anything.

What makes PostgreSQL special

PostgreSQL started as the POSTGRES research project at the University of California, Berkeley, in 1986, and has been developed in the open by a global community since 1996. Nobody owns it: the PostgreSQL License lets you use, change and ship it for free, in any product.

Its reputation rests on a few things:

  • Correctness first. Full ACID transactions, strict data validation and a long record of not losing data.
  • Standard SQL and much more. Window functions, common table expressions, MERGE, JSON path queries, ranges, arrays and full-text search are all built in.
  • Extensibility. You can add data types, index methods, languages and whole features as extensions: PostGIS for maps, pgvector for AI embeddings, TimescaleDB for time series.
  • Everywhere. Every major cloud offers managed Postgres (Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Azure Database for PostgreSQL), and platforms such as Supabase and Neon are built on it.
PostgreSQLMySQL / MariaDBSQLite
Runs asA serverA serverA library inside your app
Best atGeneral purpose, complex queries, data integritySimple, read-heavy web workloadsLocal and embedded storage
JSONjsonb with indexes and path queriesJSON typeJSON functions
ExtensionsHundredsPlugins, fewerLoadable modules
LicensePostgreSQL (permissive)GPL (MySQL), GPL (MariaDB)Public domain

Versions and release cycle

PostgreSQL ships one major version a year, usually in September. PostgreSQL 18 came out on 25 September 2025. Each major version is supported for five years, and minor releases with bug and security fixes come out every three months (February, May, August and November).

18.0  → first release of major version 18
18.6  → the same features, six rounds of fixes later

Tip: Always run the latest minor release of your major version. Minor upgrades only replace the program files, need just a restart and never change how your data is stored on disk.

Moving between major versions (for example 17 to 18) changes the on-disk format, so it needs pg_upgrade or a dump and restore. Lesson 18 shows both.

How a Postgres server is organised

Before you create anything, learn the vocabulary. A running Postgres installation is layered like this:

cluster (one server, one data directory, one port)
├── database: postgres      ← default maintenance database
├── database: orbit         ← you create this in lesson 4
│   ├── schema: public
│   │   ├── table: users
│   │   ├── table: notes
│   │   └── index: notes_user_id_idx
│   └── schema: reporting
└── roles: postgres, orbit, orbit_app  ← shared by every database
TermWhat it is
ClusterOne Postgres server process with its data directory. Not a group of machines: that is a replication setup.
DatabaseAn isolated set of schemas. A connection is always to exactly one database.
SchemaA namespace inside a database, like a folder for tables, views and functions.
RoleA user or a group. Roles exist at cluster level and are granted rights per database or object.
Table, view, index, sequence, functionThe objects that live inside a schema.

What happens when you connect

Postgres uses one process per connection. When a client connects, the main process (called the postmaster) starts a new backend process just for it. Background processes do the housekeeping:

  • WAL writer and checkpointer: every change is first written to the write-ahead log (WAL) and only later to the table files. After a crash, Postgres replays the WAL so no committed transaction is lost.
  • Autovacuum: cleans up old row versions left behind by updates and deletes (lesson 17).
  • Background writer, WAL sender, logical replication workers and others, depending on your setup.

You can see them on any Linux server:

$ ps -o pid,cmd -u postgres

Because each connection is a full process, a Postgres server is happiest with tens or a few hundred connections, not thousands. Busy applications put a connection pooler such as PgBouncer in front of it (lesson 17).

The Orbit database

Through this course you build the database for Orbit, a small notes app. By the end it has:

  • users and notes tables with keys, constraints and indexes (lessons 4 to 11).
  • Tags in a many-to-many relationship (lesson 8).
  • JSON settings and full-text search over notes (lesson 13).
  • Triggers that keep timestamps up to date (lesson 14).
  • A locked-down application role with row-level security (lesson 15).
  • Backups, monitoring and a replica (lessons 16 to 18).

Quick reference

ConceptRemember
SQLDeclarative: describe the result, the planner finds the path
Major versionOnce a year, five years of support, needs pg_upgrade
Minor versionEvery three months, fixes only, just restart
ClusterOne server instance and its data directory
Database → schema → tableHow objects are nested
RoleUser or group, shared across the cluster
WALWrite-ahead log that makes crashes safe

Exercise

Your server runs PostgreSQL 18.2 and 18.6 is available. What does the upgrade involve?

  1. A dump and restore of every database
  2. Running pg_upgrade with –link
  3. Installing the new packages and restarting the server
  4. Nothing, minor versions install themselves
Show answer

Answer: C. Minor releases keep the on-disk format, so you only replace the binaries and restart. pg_upgrade or dump and restore are only needed between major versions such as 17 and 18.

Exercise

In PostgreSQL terms, what is a “cluster”?

  1. One running server instance with its own data directory and port
  2. A group of servers that replicate to each other
  3. A set of tables that are stored together on disk
  4. A Kubernetes deployment of Postgres
Show answer

Answer: A. The word is historical: a database cluster is the collection of databases managed by one server. Several servers that copy data between them are called a replication setup.

Exercise

Why do busy applications often put a pooler like PgBouncer in front of Postgres?

  1. Postgres cannot accept more than 10 connections
  2. Poolers make SQL queries run faster on the server
  3. Postgres needs a pooler to support transactions
  4. Each connection is a separate server process, so thousands of them waste memory and CPU
Show answer

Answer: D. Postgres starts one backend process per connection. A pooler keeps a small number of server connections open and shares them among many clients.

Key takeaways

  • A relational database stores typed rows in tables and guarantees transactions, constraints and safe concurrency.
  • PostgreSQL is free, community-developed, strict about correctness and extensible with types, indexes and extensions.
  • One major version per year with five years of support; always apply the latest minor release.
  • A cluster holds databases, a database holds schemas, a schema holds tables and other objects; roles are cluster-wide.
  • Every change goes to the write-ahead log first, which is what makes crash recovery and replication possible.

FAQ

Is it “Postgres” or “PostgreSQL”?

Both are official. The project’s name is PostgreSQL, and “Postgres” is the accepted short form used by the community, the documentation and most tools.

Is PostgreSQL really free for commercial use?

Yes. The PostgreSQL License is a permissive open-source license similar to MIT or BSD. You can use it in closed-source products and pay nothing. Companies that sell support or managed hosting are optional.

Should I learn SQL or an ORM first?

Learn SQL first. ORMs generate SQL for you, but when a page is slow or data looks wrong, you will read the SQL they produced, the query plans and the error messages. Everything in this course applies whatever language your application uses.

Can Postgres replace MongoDB or Elasticsearch?

For many applications, yes. jsonb with GIN indexes covers most document-store use cases, and built-in full-text search is good enough for searching your own data. Dedicated tools still win at very large scale or for advanced relevance tuning. Lesson 13 shows both features.

Which version should I install for a new project?

The latest major version, PostgreSQL 18 today, with its newest minor release. It has the longest support window left and every performance improvement.

↑ ↓ navigate Enter open Esc close