TL;DR

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- Exact rows of every table in a schema, in a single query
select table_schema, table_name,
       (xpath('/row/cnt/text()', xml_count))[1]::text::bigint as row_count
from (
  select table_schema, table_name,
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' and table_type = 'BASE TABLE'
) t
order by row_count desc;

-- Instant estimate (does not read the tables)
select relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc;

-- Read-only user (PostgreSQL 14 or later)
create user reporting with password '…';
grant pg_read_all_data to reporting;

Counting rows

An exact count(*) on each table means scanning it whole; on a database of hundreds of GB it takes a while. What you almost always want is the estimate autovacuum maintains, refreshed on every ANALYZE and usually within a couple of points of the real number:

1
2
3

select schemaname, relname, n_live_tup, n_dead_tup, last_autoanalyze
from pg_stat_user_tables
order by n_live_tup desc;

When you need the exact number for every table without writing a DO block in PL/pgSQL, the query_to_xml trick: it runs a dynamic count(*) per table, returns the result as XML, and xpath extracts the number. It is plain SQL, works from any client, and can be filtered by schema or name. Just remember it runs the counts serially: on big tables, patience.

Sizes

1
2
3
4
5
6
7
8
9

select pg_size_pretty(pg_database_size(current_database()));

select n.nspname as schema, c.relname as table,
       pg_size_pretty(pg_total_relation_size(c.oid)) as total,   -- data + indexes + TOAST
       pg_size_pretty(pg_relation_size(c.oid)) as data
from pg_class c join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r' and n.nspname not in ('pg_catalog', 'information_schema')
order by pg_total_relation_size(c.oid) desc
limit 20;

Sessions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- Who is connected and what they are doing
select pid, usename, state, client_addr, application_name, left(query, 80) as query
from pg_stat_activity
where datname = current_database();

-- Cancel a query (the session stays)
select pg_cancel_backend(12345);

-- Kick everyone but yourself (before a DROP or a restore)
select pg_terminate_backend(pid)
from pg_stat_activity
where datname = 'app_db' and pid <> pg_backend_pid();

-- Or, since PostgreSQL 13, directly:
drop database app_db with (force);

-- Uptime
select date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

Read-only user

On 14 or later, a predefined role solves it for every schema, including tables created tomorrow:

1
2

create user reporting with password '…';
grant pg_read_all_data to reporting;

On earlier versions, or if you want to limit it to one schema, the classic path:

1
2
3
4
5
6
7
8
9

grant connect on database app_db to reporting;
grant usage on schema public to reporting;
grant select on all tables in schema public to reporting;
-- future tables: only covers those created by the role that runs this (or FOR ROLE app_owner)
alter default privileges for role app_owner in schema public grant select on tables to reporting;

-- Undo
revoke all on all tables in schema public from reporting;
alter default privileges for role app_owner in schema public revoke select on tables from reporting;

alter default privileges does not touch what already exists and only applies to objects created by the given role: if another account creates tables, reporting will not see them. Hence the value of pg_read_all_data.

Passwords, timezone, and psql

1
2
3
4
5

\password app_user                                  -- from psql, without leaving the password in the history
alter user app_user with password '…';

alter database app_db set timezone to 'America/Mexico_City';   -- new connections to that database
alter role reporting set timezone to 'UTC';                    -- new sessions of that role

1
2

psql -v ON_ERROR_STOP=1 -f data.sql app_db      # a script that stops at the first error
psql -P pager=off app_db                        # no pager (or \pset pager 0 inside)

And inside psql, \x auto (vertical columns when they do not fit) and \timing.

Minimal maintenance

1
2

vacuum (verbose, analyze) orders;
reindex table concurrently orders;               -- 12+, without blocking writes

VACUUM FULL rewrites the table and locks it meanwhile; to reclaim space in production, pg_repack.

Traps

  • n_live_tup is an estimate: freshly analyzed it is faithful; on a table with heavy churn it can drift until the next autoanalyze.
  • pg_read_all_data does not bypass RLS and grants no DDL; it is reading tables, views, and sequences, nothing else.
  • pg_terminate_backend cuts transactions: the application sees an error and reconnects. To only abort a query, pg_cancel_backend.
  • alter role … set timezone applies to new sessions; the one already open stays the same.
  • quote_ident/format('%I') whenever you build SQL with table names: a name with capitals or dashes breaks the query without them.

See also