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.g.johnston@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
