information_schema vs pg_catalog: Which Should You Query?

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 queries you might run on MySQL too. Use pg_catalog when you need Postgres-specific details or performance.

What each one is

information_schema

A set of views defined by the SQL standard. Every relational database (Postgres, MySQL, SQL Server) has the same views with the same column names.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public';

Pros: Portable. Readable. Standard.

Cons: Slower. Missing Postgres-specific types (arrays, JSONB, ranges). Can't see internal system tables.

pg_catalog

PostgreSQL's internal catalog. It's a schema that's automatically search path and contains tables, views, and functions that describe every object in the database.

SELECT c.relname AS table_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

Pros: Faster. More detailed. Has Postgres-specific types. Can see system tables.

Cons: Not portable. Harder to read. Syntax is verbose.

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
List constraints	SELECT constraint_name FROM information_schema.table_constraints	SELECT conname FROM pg_constraint
List foreign keys	SELECT ... FROM information_schema.key_column_usage	SELECT ... FROM pg_constraint WHERE contype = 'f'
Table size	Not available	SELECT pg_total_relation_size(oid)
Table owner	Not available	SELECT pg_catalog.get_owner(c.oid)
View definitions	Not available	SELECT definition FROM pg_views

When to use which

Use information_schema when:

• You need portable SQL (might run on MySQL/SQLite too).
• You're writing a simple query and don't care about performance.
• You want readable, standard syntax.
• You're building a tool that works across databases.

-- Simple: list all tables in the public schema
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE';

Use pg_catalog when:

• You need Postgres-specific info (table owners, indexes, sizes, permissions).
• You're querying large databases pg_catalog is faster.
• You need internal system tables (pg_stat_activity, pg_locks, etc.).
• You're building a Postgres-only tool.

-- See active queries with full details
SELECT pid, usename, application_name, query, state
FROM pg_catalog.pg_stat_activity
WHERE state = 'active';

Common queries

List all columns with types (information_schema)

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

List all columns with types (pg_catalog)

SELECT c.relname AS table_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
       NOT a.attnotnull AS nullable
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

List foreign keys

-- information_schema
SELECT
  kcu.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.key_column_usage kcu
JOIN information_schema.constraint_column_usage ccu
  ON kcu.constraint_name = ccu.constraint_name
WHERE kcu.constraint_type = 'FOREIGN KEY';

Show table sizes

-- pg_catalog only
SELECT
  c.relname AS table_name,
  pg_catalog.pg_size_pretty(pg_catalog.pg_total_relation_size(c.oid)) AS total_size
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_catalog.pg_total_relation_size(c.oid) DESC;

See who's connected right now

-- pg_catalog only
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_catalog.pg_stat_activity
WHERE backend_type = 'client backend';

Performance note

For simple metadata queries on small databases, the performance difference doesn't matter. But on large databases (thousands of tables, millions of rows), pg_catalog can be 2-10x faster because it's indexed internally.

FAQ

Can I query pg_catalog from MySQL?

No. pg_catalog is PostgreSQL-specific. If you need cross-database portability, stick with information_schema.

Which one does pg_dump use?

pg_catalog. It needs Postgres-specific details like table OIDs, ACLs, and storage parameters that information_schema doesn't expose.

Do tools like Prisma or Drizzle use one over the other?

They use both. Prisma uses information_schema for schema introspection. Drizzle uses pg_catalog for more detailed Postgres metadata. Both fall back to the other when one doesn't have the info they need.

Can I see system tables (pg_stat_activity, pg_locks) from information_schema?

No. System tables are only in pg_catalog. Use pg_catalog.pg_stat_activity to see active queries, pg_catalog.pg_locks to see locks, etc.