Re: tables with 300+ partitions

["Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>]

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