Table Of Contents
Intro
If you've worked with SQL before, you're familiar with writing queries to ask questions about the data in your database. Give me all the people that user A follows. Tell me how many sales we made in the last week.
Databases are good for asking questions about your data, but they also store metadata about the data in your database. PostgreSQL in particular stores data about Table names, schemas, indexes, views and much more. And it's just a few sql queries away from you.
Querying Metadata in PostgreSQL
PostgreSQL stores metadata for the database in 3 areas: The Information Schema, System Views, and System Catalogs.
The Information Schema
Postgres' Information Schema "consists of a set of views that contain information about the objects defined in the current database." A database schema is essentially a way to put tables into their own group. By default, any tables you create in Postgres are part of a database schema called "public".
Though you'll typically query your tables like:
SELECT * FROM my_table LIMIT 10;
You can also include the schema as part of your query like so:
SELECT * FROM public.my_table LIMIT 10;
The information schema, as its name suggests, is a schema in your Postgres Database with tables that store data about the data in your database. You can write queries against the tables in this schema to learn more about your database(s).
You can find the full list of tables in the information schema in the Postgres Docs, but I'm sharing a few that are handy.
-- Find all the schemas in your database
select
distinct table_schema
from information_schema.tables;
-- Find all the tables in your schema
select table_name
from information_schema.tables
where table_schema = 'public';
-- Information about the columns in a table
select
column_name,
data_type,
column_default,
is_nullable
from information_schema.columns
where table_name = 'sales';
-- Name of all the constraints for a given table
select *
from information_schema.constraint_column_usage
where table_schema ='public'
and table_name ='sales';
System Views
Postgres System Views are a collection of views that also have metadata. These tables are listed as system views. All of them are part of the pg_catalog
schema.
-- Find indexes in a schema
select
tablename,
indexname,
indexdef FROM pg_catalog.pg_indexes where schemaname = 'public';
-- Find indexes for a table
select
tablename,
indexname,
indexdef
FROM pg_catalog.pg_indexes
where schemaname = 'public' AND tablename='mytable';
You can also list materialized views in your database with:
select
schemaname,
matviewname,
ispopulated,
definition
FROM pg_matviews
where schemaname='public';
System Catalogs
"The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information."
Like Systems Views, the system catalog tables are also part of the pg_catalog
schema. This schema, just like the information_schema
exists by default in your Postgres database.
If I want some estimates about the size of a table, the System Catalog has a table, pg_class that lets me inspect the table size and other attributes:
select
relname,
relpages,
reltuples
FROM pg_catalog.pg_class
WHERE relname='sales';
Output:
relname | relpages | reltuples
---------------+----------+-----------
sales | 8 | 568
Or, with some extra functions, you can calculate the size of your tables and indexes*:
SELECT relname, pg_relation_size(oid) as bytes,
pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relnamespace = 'public'::regnamespace
ORDER BY relname;
*Query from Designing high-performance time series data tables on Amazon RDS for PostgreSQL
relname | bytes | pg_size_pretty
--------------+---------+----------------
sales | 1638400 | 1600 kB
sales_id_seq | 8192 | 8192 bytes
Putting It All Together
Queries can be really powerful when you join tables across these schemas.
For example, you can list all of the constraints of a table. Here's a query
that will give you all of the constraints for a given table in your database.
select table_name, column_name, constraint_name,
case
when pgcc.contype = 'p' then 'Primary Key Constraint'
when pgcc.contype = 'f' then 'Foreign Key Constraint'
when pgcc.contype = 'c' then 'Check Constraint'
end as constraint_type
, pg_get_constraintdef(pgcc."oid" , true) as constraint_definition
from information_schema.constraint_column_usage iccu
inner join pg_catalog.pg_constraint pgcc on iccu.constraint_name = pgcc.conname
where table_name = 'parent';
Output:
table_name | column_name | constraint_name | constraint_type | constraint_definition
------------+-------------+------------------------------+------------------------+-----------------------------------------------
parent | id | parent_pkey | Primary Key Constraint | PRIMARY KEY (id)
parent | id | child_parent_id_fkey | Foreign Key Constraint | FOREIGN KEY (parent_id) REFERENCES parent(id)
parent | parent_name | name_less_than_20_characters | Check Constraint | CHECK (length(parent_name::text) <= 20)
If you want to find more information about the Information Schema or other Postgres System Tables you can find them here:
Information Schema
System Catalogs
System Views
Top comments (0)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.