![\"Table](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXf2c_zZfYGNxvpR8vfbdmt8ohPGm9BgAajqzTaKGWj2Asq6BG54wIDaoCIS8tlL0wmHYNRza0te-UnqItPhQVryqnjrTB9Lgo2wg9H54IDl0UdOpGWX3GuU8koybh6t-4HrtMz_qwkXZVo_BTzGlLDxLsTO?key=Kc--iz-qeEASUam-NL8qug\" "\\"\\"\/")
<\/figure>\n\n\n\nImage 1<\/em><\/p>\n\n\n\n So, when should we consider partitioning? A couple of reasons mentioned in the various database manuals include<\/p>\n\n\n\n All of the major databases support partitioning of tables in some form. All of them have their own limitations on how many partitions they support, what data types are available as partition keys, whether indexes work across all partitions, and whether they can then also guarantee unique constraints across the entire table or only per partition. To top it off, it can also differ per version of a particular database. So for more information on those nitty, gritty details I\u2019ll refer to the individual database reference documentation. In this article I\u2019ll use PostgreSQL to illustrate some examples.<\/p>\n\n\n\n So let\u2019s make this a bit more concrete and look at two scenarios. First an application where you store data for multiple customers in a way that can be described as multi-tenant. A single application with data for multiple users that should not be mixed up at all. Sure, we could start with a completely isolated application per tenant, but we all know that that may not be affordable when you start off, or even considered when it\u2019s built for the first customer.<\/p>\n\n\n\n So we have a set of tables, each with a column tenant<\/em>. That makes it an ideal candidate for the partition key. Another example would be historical data. Many businesses will have some kind of billing or financial records system and they typically have to keep those records for a very long time due to tax rules. That does not mean you have to keep them easily available all the time though, and at some point you may also be able to move them to cheaper, archival storage or even throw them away.<\/p>\n\n\n\n The obvious benefit is of course that you can keep your active records set limited and thus keep performance in check, while not having to throw away or have more expensive data migration actions in place to get the same result. It also becomes easier to drop older data if it can finally be shredded. Just detach the partition, if you haven\u2019t done so already, and then drop that single table instead of executing a series of delete statements followed by an autovacuum of the table to make up for the freed up space. The detach is trivial and almost instantaneous, the delete and subsequent re-index and vacuum are more complex and can be very taxing. <\/p>\n\n\n\n If you have by now decided to go this route, what do you have to do to get here then? The easy approach would be to rename the old table, then create the new table and copy over all the data as is done in Listing 1<\/em>.<\/p>\n\n\n Listing 1<\/em><\/p>\n\n\n\n This is not taking into account any indexes created previously for the old table, queries that do not take the invoice_date<\/em> into account or views on the old table that may now point to the renamed table or other things your database of choice may or may not do due to a rename. Oh, and also not taking into account whether or not the choice of partition key here is actually the best possible\u2026 that I leave as an exercise for the reader \ud83d\ude09<\/p>\n\n\n\n Depending on the amount of data in the original table, this may also take quite some time to execute. In one case I was involved with, the original approach the developers took, the timer got to about a day for a dataset similar to production. Needless to say, at this point we asked a database administrator to help with optimising this. The resulting script still started with a rename, but didn\u2019t have to copy over the old data. So instead of creating the new partition for the old data, we used a temporary constraint to prevent the database from checking all the data to see if it matches the range set on the partition. Listing 2<\/em> show how we could do this in our example: <\/p>\n\n\n Listing 2<\/em><\/p>\n\n\n\n You may also want to check your database documentation as some also have (limited) support to partition an existing table in place, e.g. MySQL<\/a>.<\/p>\n\n\n\n Now you have some idea how to implement it in your database. That does not mean you\u2019re done with it yet. Depending on the type of partition key, you will have to put some procedures in place to create new partitions or risk a grinding halt of your application as it tries to insert data into something that isn\u2019t there. Also detaching or even removing older partitions should be thought about. This could be automated but is not necessarily the best approach to take. <\/p>\n\n\n\n While table partitioning is primarily something in your database, it is not something you as an application developer can completely ignore. First and foremost, the choice of the partition key can be of effect on your queries to the database. If you do not take that into consideration, the database may have to query all partitions. That could lead to serious performance degradation. For those that use SQL queries directly, i.e. because you use JDBC or MyBatis, you\u2019re on your own to go over all the queries in your application that use this table and rewrite your queries to use the partition key if and where necessary. Listing 3<\/em><\/p>\n\n\n\n EclipseLink also has a series of annotations for partitioning<\/a> but those target database partitioning or clustering. Also interesting and it can benefit from table partitioning, but not directly useful here.<\/p>\n\n\n\n As one of the benefits, performance was mentioned but with a caveat. That is for a reason. Partitioning can indeed get you a nice performance boost, but only if your queries hit a single or a few partitions at most. Then it will only need to run over one or a few smaller indexes or smaller tables and that saves memory and going back-and-forth to the disk. So if you get a performance improvement heavily depends on whether you can get your queries to include the partition key and thus get the query planner to limit the amount of data you have to search through.<\/p>\n\n\n\n I hope you\u2019ve gotten an idea on what you could do within your existing database when your tables grow beyond what you may initially have expected. Or maybe you already expect them to grow this large and you decide to start with this approach from the get go.<\/p>\n\n\n\n For some additional fun reading on an approach I have used on an existing set of tables, where we had constraints regarding existing data, possible downtime and some more, I leave you with this approach<\/a> where we included the date in the id column of the table<\/a>. It has its pros and cons and requires some customization in the code as well to make Hibernate aware of it, but it has been serving us for some time now.<\/p>\n\n\n\n\n
\u2026and the practice<\/h3>\n\n\n\n
Scenario 1: multi-tenancy<\/h3>\n\n\n\n
Some of the benefits of partitioning the tables in this scenario are that you can isolate all the data for a particular tenant, maintenance on data for a particular tenant becomes possible without disturbing others, and, if needs be, it\u2019s also easier to migrate a tenant to a separate instance if it grows too big to handle or has additional requirements due to changing rules and regulations like GDPR.<\/p>\n\n\n\nScenario 2: historical data<\/h3>\n\n\n\n
Implementing it in your database<\/h3>\n\n\n\n
\nALTER TABLE invoices RENAME TO invoices_legacy;\nCREATE TABLE invoices (\n id BIGINT NOT NULL,\n invoice_date DATE NOT NULL,\n description VARCHAR(255) NOT NULL,\n amount BIGINT NOT NULL,\n discount BIGINT DEFAULT 0\n) PARTITION BY RANGE (invoice_date);\n\n-- use an arbitrary old date to fill the FROM date. TO is exclusive.\nCREATE TABLE invoices_2023 PARTITION OF invoices\n FOR VALUES FROM ('1900-01-01'::DATE) TO ('2024-01-01'::DATE);\nCREATE TABLE invoices_2024 PARTITION OF invoices\n FOR VALUES FROM ('2024-01-01'::DATE) TO ('2025-01-01'::DATE);\n\nINSERT INTO invoices (id, invoice_date, description, amount, discount)\nSELECT (id, invoice_date, description, amount, discount) FROM invoices_legacy;\n<\/pre><\/div>\n\n\n\n-- ensure we do not check on the validity of the data, because we already know it's safe\nALTER TABLE invoices ADD CONSTRAINT invoices_y2023_c\n CHECK(invoice_date >= '1900-01-01'::DATE AND invoice_date < '2024-01-01'::DATE) NOT VALID;\nALTER TABLE invoices ATTACH PARTITION invoices_legacy\n FOR VALUES FROM ('1900-01-01'::DATE) TO ('2024-01-01'::DATE);\n-- now that we have attached the data, we can remove the temporary constraint\nALTER TABLE invoices_legacy DROP CONSTRAINT invoices_y2023_c;\n<\/pre><\/div>\n\n\nImplementing it in your application?<\/h3>\n\n\n\n
Beyond that, you may be hit by some of the limitations of your database of choice when it comes to unique constraints (i.e. MySQL and Postgres have this) or the number of significant characters in names for tables, indexes and constraints (not too long ago limits of less than 64 characters were quite common, sometimes much less than that).<\/p>\n\n\n\n
If you use JPA you may have a little less to do. Hibernate has added some basic support<\/a> in version 6.2 which allows you to mark the @PartitionKey<\/em> such that it will try to take that into account for at least update and delete statements (see Listing 3<\/em>). Custom queries written in JPQL<\/em> still require attention from a developer to optimise. <\/p>\n\n\n\n@Entity\npublic class Invoices {\n @Id\n private Long id;\n @PartitionKey\n private LocalDate invoiceDate;\n \u2026\n}\n<\/pre><\/div>\n\n\nAbout that performance?<\/h3>\n\n\n\n
Summary<\/h3>\n\n\n\n