Re: What's a reasonable maximum number for table partitions?

Поиск
Список
Период
Сортировка
От Tim Uckun
Тема Re: What's a reasonable maximum number for table partitions?
Дата
Msg-id CAGuHJrMxX8n1R6hmUpw=E-aGMuod7jNXcP2Z=6gbm5uVUEnVPw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: What's a reasonable maximum number for table partitions?  (Vick Khera <vivek@khera.org>)
Ответы Re: What's a reasonable maximum number for table partitions?  (Bill Moran <wmoran@potentialtech.com>)
Список 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@khera.org> wrote:

On Thu, Feb 12, 2015 at 7:44 PM, Tim Uckun <timuckun@gmail.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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Vasudevan, Ramya"
Дата:
Сообщение: Re: Question on session_replication_role
Следующее
От: David G Johnston
Дата:
Сообщение: Re: How can I refer to an ANYELEMENT variable in postgresql dynamic SQL?