PostgreSQL Basics #1 What PostgreSQL Is: Why It Became the Default, Installation and First psql Session

Build backends for a while and you notice the database has somehow already been decided: it’s PostgreSQL. Framework documentation examples, managed cloud services, the first line in every new open-source tool’s support list — all PostgreSQL. This series is nine parts of properly learning that “somehow already using it” database from the ground up. We climb from data types and table design through joins, indexes and execution plans, transactions, and JSONB up to permissions and backups, and a follow-up nine-part practice series covers operating the database of a growing service (connection pooling, performance diagnostics, VACUUM, replication). The assumed reader is not someone seeing SQL for the first time, but a developer who “writes queries but never actually learned databases.”

Why PostgreSQL became the default #

Over the past several years, “PostgreSQL unless there’s a specific reason not to” has become the industry’s default instinct. The reason is accumulation.

• Standards compliance and strictness: It follows the SQL standard faithfully and is strict about data integrity. Instead of quietly accepting an invalid date, it raises an error. That strictness becomes an asset as a service grows.
• Breadth of types and features: JSONB, arrays, range types, full-text search, window functions — much of what used to require “a separate system for that” is built in.
• The extension ecosystem: pgvector for vector search, PostGIS for geospatial data, TimescaleDB for time series. The architecture of plugging features in without changing the core built the trust that “if you start with PostgreSQL, you can go anywhere.” pgvector, which we covered in embedding-based search, is the flagship example.
• License and governance: A permissive open-source license and a community project owned by no single company — a stability that contrasts with commercial databases and with other open-source databases whose licenses have lurched around.

A new major version ships every fall. This course is written against the current stable release (PostgreSQL 18, the version whose asynchronous I/O significantly improved read performance), and anything version-dependent will be flagged as it comes up. For the sake of orientation: choosing between PostgreSQL and NoSQL is covered in DynamoDB vs RDS, and handing operations to a managed service is covered in RDS vs self-managed. For learning, running it locally yourself teaches the most.

Installation: one line of Docker #

Docker is the cleanest local learning environment. You can wipe it and recreate it without leaving installation residue.

docker run --name pg-lab -e POSTGRES_PASSWORD=devpass -p 5432:5432 -d postgres:18

That one line starts a PostgreSQL 18 server on port 5432. There are plenty of GUI clients, but this course uses psql, the official CLI, as the default. It exists on every server, it is faster than any GUI, and it is the tool that remains when everything else is broken.

docker exec -it pg-lab psql -U postgres

First psql session: five meta-commands #

Besides SQL, psql has meta-commands that start with a backslash. Five are enough to get going.

You will use \d throughout this course. It shows which indexes and constraints a table carries on a single screen — the most fundamental observation tool there is.

The first cycle: create, insert, query #

CREATE DATABASE lab;
\c lab

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (email, name) VALUES
    ('kim@example.com', 'Kim Dev'),
    ('lee@example.com', 'Lee Backend');

SELECT id, email, name, created_at FROM users WHERE email = 'kim@example.com';

Short as it is, this example already carries three habits this course instills. The primary key is bigint IDENTITY (why that is the default, and when UUID is the answer, is next chapter’s topic), timestamps are timestamptz (not plain timestamp — also next chapter), and NOT NULL is the baseline you relax only deliberately. In applications you will often go through an ORM; that layer belongs to material like the SQLAlchemy course, while this series focuses on the database underneath it.

Summary #

• PostgreSQL became the default for new projects through accumulation: standards compliance, breadth of types and features, the extension ecosystem, and stable governance.
• One line of Docker is enough for a learning environment. Learning psql as your baseline client pays off for the longest time.
• Five meta-commands (\l, \c, \dt, \d, \x) are the start of observation. \d in particular gets used throughout the course.
• The first table already plants three habits: bigint IDENTITY PKs, timestamptz, NOT NULL by default. The reasons come next chapter.
• Next up: data types and table design — the judgment criteria for “what should this be stored as.”