Chapter covered
the performance of SQL queries. This chapter discusses more
general performance considerations.
One of the most important administrative tasks is the scheduling of
the vacuumdb -a command, which vacuums all
databases. It should be run when the databases are least busy. Section
describes the purpose of vacuuming. Vacuum analyze
should also be performed periodically; it is covered in Section
.
The vacuumdb command can perform analyzing
as well. See the vacuumdb manual page for
more information.
Both postmaster and postgres have several flags that can improve performance. In POSTGRESQL release 7.0 and earlier, the postgres -F flag prevents the database server from flushing all data to disk at the end of each transaction. This improves performance, but if the operating system abnormally shuts down, the database can be left in an inconsistent state. Later releases may not use this flag.
The postmaster -B flag controls the amount of shared buffer memory allocated. The postgres -S flag controls the amount of sort memory allocated. While these flags consume system resources, they also improve performance by reducing disk access.
You can also improve database performance by moving databases to different disk drives. This strategy spreads disk access among multiple drives. The initlocation utility allows new database locations to be created on different drives; createdb can then use these locations for new databases.
POSTGRESQL stores tables and indexes in operating system files. Using operating system symbolic links, you can move databases, tables, and indexes to different disk drives, which often improves performance.