PostgreSQL Tablespaces Test

Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured.

Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system.

Tablespaces should be adequately sized. If not, the tablespaces may not be able to accomodate many critical database objects, thereby causing the performance of the database to suffer. Continuous monitoring of tablespace size and usage is hence important. The PostgreSQL Tablespaces test does just that. This test auto-discovers tablespaces managed by this PostgreSQL server and reports how well the tablespace has been utilized.

Target of the test : PostgreSQL server

Agent deploying the test: An internal/remote agent

Outputs of the test : One set of results for every tablespace in every database that is configured for monitoring in the target PostgreSQL server

Configurable parameters for the test
  1. TEST PERIOD – How often should the test be executed.
  2. Host – The IP address of the server.
  3. Port – The port on which the server is listening. The default port is 5432.
  4. User – In order to monitor a PostgreSQL server, you need to manually create a special database user account in every PostgreSQL database instance that requires monitoring. When doing so, ensure that this user is vested with the superuser privileges. The sample script we recommend for user creation for eG monitoring is:

    CREATE ROLE eguser LOGIN

    ENCRYPTED PASSWORD {‘eguser password’}

    SUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

    The name of this user has to be specified in the USERNAME text box.

  5. Password- The password associated with the above user name (can be ‘NULL’). Here, ‘NULL’ means that the user does not have any password.
  6. Confirm password – Confirm the password (if any) by retyping it here.
  7. dbname - The name of the database to connect to. The default is “postgres”.
  8. include db - Specify a comma-separated list of databases that you wish to monitor.
  9. exclude db - Specify a comma-separated list of databases that need to be excluded from monitoring.
  10. ssl - The name of this user has to be specified in the USERNAME text box.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Tablespace size:

Indicates the amount of space currently used in this tablespace.

MB

A high value of this measure indicates that the table consumes a large chunk of space in the tablespace which may cause serious performance issues ranging from slowdown to shutdowns of this database.