Re: update functions locking tables

Clodoaldo Pinto <clodoaldo(dot)pinto(at)gmail(dot)com> writes:

> I'm already doing a vacuum (not full) once a day.
>
> A vacuum full or a cluster is totally out of reach since each take
> about one hour. The biggest table is 170 million rows long.

Well a regular vacuum will mark the free space for reuse. If you insert or
update any records the new ones will go into those spots. Make sure you set
the fsm_* parameters high enough to cover all the updates and inserts for the
entire day (or repeat the vacuum periodically even if there are no deletes or
updates going on to create more free space).

You should realize that what's going on here is that the old records are still
in your table, marked as deleted. So any sequential scan will take twice as
long as otherwise. I think even index scans could take twice as long too
depending on the distribution of values.

I'm not saying that's untenable. If all your queries are fast enough then
you're set and it's just a cost of having no downtime.

--
greg