PostgreSQL already knows how to parse SQL, track object dependencies, and understand your schema. Most tools that work with schemas reimplement this from scratch. What if you just asked Postgres instead?

This talk digs into the techniques that make that possible. We’ll start with the shadow database pattern: applying schema files to a temporary PostgreSQL instance and letting Postgres handle all parsing and validation. Then we’ll explore pg_depend and the system catalogs, where PostgreSQL tracks that your view depends on a function, which depends on a table, which depends on a custom type. I’ll show the exact catalog queries that extract this dependency graph, the edge cases that make it interesting (extension-owned objects, implicit sequences, array types, function bodies that pg_depend can’t see), and how to turn it all into a correct topological ordering for migration generation.

I learned this while building pgmt, a tool that diffs PostgreSQL schemas to generate migrations. But the techniques apply to anything that needs to understand a Postgres schema -- linters, drift detectors, visualization tools, CI validation -- and they let you build on Postgres’s own knowledge instead of reinventing it.