Re: Partioning with overlapping and non overlapping constraints

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.

On Sat, Feb 7, 2015 at 3:09 AM, Melvin Davidson <melvin6925(at)gmail(dot)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_XXXXX
>
> In 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) = 1
>
> or
> EXTRACT(YEAR FROM utc_time) = 2015 AND
> EXTRACT(MONTH FROM utc_time) = 1
>
> or just
> EXTRACT(YEAR FROM utc_time) = 2015
>
>
> On Thu, Feb 5, 2015 at 10:12 PM, David G Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>