스크립트
00:00:00So it turns out all of those developers saying oh bro just use Postgres for everything are even more
00:00:04correct with the upcoming release of Postgres 19 which includes native support for graph queries
00:00:10and this is a really cool feature. So in today's video I want to cover all of the headline features
00:00:16for Postgres 19 and also take a deeper look at graph queries specifically because I think it
00:00:21can fundamentally change the way that we design our queries and use Postgres.
00:00:30So the first one up is something called on conflict do select. If you want to insert a row only if it
00:00:35does not already exist and either way you want to return that row back as a result usually you'd need
00:00:40two queries for this an insert and a select and this is a really common workflow. So here in the example
00:00:45we say we're going to insert into users email and name and then we give those values and then at the
00:00:50end we say on conflict for email do nothing returning star. Do nothing gives you nothing back when the row
00:00:56already exists so you follow it up with the select but together this is not atomic. With 19 you can do this
00:01:03with just one query. Insert into users giving the email a name and then we say on conflict email do select
00:01:11returning star. And if you want to modify in the same query we can say insert into users and then we give
00:01:16the values again on conflict for the email do select for update returning id and name. And because it's a single
00:01:23statement it's atomic you either insert it or you select it every single time and this is such a
00:01:29common use case it's going to be really useful. So next up is graphs but if you're enjoying this then
00:01:34why not subscribe to better stack to stay up to date with the latest in tech. Now as I said before this
00:01:39is the headline feature in my opinion. So say you've got the usual schema for a shop you've got customers
00:01:44orders and products plus the two join tables that wire them all together and you want to know which
00:01:50product a given customer actually bought. In SQL it's a chain of joins across all five tables and that's
00:01:56pretty manageable here but it does get pretty ugly quickly for more complex relationships. Well now we
00:02:02can query all of this as a graph so that complex join syntax now becomes a new graph syntax instead. And
00:02:08with complex joins in particular you're going to see a really big benefit here. So we're inside our database
00:02:14viewer here and you can see we've got all of our tables we've got products orders events we've got
00:02:20customers as well and then we have the pivot tables to connect all of this data. So we've got order items
00:02:25and customer orders and now let's try and run a query against all of these tables. So say for example
00:02:30i've got this user here called alice i want to work out all of the things that alice has ordered. To do
00:02:35this i'd have to go through a bunch of different join queries to connect up all of those tables. So i'd run this
00:02:41query which has got four separate join statements within it and whilst this is not necessarily hard
00:02:46to write it is very ugly but if we run this you can see that we can see all of the things alice has
00:02:51ordered the mechanical keyboard and ergonomic mouse but now we can use graph syntax to massively simplify
00:02:57this. So we'll replace all of this with the new graph syntax and we'll execute this again and you can
00:03:03see we get the same results and if we line all of this up it starts to become easier to read so you can see
00:03:07we're just going from customers to customer orders to orders to order items to products. So you can
00:03:13basically just follow this simple flow to get through the entire graph which in my opinion is much much
00:03:19easier to read. If you want to select multiple columns as well we can do that we have the same
00:03:23graph query at the top here but then we can define columns at the bottom and then we select all the
00:03:27columns that we want to display and executing that we get the results at the bottom. Now none of this
00:03:32will work unless we've actually created the graph in the first place so the query that i've used to
00:03:37generate that graph is here we say create property graph and then we give it a name my shop and then
00:03:42we outline the vertex tables which is going to be customers orders and products so these are the
00:03:47things that actually hold the data and then we have the edge tables and these are the things that
00:03:52actually make the connections so the pivot tables in our case those would be customer orders and order
00:03:57items and the syntax for this is super simple we say for customer orders the source is customers and the
00:04:02destination is orders and for order items the source is orders and the destination is products and with
00:04:08that in place postgres will now be aware every time we run a graph query of how those relationships
00:04:14are defined. For this to work you need to create a graph but this doesn't create new tables or anything
00:04:19you just point at the five tables you've already got the three that actually hold the data become
00:04:23vertices and the two join tables become the edges it works much more like a view so your previous schema
00:04:29remains completely unchanged you just get this added functionality on top so here we're going to
00:04:35say create property graph we'll call it my shop and then we start to describe how that relationship
00:04:40actually works and this is where we're doing all the work for the graph and it means that the queries
00:04:45can be much much thinner than the respective join syntax so is this a neo4j replacement well if you want a
00:04:52graph database because you needed graph storage and traversal performance then no and a dedicated graph
00:04:58database is still probably the better choice but if you want this because writing 10 table joins in
00:05:03sql is painful and ugly then it's definitely going to benefit you and it's going to be much nicer in
00:05:09comparison the third new feature is repack and this one's about getting disk space back postgres
00:05:15never updates a row in place it writes a new version and leaves the old one behind and vacuum only
00:05:21marks that dead space reusable so your disk usage never actually comes down repack rewrites the whole
00:05:27table into a fresh file with none of the wasted space in it and that's what hands the space back to
00:05:33the operating system you could actually already do this with vacuum full but that locks the table for
00:05:38the entire rewrite so for anything massive you just never run it and that's why people would install
00:05:42extensions like pg repack but now we get it built into postgres directly use the concurrently keyword with
00:05:49this and it keeps the table readable and writeable while repack works one thing to watch here though
00:05:54you need enough free disk space for a second copy of the table and all of its indexes so you need
00:06:00the spare space to actually reclaim more space so this is not an exact replacement for pg repack but for
00:06:06a normal table it does the job without the extension okay now we're going to do a quick fire round for
00:06:11the remaining features in the 19 release first up is query hints postgres decides how to actually run your
00:06:17query and it can change its mind over time so a query that's been fine for a year suddenly gets slow
00:06:22and nothing in your code changed a new module called pg plan advice lets you capture the plan while it's
00:06:28still fast and pin it so it always stays that way jit is now off by default postgres compiles heavy queries
00:06:36down to machine code but it decided when to bother from the planner's cost estimate and the release
00:06:41notes say that that costing was actually unreliable so it was firing on queries that didn't actually
00:06:46need it also vacuum can now clean up your indexes with several workers in parallel so that's less time
00:06:52spent vacuuming a big table you do have to turn this on yourself though you know when you add a column to
00:06:58do a select and then you have to add the same column to the group by as well it does all of that for you
00:07:03and the copy keyword can now export straight to json so if you've been dumping csv and converting it
00:07:09afterwards you can stop doing that so that's postgres 19 beta 2 landed in july and the final release is
00:07:15expected around september or october so if you're running anything older it's worth pulling the beta down
00:07:21and testing against it now and if you want to see more about postgres you can actually check out
00:07:25a breakdown of pg durable it puts durable workflows directly inside postgres i've been warren from
00:07:31betterstack thank you for watching and i'll see you in the next one