Re: reclaiming disk space after major updates

Поиск
Список
Период
Сортировка
От Dan Harris
Тема Re: reclaiming disk space after major updates
Дата
Msg-id 46687820.9000604@drivefaster.net
обсуждение исходный текст
Ответ на Re: reclaiming disk space after major updates  (Andrew Sullivan <ajs@crankycanuck.ca>)
Ответы Re: reclaiming disk space after major updates  (Andrew Sullivan <ajs@crankycanuck.ca>)
Список pgsql-admin
Andrew Sullivan wrote:
> On Wed, Jun 06, 2007 at 04:04:44PM -0600, Dan Harris wrote:
>> of these operations or a full dump/reload.  I do run VACUUM regularly, it's
>> just that sometimes we need to go back and update a huge percentage of rows
>> in a single batch due to changing customer requirements, leaving us with
>> significant table bloat.
>
> Do you need to update those rows in one transaction (i.e. is the
> requirement that they all get updated such that the change only
> becomes visible at once)?  If not, you can do this in batches and
> vacuum in between.  Batch updates are the prime sucky area in
> Postgres.

They don't always have to be in a single transaction, that's a good idea to
break it up and vacuum in between, I'll consider that.  Thanks

>
> Another trick, if the table is otherwise mostly static, is to do the
> updating in a copy of the table, and then use the transactional DDL
> features of postgres to change the table names.

I thought of this, but it seems to break other application logic that feeds a
steady streams of inserts into the tables.

Thanks again for your thoughts.  I guess I'll just have to work around this
problem in application logic.



В списке pgsql-admin по дате отправления:

Предыдущее
От: "Simon Riggs"
Дата:
Сообщение: Re: Attempt to re-archive existing WAL logsafterrestoringfrom backup
Следующее
От: jbar@es.co.nz
Дата:
Сообщение: problems installing postgresql 8.0 on Mac OS 10.3.9