What Is PostgreSQL

Introduction

PostgreSQL (often called Postgres) is a powerful open-source relational database known for strict SQL standards, rich data types, and reliable transactions. It stores data in tables with fixed columns, connects related tables through keys, and answers questions with SQL. This chapter explains how databases, schemas, and tables fit together, how PostgreSQL compares to MySQL and MongoDB, and where this Hello Code track fits in your learning path.

Prerequisites

• Basic comfort with files and a terminal
• No database installed yet for this overview
• Helpful: skim What Is MySQL if you already know another SQL database

Relational Data in One Picture

PostgreSQL adds a schema layer inside each database—think of it as a namespace for tables.

Cluster (server instance)
└── Database: shop
    └── Schema: public (default)
        ├── Table: users       (rows = one user each)
        ├── Table: products
        └── Table: orders      (links users to products)

Code explanation:

• Relational means tables link through keys—you normalize data instead of duplicating user names on every order row
• SQL (Structured Query Language) is how you define tables and read/write data
• search_path (covered in chapter 3) controls which schema PostgreSQL searches

What PostgreSQL Does

The postgres server process listens on port 5432 by default, executes SQL, enforces constraints, and persists data to disk using MVCC (Multi-Version Concurrency Control). You connect with a client—command-line psql, pgAdmin, DBeaver, or your app via JDBC or psycopg.

  Your app / psql                 PostgreSQL Server
        │                              │
        │  SELECT * FROM users;        │
        ├─────────────────────────────►│
        │◄─────────────────────────────┤ rows

Typical flow:

• Client sends SQL over TCP (local socket or network)
• PostgreSQL parses, plans, and executes the query
• Results return as rows; writes commit inside transactions

PostgreSQL vs MySQL vs MongoDB

PostgreSQL vs Redis

Production APIs often use Postgres + Redis—see Redis caching patterns and the planned Postgres + Redis deploy project.

Why Teams Choose PostgreSQL

Typical Use Cases

• SaaS backends — multi-tenant data with strict integrity
• Analytics and reporting — SQL aggregations, window functions
• Financial and audit trails — ACID transactions, constraints
• Geospatial apps — PostGIS extension (concept; see official docs)
• Modern Python/Node stacks — default DB for many frameworks when teams outgrow SQLite

Pairs with Hello Code tracks:

Core Features You Will Learn

A Minimal SQL Preview

After install you will run in psql:

Code explanation:

• GENERATED ALWAYS AS IDENTITY is the modern replacement for SERIAL
• RETURNING avoids a second round trip to fetch the inserted row
• Statements end with ; in psql

This track targets PostgreSQL 16+ (16 or 17 for new installs).

When PostgreSQL Is a Poor Fit

• Every record has wildly different fields and you rarely JOIN → consider MongoDB
• Pure caching or pub/sub with TTL only → use Redis
• Embedded zero-config local store in a mobile app → SQLite fits better
• Team standardized on MySQL-only tooling with no migration budget → stay on MySQL until there is a clear win

FAQ

PostgreSQL vs MySQL for a new web app?

Both work. Choose PostgreSQL when you want richer SQL, JSONB, partial indexes, or extensions. Choose MySQL when your team or host already standardizes on it—skills transfer both ways.

Is PostgreSQL free?

Yes. PostgreSQL is open source under the PostgreSQL License. Managed cloud services charge for hosting, not the database engine.

Do I need to learn SQL?

Yes for this track. ORMs generate SQL you still debug—MySQL chapters help if SQL is new.

What is MVCC?

Multi-Version Concurrency Control keeps snapshot versions of rows so readers and writers interfere less. Details in Transactions.

Supabase / Neon vs self-hosted?

Managed services run PostgreSQL-compatible engines with backups and scaling. SQL and drivers are the same; connection strings differ—Installation covers local Docker first.

JDBC or psycopg?

Java apps use JDBC with jdbc:postgresql://.... Python apps use psycopg—chapter 22.

Where is the full chapter list?

PostgreSQL Chapter Index—31 chapters; outline in frontend/gen_article_plan/postgresql.md.