PostgreSQL System Tables

Important System Catalogs

SELECT * FROM pg_tables
WHERE schemaname = 'public';

SELECT * FROM pg_indexes
WHERE tablename = 'users';

SELECT usename, usesuper
FROM pg_user;

SELECT datname
FROM pg_database;

🔹 information_schema.tables

The information_schema.tables view provides standardized information about all tables in the database. It's portable across different SQL databases and easy to query.

-- List all tables in public schema
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Get table details
SELECT 
    table_catalog,
    table_schema,
    table_name,
    table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- Count tables by schema
SELECT 
    table_schema,
    COUNT(*) as table_count
FROM information_schema.tables
GROUP BY table_schema;

table_name | table_type
-----------+------------
orders     | BASE TABLE
products   | BASE TABLE
users      | BASE TABLE

🔹 information_schema.columns

This view contains information about all columns in database tables, including data types, nullability, and default values. Essential for understanding table structure.

-- List all columns in a table
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- Find columns by data type
SELECT 
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE data_type = 'integer'
AND table_schema = 'public';

-- Get column character limits
SELECT 
    column_name,
    character_maximum_length
FROM information_schema.columns
WHERE table_name = 'products'
AND character_maximum_length IS NOT NULL;

column_name | data_type | is_nullable
------------+-----------+-------------
id          | integer   | NO
username    | varchar   | NO
email       | varchar   | YES
created_at  | timestamp | YES

🔹 pg_tables

pg_tables provides PostgreSQL-specific information about tables, including ownership and tablespace details. More detailed than information_schema for PostgreSQL-specific features.

-- List all user tables
SELECT 
    schemaname,
    tablename,
    tableowner,
    hasindexes,
    hasrules
FROM pg_tables
WHERE schemaname = 'public';

-- Find tables with indexes
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND hasindexes = true;

-- Get table ownership info
SELECT 
    tablename,
    tableowner
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

tablename | tableowner | hasindexes
----------+------------+------------
users     | postgres   | t
products  | postgres   | t
orders    | postgres   | t

🔹 pg_indexes

pg_indexes shows all indexes in the database with their definitions. Use this to understand index structure and optimize query performance.

-- List all indexes
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- Find indexes on specific table
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- List unique indexes
SELECT 
    tablename,
    indexname
FROM pg_indexes
WHERE indexdef LIKE '%UNIQUE%'
AND schemaname = 'public';

tablename | indexname        | indexdef
----------+------------------+---------------------------
users     | users_pkey       | CREATE UNIQUE INDEX...
users     | idx_users_email  | CREATE INDEX...

🔹 pg_constraint

pg_constraint contains information about all constraints including primary keys, foreign keys, unique constraints, and check constraints in your database.

-- List all constraints
SELECT 
    conname as constraint_name,
    contype as constraint_type,
    conrelid::regclass as table_name
FROM pg_constraint
WHERE connamespace = 'public'::regnamespace;

-- Find foreign key constraints
SELECT 
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

-- List primary keys
SELECT 
    tc.table_name,
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY';

constraint_name | constraint_type | table_name
----------------+-----------------+------------
users_pkey      | p               | users
orders_user_fk  | f               | orders

🔹 pg_stat_activity

pg_stat_activity shows current database connections and running queries. Essential for monitoring database activity and troubleshooting performance issues.

-- View active connections
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active';

-- Count connections by database
SELECT 
    datname,
    COUNT(*) as connection_count
FROM pg_stat_activity
GROUP BY datname;

-- Find long-running queries
SELECT 
    pid,
    now() - query_start as duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes';

-- Kill a specific connection
-- SELECT pg_terminate_backend(pid)
-- WHERE pid = 12345;

pid   | usename  | state  | query
------+----------+--------+------------------
12345 | postgres | active | SELECT * FROM...

🔹 pg_database

pg_database contains information about all databases in the PostgreSQL cluster:

-- List all databases
SELECT 
    datname as database_name,
    pg_size_pretty(pg_database_size(datname)) as size,
    datcollate as collation,
    datconnlimit as connection_limit
FROM pg_database
ORDER BY datname;

-- Get current database info
SELECT 
    current_database() as current_db,
    current_schema() as current_schema;

-- Database size statistics
SELECT 
    datname,
    pg_database_size(datname) as size_bytes,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

database_name | size    | connection_limit
--------------+---------+-----------------
myapp_db      | 125 MB  | -1
postgres      | 8 MB    | -1

🔹 Useful System Queries

Common queries for database administration and monitoring:

-- Get table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- List all sequences
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public';

-- View all views
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'public';

-- Check database version
SELECT version();

-- Get server settings
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('max_connections', 'shared_buffers', 'work_mem');