[EMAIL PROTECTED] (Rob Nagler) writes: > I've read some posts that says vacuum doesn't lock, but my experience > today indicates the opposite. It seemed that "vacuum full analyze" > was locked waiting and so were other postmaster processes. It > appeared to be deadlock, because all were in "WAITING" state according > to ps. I let this go for about a 1/2 hour, and then killed the vacuum > at which point all other processes completed normally.
VACUUM FULL certainly does lock. See the man page: INPUTS FULL Selects ``full'' vacuum, which may reclaim more space, but takes much longer and exclusively locks the table. The usual answer is that you probably _didn't_ want to VACUUM FULL. VACUUM ('no full') does NOT block updates. > The same thing seemed to be happening with reindex on a table. It > seems that the reindex locks the table and some other resource which > then causes deadlock with other active processes. Not surprising either. While the reindex takes place, updates to that table have to be deferred. > Another issue seems to be performance. A reindex on some indexes is > taking 12 minutes or so. Vacuum seems to be slow, too. Way longer > than the time it takes to reimport the entire database (30 mins). That seems a little surprising. > In summary, I suspect that it is better from a UI perspective to > bring down the app on Sat at 3 a.m and reimport with a fixed time > period than to live through reindexing/vacuuming which may deadlock. > Am I missing something? Consider running pg_autovacuum, and thereby do a little bit of vacuuming here and there all the time. It DOESN'T block, so unless your system is really busy, it shouldn't slow things down to a major degree. -- "cbbrowne","@","libertyrms.info" <http://dev6.int.libertyrms.com/> Christopher Browne (416) 646 3304 x124 (land) ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])