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 <melvin6...@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_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.johns...@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-general >> > > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. >
