From: | Dave Johansen <davejohansen(at)gmail(dot)com> |
---|---|
To: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Recommendations for partitioning? |
Date: | 2013-12-05 15:36:21 |
Message-ID: | CAAcYxUemBq7mqv_-1BVPe36udVEJjDoF2_-9G4Azv7nXZtmZ3A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I'm managing a database that is adding about 10-20M records per day to a
table and time is a core part of most queries, so I've been looking into
seeing if I need to start using partitioning based on the time column and
I've found these general guidelines:
Don't use more than about 50 paritions (
http://www.postgresql.org/message-id/17147.1271696670@sss.pgh.pa.us )
Use triggers to make the interface easier (
https://wiki.postgresql.org/wiki/Table_partitioning#Trigger-based and
http://stackoverflow.com/questions/16049396/postgres-partition-by-week )
The only data I found fell inline with what you'd expect (i.e. speeds up
selects but slows down inserts/updates
http://www.if-not-true-then-false.com/2009/performance-testing-between-partitioned-and-non-partitioned-postgresql-tables-part-3/)
So I was thinking that partitioning based on month to keep the number of
partitions low, so that would mean about 0.5G records in each table. Does
that seem like a reasonable number of records in each partition? Is there
anything else that I should consider or be aware of?
Thanks,
Dave
From | Date | Subject | |
---|---|---|---|
Next Message | Metin Doslu | 2013-12-05 15:46:44 | Re: [HACKERS] Parallel Select query performance and shared buffers |
Previous Message | Skarsol | 2013-12-05 15:16:35 | Re: WAL + SSD = slow inserts? |