The short version
information_schema is the SQL-standard way to query metadata -- portable across databases, slower, limited to what the standard defines. pg_catalog is PostgreSQL-specific -- faster, more detailed, has everything. Use information_schema for simple cross-DB queries. Use pg_catalog when you need Postgres-specific details or performance.
Head-to-head comparison
| Query | information_schema | pg_catalog |
|---|---|---|
| List tables | SELECT table_name FROM information_schema.tables | SELECT relname FROM pg_class WHERE relkind = 'r' |
| List columns | SELECT column_name, data_type FROM information_schema.columns | SELECT attname, format_type(...) FROM pg_attribute |
| List indexes | SELECT indexname FROM information_schema.statistics | SELECT indexrelname FROM pg_stat_user_indexes |
| Table size | Not available | SELECT pg_total_relation_size(oid) |
| Table owner | Not available | SELECT pg_catalog.get_owner(c.oid) |
When to use which
Use information_schema when you need portable SQL, simple queries, or readable syntax. Use pg_catalog when you need Postgres-specific info (owners, sizes, permissions), better performance, or internal system tables.
FAQ
Can I query pg_catalog from MySQL?
No. pg_catalog is PostgreSQL-specific.
Which one does pg_dump use?pg_catalog. It needs Postgres-specific details like table OIDs and ACLs.
Can I see system tables from information_schema?
No. Use pg_catalog.pg_stat_activity for active queries, pg_catalog.pg_locks for locks.