Re: Postgresql partitioning - single hot table or distributed

Hi Vick,

Currently we aren't deleting anything due to business requirements
though at some point we will have to start deleting out some data. I
suspect when we do it won't be as simple as just dropping the oldest
data; some customers will have data that we want to keep permanently,
while others will be ok to be dropped after 30 days.

Inserting directly into the specific partition is interesting, but if
you're going to go down that route then aren't you starting to
implement the partitioning yourself in application code. In that case
what benefit does keeping the Postgresql partitioning in place
actually give you?

Thanks for the reply.

Sam

On 30 June 2010 02:39, Vick Khera <vivek(at)khera(dot)org> wrote:
> 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.
>