Re: At what point does a big table start becoming too big?

Chris Travers <chris(dot)travers(at)gmail(dot)com> writes:
> On Thu, Aug 23, 2012 at 6:46 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> Partitioning doesn't reduce index size -- it makes total index size
>> *bigger* since you have to duplicate higher nodes in the index --
>> unless you can exploit the table structure around the partition so
>> that less fields have to be indexed.

> Depending on the operation it may make the effective index size bigger
> or smaller. For example if querying only one child table your
> effective index size is much smaller.

I tend to think of it like this: partitioning means *manually* replacing
the first level of index search.

As such, it is almost never a win for either complexity or performance
of simple searches and updates. As Merlin said, pretty much the only
compelling reason to do it is if you can match up the partition
boundaries with bulk tasks that are common in your application, such as
dropping a month's worth of data at a time.

regards, tom lane