postgres-impl-schema-archaeology

Quick Reference :

Schema archaeology is the disciplined inspection of an existing database : its tables, columns, constraints, indexes, sizes, dependencies, and waste. Two introspection surfaces exist :

pg_catalog          PostgreSQL-specific, complete, fast. The default tool.
information_schema  SQL-standard, portable across vendors, slower, incomplete.

ALWAYS use pg_catalog for inspection inside PostgreSQL. Use information_schema ONLY when the same query must also run on another SQL database.

The fastest first pass is psql meta-commands :