Re: Partioning with overlapping and non overlapping constraints

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.