From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | Vick Khera <vivek(at)khera(dot)org> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What's a reasonable maximum number for table partitions? |
Date: | 2015-02-13 22:14:10 |
Message-ID: | CAGuHJrMxX8n1R6hmUpw=E-aGMuod7jNXcP2Z=6gbm5uVUEnVPw@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
If I used modulo arithmetic how would the query optimizer know which table
to include and exclude? For example say I did modulo 100 based on the field
client_id. I create a base table with the trigger to insert the data into
the proper child table. Each table has the constraint (client_id % 100) = X
So if I do select from base table where client_id = 10 would postgres know
to only select from client_table_10? Normally I would always have a
client_id in my queries so hopefully the this could be very efficient.
On Sat, Feb 14, 2015 at 5:12 AM, Vick Khera <vivek(at)khera(dot)org> wrote:
>
> On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
>
>> Does anybody have experience with huge number of partitions if so where
>> did you start running into trouble?
>>
>
> I use an arbitrary 100-way split for a lot of tracking info. Just modulo
> 100 on the ID column. I've never had any issues with that. If you can
> adjust your queries to pick the right partition ahead of time, which I am
> able to do for many queries, the number of partitions shouldn't matter
> much. Only rarely do I need to query the primary table.
>
> I don't think your plan for 365 partitions is outrageous on modern large
> hardware. For 1000 partitions, I don't know. It will depend on how you can
> optimize your queries before giving them to postgres.
>
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2015-02-13 22:14:32 | Re: How can I refer to an ANYELEMENT variable in postgresql dynamic SQL? |
Previous Message | Mathieu Basille | 2015-02-13 20:37:04 | Re: Hardware requirements for a PostGIS server |