![]() For this example, the query would reference products, books and albums. If a query was issued against the products table, that query would reference information on the product table plus all of its descendants. This means that if a record was inserted into the books table, it would have all the same characteristics of the products table plus that of the books table. In this example, both books and albums inherit from products. Let’s walk through a contrived example to illustrate how inheritance works: CREATE TABLE products ( Table inheritance for Postgres has been around for quite some time, which means the functionality has had time to mature. A table is said to inherit from another one when it maintains the same data definition and interface. ![]() Inheritance for tables in Postgres is much like inheritance in object-oriented programming. Postgres has basic support for table partitioning via table inheritance. To understand how to get better performance with a large dataset in Postgres, we need to understand how Postgres does inheritance, how to set up table partitions manually, and then how to use the Postgres extension, pg_partman, to ease the partitioning setup and maintenance process. This allows us to drop tables after the two week window and we can keep queries blazing fast. Since we don’t need that information to stick around after a couple of weeks, we use table partitioning. In our control plane, we have a table that tracks all of the state transitions for any individual 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. 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. Maintenance tasks, such as vacuum, can also become inordinately long. 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. ![]() Typically, the data in this table tracks events in an application or is analogous to an application log. In terms of absolute numbers, a table that grows sufficiently large is on the order of hundreds of gigabytes to terabytes in size. One of the interesting patterns that we’ve seen, as a result of managing one of the largest fleets of Postgres databases, is one or two tables growing at a rate that’s much larger and faster than the rest of the tables in the database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |