En corto

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

-- Filas exactas de todas las tablas de un esquema, en una sola consulta
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;

-- Estimación instantánea (no lee las tablas)
select relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc;

-- Usuario de solo lectura (PostgreSQL 14 o superior)
create user reporting with password '…';
grant pg_read_all_data to reporting;

Contar filas

count(*) exacto sobre cada tabla exige recorrerla entera; en una base de cientos de GB tarda. Lo que casi siempre quieres es la estimación que mantiene el autovacuum, que se actualiza en cada ANALYZE y suele estar a un par de puntos del real:

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;

Cuando necesitas el número exacto de todas las tablas sin escribir un DO en PL/pgSQL, el truco de query_to_xml: ejecuta un count(*) dinámico por tabla, devuelve el resultado como XML y xpath extrae el número. Es SQL plano, funciona desde cualquier cliente, y se puede filtrar por esquema o por nombre. Solo recuerda que corre los conteos en serie: en tablas grandes, paciencia.

Tamaños

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,   -- datos + índices + 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;

Sesiones

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

-- Quién está conectado y qué hace
select pid, usename, state, client_addr, application_name, left(query, 80) as query
from pg_stat_activity
where datname = current_database();

-- Cancelar una consulta (la sesión sigue)
select pg_cancel_backend(12345);

-- Expulsar a todos menos a ti (antes de un DROP o un restore)
select pg_terminate_backend(pid)
from pg_stat_activity
where datname = 'app_db' and pid <> pg_backend_pid();

-- O, desde PostgreSQL 13, directo:
drop database app_db with (force);

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

Usuario de solo lectura

En 14 o superior, un rol predefinido lo resuelve para todos los esquemas, incluidas las tablas que se creen mañana:

1
2

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

En versiones anteriores, o si quieres limitarlo a un esquema, el camino clásico:

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;
-- tablas futuras: solo cubre las que cree el rol que ejecuta esto (o FOR ROLE app_owner)
alter default privileges for role app_owner in schema public grant select on tables to reporting;

-- Deshacer
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 no toca lo que ya existe y solo aplica a objetos creados por el rol indicado: si otra cuenta crea tablas, reporting no las verá. De ahí el valor de pg_read_all_data.

Contraseñas, timezone y psql

1
2
3
4
5

\password app_user                                  -- desde psql, sin dejar la contraseña en el historial
alter user app_user with password '…';

alter database app_db set timezone to 'America/Mexico_City';   -- nuevas conexiones a esa base
alter role reporting set timezone to 'UTC';                    -- nuevas sesiones de ese rol

1
2

psql -v ON_ERROR_STOP=1 -f data.sql app_db      # un script que se detiene en el primer error
psql -P pager=off app_db                        # sin paginador (o \pset pager 0 dentro)

Y dentro de psql, \x auto (columnas verticales cuando no caben) y \timing.

Mantenimiento mínimo

1
2

vacuum (verbose, analyze) orders;
reindex table concurrently orders;               -- 12+, sin bloquear escrituras

VACUUM FULL reescribe la tabla y la bloquea mientras dura; para recuperar espacio en producción, pg_repack.

Trampas

  • n_live_tup es una estimación: recién analizada es fiel; en una tabla con mucho churn puede desviarse hasta el siguiente autoanalyze.
  • pg_read_all_data no salta RLS ni concede DDL; es lectura de tablas, vistas y secuencias, nada más.
  • pg_terminate_backend corta transacciones: la aplicación ve un error y reconecta. Para solo abortar una consulta, pg_cancel_backend.
  • alter role … set timezone aplica a sesiones nuevas; la que ya está abierta sigue igual.
  • quote_ident/format('%I') siempre que construyas SQL con nombres de tablas: un nombre con mayúsculas o guiones rompe la consulta sin ellos.

Ver también