Re: Postgresql partitioning - single hot table or distributed

On Tue, Jun 29, 2010 at 4:00 PM, sam mulube <sam(dot)mulube(at)gmail(dot)com> wrote:
> Alternatively we wondered about partitioning by the server_id foreign
> key, using for example the modulo of the foreign key id. This would
> give us a finite number of partitions (rather than the potentially
> unbounded date option), and would likely cause writes to be much more
> evenly distributed between the partitions.

Do you ever delete? If so, what is the delete pattern?

I have several large tables split into 100 partitions which keeps them
in the O(10million) row size range each, and that has proven to be
ideal for our use. We insert into the partition directly, and select
from it whenever possible avoiding the constraint exclusion step. The
only time we get bad performance is when doing a join that cannot run
constraint exclusion, and that turns out to be pretty expensive.