Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Implement table partitioning

git.postgresql.org

Post reply on HN
Print view View original

51–60 of 60 posts

Re: Implement table partitioning

#51

Post by mioelnir »

SideburnsOfDoom wrote: post #10
egeozcan wrote: post #4

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?

First, if your query used the partition key in its where clause, the database knows(can calculate) which partitions can have a result and which can not. This means smaller indexes/less data to scan to find the result.

In the MSSQL case - not sure about others, this is were I had to use it - you can also switch data segments between tables indexed over the same partition function and with the same DDL. So you recreate the existing table a second time, create all the required indexes on it (which is fast because the table is empty), and then you switch partitions between them basically via pointer manipulation. The empty partition is now in the normal table, the data partition in the recreated one. Then you drop table on the recreated table. This is much more IO efficient than a delete-from statement.

This switching of course allows for a lot of other fun stuff as well, where you switch out a partition with a couple million rows, then work on it in isolation, switch the partitions back and then only have to "replay" the few rows that hit that partition while they were switched. Which is easy because they are now in the shadow table which is not updated further.

It is of course data and application dependent if you can use these things without affecting your application; but if it is suitable, the gains can be immense.

Re: Implement table partitioning

#52

Post by fusiongyro »

SideburnsOfDoom wrote: post #10
egeozcan wrote: post #4

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?

I have a scenario where we want to keep 12 months of data online. When you go to delete the 13th month of data the traditional way:

- Postgres has to scan the whole table to find the old data

- Postgres marks it as free, but doesn't give it back to the OS

Handling this the naive way winds up being both slow and unproductive. With table partitioning, I just go in and DROP TABLE data_2015_11 and get on with life. It's fast and returns space to the OS.

Re: Implement table partitioning

#53

Post by mulmen »

lobster_johnson wrote: post #40
samcheng wrote: post #11

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.

How does this work in Oracle? Seeing as the partitioning constraint would be time-dependent, wouldn't it need to re-evaluate it at regular intervals in order to shuffle data around? Is the feature explicitly time-oriented?

I don't think oracle can do this exactly but the query planner does understand time based partitions so if you do something like:

   SELECT * FROM partitioned_table WHERE partition_date_key > SYSDATE - 1;
The query planner will only use the most recent partition. Combine this with Oracle's ability to merge partitions and you get "daily" partitions that become "weekly" partitions when the new week starts. Alternately you could wait a month and combine all the days of last month into a single partition and then even combine months into years.

The partition intervals are based on specific dates/times, not on the relative time from query execution.

Oracle also supports row movement which is the biggest missing feature here I believe.

Re: Implement table partitioning

#54

Post by flukus »

mioelnir wrote: post #51
SideburnsOfDoom wrote: post #10

Earlier quoted context omitted.

So this is all about partitioning data into different storage files on the same server? What is the main benefit of that?

First, if your query used the partition key in its where clause, the database knows(can calculate) which partitions can have a result and which can not. This means smaller indexes/less data to scan to find the result. In the MSSQL case - not sure about others, this is were I had to use it - you can also switch data segments between tables indexed over the same partition function and with the same DDL. So you recreate…

So this won't help when you have something like a deleted flag and need to join on data that could be in either partition?

Re: Implement table partitioning

#55

Post by vithlani »

lobster_johnson wrote: post #40
samcheng wrote: post #11

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.

How does this work in Oracle? Seeing as the partitioning constraint would be time-dependent, wouldn't it need to re-evaluate it at regular intervals in order to shuffle data around? Is the feature explicitly time-oriented?

You need to write a function/job that looks at the current partitions for a table and does the "rollover". Then you add this to an Oracle Scheduler task...

Re: Implement table partitioning

#56

Post by amitlan »

fabian2k wrote: post #8
ktopaz wrote: post #7

I don't get it? Table partition is already supported in PostgreSQL now and has been for a long time now (at least since 8.1); Where I work we utilize table partitioning with PostgreSQL 9.4 on the product we're developing. https://www.postgresql.org/docs/current/static/ddl-partition...

As far as I understand, this is about declarative partioning. So you don't have to implement all the details yourself anymore, you just declare how a table should be partioned instead of defining tables, triggers, ...

Note that there is no shorthand syntax (yet), where you define a partitioned schema in just one line of DDL.

As of now, you still need to create the root partitioned table as one command specifying the partitioning method (list or range), partitioning columns (aka PARTITION BY LIST | RANGE ()) and then a command for every partition specifying the partition bounds. No triggers or CHECK constraints anymore though. Why that way? Because we then don't have to assume any particular use case, for which to provide a shorthand syntax -- like fixed width/interval range partitions, etc.

That said, having the syntax described at the beginning of the last paragraph in the initial version does not preclude offering a shorthand syntax in later releases, as, and if we figure out that offering some such syntax for more common use cases is useful after all.

Re: Implement table partitioning

#57

Post by bigato »

dhd415 wrote: post #44
bigato wrote: post #39

Earlier quoted context omitted.

If you had built composite index instead, wouldn't it work just the same towards improving the performance? Well I can see that an composite index would occupy more space while partitioning wouldn't, and that should be something to take into consideration. But performance-wise, wouldn't it be the same?

No, for example, a composite index on (tenant, date) would be highly non-selective whereas an index on date in each individual tenant partition is highly selective and therefore higher-performance (in my case, much higher performance).

What if you made an index on (date, tenant) instead?

Re: Implement table partitioning

#58

Post by takeda »

bigato wrote: post #27

Supposing the case in which all partitions are on the same disk and that you manage to index your data well enough according to your usage that postgres does not need to do full table scans, are there any additional performance benefits on partitioning?

To add to responses that you already got there's also a nice use case that partitioning helps with.

When you have table that you constantly inserting large amount of data, and simliarly you are removing old data at the same frequency (i.e. only care about month of data).

If you set partition for example per day, it's way faster to drop old tables than performing a delete.

Re: Implement table partitioning

#59

Post by Jweb_Guru »

bigato wrote: post #57
dhd415 wrote: post #44

Earlier quoted context omitted.

No, for example, a composite index on (tenant, date) would be highly non-selective whereas an index on date in each individual tenant partition is highly selective and therefore higher-performance (in my case, much higher performance).

What if you made an index on (date, tenant) instead?

The issue he or she is referring to is that the underlying rows are fragmented within each fetched page (so you might need to fetch 40 pages for 40 index entries, even if they would all fit on one partitioned page). Fiddling with the index order isn't going to change that (the current index order is already optimal for that type of query). There's another solution, which is to expand the index to include any covered rows so you can use index-only scans and not hit the main rows at all, but that's trading away a large amount of index size, which you usually want to avoid unless you have no other choice.

Re: Implement table partitioning

#60

Post by mioelnir »

flukus wrote: post #54
mioelnir wrote: post #51

Earlier quoted context omitted.

First, if your query used the partition key in its where clause, the database knows(can calculate) which partitions can have a result and which can not. This means smaller indexes/less data to scan to find the result. In the MSSQL case - not sure about others, this is were I had to use it - you can also switch data segments between tables indexed over the same partition function and with the same DDL. So you recreate…

So this won't help when you have something like a deleted flag and need to join on data that could be in either partition?

Yes and no. It won't help in the sense that the query planner can not identify the affected partitions. But it can help if the database supports parallel index scans, since instead of one big index, it has to scan n smaller indexes which can be done concurrently.
Post reply on HN