[rt-users] Performance Tuning for PostgreSQL

[Vivek Khera]

>>>>> "JM" == Jim Meyer <purp at wildbrain.com> writes:

JM> Meanwhile, I found that we got an astounding speedup from doing
JM> "vacuumdb -z -v -e" which executes the VACUUM ANALYZE command. I hadn't
JM> done this sooner because of my misunderstanding of what the command
JM> would do; I read its explanation as "Will remove deleted rows" and, as
JM> RT never deletes rows, I didn't expect it to help. 

In Postgres, "deleted" rows are also the rows left over after any kind
of update to a tuple.  This is part of the multi-version concurrency
feature (ie, two transactions can be running, and each sees exactly
what was there at the start of its transaction no matter what else is
changed).  Thus, you need to regularly vacuum your database to clear
out these dead rows, and allow them to be re-used.

Adding in the "analyze" is helpful if the distribution of your index
keys changes, but it is not expensive to throw it in as part of your
daily (or more frequent, depending on usage) vacuum.  Running it once
after creating the tables is of little use.  You need to analyze your
actual data.

For my usage, a daily vacuum of RT is sufficient.  For other
databases, there are some tables I vacuum every 6 hours and some every
4 hours.  This keeps the on-disk image from getting too big since the
dead rows are not used again until they've been vacuumed.

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera at kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/