Implement table partitioning
git.postgresql.org
git.postgresql.org
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.
But then again, maybe i'm the outlier here.
SELECT * FROM partitioned_table WHERE partition_date_key > SYSDATE - 1;
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.
Those patches are absolutely insane. Makes you remember how much hard work goes into building the software you use on a day to day basis.
https://www.postgresql.org/message-id/attachment/45478/0001-...
Only recently did I realize that being focused on actual data and how to process it inside PostgreSQL was maybe the best way I could spend my working time. I really can't say what's the best part of PostgreSQL, the hyperactive community, the rock solid and clear documentation or the constant roll-out of efficient, non-disruptive, user-focused features...
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.
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.
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.
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.
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.
- 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.
https://www.postgresql.org/docs/current/static/ddl-partition...
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 (<columns>)) 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.
Because table partitioning is less general than table inheritance, it
is hoped that it will be easier to reason about properties of
partitions, and therefore that this will serve as a better foundation
for a variety of possible optimizations, including query planner
optimizations.If i got this patch right, each partitioned table will have the same data structure and store whole rows (it's even more restrictive than previous inheritance mechanism that allowed extending by adding additional columns).
Column or expression should only define in which table an inserted row is supposed to be stored. A single row will never been torn apart. Still it look like a foundation that facilitate sharding BigData(Set) between multiple servers when used in conjunction with foreign data. However a lot of performance improvements will still be needed to compete against solid NoSQL projects (in which you really have a BigData use case).
But looking a bit forward, developing performances improvements on top of an ACID compliant distributed database seems less difficult than to develop a NoSQL project for it to become ACID.
But this patch is about horizontal partitions.
Partitioning can drastically improve query times because the planner can use statistics only from a single partition (assuming the query works on a single partition). Postgres uses (among other things) a range histogram of cardinalities to determine the "selectivity" — how many rows a query is likely going to match. If you have a 1B-row table and you're looking for a value that only occurs once (low cardinality), the statistics won't help all that much. But if you partition it so that you're only looking at 1M rows instead of 1B, the planner can be a lot more precise.
Another point is cache efficiency. You want the cache to contain only "hot" data that's actively being queried for. If most of your queries are against new data, then without partitioning, any single page would likely contain tuples from all time periods, randomly intermixed, and so a cached page will contain a lot of data that's not used by any query. (Note that if you use table clustering, which requires the regularly running of the "CLUSTER" command, then you can achieve the same effect at the expense of having to rewrite the entire table.) If you partition by time, you'd ensure that the cache was being used more optimally.
Write access is also helped by partitioning by cold/hot data: B-tree management is cheaper and more cache-efficient if it doesn't need to reorganize cold data along with the hot. And smaller, frequently changed partitions can be vacuumed/analyzed more frequently, while unchanging partitions can be left alone.
2. Indexing doesn't work well in all cases. You can be better off scanning entire small partition tables lacking an index on a given column than with a single very large table whether that column has an index or not. (Indexes take up space and need to be read from disk if they don't fit in a memory cache, indexes don't work well for low-cardinality columns, etc.)
3. There are operations you can parallelize on a large number of small/medium tables and perform faster or more conveniently than a single very large table. One of my favorite techniques:
# usage: seq 0 255 |parallel -j16 ./alter-tablespace.sh {}
hexn=`printf "%02x" $1`
psql -Atc "ALTER TABLE tablename_${hexn} SET TABLESPACE new_tblspace" -hxxx -Uxxx xxx
4. A nice side effect of properly/evenly partitioned data you get for free is that you can do certain types of analysis on a single partition (or a few) very quickly and have it represent a sampled version of the data set. You can think of it as another index you get for free.
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.
https://oracle2amar.wordpress.com/2010/07/09/what-are-latche...
"A latch is a type of a lock that can be very quickly acquired and freed."
That brings me a couple more questions:
1. May I infer then that the only benefit from partitioning the table (fully located on the same disk) that can not be achieved by indexes is that queries will wait less time for this kind of lock to be released?
2. May I assume while a table is only being read and not changed, there's no performance gain from partitioning a table (fully located on the same disk) that can not be achieved by indexes?
See below for many examples (I originally tried to split up the read and write benefits, but that usually isn't very meaningful since speeding up writes often speeds up reads indirectly (by enabling more indices to be maintained, releasing resources faster, leading to reduction in index size, or improved statistics).
> 2. May I assume while a table is only being read and not updated, there's no performance gain from partitioning a table (fully located on the same disk) that can not be achieved by indexes?
No. First, latch contention occurs even on fetch--latches are much lower level than the kinds of logical locks that get taken on updates and are generally required in order to enforce database invariants (in Postgres btree indices they live at the page level). Second, there are many other benefits.
An important one is separate maintenance of planner statistics. When a table has enough entries, Postgres's "most frequent values" list ends up not being able to fit all the rows with large frequencies (this can be tweaked up to storing 10,000 such buckets, but eventually even this is not enough if your table is very large). It then has to resort to its histogram, which normally only works with columns with a natural sort order. Since Postgres bases many important decisions during query planning on these statistics, you will often see order-of-magnitude improvements in query performance by improving table statistics. Note that composite indices do not keep separate statistics "per prefix" the way partitioning does (that is, an index on (a,b) does not track statistics of column b "within" column a); they just keep statistics on the individual covered columns or expressions. Not tracking cross-column correlations has a large impact in practice if your columns are not uniformly distributed throughout your table (and you have enough rows).
Another potential benefit (depending on your partition key) can be reduction in index size. For example, if you partition a table into a different segment for each tenant in your organization, you can use selective indices that don't include the tenant id. Note that you can already do this with partial indices, but Postgres does not use their statistics, which makes them much less useful, and large numbers of them can degrade planner performance (moreover, until recently, it didn't even use them for index-only scans, but I believe this was fixed in 9.6). Reduction in index size allows more indices (or more nodes of the index) to fit into memory (or even sometimes cache, for internal nodes), which can lead to dramatic speedups, especially if you can use an index-only scan. Smaller indices are also much faster to build, maintain, and scan.
The combined index size of indices in a partition is also slightly smaller (by a factor of around log(n) / log(n/p)) but that isn't really that significant (for 2^30 (over 1 billion) tuples, and 100 partitions, it is still just a 1.3x improvement, and can easily be overwhelmed by the size of the extra metadata for smaller numbers of tuples). For much the same reason, updating 100 100-entry histograms / most-frequent-value sets can be (slightly) faster than a single 10,000 entry one.
Another one is that if you are fetching more than one row at a time from a partition, keeping them (more) clustered on disk and in RAM (remember that fetching a page of RAM that's not in cache is actually quite slow!) means substantial savings in I/O and greatly increased likelihood of page buffer hits, which leads to order of magnitude savings. This is probably the biggest improvement from partitioning, and it applies to more complicated queries than point lookups (such as range queries and bitmap index scans). There are also some types of index (notably BRIN) that are most effective if your data distribution is such that you know you tend to have monotonically increasing keys over the entire table. In multitenant situations, these become much more useful if you can restrict them to a single partition, and allows you to again save index space.
There are other benefits to overall database performance (in the same vein as btree latching) from being able to address different tables in parallel, such as vacuuming being able to be performed separately on different tables (there are also some drawbacks, like operations that perform metadata scans [also VACUUM!] taking longer, but maybe this is improved in Postgres's new scheme--I'm not sure).
One that I don't see people talk about a lot, but is critically important for our usecases where I work, is that Postgres's implementation of SSI (serializable snapshot isolation), though it tries to use gap and row-level locking, will conservatively upgrade to page-level locks, and sometimes even table-level ones. If other updates are going on in the database at the same time, this can cause false conflicts, leading to many unnecessary aborts and retries. If data are partitioned and updates tend to be specialized to a partition, the chances of this happening are greatly minimized.
Finally, even though it contradicts your original premise: requiring an index to be used for every operation is asking for trouble. Making scans fast pays off in spades (especially if you have arbitrary custom filters in queries) and can free up your programmers to focus on things other than database performance :P A combination of intelligent partitioning (where appropriate), some understanding of Postgres's internals, and very little else can allow servicing surprisingly large numbers of users with varied query requirements, without creating many indices that aren't directly implied by the data model.
Hopefully, this gives you some sense of why people are excited for this feature :)
What I'm looking forward to finding out is if I can take an arbitrary expression on a column and have it derive all the same benefits of range partitioning like constraint exclusion.
Another common use case is that we want to know an average number for all/some customers. To do this, we run a subquery grouped by customer, and then calculate the average in a surrounding query. I hope that the query builder wil eventually become smart enough to use the GROUP BY clause to distribute this subquery to the different partitions.
Will hash based partitioning be included in PostgreSQL 10? The post notes
A partitioning "column" can be an expression.
So I figured partitioning the table by foreign key would group all data together in a way that allows for faster access (typical access pattern would be select * where foreign_key = x). However, as the number of keys in the foreign table is unbounded and can be quite large, I wanted to partition the data to a limited number of tables, with
mod(foreign_key, number_of_partions)
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).