Elizabeth Christensen shows some of the ways to view internal information in PostgreSQL:
Postgres has an awesome amount of data collected in its own internal tables. Postgres hackers know all about this – but software developers and folks working with day to day Postgres tasks often miss out the good stuff.
The Postgres catalog is how Postgres keeps track of itself. Of course, Postgres would do this in a relational database with its own schema. Throughout the years several nice features have been added to the internal tables like psql tools and views that make navigating Postgres’ internal tables even easier.
Today I want to walk through some of the most important Postgres internal data catalog details. What they are, what is in them, and how they might help you understand more about what is happening inside your database.
Click through for an overview of catalog tables and catalog views (similar to SQL Server’s system tables and Dynamic Management Views, respectively).