PostgreSQL in Django 1.8 by Christophe Pettus

Speaker 1: Okay, um I'm going to plunge right on in because we have so much stuff to talk about. Um specifically Let's we're going to plunge right in on Django 1. 7 introduced native migrations, um and Django 1. 8 introduced Django Contrib Postgres. And there's so many, there's so much cool stuff in here and so little time. So let's plunge forward. The first thing I want to say is most of what I'm going to talk about in Django Contributors was done by Mark Tamlin. um in belief today, Kickstarter or Indiegogo, I forget I think it was Kickstarter Project for it, who deserves endless praise for these wonderful new features. So first I'm going to talk about migrations and everyone's going to say wait migrations aren't Postgres specific. Why are you talking about migrations? And the reason is that a lot some of the stuff we're going to talk about, they're an enabler for other things

Speaker 1: And Django Wind 7 migrations are just amazing. So if you haven't used them, just you know go to your laptop. I won't be offended. Install, move, move to using migrations. And thanks, Andrew, for those. So, a quick overview of for anyone who hasn't used the 1. 7 migrations or have been treating it like a black box. Migrations are built around this notion of operations. Each operation in a migration moves the database back or forward through its schema migration timeline. So it adds a field, adds a table that corresponds to a model, does something like that. There are lots of individual operations, but there are two super interesting ones for our point of view, which are run a skew runSQL and create extension.

Speaker 1: These are operations that are built into that come with the Django framework, core framework now, and you can use them in your own migrations. Run SQL. Probably you can guess what it does. It applies Raw SQL directly to the database. And now everyone's saying, oh God, it's one of those database guys talking about Raw SQL. I'm going to launch again. But really, work with me here. Specifically, it's very useful for things you can't um do directly in um using the the models yet, like creating indexes for some new types we're going to talk about. There's also a create extension operation which runs a create extension command. You can sort of pattern is forming in this naming convention. Or uh specifically the extension mechanism in Postgres, which was introduced in 91?

Speaker 1: Something like that Um is it's a little bit like pip for Postgres, is it allows you to add extent packaged extensions into your database. Previously you had to drop a. so file here and run the SQL here and do all this stuff and it was just horrible. Specifically, we're going to talk about this extension hstore. Which is a um which needs to be added to your database before use. It doesn't come with the core Postgres installation. A lot of this will make more sense later. At the moment, I'm just teasing you. One thing to notice about HStore is if you have it, it adds a query to every time you connect to the database, but we'll talk about that also. So that's all you need to know about the new extension facility. Let's talk about 1. 8. 1.

Speaker 1: 8 adds some new field types, which are array fields, range fields, and an h -store field. Um one nine is going to add some more cool stuff, but this is what we get in one eight. So an array field. In Postgres, since pretty much the dawn of time, arrays are first-class types. You can have a single column that is an array Now database you know old school database people like their heads explode at this point because this is denormalization and this is horrible. Forget those people, they're old and in the way. Um these this is a really really cool feature Um an array field is now a field that you can import from um Django contrib Postgres and it lets you use those directly. Before there were tons of field

Speaker 1: of things you could find on Django snippets and things like that that would Let you do it, but now it's fresh out of the box. And these map into Python arrays. Why I said array here is because I'm old and in the way. What I mean, of course, is a list. One thing to remember is that Postgres arrays are of homogeneous type, unlike Python lists which are not. So you get you pick a type, ints, string, something, and that's what you get in a Postgres array. Um Python will let you put, of course, anything you want in a in a list, but don't do that if this is what you're trying to model. So , the other thing is that Python multidimensional arrays are rectangular. Remember Fortran? Just like that. Isn't that great that we're

Speaker 1: honoring our ancestors in this way. So they're traditional multidimensional arrays. They're where you say I'm going to create a three-dimensional array with dimensions of five, seven, and ten, and the whole thing appears and is populated for you. They can be null, individual entries can be null if you need to represent a hole in the array, but it's not like it's not like a list of list structures where each individual element can be any size you want. So once you've got it, as the uh dog chasing the car said, now that you've caught it, what are you going to do with it? Um you have array field queries. The basic one is contains. The contains matches if the array field on the left contains all the um all of the entries of the list on the right. So

Speaker 1: ABC contains A B, but it does not contain A D. Order is not important in a contains query. So you can continu you um they can they can appear in anything you in any order you want. You could also have contained by, which is uh effectively the reverse. Matches if the list on the right contains all the entries of the field on the left. So this this is not contained by that and that is not contained by that. But that is contained by that. Whee. Order's not important here either. Okay, that's easy. And then you have overlaps, so which is the any version of of this. So this overlaps this, but that does not overlap

Speaker 1: that. And those and those return a Boolean, then you also have the the predicate underscore underscore len, which returns the length Which returns the length of the field on the left as an integer. So this is kind of approximate syntax. You wouldn't actually write this in your Python, but you get the idea. Um that if it's storing this value, you apply length to it, you get 2. Now something to remember about this is that unless you've created a particular expression index, which we'll talk about in a bit, this is going to do a full table scan. It will pick up every single row in the database that for that table, rattle it to calculate the length and filter it. So you probably don't want to do that on a big table.

Speaker 1: You can also apply um uh transformations to it, such as an index. This takes uh like for example, this filters on everything whose first element underscore underscore zero is a um is the string A. If there's no array zero uh entry zero, for example the um the uh the um the array is of length is of length zero It um it doesn't match. Um it simply it returns false. There's no error. So if you put five million there and there are no five million length arrays, you'll just get back false for everything. One thing is directly, you can't specify this programmatically. You build in a zero there.

Speaker 1: Unless you're going to do you can do string substitution and use quarks to do that, of course. You can also slice the array, which is pretty cool. You can say, okay, anything whose entries 0 to 1 are that Or, probably more usefully, zero to two contains a. You can also index array fields. So you can just say db index equals true, and you're done, right? Wrong, sorry. It creates a B tree index on the array, which is pretty useless for an array. So this is one of the downsides right now of using these, is you have to, if you want indexing on them, and you almost certainly do want indexing, you have to do some special magic. So let's talk about that.

Speaker 1: But first let's talk about how Postgres does indexing. Postgres supports different kinds of index, which most people who are just using the Django ORM never see because you only get one kind out of post out of it, which is unless you're using Geo Django, which is a B-tree index. B tree indexes are great. They're really they are nearly the the cor the the optimal solution to a particular problem, which is they're very fast, they're compact. And they provide total ordering. So you can walk a B tree index up uh up one side and down the other. You can use it to accelerate queries like greater than, less than, or equal. It's um B tree indexes are great, but they're not perfect. Specifically, a B tree index requires a totally ordered type, like integers.

Speaker 1: Every integer is greater than, less than, or equal to another integer. Those operations apply to any two integers. Strings are totally ordered, floats are totally ordered. Points, arrays, and H source and things like that are not totally ordered. What does it mean for one array to be greater than or less than another? Well you can make up something. You can say, well We're going to compare the elements in order and then, you know, all that. But most people don't use arrays that way. That's not a very interesting way of using an array. What they do you do do on arrays are things like um inclusion, like the contains operation. What you want to do is say, does this I want to find all the instances of this of an array that contain this particular element, no matter where they are in the array. So Postgres for has you covered. It has two different types of indexes, which are gist

Speaker 1: engine indexes. For those studying along at home, if you um GIST sort stands for generalized um index storage technique, and GINS stands for generalized inverted index. So now you know. Gin indexes are generally used for types that contain keys and values. Arrays, in that case they just contain values, not keys. H-stores and JSON are good examples of things that are key-value pairs or just value lists. Gin indexes are generally used for that kind of data structure. GIS indexes are generally used for types that partition a mathematical space. So like a point or a range or a rectangle is an example of something that would be indexed using gist.

Speaker 1: Um if you want, talk to me in the hall and I can explain w the the the details on this for the moment, just roll with it And the good part is once they've created they just work. You don't have to do anything magic to them once you have them. They're updated and maintained and managed and dumped and restored and everything, all the good stuff happens to them in Postgres. So arrays support gin indexes, generalized inverted indexes. The nice part is these accelerate contains contained by and overlaps. You get you get the you will use the index as appropriate to make these guys go faster. It doesn't help length or slice operations though, so be aware of that. So what this looks like at the SQL level is you create an index on app underscore model

Speaker 1: using gin field. That using gin is the part that indicates that what you want is a gin type index. One thing to note is gins can be large, especially if there's a lot of data, if there's a lot of data in the underlying table And they're not free to update. Just don't you know run around creating them just cuz. Create them if you are going to be doing the operations that will be accelerated by them. J specifically if it's a small table, don't create a gen index. Unless it's a pedagogical exercise for yourself, for some. If let's say you want to index length, for example, a very common query is give me all the arrays that where the length is greater than seven. I'm having a hard time coming up with an example of why you'd want to do this, but the the you

Speaker 1: the world is wide, so the people do this. What you can do is create an expression index. Array underscore length is Postgres 's array length operation with the field, you have to tell it which dimension you want the array the length of, remember? Multi-dimensional arrays. So dimension one and and dimension numbers are one based. Why yes, Postgres was invented in the early 90s. Why do you ask? You can also index slice operations like that. So if you're going to be doing a um constant queries on does this slice equal that slice, you can do those. The double parens indicate this is an expression index. So it's actually going to calculate this expression for each entry in the table and stuff it into an index for you. Postgres is smart enough that when you do the query and it sees a matching expression, it'll use the index instead of running the query the expression again.

Speaker 1: So that's pretty cool. Postgres and also Postgres arrays are one-based as well. What can I say? Seemed like the thing to do at the time. Okay, so now we have all this good this machinery for doing array fields, but why would you ever want to use an array field? Well, the first one is the underlying data really is an array. You want to store an array. You're getting things like, um, for example, you know, you you um a very common situation is you're recording raw sensor input from a sensor, and the sensor is hand saying, okay, at this timestamp, I recorded 23 samples, here they are. And you want to just dump this into a field. You could denormalize, you could normalize it, sorry, not denormalizing it, and create two tables, one for the base sensor and one for each of those, but wow, that's a lot of overhead. So just stuff it in an array field

Speaker 1: One of the best uses for an array is as a replacement for a many-to-many table. One of the things that So for example, the classic social networking problem. You have things and you have people and people like things Now, in your basic correlational database model, the way you'd build this is you'd build an intermediate table that has a many-to-many table between people and things And every time someone likes a thing, you insert an entry into this many-to-many table. And then you have 1 billion people and 12 billion things. And that's kind and a, you know , a 85 petabyte. Many to many table. Huh. Okay, probably not. So what you can also do is in the people field, store an array of everything they like.

Speaker 1: And in the thing field, store an array of everything that everyone who likes this thing. The entries in array can be key fields. So these can be integers that index to a pro to the other side, much more compact and efficient to query. And then you index each field. I'm not sure I would build Facebook this way, but if you have a smaller system, this could work very well. You could also use this for denormalizing the results of an expensive query. For example, you're doing a um you're caching the results of a query Um you might you keep the many-to-many field for some other reason, but c but um have an optimization where you're storing it as a denormal denormalization. Denormalizations like this are generally Need to be approached with caution. I won't say never do them, but

Speaker 1: um because you do have to worry about maintaining them, make sure the data stays up to date, things like that. But it can be very useful Okay, and now we have h store fields. Who's ever used h store in Postgres? Okay. So h store is a semi-built-in hash store data type. It's like a dict, a Python dict, that can only take strings as keys as values and values. So it's a single level, it has to be a string on each side. Pre-the JSON type in Postgres, this was the only way of storing unstructured data, associative data like this. It's not super powerful. And you notice the semi in semi-built-in, and we'll talk about what that means. So first of all, how do you get h store to work?

Speaker 1: Because if you log into your average Postgres database and try and create an h store, a column, you know, create table, blah, blah, blah, x h store, and it throws an error saying I have no idea what the h store type is because it's not actually built into Postgres. It has to be installed before in a particular database before you can use it. It's not part of core. The good part is Django Contrib Postgres comes with an HTOR extension that'll install it for you. So you create a custom migration and we use an empty create an empty migration, add the h store extension to operation to it, and it'll apply it and you're done. So that's good. There's this one weirdness which only really matters if you're really pushing the performance of your database and for some reason you're not using connection pooling, which you should be.

Speaker 1: The problem is that it has to do every time you connect to the database, Psycho PG2, which everybody's basically using if you're using Python. um has to connect to the get the object identifier for the h store type because it can be different in every database. So this adds one query to the connection. It's usually not a big deal, especially because Django has this thing about asking all sorts of questions for the database on every connection, like what's your time zone and things like that. So one more, what's what's one more query among friends? But you just you do have to be aware that's what's going on. If you see this query fly by, now you know what's going on. So um h store types are represented in Python as a dict. The keys and values must be strings, not integers, not lists, not hat not anything else.

Speaker 1: They're translated to and from the database encoding. So if your database is in Um something besides UTF-8. They'll be translated to the right encoding on um inside of Python. Please say your database is in UTF 8. Because it's horrible if it's not. Really. Don't don't do that. It makes database DBAs cry. But if in the off chance it's not in UTF-8, it'll do the translation for you. Um hstore supports contains and contains by. Both the key and the value have to match in this. And you have has key, which matches fields containing a particular key. So that's cool. And you have a has keys, which takes a list. So that's pretty useful. Um

Speaker 1: and then there's a keys Which returns the list which um matches the list of the keys in the field. So you can say, get me the um the the get me all the fields whose um uh blah row uh rows whose field has keys which contain either a or b. So that's pretty cool. And values does the same for the values of the HTTP. So you can query on values as well. Each storage field supports GIN indexes. So exact same syntax at model loop and accelerates Contains has key has keys but not contains by. Sorry, just the way gin the just the way the gin index is built for an htor.

Speaker 1: So, why would you use an HStore field? So it's great for storing very rare attributes. Andrew actually touched on this in his great talk earlier about database anti-patterns. Which is you you write a CMS or some other system, even an inventory control system or something like that, and you send it to the customer, and you send it to a bunch of customers. And you don't want You want the customer to be able to add attributes to items. For example, an inventory control system, you might want to be able to tell people, okay, for the item, they want to be able to add um ISBN if it's a book or color if it's a thing that comes in colors or sizes, but not every item is going to have that and you don't know when you ship your product out to the customer which attributes they're going to want. Well you could create fields, individual fields in the database, but that's first kind of hard in Django, and second of all that way

Speaker 1: mad the slides from a migrations and implementation point of view. Well, but you what you can do is create a single hstore field and put use it as the place to store all of these random attributes. Generally my rule of thumb is if there are going to be fields that are null 95% of the time, consider an HSTOR field instead. But one thing to remember in this in this use is that null fields take zero space in PostgreSQL. They don't actually take any room on disk. So you you're not costing any space by creating null fields with zero use. So and another use for these is if you have a fee an attribute that's populated very, very, very rarely. That it's not worth creating the whole field for. This is another solution to that.

Speaker 1: And user-defined attributes, which we just talked about. That being said, if you're doing greenfield development right now, you probably want to use JSON instead, especially once 1. 9 comes out and we have first-class JSON support in Django. But if you have to do something right now, there's no JSON type in 1. 8, so use JSON if you h store if you need it right away. Or you're trying to plug into an existing database which has hore. Now, in increasing order of coolness, we now come to range fields. PostgresQL now has native range types. Range types um span a range of a scalar type. So for example, 1 comma 8 as in for range includes all of those.

Speaker 1: That this is an inclusive range, so it includes those guys. You can also write them as exclusive bounds. So for example, 1 comma 8 goes from 1 to 7, but doesn't include 8. So far so good. And that's the default. Notice that this is the PostgreSQL syntax. Obviously, you can't write this in Python and have it be syntactically legal. That would be awfully cool, wouldn't it? I bet there are languages that'll let you do that. Anyway, but this is important. So default and the default is this open on one and closed uh closed on one side, open on the other range. If you omit a bound, it means all values greater than or less than. For some types, particularly dates, have also have a special infinity value. I guess there's a special end of days value in types.

Speaker 1: I don't know. It's um so if you see one of those coming out of a query, you might want. Um Psycho PG2 The the database adapter that pretty much there's no reason not to use, and if you're using Django, you have to work hard not to use it, um includes a Python range-based type. that handles all the various boundary cases and the infinity special cases. And that's what the range fields in Django are built on top of. Out of the box, 1. 8 supports integer range and big integer range, so 32-bit and 64-bit integers, a float range, date time range, and a date range Um I am pleased to say the date time range is timestamp t with time zone in Postgres

Speaker 1: because if you're using timestamp without time uh timestamp tz You're probably making a very bad mistake. So everybody go and make sure that you're using timestamp, not timestamp TZ, not timestamp. And date range So contains contained by an overlap kind of work the way you'd expect to on comparing two ranges. There's also a fully less than, fully greater than, which is true if both the upper bounds and lower bounds of the field are greater than or less than the comparison value. So if the whole range is to one side or the whole range is to the other. And adjacent to is you is true if two ranges exactly bump up against each other. There's no space between them, there are no values. This is a place where the parentheses, the open bound is useful

Speaker 1: because you can imagine on a closed bound, there's no way of doing this with a continuous type. There's no way of writing two float ranges, two uh float ranges that are closed that that exactly bump up against each other because there's always another float that you can shove into there. So this is why an open range is important. There's also not less than, as the field contain does not contain any points less than comparison value, and not greater than, which works the other way around. Range fields use gist on the um indexes, which you could probably have guessed the syntax, but here it is, app model using gist field. Note that the re you'll have to for now you have to drop this into a RunSQL migration. You

Speaker 1: there's no way of saying this just at the Django model level. That's okay. That's what RunSQL is for. All the comparison operators that we um that we just described are accelerated by the by the gist uh uh by having a gist index on a range. So, why would you use this? Well, here's a problem. Let's say you're running a hotel, and your rule is don't allow two bookings for a room to be inserted in the database for the same room where the dates overlap. Okay, and you want to do so how do you solve this problem? There's actually no way of solving this with traditional unique constraints, because there's nothing that's necessarily unique. You could say room 102 for Monday through Friday and room 102 for Tuesday through Saturday.

Speaker 1: There's nothing unique. Those two taken together are not unique. But they but they are still it's not valid to have both in the database at the same time. You double book the room. Scratch, scratch, scratch. So how do we solve this problem? Postgres to the rescue. We have a facility in Postgres called an exclusion constraint, which is a relatively new feature, I think one too in Postgres, but it will allow you to not have this situation arise. It's a generalization of the idea of a unique constraint. Let's stop for a moment and think about what unique means. Unique, you can say unique says, well, no two values can be the same for this column. when you insert them. Okay, that's fine. But you can also say don't allow any two values who both of which pass the equality operator.

Speaker 1: Now we've said it the same thing, but we've said it in a slightly different way. We've said don't allow two things that that where this particular operator, equality, are in the database at the same time. So we could say, well, what if the operator is an equality? What if the operator is some other operator? So we could say, don't allow two things in the database at the same time where the overlaps operator matches them. Hmm, well that's interesting. Because now and then we say well okay and let's let's jam them together. So we'll say these two fields can't pass this operator and these two fields can't pass this operator and and and So we've generalized the idea of unique to let you use any operators in combination with and.

Speaker 1: And I think an example will be very important here. We'll keep the faces. So there's a catch. You have to have a single index for all of the for this whole shebang. And since range types require just index The index has to be a gist index. But the problem is we talked about this example where we were using room, and room is just an integer or a string. You know, however y however the hotel wants to represent them. But this is a scalar value, and scalar values don't have just type or don't have just indexing. Uh-oh. We just blew it. I was there's gr I was going to you show you this great example of how to solve this problem in Postgres and now I can't. Well, of course you can. Um because there's a module called B tree GIST, which lets you create these indexes on mostly simple scalar types. It's a Postgres extension, it's part of contrib.

Speaker 1: It has to be installed in the database, but it ships with Postgres, which means you can use the create extension migration to get it into your database. So let's talk about how we'd actually use this. So, you know, import the models, import the date time field, and here's a booking, and here's the room, and here's the range, and okay. This would probably be a foreign key to a room, but you know, you get it. That's easy And then we say, okay, what's it look like? Well, there's the integer that Django created for us, and the room, and the range, and we're all set. Okay. So far, so good. Now we create this extension B tree gist and we add this exclusion constraint. Now notice what we're doing here is we're saying add this index where the room is equal and the date is with at

Speaker 1: and and. And and is the Postgres version of the overlaps operator. So when you do an overlapse query in Django, what you're going to get is this double ampersand. Okay, now let's try adding some rooms with all of this. Say we're going to add a room and save. That worked. Add a room and save. So because it's the same room, but an entirely different range. Add a room, save, so far so good. Because same rate, same time range, but different room. Okay. Room, do do do one, two, three, save. Oh, notice however this overlaps. That. Bang. Oh, look, it didn't let me do that. Pretty cool.

Speaker 1: And notice that this means the constraint is being enforced at the database level. Sure, you could write code in Python. that would do the query and if the if it returned an overlapping range say no there's exception. But that but the nice part about doing in the database is if you're doing bulk imports, if you have other kinds of um queries You um you can um this means the database itself enforces it. So you should use ranges, range fields to represent ranges. I hope you get a lot of value out of this slide. Um you probably figured that one out It's more natural and you get better operations than the traditional high-low pair of stuff in the database. And you get more database integrity and more interesting operators available. Now, coming soon we have JSON fields.

Speaker 1: Um they're not in 1. 8, but I think they've I'm I'm almost sure they've landed for 1. 9. Um these are fields that support arbitrary JSON structures There's still a little bit of a work in progress both on the Django side and on the JSON side, on the Postgres side. Postgres has two JSON types, JSON and JSON B. Sorry about that. JSON stores the raw text of the JSON blob, white space and all. It is literally the text. It is in fact a wrapper around Postgres' text type. JSON B is a compact index indexible representation. It's a lot like it's it's similar to BSON, but it's a lot better. Um so why use JSON instead of JSON B? It's faster to insert since it doesn't have to process the data, it just shoves the raw text in the database.

Speaker 1: And JSON allows for two highly dubious features that people use in JSON, which are duplicate object keys at the same level and stable object key order These are not allowed by the these there's nothing in the JSON spec that says that these will these features are available in JSON, but people still use them. Bad people. They should feel bad. And it's also this is okay if you're just logging JSON, like you have a log table that's accumulating API calls or something like that, where you don't really need to process the stuff, you just want to have it somewhere. So JSON B, pretty much every other application you want JSON B. It's it can be indexed in useful ways, unlike JSON. Um the forthcoming JSON field in Django uses JSON B as its underlying representation, so just roll with it. Um JSON

Speaker 1: B has GIN indexing. Uh JSON JSON, the only kind of indexes available for JSON are B tree indexes that treat them like strings, which is pretty much useless. You get these operators. Um which and the query um the query has to be against the top level of the object in the index to be useful. So you can't index, you can't do a query If you have a nested JSON structure, you can't query for a key that's way down in the structure. It has to be at the top level. That's being worked on. You can query nested objects, but only in paths that are rooted to the top level. So why would you use JSON support in general? You're logging JSON data. You want audit tables that work across multiple schemas. This is a very common problem in

Speaker 1: database in relational databases where you want a single audit table that handles all the other tables, rather than having one audit table for every other table, which is that way madness lies. Um It's a nice way of pickling um Python objects so you could so that other tools can read them. The standard libraries that pickle Python objects into JSON kind of put a lot of cruft into them for my taste, but it's it works they work And for the all the things that you used to use HStore for, like user-defined objects, rare fields, things like that. The new fields have admin widgets that go with them that are really very cool. HStore and JSON widgets are really only good for debugging because you know you basically get the raw text spat

Speaker 1: out to you. But you know, why are you using the admin for this? And the unaccent filter, which I'm out of time for, so just rebound in documentation. And thank you. Questions?

Speaker 2: If in Postgres the uh arrays are one indexed, um the example you gave uh in a query set filter was Uh zero index is Django converting the

Speaker 1: Yeah. It's Python style indexing within Django because otherwise you would probably go insane.

Speaker 2: Yeah.

Speaker 1: So but it does and it does the zero to one conversion for you.

Speaker 2: Okay, cool. Thank you.

Speaker 3: Quick question about um expression indexes. Um Are expressing indexes uh B-tree indexes or can they also be?

Speaker 1: You can have B tree gin and gist indexes, yes. Um it's a little bit of an advanced class, but for example You you I can d definitely see things like um if you if the output of the operation like a slice is um um is gender just uh gener just indexable type, then by all means you can have a gender gist expression index. And j Postgres does the right thing. The the various geo extensions do this a lot for cool stuff. So

Speaker 4: So uh I'm used to having to be a super user to create extensions. Is there any hope to not need that requirement when I'm putting it in migrations?

Speaker 1: Um nope.

Speaker 4: Okay.

Speaker 1: Yeah, sadly. Um well yeah, sadly because uh because you could really seriously screw up your database with a with a bad create extension, super user access is going to be required. Which is really annoying if you're example on RDS. where you don't have superuser. So in the case of that you don't use a create extension situation. You write many angry letters to Amazon asking for that extension on the next release.

Speaker 3: Sorry, one more question.

Speaker 1: Of course.

Speaker 3: Do you have any tips on migrating MySQL to Postgres? Um

Speaker 1: hire us. Um There's it it's um the not that I can deliver in a in a uh a s uh th small thing like this. I will say that the actually the database layer is usually only about 25 % of the pay of the the nightmare The rest of the nightmare is the at the application level. Um it's not so if you've been really ruthless about your um about your database agnosticism in Django, you probably can get away with it without too much hassle. Um and there are tools that will probably do it for there. But a surprising number of MySQL applications rely on things like the return primary key on query on query for um for null thing that MySQL does in DB and things like that. So um Most of the time you

Speaker 1: spend most of the time looking for MySQLisms in the code, actually, rather than the database. There are tools out there that actually do the will do the dump and load conversion for you.

Speaker 5: Hi. Uh are there are there any performance um improvements moving from a many to many table to an array? Um

Speaker 1: Potentially huge, because the um when you think about how the many-to-many query works, it has to potentially suck up a whole huge ton of rows and go through these enormous indexes. Indexes are fast, but they're not infinitely fast. And so um if you now there there is there are upper bounds. I mean one billion row Item indexes are not going to be any more fun than a billion row table. But um potentially you could this could be a huge win, especially if you're using a gin index, because the way a gin index works very quickly is it Indexes so if for example if you have a key of four the GIN index records everything that has a four in it and can go pull those rows um could potentially much faster than um running through a many-to-many table. Thanks. And smaller.

Speaker 6: You mentioned that the new fields only um will only work with JSON B. What do you recommend in order if you want to use the plain JSON for those sort of dirty choice.

Speaker 1: Evaluate your life choices. If you absolutely must, you can write expression fields that extract the stuff out of the c that extract those fields out of the JSON type and index them as B tree. Um things. At the at some point though, you're probably better off doing the JSON B unless you absolutely must have those two f uh miss features.

Speaker 6: Sure. Well if if querying isn't necessarily a goal and you just want to get access to it, could you like subtext or subclass um like it at least accessible sort of d do your own well

Speaker 1: in theory you could alter you create it and then alter the type back to json And probably everything will work just fine at that point. I haven't tried it, but it's it would certainly be worth a go. Um The other thing, of course, is if you want the performance enhancement.

Speaker 6: Absolutely.

Speaker 7: Your booking example is really nifty. I'm wondering a couple other things. Would it be possible to create multiple conditions using the B tree? GIST extension? If so, how would you recommend uh exception handling?

Speaker 1: Um the you could um create as in mo more than one predicate on the um uh not quite sure of the question, sorry.

Speaker 7: So let's say that in addition to the date ranges, you also didn't want certain types of rooms to be booked.

Speaker 1: Yeah, I mean it can be it can be essentially types of interest. hack is because I because one of the thing one of the fields only has gist indexing and the whole thing has to be the sa has to be a gist index. But all all the indexes that are that are apparent appear in the exclusion constraint have to be of the same type So that's why I had to force the int, the the s the the string um that was the room into it. If for example everything everything is a you can write exclusion constraints as any combination of Boolean predicates added together.

Speaker 7: Okay, so effectively there's no reason to have a situation where you could have multiple kinds of integrity errors that you'd have to do.

Speaker 1: You'll literally get one. It'll i if if you had you can have twelve different exclusion constraints on the same table There's absolutely no limit to how many you can have, except that uh of course it has to check them all on every insert, which could be a little bit annoying. Um so it'll you'll only get one out. It won't tell you, oh by the way, these 12 different exclusion constraints all failed It'll hit one and then stop the insert and you'll get that one.

Speaker 7: integrity errors, how would you recommend handling that on the

Speaker 1: That's a l that is a little more complicated. In that case you may need to um I would actually do both, both query the database in advance to get the right kind of error back to the user, but still have the constraint in case you need you have other applications that don't run through the same UI. that are try to insert data, you try and bulk load it from someplace else and you know using the copy command or something like that. That way, you know, because You know, inevitably what will happen is like take the room example, you know, your your hotel's running fine, and then hotels. com comes in and says, we'd love to send you reservations, but we have this API. And you know, so that kind of thing. Uh I I'm sure room reservation systems are a tiny bit more complex than that example, but you get the idea. So

Speaker 8: Do you have any insight into what might be coming in one nine and beyond with regards to Postgres?

Speaker 1: Um well in um at this point 19 is pretty well locked down, so you can just read the release notes. Um I'm not a contributor to that stuff directly, so that that would be the best example.

Speaker 8: I wasn't certain of your involvement. Okay

Speaker 3: Thanks for the great talk. Um how do you feel about the funding model of the contributions to Django for the Postgres stuff? And are there any other things that you'd like to see funded in a similar way?

Speaker 1: That's probably uh a l probably a longer questi a bigger question than I can answer right here. It is interesting to me that that that um I have very strong opinions of that op that companies that use open source should give a little more back than they do. Um The um there are certainly features I would love to see, like being able to push different um foreign key um foreign key cascading models. specify those from the um from the model level. For example, if you want to have something other than delete , be able to specify that and have that implemented by the database rather than Django im implementing it. Um but yeah, getting getting money for these kinds of extensions is an issue. Um Kickstarter is great, but it does tend to be a popularity contest

Speaker 1: and It locks out a lot of developers who wouldn't otherwise have, you know, who are brilliant programmers but haven't worked as hard to but don't have the high profile. And that's a shame, I think. So tell your employer to write big checks is my answer. Great. And if you need anything done with your Prosgres database, give us a call. And thank you.