PostgreSQL, psql and the shop database

conceptThe concept

One PostgreSQL server process manages a cluster: a data directory holding many databases. A connection is always to exactly one database, and you cannot join tables across databases in one query. Inside a database, schemas are namespaces (public is the default) that hold tables, views, functions and types. Roles (users and groups) are cluster-wide.

psql is the standard command-line client. Lines that start with a backslash are meta-commands that psql turns into catalog queries: \dt lists tables, \d orders describes a table, \di lists indexes, \x switches to one-column-per-line output. Everything else is SQL sent to the server, ending with a semicolon.

The sample database models a small online shop: customers (2,000), products (200), orders (10,000), order_items (about 25,000), reviews (3,000), events (20,000 click-stream rows) and a small employees hierarchy. Most tables only have their primary keys indexed, on purpose: the performance lessons add the rest.

whyWhy it matters

Knowing where objects live saves real incidents: a table that "disappeared" is often in another schema that is not on the search_path, and a connection pool pointed at the wrong database looks exactly like missing data.

Use caseReal-world scenario

On a new job, the first hour on an unfamiliar database is usually \l, \dn, \dt+ and a size query against the catalog, to learn which tables are big, which are hot and which schemas belong to which service.

Try itExamples

Run and edit these in the interactive lesson.

SELECT version();
SELECT current_database(), current_schema, current_user;
SHOW search_path;

\dt
\d orders

Meta-commands are shortcuts for catalog queries. This one lists the biggest tables with an estimated row count.

SELECT c.relname AS table_name,
       c.reltuples::bigint AS estimated_rows,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace
  AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;

internalsUnder the hood

Every object has an OID in a system catalog: tables, indexes and views live in pg_class, columns in pg_attribute, schemas in pg_namespace. The information_schema views are the SQL-standard, portable layer on top. psql's meta-commands are just queries over pg_catalog; run psql with -E to print them.

Avoid / preferDo this, not that

CREATE TABLE "Orders" (id integer);
SELECT * FROM Orders;  -- ERROR: relation "orders" does not exist

CREATE TABLE orders (id integer);
SELECT * FROM orders;

psql -U postgres -d shop   # the superuser for daily work

psql -U app_rw -d shop      # a role with only the privileges the job needs

PitfallsCommon mistakes

• Sorting by pg_size_pretty(...): it returns text, so "96 kB" sorts after "1888 kB". Sort by the raw byte count instead.
• Assuming a database is like a MySQL schema. In PostgreSQL you cannot query across databases without an extension such as postgres_fdw; use schemas to separate things you need to join.
• Leaving application objects in public and granting broad rights on it. Since PostgreSQL 15, ordinary users can no longer create objects in public by default.

LimitsWhen not to use it

PostgreSQL is a superb default, but it is not a cache (use Redis or Valkey for sub-millisecond key lookups at huge rates), not a log store for petabytes of append-only events, and not a columnar warehouse for scanning billions of rows per query. Extensions narrow these gaps; they do not remove them.

SecuritySecurity notes

Never type passwords into SQL such as ALTER ROLE ... PASSWORD 'x' in psql: they land in the history file and possibly in server logs. Use psql's \password, which sends only the hash.

ChallengeTry it yourself

SELECT current_setting('server_version_num')::int AS version_num,
       current_database(),
       current_user,
       (SELECT count(*) FROM pg_tables WHERE schemaname = 'public') AS public_tables;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• psql reference
• Database cluster and schemas