31–40 of 60 posts
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?
Earlier quoted context omitted.
Not natively, as in there is no PARTITION BY HASH ( ). What limitations do you face when trying to roll-your-own hash partitioning using check constraints (in 9.6)?
I wanted to partition a table by the foreign key, as the table receives a few hundred rows per foreign key per hour (it is a timeseries db). 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 wa…
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...
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.
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.
The fundamental issue here is that you'd actually have to move the rows between relations given that Postgres maintains separate storage etc. for each. There's no good way to do that.
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?
Yes. Less latch contention for nodes of a single btree index, for instance.
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?
Earlier quoted context omitted.
Yes. Less latch contention for nodes of a single btree index, for instance.
I didn't know what latch is, so I googled it and found a nice explanation: 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 l…
Earlier quoted context omitted.
I didn't know what latch is, so I googled it and found a nice explanation: 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 l…
There are other possibilities as well. For example, if your partitioning strategy is such that it improves the selectivity of an index, it could improve query plans for queries that were on an index that was less selective. As an example, I once had a table with over a billion rows distributed among ~100 tenants on which queries were typically run by tenant and date range. Partitioning that table by tenant dramatical…
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.