Performance

 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.