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.
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.
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).
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?
Thanks,
Rob