Lesson 04 beginner

Databases, Schemas and Tables

Create the Orbit role and database, understand schemas and the search_path, design the users and notes tables with identity keys and defaults, inspect them from psql and the catalog, change them safely with ALTER TABLE, and follow naming conventions that keep a schema easy to live with.

Time to build something. In this lesson you create a role and a database for Orbit, learn how schemas organise objects, and create the first two tables: users and notes. Every later lesson builds on them.

Create the Orbit role and database

Connect with the role you created in lesson 2 and run:

CREATE ROLE orbit LOGIN PASSWORD 'change-me';
GRANT orbit TO CURRENT_USER;
CREATE DATABASE orbit OWNER orbit;
ALTER DATABASE orbit SET timezone TO 'UTC';
CREATE ROLE
GRANT ROLE
CREATE DATABASE
ALTER DATABASE
  • orbit is the owner role. It owns the database and, soon, every table in it. Applications will connect with a separate, less powerful role (lesson 15).
  • GRANT orbit TO CURRENT_USER makes you a member of orbit. Since PostgreSQL 16, you may only create a database owned by another role if you are allowed to act as that role.
  • ALTER DATABASE ... SET timezone makes every new session in this database use UTC. Storing and showing times in UTC on the server is a good default, and it makes the results in this course match yours exactly.

Now connect to the new database as the new role. Use localhost so the connection goes over TCP and asks for the password; over the local socket, Linux packages use peer authentication, which only lets the OS user orbit in:

\c orbit orbit localhost

From the shell, the same connection is psql -h localhost -U orbit -d orbit. Add a line to ~/.pgpass (lesson 3) so you are not asked for the password every time.

Tip: CREATE DATABASE copies a template database, template1 by default. Anything you put in template1, such as an extension, appears in every database created afterwards.

Schemas and the search_path

A database contains schemas, and objects live in schemas. Every database starts with one called public. When you write notes, Postgres looks for it in the schemas listed in search_path:

SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

"$user" means “a schema with the same name as the current role, if it exists”. So for the role orbit, a table called notes is looked up first in a schema orbit and then in public. You can always write the full name, public.notes, to be explicit.

Schemas are useful to separate parts of an application, or to give a reporting team its own space:

CREATE SCHEMA reporting;
CREATE TABLE reporting.daily_signups (day date PRIMARY KEY, total int NOT NULL);
SET search_path TO reporting, public;

Since PostgreSQL 15, ordinary roles can no longer create objects in public unless they own the database or are granted that right, which closes a long-standing security gap. orbit owns the database, so it can.

For this course, Orbit keeps its tables in public. Reset the path:

RESET search_path;
DROP SCHEMA reporting CASCADE;

Design the first tables

Orbit has users, and users write notes. Each note belongs to exactly one user:

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()
);

CREATE TABLE notes (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  title      text NOT NULL,
  body       text NOT NULL DEFAULT '',
  pinned     boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE
CREATE TABLE

A lot happens in those few lines:

PieceMeaning
bigint GENERATED ALWAYS AS IDENTITYPostgres numbers rows 1, 2, 3… using a sequence it manages for you
PRIMARY KEYThe column uniquely identifies a row; it is NOT NULL and gets a unique index
NOT NULLThe column must always have a value
UNIQUENo two rows may have the same email; also creates an index
DEFAULT now()Value used when an INSERT does not provide one
REFERENCES users (id)A foreign key: every user_id must exist in users
ON DELETE CASCADEDeleting a user deletes their notes too

Lessons 5 and 10 explain types and constraints in depth. For now, notice that the database itself enforces the rules: no application bug can create a note for a user that does not exist.

Look at what you created

psql describes a table with \d:

\d notes

It prints the columns with types, nullability and defaults, then the indexes (notes_pkey), the foreign key and any tables that reference this one. \dt lists all tables and \dt+ adds their size.

The same information is available with SQL from the standard information_schema views, which is handy in scripts and works the same in other databases:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
 column_name |        data_type         | is_nullable | column_default
-------------+--------------------------+-------------+----------------
 id          | bigint                   | NO          | (null)
 email       | text                     | NO          | (null)
 name        | text                     | NO          | (null)
 created_at  | timestamp with time zone | NO          | now()
(4 rows)

The identity column shows no default: identity is a property of the column, not a default expression. Postgres’s own catalog tables (pg_class, pg_attribute, pg_constraint and friends) hold even more detail and are what \d reads behind the scenes.

Changing a table

Requirements change, and ALTER TABLE changes tables in place, keeping the data:

ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users RENAME COLUMN bio TO about;
ALTER TABLE users ALTER COLUMN about SET DEFAULT '';
ALTER TABLE users DROP COLUMN about;

A few things to know before you run ALTER TABLE on a big, busy table:

  • Adding a column with no default, or with a constant default, is instant even on a billion rows.
  • Changing a column’s type (for example int to bigint) usually rewrites the whole table while holding a lock that blocks reads and writes.
  • Every ALTER TABLE needs a brief exclusive lock. If a long query is using the table, your ALTER waits, and every new query queues behind it. Set lock_timeout first so you fail fast instead (lesson 12).

Removing tables

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS old_reports CASCADE;
TRUNCATE TABLE import_staging;

DROP TABLE removes the table and its data. CASCADE also drops objects that depend on it, such as views; run it once without CASCADE first to see what would go. TRUNCATE keeps the table but removes every row instantly.

Warning: There is no recycle bin. DROP and TRUNCATE are permanent once committed. Inside BEGIN ... ROLLBACK you can still undo them, because DDL in Postgres is transactional.

Documenting tables

Comments live in the database next to the objects, where tools and \d+ can show them:

COMMENT ON TABLE notes IS 'Notes written by Orbit users';
COMMENT ON COLUMN notes.pinned IS 'Pinned notes are shown first in the app';

Naming conventions that age well

  • Use lower_snake_case for every name. Unquoted names are folded to lower case, so CreatedAt silently becomes createdat. Quoting ("CreatedAt") preserves case but then you must quote it forever.
  • Name tables in the plural (users, notes) and foreign keys as <singular>_id (user_id).
  • Prefer text over varchar(n), and add a CHECK constraint when you really need a length limit (lesson 5).
  • Give every table a primary key, and time columns a _at (timestamps) or _on (dates) suffix.

Quick reference

TaskSQL
Create a login roleCREATE ROLE orbit LOGIN PASSWORD '...';
Create a databaseCREATE DATABASE orbit OWNER orbit;
Per-database settingALTER DATABASE orbit SET timezone TO 'UTC';
Create a schemaCREATE SCHEMA reporting;
Lookup orderSHOW search_path; / SET search_path TO ...;
Create a tableCREATE TABLE ... ( ... );
Add / rename / drop a columnALTER TABLE ... ADD / RENAME / DROP COLUMN ...;
Describe\d notes
Empty a tableTRUNCATE notes;
Remove a tableDROP TABLE IF EXISTS notes;

Exercise

A teammate creates a column named CreatedAt without quotes. What is the column called in the database?

  1. CreatedAt
  2. created_at
  3. createdat
  4. It fails with a syntax error
Show answer

Answer: C. Unquoted identifiers are folded to lower case. Only a quoted name such as “CreatedAt” keeps its case, and then it must be quoted in every query.

Exercise

You run SELECT * FROM notes, and the search_path is “$user”, public. Your role is orbit and a schema named orbit exists with its own notes table. Which table is read?

  1. orbit.notes, because “$user” comes first in the path
  2. public.notes, because public is the default schema
  3. Both, combined
  4. Neither: Postgres reports that the name is ambiguous
Show answer

Answer: A. Postgres uses the first schema in search_path that contains a matching object. “$user” expands to the role name, so orbit.notes wins. Write public.notes to be explicit.

Exercise

Which ALTER TABLE is expected to finish instantly on a table with 500 million rows?

  1. ALTER TABLE notes ALTER COLUMN id TYPE numeric;
  2. ALTER TABLE notes ADD COLUMN archived boolean NOT NULL DEFAULT false;
  3. VACUUM FULL notes;
  4. ALTER TABLE notes ALTER COLUMN title TYPE varchar(100);
Show answer

Answer: B. Since PostgreSQL 11, a new column with a constant default is stored in the catalog and not written into every row. Type changes and VACUUM FULL rewrite the table.

Key takeaways

  • Create a dedicated owner role and database per application, and set useful defaults such as the time zone on the database.
  • Schemas are namespaces; search_path decides where unqualified names are found.
  • Identity columns, primary keys, NOT NULL, UNIQUE, defaults and foreign keys are declared with the table and enforced by the server.
  • \d table and information_schema.columns show a table’s structure.
  • ALTER TABLE changes tables in place; adding columns is cheap, type changes rewrite the table.

FAQ

Should I use one database per application or one schema per application?

One database per application is the usual choice: it is simpler to back up, restore, move and secure. Schemas are best for organising objects inside one application, or for multi-tenant designs where tenants share most structure.

Why bigint for ids instead of integer?

integer stops at about 2.1 billion. Running out on a busy table means a painful type change under pressure. bigint costs four more bytes per row and will never run out in practice.

What is the difference between GENERATED ALWAYS and GENERATED BY DEFAULT?

With ALWAYS, an INSERT that supplies its own id fails unless it says OVERRIDING SYSTEM VALUE, which prevents accidental collisions. BY DEFAULT accepts supplied ids silently. Prefer ALWAYS.

What about the old serial type?

serial creates an integer column with a sequence default. It still works, but identity columns are the SQL-standard replacement: they tie the sequence to the column, copy correctly with CREATE TABLE ... LIKE, and are easier to manage with permissions.

Can I rename a table that applications are using?

Yes, ALTER TABLE notes RENAME TO notes_v2; is instant, but every query using the old name breaks immediately. For a gradual move, rename the table and create a view with the old name that selects from it.

↑ ↓ navigate Enter open Esc close