Re: PostgreSQL Arrays and Performance

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