11–20 of 60 posts
I miss this from Oracle; it allows nice index optimizations as the query patterns are different for recent data vs. historical data.
I think it could be set up with a mess of triggers and a cron job... but it would be nice to have a canonical way to do this.
If you also didn't know what exactly partitioned tables are, here's a nice introduction from Microsoft: https://technet.microsoft.com/en-us/library/ms190787(v=sql.1... It is for the SQL server but I assume it would be mostly relevant. Please correct me if I'm wrong.
So this is all about partitioning data into different storage files on the same server? What is the main benefit of that?
The "General Ledger Entry" table in most accounting systems ends up being millions to billions of rows. Except for rare circumstances, prior periods are read-only due to business rules.
If you also didn't know what exactly partitioned tables are, here's a nice introduction from Microsoft: https://technet.microsoft.com/en-us/library/ms190787(v=sql.1... It is for the SQL server but I assume it would be mostly relevant. Please correct me if I'm wrong.
So this is all about partitioning data into different storage files on the same server? What is the main benefit of that?
By partitioning, you can keep the recent data on a fast disk and the older data on slower disks while still being able to run reports over the whole dataset.
And once you really don't need the old data any more, you can just bulk-remove partitions which will get rid of everything in that partition without touching anything else.
Even then you don't split over tablespaces: By keeping the data that's changing often separate from the data that's static and is only read, then you gain some advantages in index management and disk load when vacuum runs as it mostly wouldn't have to touch the archive partitions.
While seemingly extensive, I don't quite like the commit message. I doesn't say what TP is , and what its use cases would be. That's the first thing you should say, else how am I going to understand / keep interest in the rest of the text?
For them this would be needless clutter that's not at all relevant to the commit.
Once we're reaching the 10.0 release, human-friendly release notes, additional manual chapters and sample code will be written for the users to understand (in-fact, the commit linked by this submission already contains quite a bit of additional documentation to be added to the manual).
Any support for "rolling" partitions? e.g. A partition for data updated less than a day ago, another for data from 2-7 days ago, etc. I miss this from Oracle; it allows nice index optimizations as the query patterns are different for recent data vs. historical data. I think it could be set up with a mess of triggers and a cron job... but it would be nice to have a canonical way to do this.
If you also didn't know what exactly partitioned tables are, here's a nice introduction from Microsoft: https://technet.microsoft.com/en-us/library/ms190787(v=sql.1... It is for the SQL server but I assume it would be mostly relevant. Please correct me if I'm wrong.
So this is all about partitioning data into different storage files on the same server? What is the main benefit of that?
Partion switching is also fast. Say you have a summary table that is rolled up by month, but you want to recalculate the summaries every so often. You can build a month into a new table and then switch the new table for a partition in the summary table.
Any support for "rolling" partitions? e.g. A partition for data updated less than a day ago, another for data from 2-7 days ago, etc. I miss this from Oracle; it allows nice index optimizations as the query patterns are different for recent data vs. historical data. I think it could be set up with a mess of triggers and a cron job... but it would be nice to have a canonical way to do this.
Living with the cron jobs for a big mysql db, and wishing the DB understood this seemingly common use-case :(
But then again, maybe i'm the outlier here.
Will hash based partitioning be included in PostgreSQL 10? The post notes
A partitioning "column" can be an expression.
I just tried to implement table partitioning in PostgreSQL 9.6 this week. With some triggers and check constraints this seem to work quite nicely, but I was a bit disappointed that hash based partitioning is currently not possible (at least not without extensions). Will hash based partitioning be included in PostgreSQL 10? The post notes A partitioning "column" can be an expression. so I can assume it will be support…