PostgreSQL system catalogs schema diagrams
Some time ago, someone asked on a PostgreSQL mailing list whether there was a diagram of the PostgreSQL system catalogs. There wasn’t at the time. Something like that used to be included in the PostgreSQL documentation, but it was never updated, and so it was eventually removed.
I was fond of a tool called
postgresql_autodoc that could
create schema diagrams automatically by querying catalog information.
I was thinking why we couldn’t use that on the system catalogs, too.
The problem was that the system catalogs didn’t have real constraints
defined, and so a tool like
postgresql_autodoc wouldn’t be able to
figure out the associations between tables. But could we not fix
First, I set out to add primary keys and unique constraints to system catalogs. Before that, they only had unique indexes, which do the same thing in effect, but they are not a declaration of intent. So now, almost all system catalogs have a genuine primary key constraint defined. This should also help GUI tools that want to browse and update tables but get upset if there is no primary key defined.
I had originally wanted to later add foreign key constraints as well,
but that turned out to be a bit more complicated. So others devised
We don’t declare full constraints, but there is a system function
pg_get_catalog_foreign_keys) that you can get the foreign key
With all that necessary metadata available, you can now query it. I just wrote my own small program to produce a Graphviz source file. You can see the results here: https://petere.github.io/pgcatviz/. As you can see, the results are pretty big and unwieldy. Maybe someone wants to play with the Graphviz formatting options a bit. (Feel free to send pull requests.) But in any case, all the information is there now.