Well, the bell has tolled, the day is over, and at the end of it all, Postgres Open has ended its fifth year in service of the community. I will say it was certainly an honor to speak again this year, though now that it’s not conveniently in Chicago, I’ll have to work harder to justify hauling myself across the country next year. Of course at this point, I’d feel guilty if I didn’t at least try, assuming any of my submissions are accepted. 🙂
Most Postgres operators and informed users are aware that it uses MVCC for storage. One of the main drawbacks of this versioning mechanism is related to tuple reuse. In order to reuse the space,
VACUUM must complete a cycle on the table. Unfortunately this isn’t always possible to “optimize” for larger tables. How so?
One of the cool things I like most about Postgres, is that it’s probably the most inclusive database software I’ve ever encountered. It’s so full of features and functionality these days, it’s practically middleware. Almost anything plugs into it, and if it doesn’t, there’s usually a way to make it happen.
Want a demonstration?
This week we’ll be covering another method of Postgres partitioning. This is a technique I personally prefer and try to use and advocate at every opportunity. It’s designed to straddle the line between traditional partitioning and standard monolithic table structure by using table inheritance as a convenience factor. The assumption here is that end-user applications either:
- Know that partitioning is in use.
- Only load “current” data and don’t care about partitions.
These two things may seem mutually exclusive, but they can actually be complementary. There are also notable performance benefits to this approach that we’ll explore later.
Most PGDB (PostgreSQL) users who are familiar with partitioning use the method described in the partitioning documentation. This architecture comes in a fairly standard stack:
- One empty base table for structure.
- At least one child table that inherits the base design.
- A trigger to redirect inserts based on the partitioning scheme.
- A constraint on each child table to enforce the partition scheme, and help the planner exclude child partitions from inapplicable queries.