Hello everyone. Good morning. Good afternoon. Good evening depending on where you are and welcome to the latest in the series of webinars from from second-courtment. Just a reminder for people who haven't joined us before. You do have the ability to ask questions during the webinar, but there'd be written questions in the questions tab. These questions will be recorded and answered towards the end of the session. In case you want to view the recording of this webinar or any of the previous webinars that we have held before, you can view the recording on our website, just go to secondcourtment.com slash webinars. And yeah, so today we've got a big big guy himself, Simon Riggs joins us to talk about post-crest partitioning. And yeah, so over to you Simon. Okay, thank you very much. So I'm going to talk about post-crest partitioning just to let everybody know that this is quite a long presentation. I wanted to cover a number of different aspects about partitioning. This is based on a talk that I gave at Post-crest open, but what I found at Post-crest open was that one hour was not really sufficient time to cover the top, the topic in the kind of depth that we were expecting to cover that into. So I've extended that presentation quite significantly for this webinar. So what we're going to talk about today is the partitioning feature in Post-crest QL12. Okay, so we're talking about the feature set that's in the community edition of Post-crest QL. We're not talking about any forks or additional products. And we're not really going to cover the topic from the perspective of earlier versions. So this is a headline feature in Post-crest 12. So obviously if somebody says, well, I'd like to use it at Post-crest 11. Well, yeah, it's quite a long list of things that don't work the same way as we did in previous versions of Post-crest. So the objective of this talk is really to get you to understand how the Post-crest 12 features work and assist your planning for adoption of that feature. So I won't have time to answer all of your individual technical questions. I some of you have already been sending me emails ahead of time saying, why doesn't this work right and that kind of thing? And the answer to most of those questions that I've seen is actually there's just a slight misunderstanding about how these features work. So the objective here is really to cover all of this insufficient detail that you'll be able to go and do that yourself. So you probably will have to play around with it to make it work for your solution and you will need to consult the manual as well. But what I'm trying to do here is give you an understanding that will assist your later development work. Okay? So Post-crest partitioning does require additional DDL and it is going to require effort on your part. It's not just a simple turn the switch on and it goes faster like some other features. Okay? And that's really why this webinar is longer than some of the other ones that we've done in the past. So what am I going to talk about? Well, I'm going to give you an overview of partitioning. What use case is it's trying to solve and some of the history around it. And then we're going to go into a description of the syntax and the feature itself and how it works and things that work and things that don't work in some detail. And then we're going to go into a performance analysis of partitioning how that works and sort of summarize the takeaways from the feature itself to assist your planning. Okay? So that's... So we're going to start is the overview, use case and history of this particular feature. So the general idea of partitioning is that we split big tables into multiple smaller pieces which we've referred to as partitions. Now this... The concepts around this have actually existed for around 30 years, probably as long as Postgres itself has existed in the research literature. Some other databases have put these concepts into their systems, much ahead of Postgres, but of course, Postgres is filling out a very large feature set and it's taken us a while to get through to these concepts. So why would you want to do this? Well, I think that's really the big question and the one that we are hoping to address with you today. So this generally stems from the idea of VLDB or very large database tables. Obviously as a table gets larger and larger, the scan time gets longer and longer. And that's one of the reasons why we have parallelism and why we try to do a lot of other optimisations against scans. But the most important thing is that these scans are themselves optimisable and that's what we're going to look at. But there are some other issues with very large tables and that is that as the table gets bigger, indexes get impractical. What happens is that the index is typically in a B-tree structure and the scan time for that goes up as the log of the size of the table. But worse than that, indexes tend to exhibit what's known as a catastrophe when the index gets too big for memory. So the actual performance of using the index hits a cliff and the response time gets very long indeed. And that's quite a noticeable effect. Whatever size of memory you have. Obviously you can solve that problem by just buying a system with bigger memory. But there's always more data than you've got memory. So one of the big issues with VLDB is removing the older data so that we can actually maintain reasonably bounded performance for some of our queries. So the opportunity we have is that if our data exhibits some form of internal organisation, we can actually use that to optimise the scan. We can actually use the structure or the knowledge of that data to improve the scan itself. Not by improving the speed of the scan but just simply by making it avoid work that it didn't need to do. So this is not an algorithmic speed up. It's actually a different form of speed of a tool where it could well speed things up by 10 or 100 or 1000 times depending on exactly how your queries work. So something you're talking about. Sorry to interrupt you over here. But it seems like the screen isn't refreshing with the updated slide that you're referring to. Not sure if there's something that you need to do in order to refresh that. There you go. Sure done. Okay, pressing on then. So the idea is that we're going to use the structure within the table in order to optimise scan performance against a very large table. And there's actually multiple ways within Postgres of doing that. There's something called a brain index that allows you to index block ranges within the table. That does work very well when the data is organised, for example according to order ID or according to a time stamp. So as new data is inserted towards the end of a table, the actual time stamp is increasing and that allows us to optimise it. But there's one thing that that doesn't take into account and that's when you want to force the organisation of the table. So if you just simply try and to speed up queries, brain indexes work fine. But as is often the case in a VLDB system, what we actually want is what we call rolling data maintenance. So we insert new data at the front of the table and we actually remove old data from the back of the table. Now, brain indexes don't really solve that problem whereas partitioning does. So what I will typically give as my opinion on this is that if you use both partitioning and brain indexes together, you may get a better outcome than if you just rely on partitioning. But having said that, I'm not going to spend a lot of time talking about the way that brain works. I am simply going to talk about partitioning here with you today. So obviously the two cases then that we can solve with partitioning are the data maintenance where we're removing old data and we're also able to optimise access to a small number of partitions or a small range of data within the table. Now this addresses the other aspect that we found with BDRAIN indexes is if we limit the size of partitions to a reasonable size, then the partition itself never gets larger than memory and as a result we don't hit the same performance catastrophe that we did when we weren't using partitions. Okay, so there's actually multiple different benefits that we get from using partitioning. So my message really is if you've got a very large database, then you should be planning to use partitioning. I say planning because there might be some aspect to it that doesn't work for you and it's always worth trying it out before you do so but there's lots of different benefits to partitioning. So my summary at the very start is that Postgres 10 headlined with declarative partitioning but at the time that that was published it was really just syntax only and it was poorly optimised for the OLTP use case whereas what we have now in Postgres 12 is very high function partitioning properly optimised for the OLTP use case and as you can see it's been under active development for some time. I counted out the features and there's 23 new features in Postgres 11 and 12 and 27 bugs fixed in the last year alone so a lot of work gone into where we are now. One thing I would like to say is that sharding in Postgres doesn't work fully yet and I'm not really going to be talking about sharding as a feature set in this webinar. I'll probably do something on sharding early in the next year or perhaps before then certainly not in this webinar. So where did we how do we get to this point? Well Postgres 8.1 had a feature called inheritance with constraint exclusion and that kind of sort of worked if you squinted a little bit but it wasn't really good enough for production use in a lot of cases though having said that many people did make very beneficial use of those features. So we knew from many years ago really that we wanted to improve the partitioning feature. It's really just the case that other priorities got in the way. There were some abortive attempts to make partitioning work using rules and triggers and all I can say is that's unfortunate because those approaches didn't work. We then implemented print indexes which are sort of are particularly useful but they don't give you the some of the features that you need for VLDB maintenance. So over the last couple of releases we've pushed forwards with a number of different features to get us to where we are now. That has been the work of many different people over a number of years and in the last couple of years the work has been performed by Amit Alvaro and David has actually been made into a commit a large liz or as of the work that he's done in the era of partitioning. So well done gentlemen and thank you very much. So where does that leave us? Well partitioning as implemented in Post-Crescule could have been done in lots of different ways and what I've done in this slide is just show that there are actually quite a few different ways of organizing the feature within database products. It's essentially the same feature wherever you see it but the the idea is how does it actually look to it from a development perspective. So in Oracle for example partitions are objects that are the make-up part of a larger table whereas in Post-Crescule partitions are actually tables themselves and there's also another couple of other ways of doing it as well in other products but we're not going to be contrasting between them but it's just worth mentioning that there are a number of different ways of doing it and that is one of the reasons why there isn't any mention of partitioning in the SQL standard. Now that's not to say that this is highly non-standard. The SQL standards committee just simply takes the approach that this is an implementation detail like indexes which are also not mentioned in the standard but clearly they need to exist in order for us to get good performance from our applications. So where does that leave us then? Well that left Post-Cresc with with a choice of exactly what type of syntax to implement. So what I'll show you is some syntax that looks fairly similar to Oracle and the way that it's in some other database but obviously if you're comparing it across many different databases there's just lots and lots of ways of implementing it. So it's been a difficult choice to work out what syntax we should use. So what I'm now going to talk about is exactly the syntax that we've chosen, how those features work, what the capabilities and restrictions are of that feature and how it all fits together in the current form within Post-Cresc 12. So what I'm going to do is just start with an example table. It's actually similar to the one that's used in the manual which you know is really pretty simple. We've just got a table called measurement and we're imagining that we're doing some kind of Internet of Things application where we're inserting data for a a particular device that we're recorded at a particular point in time and there's some stuff that is in JSON. So doesn't really matter what the data is that we're recording. The concept is that we're just recording new data over time and you can see I've already created a a brand index on there but not a primary key at this point. So what I'm going to do is I'm going to use range-based partitioning to split this table up into chunks over time and a typical choice here might be to use range partitioning that is based around a day or week a month a year, something along those lines. So in the particular example that we have in order to enable partitioning on this table we add the phrase partition by range and then we mention the column that we're going to partition on and you'll see that we can still add the index as we could before. So that created the top-level partitions table. What we now need to do is create each of the individual partitions themselves. So what I'm then going to do is create a series of partitions for that table that all look almost exactly the same and we're going to use a naming convention here which is measurement underscore yyyy underscore mm and so you can see from the choice of naming convention but I'm going to use monthly partitions but actually I mean that that could actually work for for a number of different ways of doing it. So if you're going to have 100 months in your dataset then you would need 100 partitions given this way of doing things. Obviously if I was going to choose daily partitions I'd need significantly more partitions in order to place the same dataset within it. So how do you choose the number of partitions and the time ranges? Well that's a topic I'll leave right to the end of the webinar. For now we can look at the full syntax itself and you can see that it's possible to add partitions in two different ways. We can create a table that is a partition of measurement or we can alter the top-level table and attach a new partition. Now those two statements that create and the alter table attach are fully equivalent and the essentially the same thing as the drop and the detach. So on the create table you'll see that we specify that we're a partition of the measurement table and that we specify four values from and then there's two values there because we're actually specifying a range of values over which we want to have data for that particular partition. So one of the first things to mention with range partitioning is the idea that because we're needing to create and drop new partitions regularly we need to understand what the locking effects are of creating or dropping our partition. So in Postgres 12 share update exclusive lock is taken on the parent when we create or when we attach a new partition and what that does is it box all rights from occurring on the parent table when you add any new partition but it doesn't do anything at all to select. So select can continue but all rights will be momentarily blocked but when you drop a partition the locking is actually different it takes a full access exclusive lock which will block both reads and writes on the parent table. So the locking is actually much harsher when we drop or detach a partition than when we create one and that's an important point because it affects the way that we think about our maintenance strategies for these particular types of table. So what other options are there for partitioning strategy because the full syntax for the partition by clause is shown here we can say partition by range or list or hash and then we can specify a number of columns. So range partitioning is what I regard as the most typical form of partitioning. Typically you'll be using that for time stamp based partitioning but it's also possible to order your data for example by customer ID so that would allow you to limit the size of your partitions based upon the number of partitions and number of customers you have. So list partitioning is kind of a subset of that really in that it allows you to partition based upon fixed values for example A, B or C and hash partitioning is something that will really have more meaning for us when we move towards sharding it doesn't have a great use case at the moment in single node partitioning. So the partitioning key is what we refer to as the set of columns listed after the partition by clause and that's a set of columns or expressions that define the partition bounds. So in my example I used only one column which was the log TS column but with range and hash strategies we can actually have more than one column whereas with the list strategy we can only actually have one column or expression. Also when we're defining the partitions we can actually specify a default partition with list or range strategy and I'll talk a little bit about that later but I'm not going to mention it further at this point. So when we define the individual partitions we need to define what's called partition bounds. Now these are constraints that act very similarly to check constraints but these are specifically constraints that will be used when we make the partitioning checks when we're inserting or retrieving data. Now because the way these work it's extremely important that the bounds do not overlap. Okay obviously if you try to create a set of bounds that do overlap you will just simply get an error because that would cause us problems in the way that the tables are defined and the partitioning would work. Now this is one of the differences between the former inheritance based partitioning and the new declarative partitioning because in the old mechanism you just simply use check constraints and you didn't know which ones were the bounding constraints whereas with declarative partitioning it's extremely clear what the bounding values are. Now what's important to note is that there's actually no requirement for the number of rows in each partition to be the same. They don't need to be balanced at all. So for example if you've got two partitions, one for August and one for September you might have no data at all for the August partition and then 100 gigabytes of data for the September partition. So the bounds you choose have nothing to do with the amount of data it's just simply up to you to set sensible bounding values as part of your setup of partitioning and that's really probably one of the most important choices you need to make with partitioning is exactly how your partitions will be laid out, how many will you have and what are the values that you use for the bounds. So bounds can be defined as either constants or expressions but as we'll see later constants are actually recommended because one they're easier to define and they're actually easier to transform into different data types. So an example here is where we say we're going to create a partition for values from the first of July until the first of August. So the first value is inclusive, the second value is exclusive. So that will allow us to insert any data that's got a date during July. It's not just any date it has to be for the column that was mentioned previously as part of the partitioning key. So it isn't just any column in the table it's always related to a particular column or expression. So what else can we have with partitioning? Well it's possible to add other constraints but the partitions themselves must have the same column definitions between all of the different partitions. We can add check and we can add not null constraints onto the partition table and they're going to be inherited by all of the partitions. You can add additional constraints onto specific partitions individually but you can't remove ones that have been added to the whole table. We can also add indexes to tables. So if you say create index on a partitioned table it will add that index definition onto each of the individual partitions. You don't need to do that manually. It will do it automatically for you in a number of cases. Now the only problem with that is that it doesn't actually work with concurrently. So if you want to add indexes concurrently you do still need to add that one table at a time. Now the good news there is that each partition can have a different set of indexes to other partitions. So it is possible to add those indexes one at a time by working your way through the partitions. Clearly I would have thought that something we'd like to fix in the future but obviously that feature didn't make it into Postgres 12. There's also something else that says that the alter table command when you're using the sub command add table constraint using index is not supported on partition tables but again you can do that on the individual partitions themselves. Now at this point I usually get a question about when are we going to support global indexes. My answer to that has not changed in 15 years and that is the single global index really does not work very well and most people that I know that have tried it on a VLDB have quickly given up using it. Now the reason that the global indexes don't work very well is that they would not be partitioned because obviously they need to be ordered by all of the data in the table. Now if one of the reasons that you're using partitioning is so that you can drop data easily then a global index has got no way to drop data easily from it. So the benefit that you would get from using partitioning would actually be somewhat negated if you used a global index. So at this point in time Postgres 12 does not support global indexes and I personally won't be spending any time trying to make them work either because they really don't work for it usually in the real world. Having said that we do support primary key constraints defined on a partitioned table and the way that this works is if the primary key is part of the partition key or sorry the other way around. If the partition key is part of the primary key then we're allowed to create that primary key onto the partitioned table. So it's slightly restrictive but the purpose there is that it if you know that ahead of time then you can choose your partitioning key so that it fits the primary key that you're choosing for that table. So obviously if we have primary keys then as you might expect in Postgres 12 we now can support foreign keys and this now works in both directions in the partitioned table can refer to a non-partitioned table so you can have a fact table that refers to a reference or dimension table without problems but you can also have partitions tables that refer to each other so that the partitioned table can for example join two tables like an orders and order items table can reference each other in a foreign key situation. Now that's great but also note that foreign keys cannot be declared as not valid at this time now what that means is that if you were planning to add a foreign key to a large partitioned table after the fact you won't be able to do that except by doing it in one long running command and that's going to run for a really long time so you don't want to do that. So just be careful with your planning to make sure that you got your foreign keys set up. We do support triggers in fact the support is for road triggers on partitioned tables or after road triggers and in fact that's one of the ways that we implement foreign keys within Postgres using triggers internal triggers that you can't see but you know that's the way that we do it under the covers. So how exactly does the data storage work with partitions and partitioned tables? Well as I said earlier partitions themselves are tables and that means they can be referenced individually but the partitions table is a database object it looks like a table but it's always empty so a partitions table can have zero partitions or obviously can have more than zero partitions. Now as a result of that if you do truncate only table name it will throw an error but you can do things like select star from only partitions table and that by definition will always return zero rows which can be a little bit confusing for for some people. So the partitioned table is more like a view or a collection object rather than a data object in itself so a partitions table doesn't have any data in it can't have any data in it until you've added at least one partition to it. Now a restriction on that at the present time is that you can't add storage parameters to a partition table so if you try to do all to table measurement set fill factor equals 10% then it won't work you have to actually execute that on the individual partitions themselves so again something that will likely be improving in later releases. So what that actually tells us is that we can perform SQL directly against the individual partitions because they're tables it works is fast but it requires that you know which partition you want to access. So if you've got a range partitioned table it might be fairly straightforward to work out what the name is of the most recent partition but obviously if you use other partitioning strategies it becomes increasingly more difficult to work out which partition your accessing. So the way that partitioning is designed is that you simply supply the name of the top level parent table and then the data will be automatically routed to the correct partition or your query will be routed to the correct partitioning. So let's look at how that works for the different types of command. So if you do an insert into a partitioned table what it will do is it will take the column all columns that make up the partitioning key and then it will look through the partition bounds until it finds the correct partition to place that inserted row into and that then is an order log in operation meaning that it takes longer as the number of partitions increases. So if you were thinking you might have 10 million partitions well that certainly won't work the typical number of tables that we've been benchmarking is anywhere up to 10,000 but I've got a longer discussion about exactly how many partitions you should choose but the important thing to know is that it does take more time the larger number of partitions you have. A copy also works very well against partitions tables and what we actually do is we buffer the rows into individual partitions and that buffer is emptied every time we switch to a different partition. So for example if you're loading data into a time series based range partitions table then that will pretty much go extremely fast all of the time. If you've got a scheme where you're switching between many different partitions on a regular basis then copy won't work as fast unless you sort pre-sort the data by the partitioning key values. So update also works against partitions tables and a single row update will be rooted to just a single partition. Obviously the partition algorithm we're using is identical for update and insert so what we're doing is we're just locating that single partition. We do support multiple row updates and those can access multiple partitions so obviously they're going to get increasingly slower depending on how many rows you're updating. What has also been added to Postgres is the ability to perform an update of the partition key itself and what that means is that we will do a delete on one partition and then insert the row into a separate partition. So that's what's known as row movement. Now if you do that it is possible that you can get serialization failures because while we're performing one update it might happen that somebody tries to do a normal non-row movement update on that row while it's in the process of being moved and when that happens we throw a serialization error and that type of error can always be retried. Okay so if you do update your partition keys then you can get these failures which would need to be retried. Now I personally don't recommend that you set a partitioning key that would ever be updated. I think that kind of goes against the design principles of partitioning but there are occasions when we need to do that or perhaps we need to correct some of the data in the table. Some other points to note is that this does not work with insert on conflict update. So you actually would need to be using normal updates to have that work. The other point to say is that if you are changing the partition key and that is also changing the primary key of the table then you may have problems with logical replication systems which hopefully should go without saying but I'll mention it anyway. So deletes work pretty much the same way that updates work. We use the same algorithm for determining the partition that we're referring to or if we're doing a multi-row update then we can potentially access multiple partitions. If we want to delete all of the rows in a large partitions table you can perform a truncate operation and that will truncate all of the individual partitions in one go. A delete operation without a wear clause is still a foot gun. It is still possible to accidentally delete all of the rows in the table. The only good news is there it runs for so long you might be able to abort it halfway through but that's still going to cause an awful awful lot of wasted effort if you do that. If you want to do a bulk delete from the table where you simply want to remove a fraction of the rows in the table then you may consider using a partitioning scheme that uses drop tables. What do I mean there? I'm talking about using a partitioning strategy of range and then when you go to drop the older partitions you simply do a drop table rather than a delete. Now this is quite important because it removes any overhead from that human other maintenance because there's no table at all left to be scanned. But what I would ask you to remember is that the locking on the drop table can be quite harsh. So it might be more convenient for you to use a truncate on that partition and then come back to it at a later time. So in terms of partitions table maintenance just to remind you that partitions that don't have any further changes. So if the later in them is static then there will definitely not be any need to do further analyze vacuum or re-index. Now there isn't a specific command in Postgres to make older partitions read only but they won't require additional scans. Once they've been fully vacuumed and fully frozen once that happens you won't need to do further work. And obviously just to mention again that if you've dropped the partitions you definitely don't need to clean up at all. Couple of other points about analyzing vacuum to mention just on the slide there I won't really out but what I will highlight is that partition tables do not support re-index. So you'll need to in run those on individual partitions if you want that to work correctly. So what I've done so far is I've explained to you how one dimensional range partitioning works. What's actually possible in Postgres is that we can have multiple levels of partitioning and the way that this would work is that the table device 4 in my particular example is is itself a partitioned table. So device 1, 2 and 3 are normal partitions whereas device 4 is a partitioned table that is also a partition and then the sub partitions of that time 0, 1, 2 and 3 are just normal partitions. So here we're using two dimensional partitioning or what's also known as sub partitioning. Now in this example you can see that I've used different partitioning strategies in the different levels of the partition. So it would be easily possible to have range partitioning by one column and then another sub partition within that range partitioning by a different column for example which would give you a sort of grid of values for the ranges but it's also possible to do mixed list range and hash sub partitions. So if you do this then it allows you to come up with extremely complex partitioning structures. Now if you go down this route you have to understand very clearly what you're doing because quite clearly you're going to end up with lots of tables and you could end up in quite a considerable mess to clean up if you're not careful and you're not aware of what you're getting yourself into. If you do need to find out what your partitioning structure is we've added a number of different functions that allow you to look at a particular table and then find out what table it's a partition of which is what you'd use PIGI partition route for or you can look at a partitioned table and look at its specific ancestors or related tables but if you want to see the whole partition tree then you can use the PIGI partition tree function to view the whole tree and just as an example I've extracted from the manual the the command that's shown that allows you to tell how large a partitioned table is because what this does is it produces a number of rows one row for each partition in the table and then it does a PIGI relation size function on all of the rows on all of the individual partitions and then it uses the sum aggregate function in order to sum up all of those individual partition values to get the full size of the table. So and obviously I'm using the PIGI size pretty command to make that easier to read so that allows you to look at partitions tables and work out what's going on within them the metadata itself is accessible to you directly via catalog tables and PSCUL has been enhanced to show the difference between normal tables and partitions tables you can see that I've got two tables there one called through which is normal table and one called measurement which is the partition to table and then there's also a new command that shows you just partitioned relations which then in this particular schema just shows the measurement table. So there's a few other things to mention with regards to the partitioning feature partitions can be defined to be on separate table spaces so if you do alter table set table space on an individual partition then that will potentially run for a long time while holding the access exclusive lock. If you do set table space on a partitioned table that is the top level partition table then what that does is it's a metadata only operation which will affect the location of newly created partitions that you will create in the future. Okay so it affects the location of future partitions not the existing ones. It's also worth noting I think I mentioned already but partitions can have different index is on them so for example older partitions might not need some of the indexes that you needed when the data was fairly young so it might be possible to save space in your database by dropping certain indexes onto the older partitions for example. There are some limitations with partitioning. These ones are listed in the limitation section in the manual but there's actually quite a few other limitations that I've mentioned over the course of the webinar already and you know unfortunately those are documented in the manual but they're not so documented all in the same place. So you can't have exclusion constraints to find across a partitioned table. You if you want to have before road triggers you need to create them on the individual partitions rather than on the partitions table itself and that's because the before road triggers could actually change the value of the partition key which would affect the way that routing works. So that's one of the reasons why we don't allow before road triggers on the partition table. You can't have a partition table that mixes permanent and temporary data that kind of should be fairly obvious so it's difficult to see what you do with those thoughts but it doesn't work anyway. Also another thing to mention is that you can't easily mix local and foreign partitions. I've added the word easily because you can actually do that in some respects but not all operations work in the way that you think they would when you do that. So because we're not talking about sharding I'm not going to talk any more about the way that foreign tables interact with partitions. So unfortunately if you were hoping that I might explain that it's too big a subject to include in this presentation. Just a quick note to say that partitioning and logical replication is well I mean it's an important feature but create publication does not allow you to include partitioned tables. You have to actually manually include each partition one by one into the publication. What I would say is that the pediological utility doesn't work like that in that it's easily possible to have a normal table as the source and a partitioned table as the target for example or you can move from a partition table to a partition table so it's much easier to manage in pediological. There are a couple of gotchas that I need to explain default partitions. The way that these work is that when we add a new partition the default partition needs to be scanned to see if any of the data in the default partition need to be placed into the newly added partition and that means that when you add a partition it needs to scan the whole default partition. Now if that's a large partition that could be held for quite a long time. The lock could be held for quite a long time and so in a lot of cases I would advise against the use of default partitions unless you're able to understand that it's that the default partition would be quite small and that that scan wouldn't take very long. There's also something to be said about partition bounds in Postgres 12 we added two different features and they kind of don't work well together. One is that if you issue a query where you sort by the partition key then the partitions themselves will be referenced in the correct order and therefore a sort won't be needed when you refer to it when you bring back data in that way. But there's another feature that says that you can use expressions rather than just columns for partition bounds but if you use an expression then that destroys the ability of the optimizer to know whether it's sorted or not and so it won't be able to use the previous optimisation I mentioned. So unfortunately those two things don't work well together at the moment. That's more just a simple oversight. There's actually no way of specifying the sortability of expressions or functions. So actually there's no easy way to resolve that but it is something that we would want to be able to resolve over time. So in summary for the whole partitioning feature is that all of the important things work. So the key features work. There are still a few items that don't work with partition tables and I hope that those don't interfere with your usage very much. I've mentioned all of the restrictions and problems that I know about and that are listed in the manual. So it's not like there's all of these and then there's another load as well. So we've done a lot of work to remove all the bugs that are there and there are these small number of restrictions that still exist with the feature but those are fairly minor in terms of the usage and they are definitely things that will improve your later releases anyway. So that is the end of my summary of the syntax and the feature itself. What I'm now going to do is start talking about the performance analysis. Now before we do that I think it might be a good opportunity to see if there are any questions from the audience. Is there anything that we'd like to cover there please, Amir? Yeah we haven't getting some questions. In fact some of these questions came in a little earlier during the presentation that were answered in subsequent flights let me go through a few that we may be able to answer. So one of them says do partition tables support unique keys and can be used them in lieu of a primary key so they don't have the include so they don't have to be included in the partition key. Sorry there was a grammar mistake in there. Yeah you can have unique indexes on each individual partition so when we create a primary key on the whole partition table what that actually does is it creates a unique index on each of the individual partitions. Now the unique index guarantees uniqueness within a partition but then the definition across the partition table relies upon the bounds being unique non-overlapping ranges and that's what allows us to create a primary key across the whole partitions table. So that's where the restriction comes from is if the primary key is contained within the partition key all they're exactly the same then that's sufficient to allow you to create the partition the primary key. So if you have a column for example order ID as the partitioning key you won't then be able to create a primary key on date or the other way around. So if you partitioned by customer ID you'll be able to have a primary key of customer ID because they match. So if you're still confused it's described in the manual and I recommend that you give that a try yourself because the partitioning scheme is something that's very important to your use of partitioning. So you need to understand that fully. What I'm hoping is that you'll be able to use some of the examples in the manual or in these slides in order to just set up a small trial for yourself. So is there another question? There are a few but let's take one more before we move on. So it says it can table schema be changed after defining partitions how do they depict them? That's a good question. So what I'd said was that partitions need to have identical columns. So you can add and drop columns but you need to do it on the top level partitions table and it will affect all tables beneath you and it uses the same rules as would work on a normal or to table on a non-partitioned table. So for example if you add a column in a way that is a metadata only operation so it's very quick then that will be quick on all of the partitions whereas if you add a new column in a way that requires us to scan the table that unfortunately you're going to need to scan every partition in that table. So same rules apply you can add and remove columns from those tables. Okay we are actually getting a great deal of questions. Let's take just one more and the rest we'll try to tackle towards the end. That's okay with you. Sure. Is there a plan to be able to add new partitions for partitioning by range when based on time? Yeah I mean some systems some other database systems have already got automatic systems for that. It's certainly something we've discussed. One of the main blockers to that was the ability of the system to do that with reduced locking. One customer I spoke to wanted to have partitions per minute and obviously if the add partition statement runs for longer than a minute because it's waiting for a lock then obviously you know partitions every 60 seconds wouldn't work. So what we've done in post-cose 12 is reduce the locking somewhat but it's not down to nothing at all so there is still some locking aspects to it. So if we did have automatic partitioning that would need to work as a background task rather than as an immediate mode extension of the table. So it wouldn't it wouldn't be something that would be able to roll back in the metal. We'd just need to regularly add table. So it is a feature that we discussed and it is for example a feature that we've already implemented in second quadrant post-cose with the BDR stack but you know I know that I'm talking about community post-cose in this talk so I'll really mention that too much. Any other questions or shall I go straight into the partitioning performance analysis? Yeah let's talk about partitioning for a bit. I'm sure there will be lots of questions and for everybody attending right now we'll try and answer as many questions as we can during the session and for the ones that we're not able to answer during the session we'll try and address them in a blog that will be published subsequent for the session. So and of course your question. May guide the further development that we do in this area as well so and depending on how many people have different opinions on things. Okay so what I'm going to talk about now is the performance analysis of partitioning. One of the reasons that we're using partitioning is that it's going to speed things up we hope and so it's worth understanding what the types of parameters are and how things work from a performance perspective. So the main parameter that we have to control the way that partition works is enable partition pruning and that also tells you that the word that we use when we are looking at building an execution plan is pruning. So if we don't need to access table then we're just simply going to remove it from the execution plan and this works very similarly to the way that inheritance partitioning worked if you were familiar with that with the constraint exclusion parameter. There are also some other parameters but at this point in time those parameters are mostly related to the way that we would want partitioning to work in the future with sharding so I'm not going to talk about them here. So this is a really busy slide but it's got some fairly simple information on it. What I've done is I've created this example table called measurement and I've created that as a range partitions table using the log TS column and then I've added two separate partitions to it and now you'll notice that backslash d plus would list all of the partitions. So obviously if I add 10,000 partitions there it wouldn't be a very useful set of outputs so what I've done is use two partitions just for clarity. Okay obviously partitioning would never be set up with just two partitions. So this is just simply an example of how it works to save space on the screen. Now if we look at a query on the left where we say select star from measurement that's going to retrieve all of the rows from all of the partitions and you'll see that the query plan we use for that says append six scan on measurement partition 07 and then six scan on measurement partition 08 so you can see that it's scanning of both partitions in the table. Now if we looked the query on the right and we do an explain on that the only difference between the queries is that I'm adding a where clause and the where clause is accessing data from the first of August onwards and we know from the partition bounds or the way that they defined that that should only retrieve data from the 08 partition and that's exactly what it does if you look at the explain only one of the partitions is mentioned there so it has been literally pruned from the execution plan. Now that's worth noting because what we're saying is if the partition has been removed from the execution plan it's not mentioned in the plan. So the plan itself is thinner as a result of that and therefore the explain is returning less data. Now there's something else that you can see as well and that is that the six scan because it's now only a single table being referenced it means that we also get rid of the append as well and that means that when we do a query that accesses a single partition we're able to treat that as if it's a single table and we're able to optimize that quite differently so that's actually a second optimization that we've added to to the way that plans are developed in the optimizer. So what we're going to do now is we're going to have a look at the OLTP partitioning performance because if it's not clear what I've shown you with this partition pruning is basically all you need for for business intelligence queries because it will simply remove huge chunks of the table when it comes to scanning them and as a result those queries will go significantly faster. Obviously the query on the right if we had a thousand partitions and it was accessing only one partition you can see that that would then go approximately a thousand times faster assuming all of the partitions were equally sized which they may not be but just for the for the sake of example. So partitioning itself can make things go dramatically faster if you set up your partition keys correctly and you choose the right partition strategies. So the use of partitioning on its own is not a magic bullet that will improve performance but used intelligently appropriately with regard to your data and appropriately with regard to the questions that we ask of that data that it can improve performance. But in terms of OLTP things are a lot different than because the overheads for partitioning need to be extremely low for us to be able to take advantage of partitioning within very low latency queries. So what our goal is is for all short operations, all single row operations to work in constant time and for the most part that's what we achieve with Postgres 12 and of course we also want to improve the speed of copy especially for range partitioned systems. So what I've got here then is a number of benchmarks that show you what performance is on a non-partitioned table and then improvements that we've made over time as the development work progressed with Postgres about the gains that we've made. So partitioned performance wasn't very useful in Postgres 10 and then as we move towards Postgres 11 we've improved the performance for prepared queries we've improved things for inserts and we've improved dramatically the time or reduce the latency that inserts take when we have a large number of partitions like 10,000 partitions. If you can just see at the there is actually a point here where that's actually a data point it's just such a small data point that it was not particularly obvious. What we're saying is that in Postgres 10 the performance when we used 10,000 partitions was quite bad but in Postgres 12 the performance is now almost the same as the of the non-partitioned case. Now considering the sizes of table that we're talking about here then that's obviously extremely good. Although this looks like some kind of a regression or a bad thing what we're saying is that you get multiple additional benefits from partitioning and so this getting back close to the transaction rate the non-partitioned table is actually a great outcome. So moving on the copy performance also for Postgres 11 was not very useful and we had to actually move beyond the performance that this diagram showed which was actually from developments we made two years ago we've had to make even further improvements in copy performance to bring it up quite close to the non-partitioned performance. Now the next thing that we've done is a detailed set of measurements. I think you may well have seen discussion from various blogs and comments by timescaled DB that they had measured Postgres performance as falling away over time. Now unfortunately in that particular case they hadn't mentioned what the details of that benchmark were so we spent quite a long time investigating the performance of Postgres to see if it did actually reduce over time and what we've done here is look at the performance over a very long period of time so we used batches of 10,000 rows of the time and we issued 400,000 batches of rows so a very significant amount of data and looked at the performance of that over time and I think you can see when you look at that graph that actually the performance does jitter a small amount but there's no perceivable drop-in performance over time through the use of partitioning and that's particularly important and that was true at Postgres 11 and we've actually improved the performance from there. So looking at things from a loading perspective what we did was look at the average load speed and you can see this is related information this is the same sort of data just with a different graph you can see that the the performance does vary according to whether you use monthly or weekly partitioning but that's basically altering the way simply altering the number of partitions we use in the table does have an effect at Postgres 11 but at Postgres 12 things change and get dramatic higher so what we did here is we just sorted the data as a way of saying what in terms of what are the fastest and what are the slowest is there any structure to this now this is sorted over time this doesn't show that the data gets slower over time that was that graph that's based on time this one is just simply based on the latency to see if we can see any particular effects from that and there are none basically it's continuing to proceed very happily over time so that was good because what that showed us was that the claims that Postgres was slowing down over time we just have no evidence for at all and so when we've used a sensibly designed schema we can't see any disadvantage to using community Postgres it's now working very happily it was actually working very happily earlier on some operations but now we've got more operations that are working well. What these graphs are showing is some benchmarks that was published by a gentleman that compared Postgres 11 timescale DB on Postgres 11 which was the latest release when he made those tests and the Postgres 12 partitioning scheme and if I'll just help you to read that you can see that the performance that you get out of Postgres 12 is approximately four times the performance that you get from time scale DB and also note that the performance is essentially linear with respect to the number of partitions so the performance is not going down when we use higher numbers of partitions there is a very slight dip to that curve but it's not very substantial whereas if you look at the performance that we had with Postgres 11 you can see that the performance on Postgres 11 does dip considerably as you add more and more partitions to the table and that's one of the reasons why we would saying that when using Postgres 11 you should consider using perhaps 100 partitions in a partitioned table whereas when you're using Postgres 12 you can use a thousand or more partitions because things like simple selects and simple inserts work equally fast no matter how many partitions you have so this is a very useful result because it shows us what's possible and what we need to aim for but there are some things that we need to look at because you'll see that not every single case that we've got has been speeded up for Postgres 12 now if we do a select clause where we are using time stamp now in the wear clause what that means is that we are dynamically retrieving that data at runtime we don't know it ahead of time and so the optimizer is not able to prune the data effectively as a result it needs to calculate that value at runtime and then optimize it within the executor and as a result there is still a drain-in performance if we add more partitions so what I'm saying is that dependent upon the types of query that you issue you'll get either no effect from additional partitions or you'll get quite considerable effect from additional partitions and the message there is to understand that if you are retrieving if you are attempting to do retrievals that is in the wear clause in select or update or delete statements that contain volatile data or functions then you will not be able to gain the performance that you would like now hopefully that's relatively easy to understand but it does mean that you probably need to look at the tuning of some of the queries in your application okay so you can't just throw anything at all that's it and have it work perfectly you will need to understand that some query types won't work correctly okay now that is typically understood anyway it's very difficult to get those types of queries to perform well what I would say is that you need to make sure that you're using timestamp to z and you also need to make sure that you understand what that means this particular point is perhaps a little bit difficult to explain and I'll I'm sure I'll get questions on it and I'll make sure that we do is explain this in more detail on a on a blog later later after the webinar ends but clearly an important point but the good news is that 10,000 partitions is achievable with with Postgres 12 and that's significant steps forward so if I just go back to that graph what we're saying is that we can achieve thousands of partitions with Postgres 12 and that's very effective we've also benchmarked it significantly faster than time scale DB and we might make some inferences about other systems there as well and also the results that we showed here were that Postgres is not slowing down over time as had been claimed so I think those are those are really important points that we are able to draw from these actual measurements so just some commentary really that it is possible to add extensions into your system to speed things up there's always new and interesting features to use that is that that is the beauty of the Postgres ecosystem that these extensions to give us access to additional features but those features do eventually get into core Postgres and obviously you need to read the writing on the tin carefully to make sure that you're getting what you think you're getting so all of this is saying that Postgres 12 has made really significant advances in a very large database and especially with partitioning performance and really that's down to the great work that's been achieved by a very small number of individuals on the release management team for Postgres so thank you for all of your work much appreciated and obviously we hope that you do it again next year Postgres 12 is obviously immediately supported by second quadrant and there's other tools that coincide with that release so Rep Manager, Barman that kind of thing should now be working with Postgres 12. What I haven't talked about here is Postgres scalability I mentioned that we weren't going to talk about sharding and such like it's unclear yet how this will work in Postgres core but it is coming one day it will just take some time to get here so I'll do another webinar in the future talking about the topic of scalability itself and how this relates to Postgres just a quick mention to say that Postgres BDR does have advanced clustering and scaling features already as I said before some extensions do allow you to expand beyond what's available in community Postgres but obviously many of these features will one day make it into community Postgres so my summary all all this and I'll be taking questions in a minute is that the key Postgres the key features work in Postgres 12 including performance there are still quite a few minor points to resolve like re-index and such but you know those things will be fixed in time and they shouldn't now become a block of view to adopt partitioning with Postgres QL itself because I think the feature set is now reached to point when I can give people a green light to to go ahead and use these functions effectively in production. So just to leave you with that slide I'm happy now to take further questions so I may have a few more please. Sure we actually have been getting quite a few questions as you've been talking about this stuff so obviously you know I think our audience is very interested in the feature that you've been discussing so let's start off with partitioning what will partition pruning work if filter on partition key is dynamic. So the way that pruning works is at two levels first it will work in the in the planner if you are providing stable constants or immutable constants that can be removed at the planning stage then we will be able to produce an effective plan that removes them. If we can't remove them at planning time we can actually do pruning at runtime in the executor as well but that necessarily is slower than removing things at the plan time because the plan actually just has them those tables completely removed from the plan whereas if we do it at execution time we need to test which parts of the plan are still valid. Okay there's this question that is related to post-cressy 11 to 12 upgrade phase. Say I have some large partitioned instances running in post-cressy 11. Does the database upgrade script for post-ressy 11 to 12 correctly transform it into a PG 12 partition or do I need to drop and re-import the due date to PG 12? So the declarative partitioning is the same in post-cressy 11 and 12 so the metadata will move across as part of PG upgrade. If you are using old style inheritance partitioning then that will not be transformed into the latest declarative partitioning. That's then a manual step that you would need to do because the way the inheritance partitioning works it would be possible to drop the inheritance and add those tables as partitions to a larger partitions table. So there is something that you can do to move from one style of partitioning to the so that's from inheritance partitioning as of which worked as of 8.1. So if you've used declarative partitioning with the partitioned by syntax in either post-cressy 10 or post-cressy 11 then that will not block your upgrade. Okay next one do you have to create new range partitions in advance or is there functionality to have post-cress create them automatically say upon entering a new month with monthly partitions sets? Yeah I mean that was the same question as before I'm forcing no not at this time you do have to create partitions in advance of the being needed and that needs to be done as a background job so there's no way to automatically do that using a trigger or something okay so you have to have a background job that will run regularly to add new partitions and remove old ones. Okay can we use competitive key as primality then including partitioning key and another unique column? Well yeah I think so I mean you know my recommendation there is really you you have to go and try it yourself because you know it's the easiest way to to answer that kind of a question I don't want to mislead you by giving an incorrect answer there so you know the primary key can be defined across the whole partitions table and it can be multi-column the partitioning key can also be multi-column if you're using range partitioning so what you're saying is is possible but you need to try yourself to make sure that it it definitely works for your particular column set. Okay when we perform vacuum full on one child table does it lock the entire table or only child table would be locked. Well I think I don't know the answer to that one but I think the answer will be that it will only lock the child table. Okay the stream of questions actually still flowing I think that really just take a couple more. There's a question that asking you to expand on what you're meant by needing to understand timestamp with times all. Yeah this there are actually two different types of data type there is one called timestamp without time zone and then there's another one called time stamp with time zone and when you move data between those two data types sometimes the answer depends upon the time zone in which you ask the question and as a result the the transform between those data types is not immutable which means that you can experience optimization issues if for example you specify a constant as time stamp without time zone and you compare that with a column that's time stamp with time zone so you need to be quite careful exactly how you specify that. Sorry if that's a bit of a strange and complex sounding answer. I yeah in this example here for example this date is actually being compared with a time stamp and so there's a conversion done here and actually the conversion is from a date to a time now in this example on the table it had without time zone so it does that kind of a transform so you need to be careful there please I think the most important thing to to gain from this is that you need to write down all of your queries and the way they work and actually test them you can't just assume that everything works perfectly with partitioning you need to actually go to some trouble to benchmark that and evaluate it properly so what I recommend you do in all those cases is to write out the types of query that you'll need and then test them with explain and with Pidgey Bench to show that it meets the type of performance that you are expecting so it really is some sort of hands-on work here to make sure that your database design works correctly for the intended use case. Sorry long answer but yeah it's not simple. Sure. Okay so will the partition with parallel workers option help the query performance? Yeah you know you are going to get well if you're doing a sequential scan on that partition then yes it can be scanned in parallel so yes parallel query and partitioning can work together to your advantage. Okay there's a question on some muted direction of the evolution of this feature and that participant has asked when will postgres be able to run user defined functions on partitions in parallel? I mean I think the user defined functions the parallelization of user defined functions is within the hands of the user I mean you can mark it parallel safe it just needs to be parallel safe I can't really answer that in more detail. Okay so we'll just take one last question and for the ones that remain unanswered we'll try to answer them in a in a blog that will be published subsequently so we get thanks for the nice introduction can I ask if there are any restrictions using local and foreign tables combination when foreign tables are is only for read-only purposes? Yeah I said that that whole topic area is out of scope I mean I'll do another webinar on that whole topic area I think it's it's too complex to just give a kind of quick answer. Okay so yeah I think that you know we will try and answer some other questions a little later I think we are well I see over time by now but thank you everyone who joined in to to listen to this this you know enhancements in partitioning that are coming in in postgres 12 as Simon mentioned postgres 12 is fully supported by the community is it it's generally available now and it is it is also part of the supported software listing now with second quadrant so so second quadrant fully supports it in production if you if you need help in upgrading to postgres 12 you provide those services as well and you can get in touch with us at info at second quadrant.com I hope everyone had a good day ahead thank you for joining in thank you Simon for presenting and walking us through the interesting topic I hope to see everyone again thank you very much thank you everybody for listening can I