Django and PostgreSQL: An Even-Closer Union by Christophe Pettus

Speaker 1: Come on, no.

Speaker 2: Cool. Okay. So hi, I'm um Birk. I'm Christoph Pennis. I'm the CEO of PostgresQL Experts Inc. We're in Alameda, California. You can look at the slides will be up in about a week at thebuild. com and my Twitter handle is XOF. I was an early adopter. And there's my um uh email address. Okay. So um there's so much stuff here. 1. 7 introduced native migrations. And but what we're specifically talking about today is 1. 8 and 1. 9 extended um introduced an extended uh Django Contrib Postgres, which is basically what we're talking about today. Lots of features, not much time. And but we have to thank Mark Tamlin who did most of the work on this and deserves the thanks of a grateful nation. Okay.

Speaker 2: The big stuff. Array field, range field, h sort field, JSON field, whee, array field, okay. Um arrays are first class types in Postgres QL. Um and array field allows you to use them directly. You can store an array as a single field in Postgres, and it's a very handy feature. They map into Python lists, you probably could have guessed that. Remember, PostgreSQL arrays are of homogeneous type. They're good old Fortran style arrays. So they're integer arrays or their character arrays or they're something like that. They're not you can they they can't be homogeneous uh heterogeneous the way uh Python lists can be. So um the and um post-groscular multidimensional arrays are rectangular. Um I realize most people in the world were not born before languages discarded this as a bad idea, but

Speaker 2: um The multidimensional arrays are rectangular, so it's through it's four entries wide by three entries high. It's a grid. Individual entries can be null though. So you can kind of do ragged arrays that way. Um when you have a once you have one of these, you can do queries on them. The basic one is contains. So it matches the field array on the left, contains all of the entries of the list on the right. So ABC contains A B, but do you Did you? You probably could have figured that out. And order is not important on contains. You have contains by, which basically goes the other way. uh matches uh uh the list on the right with all the entries on the left. So da -da-da-da. And order is not important here either.

Speaker 2: Um overlaps works the way you'd probably expect. So um ABC overlaps A and D, but um ABC does not overlap D because they have no members in common. You also can take the length of it, which returns the length of the field on the left as an integer. The so you can do queries like this where you um you're querying for everything of length two or whatever you might want. Remember that in unless and we'll talk about indexing on these things because that's where things get really fun You if you the um if you create unless you create an expression index, you're going to do a full table scan. So it'll pick up every entry and rattle it checking its length. We also have transforms such as I want to find everything where

Speaker 2: the first element, the zero width element, is a. The one nice thing about this is if there's if it it's walking through this and you're saying f find me the 12th element and there's an array field that only has seven elements in it, it'll just say false. It won't return an error, so you don't have to write fancy trapping code for that. One downside is unless you use Quargs, you can't specify the index, the zero the zero in this case programmatically because it's baked into the um the parameter Except for the cards. You could also do slices. So find me everything for zero zero entry zero or one, match that Or find everything we're zero or two, contain this. So you can get arbitrarily complex expressions and really slow your database down.

Speaker 2: Unless you index them. Yay. So you just specify db index equals true and everything is solved, right? Wrong. This creates a B tree index which is pretty useless on array and other non-scalar types. So Postgres has lots of index types. Most people are only familiar with B tree indexes because if you say db index equals true, which everyone does too much, stop it , then what you get is a basic B tree index. B trees are nice. Interestingly enough, no one actually knows why they're called B trees. They're like balanced or Boeing or something like that. But they're really, they're they're kind of like a really good solution to the problem. They're fast, they're compact, they provide total ordering. Um but they're not perfect because first of all uh B tree indexes require a totally ordered type.

Speaker 2: And you know a points and arrays and h store, total ordering doesn't make sense. I mean two points Which one's greater than this point? I mean you can make something up, but there's no intrinsic meaning to that. So never feel fa fail. When in DAO Postgres has your feature wit waiting for you, um it'll do just in JIT indexes. Um GIN indexes are used for t uh types that contain keys and values like arrays and h store and JSON B. And just are for things that this is my really big I sound so smart, even though I'm the world's worth mathematician. Um G just index is you for types of partition in mathematical space like a point or a range or something like that. The nice part about them is once you create them, just like any index, they just work. If it can optimize a query, it will.

Speaker 2: You don't have to say, oh by the way, on this query, use this index. So indexing array fields, um generally you want a gin index. Um and it accelerates contains contained by and overlaps. It doesn't help with links or slice, sorry. Um But you kind of have to use a raw SQL migration. Who's using Raw SQL migrations? Everyone gratuitously put one into your next push. Just to get familiar with them. You'll love them. They've always spoken well of you. Um so you you throw in this fancy using gin field clause. One downside of Gin Things is they're not free to update. They're actually updated by in a batch when you do a vacuum. And if that makes no sense at all to you, just ask me up the booth. about that. Um

Speaker 2: so don't create one unless you need it. Specifically if it's a small table, like an easy little mapping table, the whole thing's gonna be in memory, so don't don't do this. But if it's a big table, do that. So you say, well, I was promised by an earlier slide that you would show me how to accelerate the search on the length field. That's how you accelerate the search on the length field. What you're doing there is saying I want to create a index on the expression array length field, comma 1. The comma 1 is the dimension, so you're saying on the first dimension of this table. Remember Fortran? It's back. So um and you can index slice you can also do slices. If, for example, this is something you do a lot. Postgres is really smart, and when you use this expression in a query, it'll just find that index and use it.

Speaker 2: Uh just remember also Postgres arrays are one-based, not zero-based, like every other programming language, you know, since well, you know, Postgres is from the 90s. It was different then. Um So why would you use array field? Well, if the underlying data actually is an array, sometimes you want to put arrays in the database. They can be a replacement for a many-to-many table. If you want, we can chat about later about how that um you know hallway track about how you might do that. You can also, and this is very common, use them as a denormalization if you have a really expensive query and you want to stuff that result in the database, and the result of it happens to be an array. Okay, range fields. Range fields just are wonderful. Um they have native range types. They um specif they are about a sp

Speaker 2: uh uh they span a range of a scalar type. I can read my own slides. Um for example, one comma eight. is an in four range that includes one through eight. The the brackets, the square brackets mean inclusive. You can also do Parins, which are exclusive, which means it only goes up through 7, that's really handy if it's like a float, because, or some infinitely divisible type. And that's the default. Um you can omit a bound to mean all values greater than or less than. So dates also have a special infinity value, which Psycho PG does everything it can to handle in a nice way, given that the there's kind of this impedance mismatch between Postgres datetime and Python date time. Psycho PG2, which is the interface library, if you're not familiar with it,

Speaker 2: includes a Python range-based type that handles all of that stuff for you. So you probably when you're using it inside your application code, you want to import that and use that as your actual application type. Um 1. 8 and thus 1. 9 support integer range and big integer range, float range, date time range, and date range. You can do contains, contained by and overlaps. They work pretty much the way you'd expect. You can also do fully less than, fully greater than, so if they're completely disjoint one way or the other. He said, gesturing wildly. And adjacent two is means that they bump up against each other exactly. There are no values missing between them. And then not less than, not greater than, kind of worked the way you expect.

Speaker 2: This is where you use a gist index. Um it looks just like the gin index, only you say gist instead of gin. Um and accelerates everything we've talked about. Woo-hoo! Goes really fast. And why would you use one of these? Well, here's a really hard problem. And we we had no good solution for it until we had range types. So you're writing a booking system. because you know people do things like that. And you say, don't allow two bookings to be inserted into the database for the same room where the dates overlap. Okay? You want a constraint that prevents that situation from arising in the database. With traditional unique constraints, there's no way of expressing that because you're not talking about a unique range, because the ranges could overlap, but they're not equal, so they don't do that. But Postgres has a feature, because Postgres has always has a feature, called constraint exclusion that will save you.

Speaker 2: Constraint exclusion is a generalization of the idea of unique indexes. Which basically it says don't allow two equal entries based on this set of comparison operators. So it's a generalization beyond equals. If these two things pass this comparison operator, which could be equals or it could be something else. um into the table. Like those could be overlap, for example. Hmm, now this is starting to sound pretty good. And you can um they can be any index supported Boolean predicate um and they're added together and they're all added together. So, there's one catch, which just has to be a single index. And range types require just indexes. So the index has to be a just index But by default, the simple scalar values like the room number, which was probably a character field or an integer, don't have just indexing.

Speaker 2: Oh dear, I lied to you. This feature won't work at all. Well, no, okay. There's an extension built into Postgres called BtreeGist that gives you all of those gist uh uh indexes for simple types like integer and character. It's a Postgres extension, it's part of contrib. You can use um you can use create the create extension migration to get it for you. But it ships with Postgres, so you don't have to worry about it not being there on any installation. And just use the create extension migration operator and you're done. Okay. So how does this look? In your model, you say, I have a bookie, and it's a character field, and and the and the dates of the booking. Probably all you need to build a reservation system, right? Long weekend project done. Um and so you run you run a migration and that's what it looks like.

Speaker 2: You know, it has the usual Integer sequence that we all love so much in post in Django. Um, the room and the dates and a primary key. But who cares about the primary key? We're not going to use that for this. And then you run this really exciting looking statement, which you are adding an exclusion constraint that says don't let anything where the room is equal and the dates overlap be inserted into this database. Because at at is the uh is the Postgres version of the overlap operator. Pretty neat looking. So you'll you'll people will be so impressed when you do stuff like this. Um so you create a this booking and you create a date range and you save it. Say, well, okay, it's the first one in the database, so it better not

Speaker 2: that better work mod. Um you create another one, but th those dates don't overlap. They've checked out then. You create another one with the exact same time, but it's a different room. So that's fine. And now we're going to create a third one back on that room, except we're check trying to check in before that guy is checked out, and then you get the blah blah blah blah stack trace integrity error, and it didn't let you do that. So you could catch that and produce an error too, or you know, whatever. Um so why would you use rain fields? Well, you probably want to use them to represent ranges. That's a good idea. They're more natural than the traditional high-low thing that we were stuck with before we had them. And you can do all this kind of cool database integrity checking on them. So, they have H-SOR fields.

Speaker 2: H-SOR fields are boring. We're not going to talk about those. Um because They have been sub the you can read about them if you want to, because we have the new hotness, which is JSON fields. Um they're new in 1. 9, um, and they support arbitrary JSON structures. And they're stored as JSON B because J Postgres has two different JSON types. Contains and contains by both the key and the value have to match, has key, which finds JSON structure. with a particular key, has keys, finds everything that matches a list, and you know, all the good stuff, and has any keys. So cool. You can do path type queries. You know, you can say, find me an op find me a the data is a JSON field, so

Speaker 2: find me data that has an object owner that has a field name whose value is Bob. Okay, that's cool. This is straight out of the docs. You can use um array indexing if there's a JSON, if it's JSON. So data owner other pets, which is an array inside of the JSON structure, not a Postgres array, a JSON array. Number zero has the name fishy. There remember I said there are two different JSON types. There's JSON and JSON B. JSON stores the raw text of the JSON white space and all exactly the way you put it in. It is in fact just a syntactic wrapper around the text type. Um JSON B is a compact indexable representation. It's like BSON only much, much better. So why use JSON instead of JSON B

Speaker 2: JSON is a little faster to insert since it doesn't have to do all this parsing fun. And there are two dubious features that some people because JSON started using in JSON, which are duplicate object keys at the same level, which is not supported in the spec, and stable object key order. Where the idea being if you store a JSON field that has field A and field B, it'll always come out AB. There's nothing in the spec that requires this behavior. One thing to use JSON for is if you're just logging data and you don't really expect to do a lot of querying on it, it's faster to insert. Um basically everything else you want JSON B. And the the biggest advantage is JSON B can be indexed in useful ways, unlike JSON.

Speaker 2: And the JSON field type gives you JSON B anyway, so just roll with it. Indexing JSON. So we use GIN indexing. Remember this is for key value pair kind of stuff. It supports the um has the contain the has key um has uh has a single key. The the path has a single key, has any keys, has all keys you get it. Um one thing to remember is right now there's no single operator that's in Postgres that says, find me this JSON field that has this key at any level in the structure. You have to start at the top level and walk your way down, finding things. You can query them, but only in paths that are rooted at the top level. This isn't as big a restriction as you might think, but it is a restriction.

Speaker 2: So why would you use JSON? Well, if you're logging JSON data like API calls, that's a good reason. Um you can use it for an audit table that works across schemas. This is a big problem in in relational databases. You want an audit table that every time you change something You spit a record into a table that says I change this. But that means but what schema does that audit table have? It could mirror you might have to that does that mean you have to create a separate audit table for every table you're to you in the database? That's pretty gross Or you s but with JSON, you can just turn the um the record into a JSON blob and store it right in. It's an it's a friendly, readable way of pickling JSON objects. that isn't um that doesn't have like in infinite security problems like cpickle or yAML. Um and you can use it for attributes or rare fields.

Speaker 2: For example, you're building a system and you're going to deploy it. But your model is what it is, but you want the user to be able to create their own objects, like it's an inventory control system, and they want to be able to create color or size or something like that. You can store them in JSON so you don't have to modify the schema in the field. And there's other goodies. Um most of these things have ad everything has admin widgets that go with it, of course. Um the HStore and JSON widgets are kind of really only good for debugging because it's like gives you this text and burp and there you do deal with it. Um And there's full text search. Now, full text search is a 110 feature, which is in beta. Everyone download it and give it a go. It's it's uh Postgres had full text search built in for a long time. Uh 110 finally contains model level support for it.

Speaker 2: First, you have to get some concepts down, and this is a little bit hard to wrap your brain around. I have to go back to the docs sometimes onto this. There's this thing called a TS vector. Which is a block of text. You can think of it like the blog entry or the description in a catalog or something like that. Big old block of text encoded for full text search. So transformations have been done on it to normalize it. strip all the uh the um uninteresting extensions out, remove the stop words, all that stuff. It's a built-in Postgres type. Um doing that transformation requires this thing called a configuration, which I am not going to go into because we could have spent the entire time. So um and I am I out? You have three minutes of questions. Okay. I I'm gonna plow through because I want to get through this. Um So

Speaker 2: uh Postgres has a bunch of configurations. Generally just use those like English is the standard one, just use it. Um 2TS vector is a function built into Postgres that takes text and returns a tx vector Like that. Fortunately you don't have to worry too much about this. Django calls this under the hood for most situations. And then you have a search functionality. So you can search a text field using full text searching using the underscore underscore search predicate in a query set. Without indexes, it does a sequential scan, which is bad. So you don't want to do that. There's a TS vec a search vector object at the top level in Django that repr uh represents the TS vector. It lets you search more than one field at a time by combining them. Merging and searching happens as a query runs.

Speaker 2: Search query represents a query object, tries, does the stemming and things like that. So you can do searches like that. Cool. PostgreSQL also has rankings, so you can sort things in relevancy order and blah blah blah. There's lots more. These slides will be online. The important thing to remember is you need an index, which is a JIT index, in order to make this work fast. You can combine multiple fields with different weights and all this kind of stuff. Generally, you need to use a trigger to do that kind of stuff. There's examples in the documentation on how to do that And there's three things which you can read about in the docs, which are relatively small features. Questions? No.

Speaker 2: And if you if you didn't understand any of that, hire us. And um thank you.