Re: tables with 300+ partitions

On 10/31/07, Pablo Alcaraz <pabloa(at)laotraesquina(dot)com(dot)ar> wrote:
>
> I was a program inserting into the base table. The program ran in 200+
> threads and every thread insert data on it. Every thread inserts a row every
> 3 seconds aprox.(or they used to do it), but when I put more partitions the
> insert speed went to 1 insert every 2 minutes.
>

We still need to know how you're redirecting inserts to the appropriate
partition. My guess is that you have rules on the base table which say "if
an incoming row matches a certain criteria, then insert into partition x
instead". There are several discussions on this newsgroup already about why
using rules for partitioning hurts insert performance.

Next question is *how* are you partitioning the table? If you're
partitioning based on some sort of log time field (i.e. today's data goes in
this partition, tomorrow's data goes in that partition, etc.), then it is
reasonably easy to use a single trigger (as oppose to many rules) on the
base table which you can modify on some schedule (using cron, for example).
If you're partitioning based on some other id field, then using a trigger
won't work as nicely.

Do I need to do a trigger for insert only or I need a trigger to update and
> delete too?
>

For delete, probably not. For update, depends on how you're partitioning.
Could the update cause the row to belong to a different partition? Do you
care about moving it at that point?

Steve