PostgreSQL for developers: install to your first production-ready schema

The bottom line

A working PostgreSQL install plus a normalised first schema is achievable in 60 minutes for a developer with a terminal and admin rights on their machine. This tutorial walks through installing PostgreSQL 18 (the current major version as of May 2026 ¹ ), connecting via psql, designing a three-table schema for a small task-tracker application with primary keys, foreign keys, and check constraints, indexing the columns that benefit from it, and seeding rows. Every command is verifiable against the official PostgreSQL 18 documentation. This is not a production-deployment tutorial; the database runs locally on the default port 5432 ² .

PostgreSQL uses a client/server model: a server process named postgres manages the database files and accepts connections, while client applications like psql connect over a TCP/IP socket ³ . The architecture matters less for a first schema than the SQL does; the architecture context shows up once when something doesn’t connect.

Replication, partitioning, role-based access control, and connection-pooling middleware (PgBouncer) are separate articles.

What you’ll build

The end state is a single database named tasktracker_dev with three tables: users, projects, and tasks, normalised so a user can own many projects, a project contains many tasks, and a task belongs to exactly one project and is assigned to at most one user. The schema includes primary keys, foreign keys with ON DELETE rules, NOT NULL and CHECK constraints for data integrity, and indexes on the columns the application will filter and join on most often.

Three SQL files at the end:

• schema.sql: CREATE TABLE statements for the three tables.
• indexes.sql: CREATE INDEX statements for the foreign-key columns and one composite index.
• seed.sql: a small INSERT set for sanity-checking queries.

Prerequisites

Before starting, the reader needs:

• A supported operating system. PostgreSQL ships for macOS, Linux, Windows, BSD, and Solaris per the official downloads page ⁶ . macOS users have the smoothest path via Homebrew or Postgres.app; Linux users use their distro’s package manager; Windows users use the EnterpriseDB installer.
• Admin rights on the machine. The install step writes to /usr/local or /opt (macOS / Linux) or Program Files (Windows).
• A terminal. Terminal.app or iTerm on macOS, any Linux terminal, PowerShell on Windows.
• Around 500 MB free disk. PostgreSQL itself is small; the bulk goes to the data directory.

A SQL client GUI (DBeaver, TablePlus, pgAdmin) is optional. This tutorial sticks to the psql command-line client because every PostgreSQL install ships with it ² .

Image: postgres/postgres official GitHub repository (github.com/postgres/postgres), used for editorial coverage of the PostgreSQL project this tutorial covers.

The mental model

PostgreSQL splits its world into clusters, databases, schemas, and objects. A cluster is the running server instance and its associated data directory; a single cluster can host multiple databases. A database is the unit a connection attaches to; one connection can only query one database at a time. A schema is a namespace inside a database; the default is public. Tables, indexes, sequences, views, and functions live inside schemas.

The architectural baseline matters when something fails to connect. Per the official docs: PostgreSQL’s postgres server process manages database files and accepts connections; clients communicate over TCP/IP or a Unix-domain socket; the server forks a new process for each connection ³ . A failed connection is usually one of three things: the server isn’t running, it’s running on a different port than the client expects, or pg_hba.conf rejects the connection method.

The schema-design baseline this tutorial uses is third-normal-form for relational data: one fact per row, foreign keys for relationships, indexes on join and filter columns. PostgreSQL supports far more (JSONB columns, partial indexes, generated columns, materialised views) but those belong in later articles.

Step 1: Install PostgreSQL

The official downloads page ⁶ lists the canonical install path for each operating system. The commands below are the most common ones.

macOS (Homebrew):

brew install postgresql@18
brew services start postgresql@18

Homebrew installs PostgreSQL into /opt/homebrew/Cellar/postgresql@18 on Apple Silicon (/usr/local/Cellar/postgresql@18 on Intel) and creates a data directory at /opt/homebrew/var/postgresql@18. brew services start runs the server in the background and configures it to relaunch on reboot.

Ubuntu / Debian:

sudo apt update
sudo apt install postgresql-18 postgresql-client-18
sudo systemctl enable --now postgresql

The PostgreSQL APT repository (apt.postgresql.org) is the canonical source for current major versions on Debian-family Linux; the default Ubuntu repos lag behind. See the downloads page for the repository setup commands ⁶ .

Windows: download the EnterpriseDB installer linked from the downloads page ⁶ ; run it; accept the defaults; remember the password set for the postgres superuser during install.

Confirm the install succeeded:

postgres --version
psql --version

Both should print psql (PostgreSQL) 18.x or postgres (PostgreSQL) 18.x. The May 2026 release notice on the documentation index confirms 18.4 as the current point release for the 18 series ¹ .

Step 2: Create a database and connect

PostgreSQL provides a command-line utility createdb that wraps CREATE DATABASE for convenience. Per the official tutorial chapter ² :

createdb tasktracker_dev

If the command produces no output, the database was created successfully ² . Common failure modes: the server isn’t running (Connection refused); the OS user doesn’t have a matching PostgreSQL role (role "yourname" does not exist); the data directory has the wrong permissions (could not connect to server: No such file or directory).

On a Homebrew install on macOS, the OS user matches the PostgreSQL role automatically. On Linux, apt-installed PostgreSQL creates a postgres system user; switch to it for the first connection:

sudo -u postgres createdb tasktracker_dev
sudo -u postgres createuser --interactive

createuser --interactive prompts for a role name (use the host OS username) and whether to grant superuser privileges (answer y for local development only, never on a production database).

Connect via psql:

psql tasktracker_dev

The prompt changes to tasktracker_dev=> (or tasktracker_dev=# if connected as a superuser). The reader is now inside the database; everything after this point runs SQL.

A few psql meta-commands worth memorising:

• \dt: list tables in the current schema.
• \d table_name: describe a table’s columns, indexes, and constraints.
• \l: list databases on the cluster.
• \du: list roles.
• \q: quit.
• \i /path/to/file.sql: execute a SQL file.

Step 3: Design the schema

Open a new file schema.sql in an editor. Paste:

-- schema.sql: tasktracker_dev database schema

BEGIN;

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT       NOT NULL UNIQUE,
    full_name   TEXT       NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT users_email_lowercase CHECK (email = lower(email))
);

CREATE TABLE projects (
    id          BIGSERIAL PRIMARY KEY,
    owner_id    BIGINT     NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name        TEXT       NOT NULL,
    archived    BOOLEAN    NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT projects_name_not_empty CHECK (length(name) > 0),
    UNIQUE (owner_id, name)
);

CREATE TABLE tasks (
    id           BIGSERIAL PRIMARY KEY,
    project_id   BIGINT     NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    assignee_id  BIGINT     REFERENCES users(id) ON DELETE SET NULL,
    title        TEXT       NOT NULL,
    status       TEXT       NOT NULL DEFAULT 'todo',
    due_at       TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT tasks_status_valid CHECK (status IN ('todo','doing','done')),
    CONSTRAINT tasks_completed_status CHECK (
        (status = 'done' AND completed_at IS NOT NULL)
        OR
        (status <> 'done' AND completed_at IS NULL)
    )
);

COMMIT;

A walk through the deliberate choices, each tied to a PostgreSQL feature documented in the CREATE TABLE reference ⁴ :

• BIGSERIAL for primary keys. Auto-incrementing 64-bit integer; expands to a sequence-backed BIGINT under the hood. A surrogate key keeps natural-key changes (like an email update) from cascading.
• PRIMARY KEY constraint. Per the official reference, a primary key implies NOT NULL plus UNIQUE and automatically creates a unique B-tree index ⁴ . No need to add the index manually.
• NOT NULL on every column that should always have a value. Forgetting NOT NULL on a foreign-key column is the most common subtle bug in a first schema; nullable foreign keys make JOINs drop rows silently.
• UNIQUE (owner_id, name) on projects. A composite unique constraint: a single user can have two projects called “Personal” if they belong to different owners, but the same owner cannot. Implies a unique B-tree index, same as primary key.
• REFERENCES users(id) ON DELETE CASCADE. When a user is deleted, their projects (and via the cascade, those projects’ tasks) are deleted too. ON DELETE SET NULL on tasks.assignee_id does the opposite: deleting a user un-assigns their tasks without deleting them.
• CHECK constraints. users_email_lowercase rejects mixed-case emails at the database level so application bugs cannot insert duplicates that differ only in case. tasks_status_valid enforces a finite enumeration of status values; tasks_completed_status enforces a cross-column invariant: a task is in done status if and only if completed_at is set.
• TIMESTAMPTZ for all timestamps. TIMESTAMP WITH TIME ZONE stores UTC internally and converts on read; TIMESTAMP WITHOUT TIME ZONE stores wall-clock time and is the cause of most timezone bugs. Default to TIMESTAMPTZ.
• BEGIN; ... COMMIT;. Wraps the schema creation in a transaction so a partial failure rolls back cleanly. DDL in PostgreSQL is transactional, unlike in some other databases.

Apply the schema:

psql tasktracker_dev -f schema.sql

Inside psql, run \dt; three tables appear: users, projects, tasks. Run \d tasks to see columns, indexes (PostgreSQL has already created two: the primary-key index and the project foreign-key constraint’s), and constraints.

Image: PostgreSQL official press kit (postgresql.org/media/img/about/press/elephant.png), the project’s canonical brand mark.

Step 4: Add indexes

PostgreSQL creates indexes automatically for primary keys and unique constraints ⁴ . It does NOT create indexes automatically for foreign-key columns; the application has to add them. Without indexes on foreign-key columns, queries that join from child to parent (or filter children by parent) trigger full table scans.

Create indexes.sql:

-- indexes.sql: hot-path indexes for tasktracker_dev

CREATE INDEX projects_owner_id_idx ON projects (owner_id);

CREATE INDEX tasks_project_id_idx  ON tasks (project_id);
CREATE INDEX tasks_assignee_id_idx ON tasks (assignee_id);

-- Composite: list a user's open tasks across all their projects.
CREATE INDEX tasks_open_by_assignee_idx
  ON tasks (assignee_id, status)
  WHERE status <> 'done';

The composite partial index at the end is the kind of optimisation worth flagging early. PostgreSQL supports partial indexes, which exclude rows matching a predicate. Done tasks fill up a table over time but are queried less often; a partial index on open tasks stays small and serves the most-frequent query (the user’s open-task list) without scanning the bulk of historical rows. The indexes documentation chapter covers B-tree, hash, GiST, SP-GiST, GIN, and BRIN index types ⁵ ; B-tree is the default and covers most real-world cases.

Apply the indexes:

psql tasktracker_dev -f indexes.sql

Re-run \d tasks; three new indexes appear under the table’s index list.

Step 5: Seed test data

A schema with no data is hard to query against. Create seed.sql:

-- seed.sql: minimal data for sanity-checking queries

BEGIN;

INSERT INTO users (email, full_name) VALUES
    ('alice@example.com', 'Alice Singh'),
    ('bob@example.com',   'Bob Pereira'),
    ('carol@example.com', 'Carol Lee');

INSERT INTO projects (owner_id, name) VALUES
    ((SELECT id FROM users WHERE email = 'alice@example.com'), 'Website redesign'),
    ((SELECT id FROM users WHERE email = 'alice@example.com'), 'Q3 planning'),
    ((SELECT id FROM users WHERE email = 'bob@example.com'),   'API migration');

INSERT INTO tasks (project_id, assignee_id, title, status, due_at) VALUES
    ((SELECT id FROM projects WHERE name = 'Website redesign'),
     (SELECT id FROM users    WHERE email = 'alice@example.com'),
     'Audit current homepage copy',
     'todo',
     now() + interval '7 days'),
    ((SELECT id FROM projects WHERE name = 'Website redesign'),
     (SELECT id FROM users    WHERE email = 'carol@example.com'),
     'Draft new visual design',
     'doing',
     now() + interval '14 days'),
    ((SELECT id FROM projects WHERE name = 'API migration'),
     (SELECT id FROM users    WHERE email = 'bob@example.com'),
     'Inventory deprecated endpoints',
     'todo',
     now() + interval '5 days');

COMMIT;

The seed uses SELECT id FROM users WHERE email = ... subqueries instead of hard-coded integer IDs because BIGSERIAL values aren’t predictable across re-runs. The wrapping BEGIN; ... COMMIT; rolls back the entire seed if any insert fails (for example, a typo in a status value would trip the CHECK constraint).

Apply the seed:

psql tasktracker_dev -f seed.sql

Confirm with a query that exercises the joins:

SELECT
    u.full_name        AS assignee,
    p.name             AS project,
    t.title,
    t.status,
    t.due_at::date     AS due_date
FROM tasks t
JOIN projects p ON p.id = t.project_id
JOIN users    u ON u.id = t.assignee_id
WHERE t.status <> 'done'
ORDER BY t.due_at;

Three rows render, ordered by due date. The query uses the tasks_project_id_idx and tasks_assignee_id_idx indexes for the joins; EXPLAIN ANALYZE confirms which.

Step 6: Verify the schema enforces its invariants

A schema is only as good as its constraints. Run these inserts in psql to confirm each constraint rejects bad data:

-- Should fail: uppercase email.
INSERT INTO users (email, full_name) VALUES ('Alice@example.com', 'X');
-- ERROR: new row for relation "users" violates check constraint
--        "users_email_lowercase"

-- Should fail: status not in (todo, doing, done).
INSERT INTO tasks (project_id, title, status)
  VALUES (1, 'Bad task', 'archived');
-- ERROR: new row violates check constraint "tasks_status_valid"

-- Should fail: status=done without completed_at.
INSERT INTO tasks (project_id, title, status)
  VALUES (1, 'Bad task', 'done');
-- ERROR: new row violates check constraint "tasks_completed_status"

-- Should fail: project name duplicate for same owner.
INSERT INTO projects (owner_id, name)
  VALUES (
    (SELECT id FROM users WHERE email = 'alice@example.com'),
    'Website redesign'
  );
-- ERROR: duplicate key value violates unique constraint
--        "projects_owner_id_name_key"

-- Should cascade: deleting a user removes their projects and tasks.
DELETE FROM users WHERE email = 'bob@example.com';
SELECT count(*) FROM projects WHERE owner_id IS NULL;  -- → 0
SELECT count(*) FROM tasks WHERE assignee_id IS NULL;
-- → some rows; tasks where Bob was assignee now have assignee_id NULL
--   because tasks.assignee_id is ON DELETE SET NULL.

Each error message names the violated constraint. A schema that fails loudly on bad data prevents subtle corruption that’s painful to clean up later.

Things that commonly go wrong

A short list of issues that bite first-time PostgreSQL users:

• Connection refused on port 5432: the server isn’t running. brew services list (macOS) or systemctl status postgresql (Linux) confirms. Start with brew services start postgresql@18 or sudo systemctl start postgresql.
• role "yourname" does not exist: PostgreSQL needs a role matching the OS user. sudo -u postgres createuser --interactive creates one on Linux.
• psql: error: connection to server on socket ... failed: the socket file path the client expects differs from where the server is listening. Set PGHOST=localhost to force a TCP connection on port 5432.
• relation "users" already exists: the schema was applied twice. Either drop the database (dropdb tasktracker_dev) and re-run, or use DROP TABLE IF EXISTS users CASCADE; before CREATE TABLE.
• column "id" is of type bigint but expression is of type text: PostgreSQL is strictly typed. A literal like '1' is text; cast with '1'::bigint or use unquoted integer literals.
• Sequence out of sync after manual ID inserts: setval('users_id_seq', (SELECT max(id) FROM users)) resets the sequence so future BIGSERIAL inserts don’t collide.

What was deliberately skipped

This tutorial covers the smallest meaningful schema-and-query loop. A production schema would also use row-level security policies (CREATE POLICY), separate schemas per bounded context (CREATE SCHEMA billing; CREATE SCHEMA tasks;), explicit role hierarchies (a read-only role for analytics, a write role for the application), and migration tooling (Flyway, Liquibase, dbmate, or framework-native migrations like Django’s, Rails’s, or Prisma’s).

JSONB columns, full-text search via GIN indexes, partitioning for time-series tables, logical replication, pg_stat_statements query profiling, connection pooling via PgBouncer, and backup strategies via pg_dump and pg_basebackup are separate articles.

Image: postgres/postgres GitHub repository wiki (github.com/postgres/postgres/wiki), used for editorial coverage of the PostgreSQL project this tutorial covers.

Recap

A working PostgreSQL 18 database with a three-table normalised schema, foreign-key cascades, check constraints, and hot-path indexes, in three SQL files. The mental model: cluster contains databases contain schemas contain tables; primary keys and unique constraints get free indexes; foreign-key columns and frequent filter columns need explicit ones; check constraints enforce invariants at the database tier so the application cannot violate them. The official CREATE TABLE reference ⁴ and the Indexes chapter ⁵ are the canonical references; the documentation index ¹ confirms PostgreSQL 18 as the current major release.

Image: PostgreSQL Wikipedia article (en.wikipedia.org/wiki/PostgreSQL), used for editorial coverage of the project this tutorial covers; image under Wikimedia Commons license.