Well, thank you so much.
So, over the last couple of years, I've been leaning more and more into a lot of, like,
I don't even say more advanced, but like, beyond tables in Postgres.
So, like, views and functions and triggers and stuff.
And I hit a wall when I was trying to figure out the best way to migrate my schema.
And so, I started to go digging into how I wanted to do that.
And I ended up rolling my own.
This talk is not about the tool that migrates my schema.
But it's about how I was able to solve a really critical question.
As part of that, which is, how do I end the tool that truly understands the Postgres schema?
Not just like running the, you know, SQL files, not running migrations against it, not storing it.
But like, actually understanding it.
What objects exist? How do they relate? And what depends on what?
So, I know I had two options.
I could write an SQL parser.
I could parse those, create table, create function, create view, all of that.
Handle all the edge cases and keep up to date with every Postgres release.
Or, option B, I could just ask Postgres.
It already parses SQL. That's kind of its whole job.
And so, what if I let it do all the work and ask it what it knows?
And also, I really, really didn't want to write a SQL parser.
Because, like, what I have wanted to parse this, I mean, there's, look at that.
We've got, like, a returns table with, like, four different arguments.
Some of, there's sudden array with a default up there.
There's a bunch of JSONB stuff down below.
And even if you thought you could, like, skip over the whole thing.
Notice that there's, like, an end for the begin atomic.
And then there's also an end for the case statement.
I knew I would just be messing the whole thing up the whole time.
So, I didn't want to go down that path.
And if you look at, like, what Postgres knows about a function like that.
You know, it's got the name, it's got the argument types.
It's got all these names. It's got the whole tree, whether it was stable.
And that's kind of the information that I want to have.
So, how do I go about actually setting that up?
So, I was pretty simple.
And I kind of leaned on this, like, shadow database pattern,
which I've, so elegantly put up here in three steps,
which is, you start with actual raw SQL files,
so whatever you got, one file, many files, just whatever.
And then you need some form of, like, a temporary postgres instance.
So, a container would make sense, but, you know,
if you just have some local running instance to, like, you can use that.
And then you load it all in there, and you start in the system catalogs,
which is super queryable, and you can get back everything you have.
Of course, this does mean that you have to have a running postgres instance,
but I'm kind of assuming you already do if you're doing some work with Postgres.
Or at least have an easy way to access it.
So, there's a whole bunch of system catalogs.
You may have explored some of these before.
I think there's, like, more than 50.
You know, the big ones, PG classes like the big one.
Anything that's, like, a table or, like, table,
S, like, has columns or anything like that.
The attributes or the, you know, columns on there,
I'm not going to be this entire slide, but there's a lot of good stuff in there.
And so, you can start, you know, querying them,
and so you have a little table here, and you say, okay,
what's going to be, you know, what are my columns, what are the defaults,
and, you know, it takes a lot of reading the Postgres docs,
but you can really start to understand some pretty rich details about
about what you're looking at.
And what's really cool is, you know, you're not even looking at the
Create statement that would have created this table,
but, like, I'm sure you can visualize in your head, like,
what it would have looked like.
I think everybody would have had slightly different formatting of what that
Create statement would look like, but the, you know,
the overall vibes are still there.
And so, then if you've probably already done stuff like this before,
so I won't belabor it.
But the interesting bit is when we start getting into dependencies.
Because order matters.
You've probably seen something like this before.
You go to, you know, drop a table, which, you know,
we all do in production all the time.
And Postgres says no, because it's a bunch of stuff depends on it.
Or you try to, like, drop a type, because you want to, you know,
do something with it.
And it, again, says no, there's a whole bunch of dependencies on it.
And so, if I'm trying to generate some TDL for, like,
migrations or scheme a dump or whatever, that order really, really matters.
And you can't create a view that references a function that doesn't exist yet.
And I can't drop a table that a function still needs without using a cascade,
but it's a little scary.
And so, you know, we have this little dependency graph here,
but how do we get it?
Well, it turns out Postgres has the graph,
and it's in something called PG-depend.
And so, you know, Postgres wasn't just complaining
retroactively when you try to drop things.
It is storing the entire dependency graph in PG-depend.
Every dependency is a row in this kind of view here that I've kind of adjusted a little bit,
because those should be object IDs, and I put the names in there for readability here.
But you can see that the pending order account function depends on the pending order's view,
and the pending order's view depends on the order's status,
and that's it.
That's our whole chain, and we can look right there.
And so, from there, to figure out some ordering,
the graph gives you all the edges, and then you can just do a little topological sort.
You go one direction if you need to do pre-ates.
You go the other direction if you need to do some drops.
However, there are a few nuances that you should be aware of.
There was a column a couple slides ago that I kind of glossed over,
and that was this dependency type.
And there's more than just the four I have labeled here,
but I'll go through them kind of quickly.
So N is for normal.
These are like the real dependencies between the objects.
You created something, it depends on something else.
You know, the kind of examples I gave before.
The views depend on a function, it depends on table, whatever.
Now the next one to start getting a little more interesting.
A is automatic.
Those are going to be like other objects that you could drop yourself,
but they're created together.
So like if you have a serial column, it creates a sequence,
or there's a primary key has an index.
So they're created together and they're kind of linked.
And then there's I for internal.
This is going to be stuff like your toast or some of the array types,
which we'll get to in a moment, and then ease our extensions.
And so like if you're querying something and the extension created a function,
then you'll see that here.
And so you start kind of going through these,
and there are some catches, though.
And so PG depend can't see stuff that's in string literals.
By default, Postgres will enforce that the stuff exists at create time.
But you generally can't create a function referencing a table that doesn't exist,
but the dependency agent actually tracked.
You could drop the table later and like PG depend won't care.
And so for some of this stuff, there's really easy fix.
If you're somewhat recent version of Postgres,
which is just used begin atomic.
And then it actually parses the whole query and stores it,
and then it knows all of those things.
It doesn't just sort as a string.
So now we know that the function body depends on a whole bunch of things.
That only works if you're using language SQL.
So if you need PLPGSQL or any of the other things for any other reasons,
then it's back to storing as a string.
And so there's no fix for that.
So you should use bigototomic, whether you can,
but for everything else, you know, know the limits.
And then I started to talk about this a little bit when we're talking about the dependency types.
But there's implicit objects.
So in this example here, you create a table.
We have this serial primary key.
And so behind the scenes, it's creating a constraint.
It's creating an index and it's creating that sequence.
And those are those dependency type A.
At the end of the day, PG depend is mostly just that they're to yell at you
if you try to drop something.
And so these are A's, which means it'll just drop the other things if you do it won't stop you.
But it's relevant information.
And then there's array types.
And these ones have a little bit of a different quirk.
So like every type, you get as a array sibling.
So if you create this order status, what you're actually going to see in the type catalog is you've
loaded status.
And then it's generally prefix with an underscore.
But I think there's some sort of like collision detection things.
So maybe it's different.
Definitely don't like do like if it starts with an underscore thing.
That's that's bad.
But it creates these two.
And that's that internal dependency type.
And so what's interesting is now if I create a table that has something that uses that array type.
Now I have to make two different jumps on PG depend.
Because technically the table depends on that internal array type.
And then internal array type looks at the actual type.
So you've got to be able to follow those jumps there.
And it's a somewhat similar story depending on what you need to do with the extension types as well.
And so you also want to be a little bit honest with some of the trade-offs with this approach.
So you do need money postgres.
We talked about that one already.
You do need to build support for like every object type explicitly.
There's a lot of catalog tables and there's a lot of different things that you might not know about for all the different types of objects.
So you kind of have to actually read the docs and codify all those things.
So like this isn't magic.
You're still offloading all the parsing to postgres.
But you still need to build the queries that understand all those things.
The postgres matters. Postgres versions do matter catalogs do evolve.
They don't evolve as quickly as the I would say like the actual like language like syntax does.
But things do change like that as well body.
Does require a later version of postgres.
And so sometimes we'll need to do some switching there if you try to support multiple versions.
And then most importantly and this kind of relates to all these points you just review whatever out.
But you have don't try to do anything super magical because there might be some feature that you didn't know existed and so you're not looking at.
So don't just blindly trust it because postgres is doing the parsing.
Like you still have to do some critical thinking of your own.
So yeah overall we've got the shadow database let postgres to the parsing.
We've got to clear the catalogs.
Look through all the catalogs read all the docs implemented all PG depend is your friend for basically anything that requires ordering ever.
So that's going to be you know the graph you can filter on the dependency types to know which ones you care about and how you want to treat them.
Definitely know the blind spots and watch out for those implicit objects and then sort the whole thing.
And so to kind of talk about the thing that IT's in the beginning I did build a tool that used all of this.
This was my migration tool that needed to have really intimate knowledge of all of the different objects in the tree.
So again this isn't to talk about that but it is schema as code for postgres so you add to the SQL files.
We generate the diff from there and then let you review it and do everything you'd want to do there.
And so you can find it on GitHub or the little website there you know give me a star or an issue or maybe even a PR.
But of course all of these things we just talked about aren't just for migrations you could use it for drift detection some sort of schema lenders CI validation you know visualizations are always super fun.
And that is so nervous about fitting under 20 minutes that I was able to get under 15 so that's the whole idea.
Postgres knows your schema it parses SQL that's its whole point tracks dependencies and if you understand the types and columns then you don't have to reimplement it just ask postgres.