Recommendations for partitioning?

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

Responses

Browse pgsql-performance by date

  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?