PostgreSQL Partitioning
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Treat, Robert
Begining in 8.1, PostgreSQL has offered table partitioning, allowing you to split data across different physical segments for significant performance improvements. Until recently not much had changed, but starting in Postgres 10, and new, more native partitioning system was added into Postgres. While the initial implementation left some things to be desired, many of those issues are being addressed. This talk will cover many of the insider tricks that are used in large setups and explain the trade-offs between the options you now have available. PostgreSQL table partitioning allows you to scale your data to significantly larger volumes than would otherwise be possible. Getting started with table partitions in and of itself is not complicated, but if you want to get maximum benefits from your setup you can't just follow the documentation. This talk will walk through the ins and outs of table partitioning including: What table partitioning is, and how it is implemented in PostgreSQL. Differences between native and inheritance based partitioning. Migrating from one format to the other. Automated partition maintenance. Learn how to setup and maintain table partitioning in PostgreSQL with hands-on examples. We'll walk through manipulating SQL and look at tools to automate your partition maintenance.
 So, postgres partitioning. Yay. If you want to follow along online, you can find me at atrobtreat2. I will post a link to the slides or something along those lines there. If you feel the need to tell everybody how awesome this talk was and it's the best thing you ever saw, that's a good place to do it. If you find that you have issues or like moral, you know, distaste for what I'm saying, please write me a lengthy email, send it to robertreatomniti.com. I will totally take care of that. And then, again, if you're looking for the slides later on, omniti.com slash presents, probably there should be a version there, maybe now or already or soon thereafter. So you can look for that there. Feel free to take notes but know that the slides will be up online so you don't have to kill yourself with that kind of thing. Also, probably feel free to ask any questions. Let me look. I don't think anybody who I don't want to ask any questions is in the room, so that's fine. Okay. And then we will just go ahead and move along. So, postgres partitioning. How many people actually use partitioning of some type now? You're in the room? Okay. So a bunch of people here do actually use it. So the part of the genesis for me doing this particular talk is in 2007 I did a talk at PGCon called postgres partitioning. And so I was like, wow, I can reuse a lot of my slides if I do this talk. So that was awesome. But actually, so I did that talk in like 2007, 2008, did it a few times. And basically nothing changed in the world of postgres partitioning for like 10 years time, right? Up until, you know, postgres 10, whatever you're doing. There's a few tweaks and changes here and there, but by and large, like whatever you're doing at that time when that first got implemented, that is basically what you'd be doing today. And there's potentially a number of you out there, you'll find them out in the wild that are still doing all that stuff, quote unquote, the old way. And so that's sort of still a thing and something to be aware of. So we were doing a lot of partitioning very early on where I work and on some of the clients that we had. If I think about those systems that we had back then, most of them have gotten bigger. We've been lucky to get some of them to shrink, like going back at companies and saying like, you know, this Billion Row Table has a lot of data that you don't really use that you think you need to keep around. And if you talk to the lawyers, they realize like potentially there's risk of keeping all this data as GDPR has now sort of codified like, hey, maybe you don't want to keep all the data all the time. So we've actually been able to shrink some of that stuff down. But thinking about the idea that like 10 years ago with the inheritance-based partitioning system that Peschers had, we were doing terabyte plus tables, right, billions of rows, tables with easily over 2,000 partitions. And like some of those, if you're like, how do you get to a table that's that crazy? There are really bad ways to do it. I think most of the time that we did it would be something like you're doing daily partitioning and you have 10 years of data and then, you know, like, bam, you've got a lot of partitions, right? And it's pretty legit because each partition is probably like 100,000 rows at least. So you're like, if you total that up, right, you get to decent-sized numbers, especially 10 years ago. So that was sort of the realm that we were coming at this from. And like I said, we ran that for years and years and years. Life always got better even if partitioning didn't change because post-dress got better, right? And it was like sorting in the query planner and all that that would get better. So that was always good. We don't have to worry too much about that. So, okay. So what is then post-dress partitioning in the context at least that I'm speaking about it? And what are we going to talk about? Like I said, so I want to talk about what it is now-ish, how it was implemented, which like I said is probably what a lot of people would see if you went and looked at a system and understand the difference. The new implementation, I will talk a little bit about future goals. I'm not going to totally ignore it, but if you're like really on the bleeding edge, I'm pretty sure there's a talk tomorrow, I think it is, that is strictly on post-dress 11. So I would certainly encourage going to see that. And then a little bit about partition maintenance because that's always fun to talk about when you get into this stuff. So, all right. So we're going to start with the why, which is why you use partitioning. For those that aren't out there, most of these reasons should be sort of obvious, I think, but as, you know, if you're coming from other databases, I think the reasoning can be a little bit different. So the main thing is really just right as your table size grows, eventually things slow down or maintenance becomes a problem, that kind of a thing, right? Even with indexing, you can get really, really far with indexing. Certainly in post-dress, you have, you know, capabilities for like functional indexing and, you know, partial indexes and all this stuff. And that can get you really far. You can build very large tables and if you know you only query the last three months, you can keep rolling indexes on those tables and if that's your query pattern, like that can work and that can be really good. But eventually at some point, right, that still sort of becomes an issue for other things, right? Usually that's on the management side, right, when you think about adding in more data and if you have to remove data and that kind of thing. You can use partitions as a way to make that process easier, right? If you're doing like constant delete cycles and that sort of thing, especially because usually for most people that tends to be sort of time series oriented where it's old data in the table that you're deleting and the problem is with the heap structure, like that tends to get captured in ways that's harder to reuse. Similarly, things like vacuum just, you know, it takes a long time on really large tables, right? We've all sort of experienced that, I'm sure. And the larger that that gets, the more improbable it becomes to do certain commands, right? Like a table that you could cluster and maybe you have a downtime window enough where you can lock that table and have it be clustered early on, eventually gets large enough that rewriting it, you know, just takes too long and that's not an option. This way, like if you use partitions, right, you start to separate your data into active and inactive data is one sort of strategy you could take and then focus on the active data for things that maintenance is going to do. And then finally, like sort of general scalability, right, if you're trying to grow the system out, mostly I would say, certainly for the initial implementation, the thought was if you're vertically scaling the system versus horizontally scaling the system, right, that scalability partitioning really addresses the vertically scaling type thing. So obviously hardware, right, it's limited even in the cloud, like you still have certain limits as to what you can do, instant sizes you can get and that type of a thing. If you're going to maintain performance and keep that going, like at some point you turn to the partitioning tool belt and sort of do that. So those are sort of the basic things that will drive you in this direction and then it's sort of a matter of saying like, okay, what are the tools that Postgres is giving me, right, and then how do I actually apply them. Thinking on the historical notes, like I said, it's been a long time, right, so we had, if you were pre-8-1, how many people here were pre-8-1? Okay, yeah, this is the conference with the old people, it's fun. So you would do, maybe you would do this, I don't, we tried to do this and it really didn't work that well, but you know, you got to try something. You would try to do like manual partitioning, just using like Postgres is built in inheritance and maybe you'd put views on top of that, so if you're querying into the view, right, it would go and hit these tables that had an inheritance scheme. The basic problem was the query planner didn't know much about that, so it didn't work all that well. 8-1 was really the, you know, the release that gave you what I thought was a usable partitioning scheme that could then go and sort of compete with other databases, and that was all based on constraint exclusion, which meant the query planner could at least be smart enough to try to attempt to do queries on these large tables. And then like I said, nothing changed, I wouldn't say nothing, but that was mostly it until version 10, which is generally, there's different terminology for this, but I tend to refer to it as declarative partitioning, and it tends to be the way that people refer to that type of partitioning and going about it that way. And I'm going to show you like some different commands with the old style versus the declarative style, so you'll see the difference there. Speaking of terminology, another thing to be aware of when you first go into partitioning and you're trying to think about how you want to start slicing and dicing the data, there's two ways people generally talk about partitioning if you're out there on the Googles looking at stuff, right? It's a range partitioning, I find to be the very common case, which is, you're right, you're just doing specific ranges, usually it's based on a timestamp. It can be other stuff, but that's sort of the most obvious one that people do. But you also have another type of partitioning, which is list partitioning, and then again, it's sort of like what it says, like you have a list of sort of values and data, and then you want to sort of slice that up and put it that way. Probably the idea of calling it a list versus a range implementation-wise is arguably not much different, but the usage of that data tends to be very different, and that's where the differences come in, right? The list idea is usually that it's a fairly static list, it's not going to change very often, so if you think of like zip codes as a good one, at least in some places it's a good one. I guess like in Ireland, I don't think they really have zip codes, so no offense to anyone there. If you've tried to send a letter there, then you know what I'm saying. But in a lot of places, like it's pretty well defined what your zip codes are. They could change over time. Area codes for phones might be another way that you might do it. And again, like there can be some change in that, but the basic idea is it's a fairly static list and you'll put your data into the right bucket accordingly. User ID as a hash is another one I've seen, or taking like user ID or an email address and splitting off the first one or two letters and making buckets out of that, right? You've only got 26 letters, you don't really expect them to add more if you're working on like the American English alphabet. So just think about it in those kinds of ranges and think of the data that you're having. If it's accumulating over time, obviously it's going to fall into range partitioning. And if you're trying to do some other thing, you're probably looking more at list partitioning. Most of what you find if you try to do research on the subject is generally oriented towards range partitioning and like where guys are getting, you know, sort of funding and money to solve problems, all seems to be like time stamp oriented range stuff because that's what a lot of people are trying to do. So you'll see most of the literature that way, but the other way is just as valid and also certainly useful. Digging into that a little bit more, right? So list, thinking hashes or states, that kind of a thing versus dates and serial. In both cases, you do have to do some level of list maintenance, right? But I think in partition maintenance, sorry, it'll do some level of partition maintenance. But with the list, again, it's assumed it's set. One important key difference to think about if you're doing sort of long-term planning, that in a list, you generally expect data per partition to grow, right? So like you have a static set of lists, so the data within each partition is going to grow. So in the range, usually you think that that amount of data might be fairly static, right? Like it's a month's worth of data and it's always going to be a month's worth of data. That doesn't always work out because obviously if you have a service that's growing, you might start collecting more data over time, right? So those future partitions you probably would assume might be bigger than the ones that you have currently. But otherwise, like it can be fairly static if you have, you know, a set number of things you're collecting from. One of the other things with list partitioning, there's nothing in there that really keeps those things being equal, right? So if you divide, if you take like email addresses and divide them up by letters, you know, there's going to be a lot more that start with certain letters and less data in the ones and further ones. So you just have to be aware of that. User IDs is another one that people can get in trouble with. If you do strict partitioning based on user IDs and the data that's accumulated is dependent on how users actually use your system, you'll definitely have some users that create like hotspots in the system and other users that won't, right? So you can end up with partitions that are very large, a good sort of example. If you talk to folks at Instagram or even, I think, like Flickr have this issue where, like, you'll get celebrities who are on the platform, right? And so they have, they just get slotted into a bucket because nobody really knows that's what's happening in the initial part. And then they post something and it generates so much more traffic than, you know, the average user that's on the platform that they've tried to do sharding or partitioning or whatever and it just grows a lot larger. So maybe be aware of that if you have sort of a very open system like that where user-generated data could be different depending on how people are using the system. And then another one sort of, at least in theory, you have more static query plans when it's list-based partitioning. And again, that just ties back to, because you have a static set of partitions, in theory, at least your queries might be more static as you're going to go query over stuff. Whereas with range, I tend to think it's a little bit more volatile with time-samped data and that kind of thing. Oh, and then, like I said, low maintenance on the list, higher maintenance on the range. And you can automate that maintenance for the range stuff, but you just have to be aware, like, if you're doing daily partitions, right, like you need a partition there every day, so you have to figure out how you want to solve that problem, right? The first question, either way that you're doing this, when you start to go into this, right, like, should the table be partitioned? Here's sort of a high-level breakdown of this before you go into it. You have to remember that when you add partitioning, it does add complexity and it does add administration cost, right? Like, you now have introduced something into your system that probably is going to need attention. You'll need to add some monitoring alerts around it to make sure that the system is functioning okay. So, so be aware of that. Another thing that we have often found, and this is one where, because we do a lot of sort of consulting with people, a lot of times they come in and they've already got a large database and they have this one big table because that's a pretty common pattern, and they're like, can you partition this data? And we look at it in a lot of cases, yes, we can partition the data. That's not the hard part. But depending on how your application is actually querying against that data, those partitions may or may not help, right? So there are ways that you can query that won't really take advantage of that. So you may need application changes. And then I think sometimes people just assume if I partition the table, it's like, you know, it's one of the magic pixie dusts that I've sprinkled that now my performance problems go away. It's like, no, you might still have to change the application. So if you're doing this because you're using like a proprietary application where you can't go back and change the code, it's not a guarantee that this will solve your problem, right? It doesn't mean it's not worth a shot and you probably can test it to some degree, but just be aware that you may need application changes. You might want to look at indexing first. Like I said, you have partial indexes. There are print indexes now. Indexing might be an easier way to solve your problem. It's certainly much easier to just add an index to a table than it is to add partitions and shuffle data around. So if an index can solve your problem, like I would totally recommend doing that first. And then again, right, number of rows, the types of queries that you're doing, data growth that you expect. You know, somebody, I remember getting a question on, I think they're like running out of space on their system and they're like, if I partition this data, will that help? You know, and you're like, well, probably not because if, like, the data is the data and it might actually be worse. Unless you have a lot of bloat in your table and it's because you're trying to do some delete cycles and maybe that would help. But there are other ways to solve that problem as well. Like that's really not, that's not what partitioning is for. So think about that, if your real problems are, I just have so much growth that I'm out growing my systems, like partitioning might not actually solve that problem. Right? So just be aware of that. So let's say you foolishly decide, yes, I do want to partition. I haven't done that. I need to check that off to Postgres activity belt. So this is sort of the old way to think about how partitioning works. And again, old means like nine, six or before. So, you know, maybe not that old. So here what we're basically doing, we're defining a parent table theoretically. I'm either going to turn my computer off or I will have a red dot. Okay. Hey, look at that. Move this out a little bit. So we're creating a table of payment. Sort of the key stuff is in here. Right? It's pretty straightforward. This is just a regular old thing. Right? I got a payment ID, customer ID, blah, blah, blah. I'm using timestamp without time zone just to spark debate later at the bar. Got a sequence in here. Right? So this is sort of a regular table. And this might look like what a table would look like before you partitioned it. Right? But this is going to be the parent table. And then one of the things that's interesting is this whole thing about the primary key, you'll notice that this only looks like a primary key. It is not actually a primary key. And so this is one of those quirks that you start to find when you get into partitioning that primary keys aren't exactly a thing when you start doing partition tables. And we'll talk about that a little bit more in a second. So here I'm actually defining, again, this is the old way. This is me defining a child partition. And I use those terms parent and child really only because this came out of Postgres' inheritance scheme. Right? And so then those words are sort of stuck in my head. And other than that, like, just sort of feel free to roll with that on some level. It's pretty straightforward again, right, on creating a table. The important part is I've got this constraint here, right, that's going to tell me what data is going to actually go into this table. And then this is sort of the key, an inheritance payment, right? So it inherits this table back over here. So it's going to get all these attributes for the most part into the partition, right? All the columns are coming from the base table. Right? So that's fairly straightforward. This is basically saying, I'm naming this, you don't have to name it, you can name whatever you want, right? I would say try to do something to preserve your sanity. I'm just naming it, like, 2007 for the year and then 01. I'm going to do a monthly partition set, right? So this is going to be January 2007. Incidentally, so this is a slide. This schema, if you want to play with it, it's on GitHub under a project called Pajila, which is P-A-G-I-L-A, and it has the partitioning schema and some other tables and whatnot, if you want to play around with it. So just FYI. So again, name the table good, right? And like I said, you can do whatever you want with that. Here's the important part, right, is this non-overlapping constraint. My payment date has to be greater than or equal to January 1st or less than February 1st, right? So all the data for January is going to go in here. And then I have to match the data type on my constraint, so I'm still using times table without time zone. It's getting worse all the time. Oh, my God. Okay. So now, I mentioned things like primary keys. You didn't see one in the beginning. And then there are things that you might care about, like, foreign keys. How many people care about foreign keys? That's right, because this is not a MongoDB conference, Postgres conference. We care about that stuff. Some of you didn't raise your hands. I don't know. I wonder about that. So if you want to do foreign keys, this is one of the issues that, at least in the old way, you basically have to add those to every partition, right? So you go back sort of after the fact. And if you want to have those partitions, foreign key, off to a table, you can do that, right? And you add those into the individual partition. And so here, right, I'm just going off the rental ID, references rental. And so all of those little IDs, those are all references to a thing. Now, be aware that if your payment table is really large, right, and you make a thousand partitions, you're going to have a thousand foreign key constraints going back to the rental table, right, or to the customer table, that could have performance implications if you get big enough and have enough foreign key checking going on. So just be aware of that. Similarly, indexes are all sort of things that are not automatically generated in the old way, right? So if you want indexes, you have to add indexes to each individual table. You may want to do that. Here, I'm just doing, again, like, this is my naming structure for reasons I can't explain. But it mostly is, here's, right, 2001, January, this is a customer ID, and it's for the foreign key and it's an index. So it does make sense, even if it's really long. And this is for the customer ID. And I want another one on the staff ID, because I expect to be querying on those columns, right? And so you're going to add those in there. And again, much like with shampoo, you rinse and you repeat and you do it over and over again until the bottle is empty. Right. So, that seems tedious, but all of this can be automated and it's not actually that hard, and so I'll show you that in a bit. But just be aware that, like, you need to sort of think about that. Also, it doesn't actually matter if the partitions match. So if you, at some point, decide, I need an index on this column and I already have, you know, 500 partitions that don't have that index. If you're not going to query on those old partitions in that same way and you're only going to do it with new data, then you don't actually have to go add those indexes to the old ones. You can mix and match this as much as you want. You can drop the foreign keys off of some and keep them on others and all of that stuff. So, there you go. All right. So then, you have to think about how do we actually get data into the partitions, right? And again, here we are the old way. And this is a basic function that we came up with that we use. And essentially in the old system, you have sort of two ways to do this. You could directly insert into a partition and that is actually the fastest way. The downside of doing that is your application has to have knowledge of how the partitions are named and where the data belongs within those partitions. But that would be the fastest, just directly insert right into the partition if you're going to do that. If you don't want to do it that way and you just want to insert into the table and then have some magic figure it out, is anyone going to mention rules here? No? Okay, good. So, then the only way that you should do that is by using triggers. And so, here's an example trigger if you're going to write one and, right, so the way this works is just a regular function as PLP.gsql is going to be a trigger and I'm inserting into the payment table. And the way that I'm writing this one and you can do this slightly different ways, I'm just basically taking the payment date and I'm turning that into the partition that it belongs into. So, this will work for any number of partitions and it will stay there. This is not the fastest way to do this but it's sort of the most generic and maintenance free way to do it, so, you know, trade-offs. Obviously, I need to create my trigger once I have this in place and if I want to do updates and deletes I need to handle that in a trigger as well. So, on the old way, right, as they say. So, that's sort of the basics of doing it the old way. There's lots of docs on that and whatnot. But you can sort of see the process that's there and it feels very manual because it is very manual. But the upside of that is that, like, you know, you have a nice toolbox that lets you do a lot of stuff. You just have to figure out how to put all the tools together and sort of work your way through that for your individual problem. The thing that I think people generally have come to the conclusion is that most people don't want to figure out how all the tools work in the toolbox. So, for 90 percent of cases can we come up with a more, you know, like an easier version of how this stuff could work where the system would do a lot more of the heavy lifting for us so we don't have to deal with that. And there goes the dawn of the new system. Ta-da! Declarative partitioning. So, here's walking through what that syntax looks like. So this is if you have Postgres 10 or if you're playing around with Postgres 11, it should look something like this. So here again, this looks very similar and I copy and pasted it from the other slide when I did this, so I know it's very similar. I still have, right, this is not a real primary key, just FYI. The big real difference here is that part there, partitioned by range on payment date. Right? So, now we're talking, this is cooking with gas right here. So, range. I'm cooking with gas. All right. All right. Anyways, so this looks very familiar to the other one because it is very familiar and the only real difference is there and you know you're doing a range. So it's going to expect that. Now, the important thing is this doesn't do anything other than create a table. There are no partitions created based off of this. You just end up with a single table. So it's not entirely auto-magic. Here is where we're defining a partition. This also looks somewhat similar to the other syntax. So here we do create table payment. It is a partition of payment. All right. That is the key magic sauce right there. And I give it a from values so I know the date range that's there. Right? The January 1st to February 1st, very similar to the other way. You might have an internal debate with yourself. This one is like moral debates that I think people have. If this is easier to understand than doing greater than equal to, so I don't know. You have to know that this is inclusive and this one is not inclusive. So just be aware of that. I'd like to say there's a way to tell by looking at that syntax but I guess it's just knowing it is the way you can tell. But it has to be not overlapping and it will do some tests of that. If you try to create one that is overlapping, you'll get an error. So that's basically it. All columns again are coming from the base table. So whatever I define in the first thing is what's going to end up over here. Now, I might still want to have constraints. Right? Remember those things? We thought we liked them. I might still want to have indexes. And you'll note this looks very similar to the other slide. I hand typed all this though so it might not be. But it looks very similar and basically is the same thing. Right? If I want those foreign keys on those partitions, I still have to go back and add them manually. It's not going to automatically happen. And that's a fair thing in the sense that when you create a table, you don't automatically get foreign keys. So you don't necessarily want those. I mean, you probably do but you might not want them. Same thing with indexes. This is a thing. Right? So I'm just making indexes the same way. And again, this is still fairly manual. And I will only foreshadow that the idea is that since the system actually has all this kind of information in it about foreign keys and indexes and whatnot, that if we are trying to build a system that's going to be auto-magic, it may be possible at some point in the future that we don't live in today to automate these pieces and make you not have to do this. Right? But remember that when that automation happens, something like having an index on a particular column that maybe you would define in the parent which would automatically go to all child tables would be a thing that is forced upon you so you can't do the mixing and magic matching if that's a thing you are relying on. So just, that's all foreshadowing. Otherwise, you know, it is what it is today, though. All right. So let's talk about old way versus new way. So we have verbose syntax on the old way, as people would say. And they argue that this is simplified syntax so it's better. Right? I don't know how much simpler the new syntax is than the old one but it is less overall word so it is simpler, I suppose. Basically, you trigger us for all DML because we all promise we're going to talk about rules. So triggers for all DML, if you're going to do anything, you've got to have the trigger system set up there. Right? And then in theory on the new one, inserts and deletes are auto-magic and that's actually, that's a little bit better than theory. That is actually true. So one of the things that is different, I didn't have to create that trigger. When I do an insert into my payment table in the new system, right, with declarative partitioning, it does actually figure out where it needs to go and it just puts the data where it belongs. I would argue that the old way is more flexible because it gives you certain magical things that you can do that you can't do in the new one. But the new way has the possibility of future enhancements. So if I were a salesman, I could tell you there's nothing that this one can't do that won't be in some future version of Pestgrass even if it's a hundred years from now. Here's where you sign the check. But I'm not selling it so pay no attention to that comment. All right. So that's basically the two ways to look at this now. I will also mention that I'm calling this the old way in the new way, but everything in the old way you could actually still do in Pestgrass 10. There's nothing that actually prevents that now. So if you have a big 9.6 system and you got, you know, all this crazy partitioning stuff that you've been building up for 10 years and you're like, oh, my lord, I don't want to go to Pestgrass 10 because I've got to do a massive upgrade and change my partitioning scheme and all that jazz, that isn't actually true. You can keep the same partitioning scheme that you have now and get that into Pestgrass 10 and then see about changing over to the new syntax, which you probably will want to do at some point, but you don't have to do that as part of an upgrade. All right. So don't fret about that kind of a thing. And again, future enhancements, they're very magical. All right. Automating maintenance. This is obviously a thing that we all seem to care about because doing less work is usually better, especially if it looks like you're doing more work when you do less work. So if you're doing deterministic partitions and generally calendar-oriented stuff is deterministic, not always, but usually, so those can all be pre-created if you want to, right? So if I say I'm going to have 10 years worth of partitions or, I guess, more realistically, right, the average tech worker stays on a job about two and a half years, maybe. So I need three years worth of partitions to know that it's the next guy's problem. So I could create all of those partitions today for three years, and then I don't actually have to automate anything. Like, they're already there, right? So hey, I'm awesome. And when it breaks on the next guy, you should never let me go, right? Like, that's how that works. You can also do these triggers and functions. You can create those dynamically as well. So if you want to do some tricks with that and, like, recompile your function every day for whatever reason, usually it's performance-related type stuff, you can do that as a group. Do the cron job and just keep rebuilding those functions and do that kind of thing. Again, I mentioned the partition structure doesn't have to be complete. One thing we found in moving systems from, or moving data from older systems like non-postgres when we import them into postgres, like in a migration system, we have found that in some cases, because they've used something more akin to declarative partitioning in the other database system, they have, like, lots of empty partitions. In the postgres world, you don't actually have to have those. You can just drop those pieces if you don't want them. You can argue whether that's better or worse, because it looks weird when you miss partitions, but then you have less stuff to prune through, so it just kind of depends. In all of this stuff, when it talks about automating maintenance, the main thing to know is that you're going to get very familiar with this function called generate series. It'll be a good friend of yours. So, and I say this because most of all the stuff ends up running around this if you're doing anything with, like, intervals and times and all that, right? So everybody, I hope, knows what generate series is. If you have not seen this function before, there's a good chance that this was worth the price of admission right here. And I know this looks like really simple, right? I'm just doing a call, 1 to 12, it gives me 12 rows back, but that's pretty powerful. Right? Now you can do things like loops and numbers and all that stuff. As an example, this is the way we used to do this back in the day, you could turn that 1 to 12 into a series of dates, right? So I'm doing, picking my starting date, let's say I want to do that for today, I want to build some partitions, I multiply it by one month for each one, and then I get a list of dates back, right? So I can now, I can turn 1 to 12 into a list of dates. This is just a matter of doing, you know, SQL to add in table names and naming scheme and that kind of thing. And you can do that for tables and indexes and foreign keys and all of that stuff, right? So and then you can create and delete everything based off of the list that you're generating, right? And you can even get this fancy enough where like, what we would usually do if we're doing like a cron job that's going to build, you know, new partitions out, we have it build like a range of them. So look, you know, three or four in advance and three or four behind and then if you don't see anything, build those that are there. So you can sort of do some querying against the system tables to do that. Now if you're doing that and you're doing this as cron jobs, like you got to pick a language and make that work and then, you know, got to have cron, which probably you have. And putting all that together, that can be quite a bit of work, especially the number of, you know, every time you have a new partition table in your system, it's like a new set of this stuff that you have to build. If you were the kind of person who doesn't like writing the same code over and over again, you could actually build an entire toolkit based around this and we actually did that. And we did it several times before we did it the final last time that we're like, let's stop doing this all the time. So we did this like all the cron jobs and building this stuff and triggers and functions that recompile and all that. And for each implementation, we kept writing it and then we got smarter and I guess it was 2012, we wrote it as an actual extension and made a generic toolkit. So and this lives, this is the version that we use, which we sort of track to stable. I believe Keith has a talk later. He's sort of the primary author of this and he's got to talk, I don't know if it's today or tomorrow, also about partitioning. He's going to talk quite a bit more about this, I believe. So I will not talk too much about it here. But this is basically a toolkit for automating partition maintenance, right, to doing the triggers and indexes and pruning away old ones and building the new ones and all that stuff. I will say that like, I don't know the long-term future of this, right, because the theory is that declarative partitioning and all the new bells and whistles is going to make things like this not necessary. We are not in that future yet, so you might still want to use it. And certainly if you're doing the old stuff before you go building a lot of your own stuff, I would say take a look at it. There are certain parts that we're trying to sort of enhance, for example, having it work as a background worker versus a cront job, right, so that you don't necessarily need cront to do it. And then support for Postgres 10 is complicated, but there's some level of it happening. Part of the issue is that as Postgres adds those features in line, right, how do you actually manage that and keep some level backwards compatibility for old features that you don't need anymore, right, because the syntax is changing and whatnot. So that's a thing, though. Definitely check it out. If you're on an older system now, it's worth looking at. And in the future, I'd keep an eye on it. So, all right. So now I mentioned the whole, like, unique primary key thing and sort of other issues, and I want to talk about that a little bit more. So first off, let's start with the whole primary key is in unique constraints. I think we generally like those. They tend to be useful. I'm pretty sure there's got to be at least one replication talk at this conference, and it's probably going to say during that that you need a unique key or primary key in order to use that system. And so partitioning kind of doesn't jive with that. And the big issue in the Postgres world, if you're trying to sort of follow along, the way that Postgres implements uniqueness in the system is by using indexes. And at the moment, in our current reality, they don't have multi-table indexes, right, to span across the parent and all those children. There are several different ways that has been debated, and maybe something will get implemented at some point. But that's sort of the heart of that matter, that you don't have a way to create those indexes. Now, if you still need uniqueness on a particular thing, you might be able to get that to work, but it does depend. If what you happen to want uniqueness on is your partition key, you can logically guarantee that within the system, right? So and basically what you do is for the partition key, you can still define that individual column with a unique index, right? And so you can have that there. The constraint itself is going to guarantee that data across those partitions is unique because it has to be something that doesn't fit in multiple partitions, right, any given piece of data. So we know that it's unique across those partitions. So if you make that unique index on each individual partition, you guarantee uniqueness within the partition. And so now you know that across your entire partition set, that data is still unique, right, from a logical standpoint. The issue there is if you're not doing this with the partition key, right, you want some other column to be unique, that you can't really do. You can add the unique index within partitions. You can still do that, but that doesn't guarantee across partitions that you don't have a duplicate. So that might almost be more dangerous, I think, because it might give the illusion that you have uniqueness there when you don't. Now I have seen people try to do things like create a before trigger that selects on the table to verify that there isn't anything in there. That usually creates performance issues and it creates sort of logical conflicts. So if you want to employ a consultant at some point, I would totally recommend implementing that, but otherwise, probably, you don't want to do that. I will say, even though I said I wasn't going to talk about PesCrest 11, PesCrest 11 does actually handle this automatically. So if you want unique on the partition constraint, that will be there in PesCrest 11. But you did not hear that from me because I'm not talking about PesCrest 11. So, like I said, non-partition key, you're kind of out of luck. Maybe you can do a custom function. I like that because that's so open-ended that it could probably be true. All right. Queering partitions is another sort of important thing. I mentioned earlier, way back in the beginning, where everyone was excited about partitions and they're probably less so now, that you might have to change your application. So I did want to talk about that a little bit because this can be tricky to get right. And I don't know. Some developers I find, none of the developers here because you're all super smart people. Some developers I find have a real hard time with the quirks of PesCrest's query planner and how it likes to think of data. And so being aware of some of these things is a good idea. The sort of heart of this is that you have to query on static values in order for a constraint exclusion to work. And this is PesCrest's definition of static value. So if you think it's static, but PesCrest doesn't, PesCrest wins. And really the only way to guarantee this is to go test the queries. And you don't really have to run the thing if you're worried about it being expensive. Just do like an explain and look at the plan that's generated. If you're doing an explain on partitions set and you think constraint exclusion should work, you'll see that in the plan. So you don't actually have to run the query itself, but at least test to make sure that the plan that you want is being generated and then you're good. Can you give an example of what you use by static value? I can. Oh, fantastic. It's not a plan, I swear. So this is not the obvious version, but I find it humorous. So I went with this one. I'll give you the more obvious one after this. But, okay, so let's say for some reason I decided that I wanted to do this. I created a table called tf, which stands for true false, right? Because I want to have truths and falses, and I'm going to partition them. And so I have a column B, which is just a Boolean. It is not null, so you have to be, it's not a three-valued Boolean, right? It's a two-valued Boolean. So that's a thing. And I partitioned it by a list because I like list partitioning. On the column B, I create a partition for my truths, right, for values in true, create table f partition, tf for values in false. And now I have constraint inclusion on, so I just wanted you to see that, show constraint inclusion. It's set to partition, which is the sort of new version. I forget when that changed. If you look on a 9.2, which hopefully nobody's running anymore or older, I think it'll just say on. But I have it, it's set to partition, so it's legit. And then I do this. So as I said, just want to explain on it. Select all from true false where B is true, and you can see that it looks at both partitions. So that didn't really work the way I wanted it to. Oh my, what is going on here? So anybody know what's going on here? No, let's just. Well, sort of, yes. So this is where you get into super quirky things. So this is actually the same as this, right? So here I said where B is true. Here I'm just saying where B. Which also means where B is true in a logical context. And here you can see that it only does a sequential scan on the true. So it only looks at the true partition. So it knows what it's supposed to do. And that's weird, right? I think we would all agree. So if your app is doing this, which is far more likely, I would say, that your app is written this way, it won't actually work with your partitioning, right? You have to go ahead and change your app code to do something like this. Now you can play around with this some more if you do this at home. It's all kinds of fun. So if you want to do where not B, right, that actually does sequential scan on the false partition because it knows that not B must be false because we only have two values, right? It's Boolean, true and false. So, and the filter is not B. So awesome, right? Nobody wants to write their stuff like that. Okay, this is also a good one. So, because there's lots of ways to write things in Postgres. So if I do select all the true values where B in T, right, so T essentially means true for Postgres, then that works and it knows only look at the truths in the true partition, right? And if I do this one where B in yes, it also understands that because yes is another thing that Postgres understands means true. But if I do this because obviously this would work where B in true or yes, then it has to actually look at both partitions because reasons, right? And so that's just, you know, that's the way it goes. And what I love about this is the filter is B equals any true, true in Boolean, right? So it's almost like it actually knows that the yes is a true and that this should work, but it doesn't actually work. So, yeah. Now this is the one that I find fun because I just think software is out to destroy me and this is an example of it's true. It is. The more likely thing that you're going to run into is this problem where you're going to do like select all from payment where payment date equals current date and you cast that out to a timestamp and you're going to see it goes after all the partitions. And this one, people probably, somebody probably knows why. Do you know what? You were bold before. Maybe that was a mistake. No. That's a red herring, but no. Okay, so if I do this, select all from payment date equals 20808 and I do a timestamp TZ, you will see that it does actually see that, hey, that looks like the wrong results entirely. This will actually work, believe it or not. Don't pay no attention to the slide. So the reason that this does not work is, yeah, current date is actually not a static value, right? Depending on the time zone that you are in, you would get a different result back for what the current date is. And this is the kind of thing that I think drives people crazy where if you want to know, give me aggregates over the last 30 days, right? And you take current date minus 30 days or you even pass in a static date value minus 30 days, like Postgres will not necessarily interpret that as a static value because time zones can change what you are dealing with. Yes, sir? No. Pretty sure that won't work. What we have basically found is that you have to generate the dates that you want in your app and pass them to Postgres as static variables, right? Like anything that involves arithmetic starts to become trouble. So I would just say, I mean, again, I would always say feel free to test it, right? If your app is already written one way, you are certainly going to test it. But believe me, I'm just a dude doing a talk, right? Like certainly test it. It's a better idea because this also could be slightly version dependent depending on the results that you'll get back. But generally speaking, what we found is generate that on the app side and then pass those values in and then that's the only way, right? Because Postgres doesn't, for constraint exclusion, you almost have to assume it doesn't know what the data type is. Like it's just looking at this thing as like, it's almost like text matching it, right? It's kind of that dumb not to be mean, but that's how it approaches this, right? And so if it doesn't actually match, it'll have an issue. I think the one of the ways to explain this is I think that the, this is happening at the lab time where it's the evaluation of the current data type. So there is some work going on to do, I think some people call like execution time pruning. So there's some work going on to make that value in its kind of progress. That's not even a Postgres 11 feature. So but yeah, but there is talk about, because this is a problem that everybody kind of complains about because it's a, it's a pretty normal kind of thing that you might want to do. And so that, yeah, it becomes problematic. So would you end up seeing, if you ran and explained a plan like this, but you explained it in all ways, you'd see the pruning and the execution? I don't know how that would actually look like. I think that's actually one of the questions that's there is how would you actually show that? Because at planning time, that would actually be my expectation, but that's exactly like the behavior you don't want. So blah. So just like I said, be aware that this is the kind of thing, you might have an issue. And we've tried like, oh, what if I, you know, wrap this in a transaction? Does it matter, that kind of thing? That doesn't matter. Yeah, or trying to do it in a function doesn't really work. What you may do is instead of this, you could call a function that passed in current date and then you could rewrite that as a static SQL statement. But if you've changed your app to call a function, you might as well just change your app to pass in the date. So I would think so. So anyways, be aware of that because somebody is going to yell at you if you implement, this is another way that like you implement partitioning and it's going to solve all your problems. And then again, like it doesn't because they're querying it in some way, you know, they're using this thing, the BNTS type thing and like it doesn't work and so then you get yelled at. So, all right. So I'm actually out of time, which is fine because I have this slide and then I can be done. Thanks everybody for coming. I hope you enjoyed it. I hope you have a good PGCon. I'm going to go get drunk, I guess, because I'm done. If you want to find code for some of this stuff, the Pajila thing is at github.com slash Xilla. That's my GitHub and so you can find that there. OmniTI Labs will have a link to Partman if you want to get that. Reminder there is a Postgres Slack. If you want to join that and have chats, there's a PGCon room so you can chat with fellow attendees or ask questions and whatnot. If you're too hip for IRC or whatever, you can use the Postgres Slack and slides will be omniTI.com slash presents. So that's my show. Thanks everybody. Thank you.
 So, postgres partitioning.  Yay.  If you want to follow along online, you can find me at atrobtreat2.  I will post a link to the slides or something along those lines there.  If you feel the need to tell everybody how awesome this talk was and it's the best thing  you ever saw, that's a good place to do it.  If you find that you have issues or like moral, you know, distaste for what I'm saying, please  write me a lengthy email, send it to robertreatomniti.com.  I will totally take care of that.  And then, again, if you're looking for the slides later on, omniti.com slash presents,  probably there should be a version there, maybe now or already or soon thereafter.  So you can look for that there.  Feel free to take notes but know that the slides will be up online so you don't have  to kill yourself with that kind of thing.  Also, probably feel free to ask any questions.  Let me look.  I don't think anybody who I don't want to ask any questions is in the room, so that's  fine.  Okay.  And then we will just go ahead and move along.  So, postgres partitioning.  How many people actually use partitioning of some type now?  You're in the room?  Okay.  So a bunch of people here do actually use it.  So the part of the genesis for me doing this particular talk is in 2007 I did a talk at  PGCon called postgres partitioning.  And so I was like, wow, I can reuse a lot of my slides if I do this talk.  So that was awesome.  But actually, so I did that talk in like 2007, 2008, did it a few times.  And basically nothing changed in the world of postgres partitioning for like 10 years  time, right?  Up until, you know, postgres 10, whatever you're doing.  There's a few tweaks and changes here and there, but by and large, like whatever you're  doing at that time when that first got implemented, that is basically what you'd be doing today.  And there's potentially a number of you out there, you'll find them out in the wild that  are still doing all that stuff, quote unquote, the old way.  And so that's sort of still a thing and something to be aware of.  So we were doing a lot of partitioning very early on where I work and on some of the clients  that we had.  If I think about those systems that we had back then, most of them have gotten bigger.  We've been lucky to get some of them to shrink, like going back at companies and saying like,  you know, this Billion Row Table has a lot of data that you don't really use that you  think you need to keep around.  And if you talk to the lawyers, they realize like potentially there's risk of keeping all  this data as GDPR has now sort of codified like, hey, maybe you don't want to keep all  the data all the time.  So we've actually been able to shrink some of that stuff down.  But thinking about the idea that like 10 years ago with the inheritance-based partitioning  system that Peschers had, we were doing terabyte plus tables, right, billions of rows, tables  with easily over 2,000 partitions.  And like some of those, if you're like, how do you get to a table that's that crazy?  There are really bad ways to do it.  I think most of the time that we did it would be something like you're doing daily partitioning  and you have 10 years of data and then, you know, like, bam, you've got a lot of partitions,  right?  And it's pretty legit because each partition is probably like 100,000 rows at least.  So you're like, if you total that up, right, you get to decent-sized numbers, especially  10 years ago.  So that was sort of the realm that we were coming at this from.  And like I said, we ran that for years and years and years.  Life always got better even if partitioning didn't change because post-dress got better,  right?  And it was like sorting in the query planner and all that that would get better.  So that was always good.  We don't have to worry too much about that.  So, okay.  So what is then post-dress partitioning in the context at least that I'm speaking about  it?  And what are we going to talk about?  Like I said, so I want to talk about what it is now-ish, how it was implemented, which  like I said is probably what a lot of people would see if you went and looked at a system  and understand the difference.  The new implementation, I will talk a little bit about future goals.  I'm not going to totally ignore it, but if you're like really on the bleeding edge, I'm  pretty sure there's a talk tomorrow, I think it is, that is strictly on post-dress 11.  So I would certainly encourage going to see that.  And then a little bit about partition maintenance because that's always fun to talk about when  you get into this stuff.  So, all right.  So we're going to start with the why, which is why you use partitioning.  For those that aren't out there, most of these reasons should be sort of obvious, I think,  but as, you know, if you're coming from other databases, I think the reasoning can be a  little bit different.  So the main thing is really just right as your table size grows, eventually things slow  down or maintenance becomes a problem, that kind of a thing, right?  Even with indexing, you can get really, really far with indexing.  Certainly in post-dress, you have, you know, capabilities for like functional indexing  and, you know, partial indexes and all this stuff.  And that can get you really far.  You can build very large tables and if you know you only query the last three months,  you can keep rolling indexes on those tables and if that's your query pattern, like that  can work and that can be really good.  But eventually at some point, right, that still sort of becomes an issue for other things,  right?  Usually that's on the management side, right, when you think about adding in more data and  if you have to remove data and that kind of thing.  You can use partitions as a way to make that process easier, right?  If you're doing like constant delete cycles and that sort of thing, especially because  usually for most people that tends to be sort of time series oriented where it's old data  in the table that you're deleting and the problem is with the heap structure, like that tends  to get captured in ways that's harder to reuse.  Similarly, things like vacuum just, you know, it takes a long time on really large tables,  right?  We've all sort of experienced that, I'm sure.  And the larger that that gets, the more improbable it becomes to do certain commands, right?  Like a table that you could cluster and maybe you have a downtime window enough where you  can lock that table and have it be clustered early on, eventually gets large enough that  rewriting it, you know, just takes too long and that's not an option.  This way, like if you use partitions, right, you start to separate your data into active  and inactive data is one sort of strategy you could take and then focus on the active  data for things that maintenance is going to do.  And then finally, like sort of general scalability, right, if you're trying to grow the system  out, mostly I would say, certainly for the initial implementation, the thought was if  you're vertically scaling the system versus horizontally scaling the system, right, that  scalability partitioning really addresses the vertically scaling type thing.  So obviously hardware, right, it's limited even in the cloud, like you still have certain  limits as to what you can do, instant sizes you can get and that type of a thing.  If you're going to maintain performance and keep that going, like at some point you turn  to the partitioning tool belt and sort of do that.  So those are sort of the basic things that will drive you in this direction and then  it's sort of a matter of saying like, okay, what are the tools that Postgres is giving  me, right, and then how do I actually apply them.  Thinking on the historical notes, like I said, it's been a long time, right, so we had, if  you were pre-8-1, how many people here were pre-8-1?  Okay, yeah, this is the conference with the old people, it's fun.  So you would do, maybe you would do this, I don't, we tried to do this and it really  didn't work that well, but you know, you got to try something.  You would try to do like manual partitioning, just using like Postgres is built in inheritance  and maybe you'd put views on top of that, so if you're querying into the view, right,  it would go and hit these tables that had an inheritance scheme.  The basic problem was the query planner didn't know much about that, so it didn't work all  that well.  8-1 was really the, you know, the release that gave you what I thought was a usable  partitioning scheme that could then go and sort of compete with other databases, and  that was all based on constraint exclusion, which meant the query planner could at least  be smart enough to try to attempt to do queries on these large tables.  And then like I said, nothing changed, I wouldn't say nothing, but that was mostly it until  version 10, which is generally, there's different terminology for this, but I tend to refer  to it as declarative partitioning, and it tends to be the way that people refer to that  type of partitioning and going about it that way.  And I'm going to show you like some different commands with the old style versus the declarative  style, so you'll see the difference there.  Speaking of terminology, another thing to be aware of when you first go into partitioning  and you're trying to think about how you want to start slicing and dicing the data, there's  two ways people generally talk about partitioning if you're out there on the Googles looking  at stuff, right?  It's a range partitioning, I find to be the very common case, which is, you're right,  you're just doing specific ranges, usually it's based on a timestamp.  It can be other stuff, but that's sort of the most obvious one that people do.  But you also have another type of partitioning, which is list partitioning, and then again,  it's sort of like what it says, like you have a list of sort of values and data, and then  you want to sort of slice that up and put it that way.  Probably the idea of calling it a list versus a range implementation-wise is arguably not  much different, but the usage of that data tends to be very different, and that's where  the differences come in, right?  The list idea is usually that it's a fairly static list, it's not going to change very  often, so if you think of like zip codes as a good one, at least in some places it's a  good one.  I guess like in Ireland, I don't think they really have zip codes, so no offense to anyone  there.  If you've tried to send a letter there, then you know what I'm saying.  But in a lot of places, like it's pretty well defined what your zip codes are.  They could change over time.  Area codes for phones might be another way that you might do it.  And again, like there can be some change in that, but the basic idea is it's a fairly  static list and you'll put your data into the right bucket accordingly.  User ID as a hash is another one I've seen, or taking like user ID or an email address  and splitting off the first one or two letters and making buckets out of that, right?  You've only got 26 letters, you don't really expect them to add more if you're working  on like the American English alphabet.  So just think about it in those kinds of ranges and think of the data that you're having.  If it's accumulating over time, obviously it's going to fall into range partitioning.  And if you're trying to do some other thing, you're probably looking more at list partitioning.  Most of what you find if you try to do research on the subject is generally oriented towards  range partitioning and like where guys are getting, you know, sort of funding and money  to solve problems, all seems to be like time stamp oriented range stuff because that's  what a lot of people are trying to do.  So you'll see most of the literature that way, but the other way is just as valid and  also certainly useful.  Digging into that a little bit more, right?  So list, thinking hashes or states, that kind of a thing versus dates and serial.  In both cases, you do have to do some level of list maintenance, right?  But I think in partition maintenance, sorry, it'll do some level of partition maintenance.  But with the list, again, it's assumed it's set.  One important key difference to think about if you're doing sort of long-term planning,  that in a list, you generally expect data per partition to grow, right?  So like you have a static set of lists, so the data within each partition is going to  grow.  So in the range, usually you think that that amount of data might be fairly static, right?  Like it's a month's worth of data and it's always going to be a month's worth of data.  That doesn't always work out because obviously if you have a service that's growing, you  might start collecting more data over time, right?  So those future partitions you probably would assume might be bigger than the ones that  you have currently.  But otherwise, like it can be fairly static if you have, you know, a set number of things  you're collecting from.  One of the other things with list partitioning, there's nothing in there that really keeps  those things being equal, right?  So if you divide, if you take like email addresses and divide them up by letters, you know, there's  going to be a lot more that start with certain letters and less data in the ones and further  ones.  So you just have to be aware of that.  User IDs is another one that people can get in trouble with.  If you do strict partitioning based on user IDs and the data that's accumulated is dependent  on how users actually use your system, you'll definitely have some users that create like  hotspots in the system and other users that won't, right?  So you can end up with partitions that are very large, a good sort of example.  If you talk to folks at Instagram or even, I think, like Flickr have this issue where,  like, you'll get celebrities who are on the platform, right?  And so they have, they just get slotted into a bucket because nobody really knows that's  what's happening in the initial part.  And then they post something and it generates so much more traffic than, you know, the average  user that's on the platform that they've tried to do sharding or partitioning or whatever  and it just grows a lot larger.  So maybe be aware of that if you have sort of a very open system like that where user-generated  data could be different depending on how people are using the system.  And then another one sort of, at least in theory, you have more static query plans when  it's list-based partitioning.  And again, that just ties back to, because you have a static set of partitions, in theory,  at least your queries might be more static as you're going to go query over stuff.  Whereas with range, I tend to think it's a little bit more volatile with time-samped  data and that kind of thing.  Oh, and then, like I said, low maintenance on the list, higher maintenance on the range.  And you can automate that maintenance for the range stuff, but you just have to be aware,  like, if you're doing daily partitions, right, like you need a partition there every day,  so you have to figure out how you want to solve that problem, right?  The first question, either way that you're doing this, when you start to go into this,  right, like, should the table be partitioned?  Here's sort of a high-level breakdown of this before you go into it.  You have to remember that when you add partitioning, it does add complexity and it does add administration  cost, right?  Like, you now have introduced something into your system that probably is going to need  attention.  You'll need to add some monitoring alerts around it to make sure that the system is functioning  okay.  So, so be aware of that.  Another thing that we have often found, and this is one where, because we do a lot of  sort of consulting with people, a lot of times they come in and they've already got a large  database and they have this one big table because that's a pretty common pattern, and  they're like, can you partition this data?  And we look at it in a lot of cases, yes, we can partition the data.  That's not the hard part.  But depending on how your application is actually querying against that data, those partitions  may or may not help, right?  So there are ways that you can query that won't really take advantage of that.  So you may need application changes.  And then I think sometimes people just assume if I partition the table, it's like, you  know, it's one of the magic pixie dusts that I've sprinkled that now my performance problems  go away.  It's like, no, you might still have to change the application.  So if you're doing this because you're using like a proprietary application where you can't  go back and change the code, it's not a guarantee that this will solve your problem, right?  It doesn't mean it's not worth a shot and you probably can test it to some degree, but  just be aware that you may need application changes.  You might want to look at indexing first.  Like I said, you have partial indexes.  There are print indexes now.  Indexing might be an easier way to solve your problem.  It's certainly much easier to just add an index to a table than it is to add partitions  and shuffle data around.  So if an index can solve your problem, like I would totally recommend doing that first.  And then again, right, number of rows, the types of queries that you're doing, data  growth that you expect.  You know, somebody, I remember getting a question on, I think they're like running out of space  on their system and they're like, if I partition this data, will that help?  You know, and you're like, well, probably not because if, like, the data is the data  and it might actually be worse.  Unless you have a lot of bloat in your table and it's because you're trying to do some  delete cycles and maybe that would help.  But there are other ways to solve that problem as well.  Like that's really not, that's not what partitioning is for.  So think about that, if your real problems are, I just have so much growth that I'm out  growing my systems, like partitioning might not actually solve that problem.  Right?  So just be aware of that.  So let's say you foolishly decide, yes, I do want to partition.  I haven't done that.  I need to check that off to Postgres activity belt.  So this is sort of the old way to think about how partitioning works.  And again, old means like nine, six or before.  So, you know, maybe not that old.  So here what we're basically doing, we're defining a parent table theoretically.  I'm either going to turn my computer off or I will have a red dot.  Okay.  Hey, look at that.  Move this out a little bit.  So we're creating a table of payment.  Sort of the key stuff is in here.  Right?  It's pretty straightforward.  This is just a regular old thing.  Right?  I got a payment ID, customer ID, blah, blah, blah.  I'm using timestamp without time zone just to spark debate later at the bar.  Got a sequence in here.  Right?  So this is sort of a regular table.  And this might look like what a table would look like before you partitioned it.  Right?  But this is going to be the parent table.  And then one of the things that's interesting is this whole thing about the primary key,  you'll notice that this only looks like a primary key.  It is not actually a primary key.  And so this is one of those quirks that you start to find when you get into partitioning  that primary keys aren't exactly a thing when you start doing partition tables.  And we'll talk about that a little bit more in a second.  So here I'm actually defining, again, this is the old way.  This is me defining a child partition.  And I use those terms parent and child really only because this came out of Postgres' inheritance  scheme.  Right?  And so then those words are sort of stuck in my head.  And other than that, like, just sort of feel free to roll with that on some level.  It's pretty straightforward again, right, on creating a table.  The important part is I've got this constraint here, right, that's going to tell me what  data is going to actually go into this table.  And then this is sort of the key, an inheritance payment, right?  So it inherits this table back over here.  So it's going to get all these attributes for the most part into the partition, right?  All the columns are coming from the base table.  Right?  So that's fairly straightforward.  This is basically saying, I'm naming this, you don't have to name it, you can name whatever  you want, right?  I would say try to do something to preserve your sanity.  I'm just naming it, like, 2007 for the year and then 01.  I'm going to do a monthly partition set, right?  So this is going to be January 2007.  Incidentally, so this is a slide.  This schema, if you want to play with it, it's on GitHub under a project called Pajila,  which is P-A-G-I-L-A, and it has the partitioning schema and some other tables and whatnot,  if you want to play around with it.  So just FYI.  So again, name the table good, right?  And like I said, you can do whatever you want with that.  Here's the important part, right, is this non-overlapping constraint.  My payment date has to be greater than or equal to January 1st or less than February  1st, right?  So all the data for January is going to go in here.  And then I have to match the data type on my constraint, so I'm still using times table  without time zone.  It's getting worse all the time.  Oh, my God.  Okay.  So now, I mentioned things like primary keys.  You didn't see one in the beginning.  And then there are things that you might care about, like, foreign keys.  How many people care about foreign keys?  That's right, because this is not a MongoDB conference, Postgres conference.  We care about that stuff.  Some of you didn't raise your hands.  I don't know.  I wonder about that.  So if you want to do foreign keys, this is one of the issues that, at least in the old  way, you basically have to add those to every partition, right?  So you go back sort of after the fact.  And if you want to have those partitions, foreign key, off to a table, you can do that,  right?  And you add those into the individual partition.  And so here, right, I'm just going off the rental ID, references rental.  And so all of those little IDs, those are all references to a thing.  Now, be aware that if your payment table is really large, right, and you make a thousand  partitions, you're going to have a thousand foreign key constraints going back to the  rental table, right, or to the customer table, that could have performance implications  if you get big enough and have enough foreign key checking going on.  So just be aware of that.  Similarly, indexes are all sort of things that are not automatically generated in the old  way, right?  So if you want indexes, you have to add indexes to each individual table.  You may want to do that.  Here, I'm just doing, again, like, this is my naming structure for reasons I can't explain.  But it mostly is, here's, right, 2001, January, this is a customer ID, and it's for the foreign  key and it's an index.  So it does make sense, even if it's really long.  And this is for the customer ID.  And I want another one on the staff ID, because I expect to be querying on those columns,  right?  And so you're going to add those in there.  And again, much like with shampoo, you rinse and you repeat and you do it over and over  again until the bottle is empty.  Right.  So, that seems tedious, but all of this can be automated and it's not actually that hard,  and so I'll show you that in a bit.  But just be aware that, like, you need to sort of think about that.  Also, it doesn't actually matter if the partitions match.  So if you, at some point, decide, I need an index on this column and I already have, you  know, 500 partitions that don't have that index.  If you're not going to query on those old partitions in that same way and you're only  going to do it with new data, then you don't actually have to go add those indexes to the  old ones.  You can mix and match this as much as you want.  You can drop the foreign keys off of some and keep them on others and all of that stuff.  So, there you go.  All right.  So then, you have to think about how do we actually get data into the partitions, right?  And again, here we are the old way.  And this is a basic function that we came up with that we use.  And essentially in the old system, you have sort of two ways to do this.  You could directly insert into a partition and that is actually the fastest way.  The downside of doing that is your application has to have knowledge of how the partitions  are named and where the data belongs within those partitions.  But that would be the fastest, just directly insert right into the partition if you're  going to do that.  If you don't want to do it that way and you just want to insert into the table and then  have some magic figure it out, is anyone going to mention rules here?  No?  Okay, good.  So, then the only way that you should do that is by using triggers.  And so, here's an example trigger if you're going to write one and, right, so the way  this works is just a regular function as PLP.gsql is going to be a trigger and I'm inserting  into the payment table.  And the way that I'm writing this one and you can do this slightly different ways, I'm  just basically taking the payment date and I'm turning that into the partition that it  belongs into.  So, this will work for any number of partitions and it will stay there.  This is not the fastest way to do this but it's sort of the most generic and maintenance  free way to do it, so, you know, trade-offs.  Obviously, I need to create my trigger once I have this in place and if I want to do updates  and deletes I need to handle that in a trigger as well.  So, on the old way, right, as they say.  So, that's sort of the basics of doing it the old way.  There's lots of docs on that and whatnot.  But you can sort of see the process that's there and it feels very manual because it  is very manual.  But the upside of that is that, like, you know, you have a nice toolbox that lets you  do a lot of stuff.  You just have to figure out how to put all the tools together and sort of work your way  through that for your individual problem.  The thing that I think people generally have come to the conclusion is that most people  don't want to figure out how all the tools work in the toolbox.  So, for 90 percent of cases can we come up with a more, you know, like an easier version  of how this stuff could work where the system would do a lot more of the heavy lifting for  us so we don't have to deal with that.  And there goes the dawn of the new system.  Ta-da!  Declarative partitioning.  So, here's walking through what that syntax looks like.  So this is if you have Postgres 10 or if you're playing around with Postgres 11, it  should look something like this.  So here again, this looks very similar and I copy and pasted it from the other slide  when I did this, so I know it's very similar.  I still have, right, this is not a real primary key, just FYI.  The big real difference here is that part there, partitioned by range on payment date.  Right?  So, now we're talking, this is cooking with gas right here.  So, range.  I'm cooking with gas.  All right.  All right.  Anyways, so this looks very familiar to the other one because it is very familiar and  the only real difference is there and you know you're doing a range.  So it's going to expect that.  Now, the important thing is this doesn't do anything other than create a table.  There are no partitions created based off of this.  You just end up with a single table.  So it's not entirely auto-magic.  Here is where we're defining a partition.  This also looks somewhat similar to the other syntax.  So here we do create table payment.  It is a partition of payment.  All right.  That is the key magic sauce right there.  And I give it a from values so I know the date range that's there.  Right?  The January 1st to February 1st, very similar to the other way.  You might have an internal debate with yourself.  This one is like moral debates that I think people have.  If this is easier to understand than doing greater than equal to, so I don't know.  You have to know that this is inclusive and this one is not inclusive.  So just be aware of that.  I'd like to say there's a way to tell by looking at that syntax but I guess it's just  knowing it is the way you can tell.  But it has to be not overlapping and it will do some tests of that.  If you try to create one that is overlapping, you'll get an error.  So that's basically it.  All columns again are coming from the base table.  So whatever I define in the first thing is what's going to end up over here.  Now, I might still want to have constraints.  Right?  Remember those things?  We thought we liked them.  I might still want to have indexes.  And you'll note this looks very similar to the other slide.  I hand typed all this though so it might not be.  But it looks very similar and basically is the same thing.  Right?  If I want those foreign keys on those partitions, I still have to go back and add them manually.  It's not going to automatically happen.  And that's a fair thing in the sense that when you create a table, you don't automatically  get foreign keys.  So you don't necessarily want those.  I mean, you probably do but you might not want them.  Same thing with indexes.  This is a thing.  Right?  So I'm just making indexes the same way.  And again, this is still fairly manual.  And I will only foreshadow that the idea is that since the system actually has all this  kind of information in it about foreign keys and indexes and whatnot, that if we are trying  to build a system that's going to be auto-magic, it may be possible at some point in the future  that we don't live in today to automate these pieces and make you not have to do this.  Right?  But remember that when that automation happens, something like having an index on a particular  column that maybe you would define in the parent which would automatically go to all  child tables would be a thing that is forced upon you so you can't do the mixing and magic  matching if that's a thing you are relying on.  So just, that's all foreshadowing.  Otherwise, you know, it is what it is today, though.  All right.  So let's talk about old way versus new way.  So we have verbose syntax on the old way, as people would say.  And they argue that this is simplified syntax so it's better.  Right?  I don't know how much simpler the new syntax is than the old one but it is less overall  word so it is simpler, I suppose.  Basically, you trigger us for all DML because we all promise we're going to talk about rules.  So triggers for all DML, if you're going to do anything, you've got to have the trigger  system set up there.  Right?  And then in theory on the new one, inserts and deletes are auto-magic and that's actually,  that's a little bit better than theory.  That is actually true.  So one of the things that is different, I didn't have to create that trigger.  When I do an insert into my payment table in the new system, right, with declarative  partitioning, it does actually figure out where it needs to go and it just puts the  data where it belongs.  I would argue that the old way is more flexible because it gives you certain magical things  that you can do that you can't do in the new one.  But the new way has the possibility of future enhancements.  So if I were a salesman, I could tell you there's nothing that this one can't do that  won't be in some future version of Pestgrass even if it's a hundred years from now.  Here's where you sign the check.  But I'm not selling it so pay no attention to that comment.  All right.  So that's basically the two ways to look at this now.  I will also mention that I'm calling this the old way in the new way, but everything  in the old way you could actually still do in Pestgrass 10.  There's nothing that actually prevents that now.  So if you have a big 9.6 system and you got, you know, all this crazy partitioning stuff  that you've been building up for 10 years and you're like, oh, my lord, I don't want  to go to Pestgrass 10 because I've got to do a massive upgrade and change my partitioning  scheme and all that jazz, that isn't actually true.  You can keep the same partitioning scheme that you have now and get that into Pestgrass  10 and then see about changing over to the new syntax, which you probably will want to  do at some point, but you don't have to do that as part of an upgrade.  All right.  So don't fret about that kind of a thing.  And again, future enhancements, they're very magical.  All right.  Automating maintenance.  This is obviously a thing that we all seem to care about because doing less work is usually  better, especially if it looks like you're doing more work when you do less work.  So if you're doing deterministic partitions and generally calendar-oriented stuff is deterministic,  not always, but usually, so those can all be pre-created if you want to, right?  So if I say I'm going to have 10 years worth of partitions or, I guess, more realistically,  right, the average tech worker stays on a job about two and a half years, maybe.  So I need three years worth of partitions to know that it's the next guy's problem.  So I could create all of those partitions today for three years, and then I don't actually  have to automate anything.  Like, they're already there, right?  So hey, I'm awesome.  And when it breaks on the next guy, you should never let me go, right?  Like, that's how that works.  You can also do these triggers and functions.  You can create those dynamically as well.  So if you want to do some tricks with that and, like, recompile your function every day  for whatever reason, usually it's performance-related type stuff, you can do that as a group.  Do the cron job and just keep rebuilding those functions and do that kind of thing.  Again, I mentioned the partition structure doesn't have to be complete.  One thing we found in moving systems from, or moving data from older systems like non-postgres  when we import them into postgres, like in a migration system, we have found that in  some cases, because they've used something more akin to declarative partitioning in  the other database system, they have, like, lots of empty partitions.  In the postgres world, you don't actually have to have those.  You can just drop those pieces if you don't want them.  You can argue whether that's better or worse, because it looks weird when you miss partitions,  but then you have less stuff to prune through, so it just kind of depends.  In all of this stuff, when it talks about automating maintenance, the main thing to know is that  you're going to get very familiar with this function called generate series.  It'll be a good friend of yours.  So, and I say this because most of all the stuff ends up running around this if you're  doing anything with, like, intervals and times and all that, right?  So everybody, I hope, knows what generate series is.  If you have not seen this function before, there's a good chance that this was worth  the price of admission right here.  And I know this looks like really simple, right?  I'm just doing a call, 1 to 12, it gives me 12 rows back, but that's pretty powerful.  Right?  Now you can do things like loops and numbers and all that stuff.  As an example, this is the way we used to do this back in the day, you could turn that  1 to 12 into a series of dates, right?  So I'm doing, picking my starting date, let's say I want to do that for today, I want to  build some partitions, I multiply it by one month for each one, and then I get a list of  dates back, right?  So I can now, I can turn 1 to 12 into a list of dates.  This is just a matter of doing, you know, SQL to add in table names and naming scheme  and that kind of thing.  And you can do that for tables and indexes and foreign keys and all of that stuff, right?  So and then you can create and delete everything based off of the list that you're generating,  right?  And you can even get this fancy enough where like, what we would usually do if we're doing  like a cron job that's going to build, you know, new partitions out, we have it build  like a range of them.  So look, you know, three or four in advance and three or four behind and then if you don't  see anything, build those that are there.  So you can sort of do some querying against the system tables to do that.  Now if you're doing that and you're doing this as cron jobs, like you got to pick a  language and make that work and then, you know, got to have cron, which probably you  have.  And putting all that together, that can be quite a bit of work, especially the number  of, you know, every time you have a new partition table in your system, it's like a new set  of this stuff that you have to build.  If you were the kind of person who doesn't like writing the same code over and over again,  you could actually build an entire toolkit based around this and we actually did that.  And we did it several times before we did it the final last time that we're like, let's  stop doing this all the time.  So we did this like all the cron jobs and building this stuff and triggers and functions  that recompile and all that.  And for each implementation, we kept writing it and then we got smarter and I guess it  was 2012, we wrote it as an actual extension and made a generic toolkit.  So and this lives, this is the version that we use, which we sort of track to stable.  I believe Keith has a talk later.  He's sort of the primary author of this and he's got to talk, I don't know if it's today  or tomorrow, also about partitioning.  He's going to talk quite a bit more about this, I believe.  So I will not talk too much about it here.  But this is basically a toolkit for automating partition maintenance, right, to doing the  triggers and indexes and pruning away old ones and building the new ones and all that  stuff.  I will say that like, I don't know the long-term future of this, right, because the theory  is that declarative partitioning and all the new bells and whistles is going to make things  like this not necessary.  We are not in that future yet, so you might still want to use it.  And certainly if you're doing the old stuff before you go building a lot of your own stuff,  I would say take a look at it.  There are certain parts that we're trying to sort of enhance, for example, having it  work as a background worker versus a cront job, right, so that you don't necessarily need  cront to do it.  And then support for Postgres 10 is complicated, but there's some level of it happening.  Part of the issue is that as Postgres adds those features in line, right, how do you  actually manage that and keep some level backwards compatibility for old features that you don't  need anymore, right, because the syntax is changing and whatnot.  So that's a thing, though.  Definitely check it out.  If you're on an older system now, it's worth looking at.  And in the future, I'd keep an eye on it.  So, all right.  So now I mentioned the whole, like, unique primary key thing and sort of other issues,  and I want to talk about that a little bit more.  So first off, let's start with the whole primary key is in unique constraints.  I think we generally like those.  They tend to be useful.  I'm pretty sure there's got to be at least one replication talk at this conference, and  it's probably going to say during that that you need a unique key or primary key in order  to use that system.  And so partitioning kind of doesn't jive with that.  And the big issue in the Postgres world, if you're trying to sort of follow along, the  way that Postgres implements uniqueness in the system is by using indexes.  And at the moment, in our current reality, they don't have multi-table indexes, right,  to span across the parent and all those children.  There are several different ways that has been debated, and maybe something will get  implemented at some point.  But that's sort of the heart of that matter, that you don't have a way to create those  indexes.  Now, if you still need uniqueness on a particular thing, you might be able to get that to work,  but it does depend.  If what you happen to want uniqueness on is your partition key, you can logically guarantee  that within the system, right?  So and basically what you do is for the partition key, you can still define that individual  column with a unique index, right?  And so you can have that there.  The constraint itself is going to guarantee that data across those partitions is unique  because it has to be something that doesn't fit in multiple partitions, right, any given  piece of data.  So we know that it's unique across those partitions.  So if you make that unique index on each individual partition, you guarantee uniqueness  within the partition.  And so now you know that across your entire partition set, that data is still unique,  right, from a logical standpoint.  The issue there is if you're not doing this with the partition key, right, you want some  other column to be unique, that you can't really do.  You can add the unique index within partitions.  You can still do that, but that doesn't guarantee across partitions that you don't have a duplicate.  So that might almost be more dangerous, I think, because it might give the illusion  that you have uniqueness there when you don't.  Now I have seen people try to do things like create a before trigger that selects on the  table to verify that there isn't anything in there.  That usually creates performance issues and it creates sort of logical conflicts.  So if you want to employ a consultant at some point, I would totally recommend implementing  that, but otherwise, probably, you don't want to do that.  I will say, even though I said I wasn't going to talk about PesCrest 11, PesCrest 11 does  actually handle this automatically.  So if you want unique on the partition constraint, that will be there in PesCrest 11.  But you did not hear that from me because I'm not talking about PesCrest 11.  So, like I said, non-partition key, you're kind of out of luck.  Maybe you can do a custom function.  I like that because that's so open-ended that it could probably be true.  All right.  Queering partitions is another sort of important thing.  I mentioned earlier, way back in the beginning, where everyone was excited about partitions  and they're probably less so now, that you might have to change your application.  So I did want to talk about that a little bit because this can be tricky to get right.  And I don't know.  Some developers I find, none of the developers here because you're all super smart people.  Some developers I find have a real hard time with the quirks of PesCrest's query planner  and how it likes to think of data.  And so being aware of some of these things is a good idea.  The sort of heart of this is that you have to query on static values in order for a constraint  exclusion to work.  And this is PesCrest's definition of static value.  So if you think it's static, but PesCrest doesn't, PesCrest wins.  And really the only way to guarantee this is to go test the queries.  And you don't really have to run the thing if you're worried about it being expensive.  Just do like an explain and look at the plan that's generated.  If you're doing an explain on partitions set and you think constraint exclusion should  work, you'll see that in the plan.  So you don't actually have to run the query itself, but at least test to make sure that  the plan that you want is being generated and then you're good.  Can you give an example of what you use by static value?  I can.  Oh, fantastic.  It's not a plan, I swear.  So this is not the obvious version, but I find it humorous.  So I went with this one.  I'll give you the more obvious one after this.  But, okay, so let's say for some reason I decided that I wanted to do this.  I created a table called tf, which stands for true false, right?  Because I want to have truths and falses, and I'm going to partition them.  And so I have a column B, which is just a Boolean.  It is not null, so you have to be, it's not a three-valued Boolean, right?  It's a two-valued Boolean.  So that's a thing.  And I partitioned it by a list because I like list partitioning.  On the column B, I create a partition for my truths, right, for values in true, create  table f partition, tf for values in false.  And now I have constraint inclusion on, so I just wanted you to see that, show constraint  inclusion.  It's set to partition, which is the sort of new version.  I forget when that changed.  If you look on a 9.2, which hopefully nobody's running anymore or older, I think it'll just  say on.  But I have it, it's set to partition, so it's legit.  And then I do this.  So as I said, just want to explain on it.  Select all from true false where B is true, and you can see that it looks at both partitions.  So that didn't really work the way I wanted it to.  Oh my, what is going on here?  So anybody know what's going on here?  No, let's just.  Well, sort of, yes.  So this is where you get into super quirky things.  So this is actually the same as this, right?  So here I said where B is true.  Here I'm just saying where B.  Which also means where B is true in a logical context.  And here you can see that it only does a sequential scan on the true.  So it only looks at the true partition.  So it knows what it's supposed to do.  And that's weird, right?  I think we would all agree.  So if your app is doing this, which is far more likely, I would say, that your app is  written this way, it won't actually work with your partitioning, right?  You have to go ahead and change your app code to do something like this.  Now you can play around with this some more if you do this at home.  It's all kinds of fun.  So if you want to do where not B, right, that actually does sequential scan on the false  partition because it knows that not B must be false because we only have two values,  right?  It's Boolean, true and false.  So, and the filter is not B. So awesome, right?  Nobody wants to write their stuff like that.  Okay, this is also a good one.  So, because there's lots of ways to write things in Postgres.  So if I do select all the true values where B in T, right, so T essentially means true  for Postgres, then that works and it knows only look at the truths in the true partition,  right?  And if I do this one where B in yes, it also understands that because yes is another thing  that Postgres understands means true.  But if I do this because obviously this would work where B in true or yes, then it has to  actually look at both partitions because reasons, right?  And so that's just, you know, that's the way it goes.  And what I love about this is the filter is B equals any true, true in Boolean, right?  So it's almost like it actually knows that the yes is a true and that this should work,  but it doesn't actually work.  So, yeah.  Now this is the one that I find fun because I just think software is out to destroy me  and this is an example of it's true.  It is.  The more likely thing that you're going to run into is this problem where you're going  to do like select all from payment where payment date equals current date and you cast that  out to a timestamp and you're going to see it goes after all the partitions.  And this one, people probably, somebody probably knows why.  Do you know what?  You were bold before.  Maybe that was a mistake.  No.  That's a red herring, but no.  Okay, so if I do this, select all from payment date equals 20808 and I do a timestamp TZ,  you will see that it does actually see that, hey, that looks like the wrong results entirely.  This will actually work, believe it or not.  Don't pay no attention to the slide.  So the reason that this does not work is, yeah, current date is actually not a static  value, right?  Depending on the time zone that you are in, you would get a different result back for  what the current date is.  And this is the kind of thing that I think drives people crazy where if you want to know,  give me aggregates over the last 30 days, right?  And you take current date minus 30 days or you even pass in a static date value minus  30 days, like Postgres will not necessarily interpret that as a static value because  time zones can change what you are dealing with.  Yes, sir?  No.  Pretty sure that won't work.  What we have basically found is that you have to generate the dates that you want in your  app and pass them to Postgres as static variables, right?  Like anything that involves arithmetic starts to become trouble.  So I would just say, I mean, again, I would always say feel free to test it, right?  If your app is already written one way, you are certainly going to test it.  But believe me, I'm just a dude doing a talk, right?  Like certainly test it.  It's a better idea because this also could be slightly version dependent depending on  the results that you'll get back.  But generally speaking, what we found is generate that on the app side and then pass those values  in and then that's the only way, right?  Because Postgres doesn't, for constraint exclusion, you almost have to assume it doesn't know what  the data type is.  Like it's just looking at this thing as like, it's almost like text matching it, right?  It's kind of that dumb not to be mean, but that's how it approaches this, right?  And so if it doesn't actually match, it'll have an issue.  I think the one of the ways to explain this is I think that the, this is happening at  the lab time where it's the evaluation of the current data type.  So there is some work going on to do, I think some people call like execution time pruning.  So there's some work going on to make that value in its kind of progress.  That's not even a Postgres 11 feature.  So but yeah, but there is talk about, because this is a problem that everybody kind of complains  about because it's a, it's a pretty normal kind of thing that you might want to do.  And so that, yeah, it becomes problematic.  So would you end up seeing, if you ran and explained a plan like this, but you explained  it in all ways, you'd see the pruning and the execution?  I don't know how that would actually look like.  I think that's actually one of the questions that's there is how would you actually show  that?  Because at planning time, that would actually be my expectation, but that's exactly like  the behavior you don't want.  So blah.  So just like I said, be aware that this is the kind of thing, you might have an issue.  And we've tried like, oh, what if I, you know, wrap this in a transaction?  Does it matter, that kind of thing?  That doesn't matter.  Yeah, or trying to do it in a function doesn't really work.  What you may do is instead of this, you could call a function that passed in current date  and then you could rewrite that as a static SQL statement.  But if you've changed your app to call a function, you might as well just change your app to  pass in the date.  So I would think so.  So anyways, be aware of that because somebody is going to yell at you if you implement, this  is another way that like you implement partitioning and it's going to solve all your problems.  And then again, like it doesn't because they're querying it in some way, you know, they're  using this thing, the BNTS type thing and like it doesn't work and so then you get yelled  at.  So, all right.  So I'm actually out of time, which is fine because I have this slide and then I can be  done.  Thanks everybody for coming.  I hope you enjoyed it.  I hope you have a good PGCon.  I'm going to go get drunk, I guess, because I'm done.  If you want to find code for some of this stuff, the Pajila thing is at github.com slash  Xilla.  That's my GitHub and so you can find that there.  OmniTI Labs will have a link to Partman if you want to get that.  Reminder there is a Postgres Slack.  If you want to join that and have chats, there's a PGCon room so you can chat with fellow attendees  or ask questions and whatnot.  If you're too hip for IRC or whatever, you can use the Postgres Slack and slides will  be omniTI.com slash presents.  So that's my show.  Thanks everybody.  Thank you.
Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_0_107.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_108_251.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_252_383.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_384_431.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_432_707.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_708_1019.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_1020_1991.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_1992_3311.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_3312_3431.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_3432_4451.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_4452_4571.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_4572_4811.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_4812_5783.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_5784_6731.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_6732_8027.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_8028_9623.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_9624_10895.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_10896_12923.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_12924_16355.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_16356_16547.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_16548_16907.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_16908_19775.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_19776_20759.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_20760_20939.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_20940_21515.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_21516_22967.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_22968_23459.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_23460_24563.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_24564_26639.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_26640_27563.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_27564_27671.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_27672_28667.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_28668_28811.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_28812_29135.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_29136_29363.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_29364_31019.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_31020_33131.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_33132_36683.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_36684_37391.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_37392_38207.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_38208_38663.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_38664_39671.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_39672_39851.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_39852_42131.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_42132_45227.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_45228_48227.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_48228_48923.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_48924_51059.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_51060_54011.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_54012_55727.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_55728_58295.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_58296_58607.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_58608_60383.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_60384_62099.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_62100_62651.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_62652_62831.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_62832_62903.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_62904_63359.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_63360_63551.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_63552_63659.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_63660_64223.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_64224_64859.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_64860_65795.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_65796_66695.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_66696_67091.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_67092_71903.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_71904_71951.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_71952_72143.png Robert_Treat__PostgreSQL_Partitioning_--_PGCon_201_yp_8QSWOcTI_72144_73326.png