Re: Partioning with overlapping and non overlapping constraints - Mailing list pgsql-general
Partitioning by day would result in less partitions but of course it would create a "hot" table where all the writes go.
Actually I have thought of an alternative and I'd be interested in your opinion of it.I leave the metrics table alone, The current code continues to read and write from the metrics. Every night I create a table based on metricts_YYYYMMDD which inherit from metrics and move data (using the "ONLY" clause in the delete) into the table and then set a constraint for that table for that day. I also adjust the constraint for the metrics table which is basically saying "where timestamp > YYYMMDD".
This way there is no trigger in the parent table to slow down the inserts and I still have partitions that will speed up read queries. I realize that moving large amounts of data is going to be painful but perhaps I can do it in chunks.
This way there is no trigger in the parent table to slow down the inserts and I still have partitions that will speed up read queries. I realize that moving large amounts of data is going to be painful but perhaps I can do it in chunks.
On Sat, Feb 7, 2015 at 3:09 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
Perhaps, I do not fully understand completely, but would it not be simpler to just rearrange the key (and partition) by date & location?EG: 2015_01_01_metrics_location_XXXXXIn that way, you would only have 365 partitions per year at most. But you also have the option to break it down by week or month, or year.EG:EXTRACT(YEAR FROM utc_time) = 2015 AND
EXTRACT(WEEK FROM utc_time) = 1or
EXTRACT(YEAR FROM utc_time) = 2015 AND
EXTRACT(MONTH FROM utc_time) = 1or just
EXTRACT(YEAR FROM utc_time) = 2015--On Thu, Feb 5, 2015 at 10:12 PM, David G Johnston <david.g.johnston@gmail.com> wrote:Tim Uckun wrote
> 1. Should I be worried about having possibly hundreds of thousands of
> shards.
IIRC, yes.
> 2. Is PG smart enough to handle overlapping constraints on table and limit
> it's querying to only those tables that have the correct time constraint.
Probably yes, but seems easy enough to verify.
All constraints are checked for each partiton and if any return false the
entire partiton will be excluded; which means multiple partitions can be
included.
Note, this is large reason why #1 poses a problem.
David J.
--
View this message in context: http://postgresql.nabble.com/Partioning-with-overlapping-and-non-overlapping-constraints-tp5836869p5836871.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalMelvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: