Re: PostgreSQL Arrays and Performance

[Joe Conway <mail@xxxxxxxxxxxxx>]

Marc Philipp wrote:
> During a daily update process new timestamps are collected and  existing 
> data rows are being updated (new rows are also being added).  These 
> changes affect a large percentage of the existing rows.
>
> What we have been observing in the last few weeks is, that the  overall 
> database size is increasing rapidly due to this table and  vacuum 
> processes seem to deadlock with other processes querying data  from this 
> table.

This sounds like it has more to do with inadequate freespace map 
settings than use of arrays. Every update creates a dead tuple, and if 
it is large (because the array is large) and leaked (because you have no 
room in your freespace map), that would explain a rapidly increasing 
database size.

> Therefore, the the database keeps growing and becomes more and more  
> unusable. The only thing that helps is dumping and restoring it which  
> is nothing you are eager to do on a large live system and a daily basis.

Arrays are stored as compressed varlenas, pretty much exactly like 
varchar or text fields. However, if your use of arrays causes you to 
need to perform updates to a large percentage of your rows on a daily 
basis, instead of just inserting new rows, you should probably rethink 
your data model.

> Would it be more efficient to not use an array for this purpose but  
> split the table in two parts?

I think so.

Joe