Best web hosting - CHAPTER 26 POSTGRESQL ADMINISTRATION The VACUUM command
CHAPTER 26 POSTGRESQL ADMINISTRATION The VACUUM command breaks down into two basic use cases, each with a variation of the above syntax and each accomplishing different tasks. The first case, sometimes referred to as regular or lazy vacuums, is called without the FULL option, and is used to recover disk space found in empty disk pages and to mark space as reusable for future transactions. This form of VACUUMis nonblocking, meaning concurrent reads and writes may occur on a table as it is being vacuumed. Calling this version of the command without a table name vacuums all tables in the database; specifying a table vacuums only that table. Caution If you are managing your vacuuming manually, you can normally get away with just vacuuming specific tables under normal operations, but you do need to do a complete vacuum of the database once every one billion transactions in order to keep the transaction ID counter (an internal counter used for managing which transactions are valid) from getting corrupted. The other case for VACUUM is referred to as the full version, based on the inclusion of the FULL keyword. This version of VACUUM is much more aggressive with regard to reclaiming dead tuple space. Rather than just reclaim available space and mark space for reuse, it physically moves tuples around, maximizing the amount of space that can be recovered. While this is good for performance and managing disk space, the downside is that VACUUM FULL must exclusively lock the table while it is being worked on, meaning that no concurrent read or write operations can take place on the table while it is being vacuumed. Because of this, the generally recommend practice is to use regular lazy vacuums and reserve VACUUM FULL for cases in which a large majority of rows in the table have been removed or updated. There is actually a third version of the VACUUM command, known as VACUUM FREEZE. This version is meant for freezing a database into a steady state, where no further transactions will be modifying data. Its primary use is for creating new template databases, but that is not needed in most, if any, routine maintenance plans. The ANALYZE option can be run with both cases of VACUUM. If it is present, PostgreSQL will run an ANALYZE command for each table after it is vacuumed, updating the statistics for each table. We discuss the ANALYZE command more in just a moment. The VERBOSE option provides valuable output that can be studied to determine information regarding the physical makeup of the table, including how many live rows are in the table, how many dead rows have been reclaimed, and how many pages are being used on disk for the table and its indexes. Analyze When you execute a query with PostgreSQL, the server examines the query to determine the fastest plan for retrieving the query results. It bases these decisions on statistical information that it holds on each of the tables, such as the number of rows in a table, the range of values in a table, or the distribution of values. In order for the server to consistently choose good plans, this statistical information must be kept up to date. This task is accomplished through the ANALYZE command, using the following syntax: ANALYZE [ VERBOSE ] [ table [ (column [, …] ) ] ]
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.