psql and the System Catalog

psql is not the fallback for when the GUI is unavailable. It is the interface the database's own maintainers use, and every backslash command in it is shorthand for a query against the system catalogue. \d orders is pg_class joined to pg_attribute, pg_index and pg_constraint, formatted for a terminal.

Knowing that changes what the terminal is for. Anything \d can display, you can compute, filter, sort, join against your own tables and put on a schedule, which is the difference between looking at a database and monitoring one. This topic covers the commands worth memorizing, the catalogue underneath them, and the four-line configuration file that prevents a whole class of misread results.

The Commands That Earn Their Keep

A dozen backslash commands cover most of what an investigation needs, and they compose: every listing command takes an optional pattern, and a + suffix asks for the expensive extra columns such as sizes and descriptions.

\l                 -- databases in this cluster
\dn                -- schemas in this database
\dt                -- tables the search_path can see
\dt analytics.*    -- tables in one named schema
\d+ orders         -- columns, indexes, constraints, storage, size
\di+ orders*       -- the indexes on orders, with their sizes
\df analytics.*    -- functions in the analytics schema
\du                -- roles, cluster-wide
\dx                -- extensions installed in this database

\x auto            -- expand output only when a row is too wide
\timing on         -- report the duration of every statement
\e                 -- edit the last query in $EDITOR
\watch 5           -- re-run the last query every five seconds

\d+ on a table is the highest-value keystroke here: one screen with every column and its type, every index and what it covers, every constraint, the storage settings, and the total size. Start any investigation there. \x auto is what makes a wide row from pg_stat_activity readable instead of wrapped into noise, and \watch 5 turns any query into a live monitor without leaving the terminal — point it at a lock-waiting query during an incident and you have a poor engineer's dashboard.

One behaviour catches everyone once. With no pattern, these commands list what is visible — what the current search_path can reach. Cartwheel's analytics schema is not on the default path, so a bare \dt shows the eight public tables and gives no hint that anything else exists. \dt *.* or a schema-qualified pattern shows the rest.

Client-Side \copy, Server-Side COPY

The two differ in one respect that decides everything else: which machine's filesystem is involved. COPY with a file name reads and writes on the server, as the operating-system user the server runs as, which is why it is restricted to superusers and to roles granted pg_read_server_files, pg_write_server_files or pg_execute_server_program. \copy is a psql instruction that runs COPY … FROM STDIN and streams the data through the existing connection, so the file is opened by psql with the privileges of whoever is sitting at the terminal.

-- server-side: the file must exist on pg-primary,
-- readable by the postgres OS user; needs elevated rights
COPY products (sku, name, price)
  FROM '/var/lib/postgresql/import/products.csv'
  WITH (FORMAT csv, HEADER);

-- client-side: the file is on Nadia's laptop and travels
-- over the connection; no special database privilege
\copy products (sku, name, price) FROM 'products.csv' WITH (FORMAT csv, HEADER)

Loading Cartwheel's product catalogue from a laptop is the second form. Reaching for the first and hitting a permission error is the classic mistake of the first week, and the damaging part is the repair: granting the application superuser to make the message go away solves a permission problem by deleting the permission model. On a managed provider the question does not even arise, because there is no server filesystem you can put a file on.

The Catalogue Is Just Tables

The catalogue is not an API. pg_class holds one row per relation of any kind (tables, indexes, sequences, views) with the planner's estimates reltuples and relpages on it. pg_attribute holds the columns, pg_index the index definitions, pg_constraint the constraints, pg_namespace the schemas. They are ordinary tables, they join like ordinary tables, and every "find the unused indexes" or "list the tables with no primary key" query in this book is written against them.

SELECT c.relname, c.reltuples::bigint AS est_rows
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = 'r'
   AND n.nspname = 'public'
   AND NOT EXISTS (
         SELECT 1 FROM pg_constraint k
          WHERE k.conrelid = c.oid AND k.contype = 'p')
 ORDER BY c.reltuples DESC;

That query walks every ordinary table in the public schema, checks whether any constraint on it is of type primary key, and reports the ones with none, ordered by estimated size. It is a handful of lines, and it answers a question no GUI screen answers directly. Run it against Cartwheel and inventory comes back, because that table has never had a primary key — courier_shifts has none either, and the two gaps get closed differently: inventory gets a real primary key when Chapter 3 puts constraints on the schema, and courier_shifts gets something stronger than a key in Chapter 2, a constraint that makes two overlapping shifts for one courier impossible to store.

Read reltuples for what it is. It is the planner's estimate, refreshed by VACUUM, ANALYZE and a few DDL commands, and it is -1 on a table that has never been vacuumed or analyzed at all. After a bulk load into orders it can be millions of rows out of date, and the gap widens with every write until the next ANALYZE. A true count costs a full scan of the table and returns one number.

information_schema Against pg_catalog

information_schema is defined by the SQL standard, so it is portable and stable across engines and across major versions. pg_catalog is native to Postgres, is modelled on the implementation rather than on the standard, and is implicitly in every session's search path. The information schema is a set of views over the same underlying data, with a layer of standard-shaped joins in between.

The split in practice is clean. Write against information_schema when the tool has to run on more than one engine and asks only standard questions — which columns, which types, which foreign keys. Write against pg_catalog and the pg_stat_* views for anything Postgres-specific, which is everything this book cares about: index usage, dead tuples, last autovacuum time, bloat, TOAST. Those facts have no standard representation, so they are simply absent from the portable view.

Sizes and Live Counters

The three size functions answer three different questions, and mixing them up produces reports that disagree with the disk. pg_relation_size returns one fork of the relation, the main data fork by default, which is the heap alone. pg_indexes_size is every index attached to the table. pg_total_relation_size is the whole footprint: table, indexes and TOAST together. pg_size_pretty formats any of them in units that are powers of two.

SELECT c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid))       AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))        AS indexes,
       s.n_live_tup, s.n_dead_tup, s.seq_scan, s.idx_scan,
       s.last_autovacuum
  FROM pg_class c
  JOIN pg_stat_user_tables s ON s.relid = c.oid
 ORDER BY pg_total_relation_size(c.oid) DESC;

One query, and the shape of a database becomes visible: which table owns the disk, how much of that is index rather than data, how many dead rows are sitting in each, whether anything is being read by sequential scan when it should be using an index, and when autovacuum last touched it. Every number in it except the three sizes is an estimate maintained by the statistics system rather than a measurement taken now, and every one of them is what Chapters 7 and 10 act on.

The .psqlrc That Pays for Itself

psql reads ~/.psqlrc at startup unless it is told not to. Four lines in it remove more confusion than any other configuration in this chapter.

\timing on
\set ON_ERROR_STOP on
\pset null '¤'
\x auto

\timing on means every statement reports its duration, so you never guess whether something was slow. ON_ERROR_STOP makes psql abort on the first error instead of ploughing on: without it a migration script runs statement four after statement three failed, and exits reporting success over a half-applied schema. \pset null matters more than it looks, because psql prints nothing for a NULL by default, and "nothing" is indistinguishable from an empty string when the question is whether a bug is in the data or in the query. \x auto keeps wide rows readable. Set ON_ERROR_STOP inside every script as well; a startup file lives on one laptop, and the script runs on whatever machine the deploy uses.