Our usage of the data in the events table only spans a couple of days. On top of that, we have 10 million events generated a day.
Given these extra assumptions, it makes sense to create daily partitions.
If a query was issued against the products table, that query would reference information on the product table plus all of its descendants.
For this example, the query would reference products, books and albums. But, you can also issue queries against any of the child tables individually.
Depending on how you need to work with the information being stored, Postgres table partitioning can be a great way to restore query performance and deal with large volumes of data over time without having to resort to changing to a different data store.
We use pg_partman ourselves in the Postgres database that backs the control plane that maintains the fleet of Heroku Postgres, Heroku Redis, and Heroku Kafka stores.
Since we don’t need that information to stick around after a couple of weeks, we use table partitioning.When doing table partitioning, you need to figure out what key will dictate how information is partitioned across the child tables.Let’s go through the process of partitioning a very large events table in our Postgres database.The ALTER TABLE statement specifies EMPLOYEES as the table to alter.The MODIFY clause permanently modifies the format of the Salary column. The SELECT clause displays the table before the updates. The FROM clause specifies EMPLOYEES as the table to select from. The UPDATE statement updates the values in EMPLOYEES.The SET clause specifies that the data in the Salary column be multiplied by 1.04 when the job code ends with a 1 and 1.025 for all other job codes.(The two underscores represent any character.) The CASE expression returns a value for each row that completes the SET clause.Modify the format of the Salary column and delete the Phone column.Having a table of this size isn’t a problem in and of itself, but can lead to other issues; query performance can start to degrade and indexes can take much longer to update.Maintenance tasks, such as vacuum, can also become inordinately long.