Tablespace growth and information

Job details

Name:	Tablespace growth and information
Platform:	Postgres
Category:	Capacity
Description:	Shows an overview of tablespace growth and statistics.
Long description:	Shows an overview of tablespace growth and statistics.
Version:	1.4
Default schedule:	10 18 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’]

Parameters

Name	Default value	Description
ignore tablespace		Tablespaces you do not want to list in report.
keep data for	7	The number of days to keep the data for.

Job Summary

Purpose: The purpose of this job is to monitor the growth statistics and overall growth rates of tablespaces in a PostgreSQL database environment.
Why: This job is essential to track the increase in data stored within tablespaces, helping database administrators manage storage capacity and plan for future storage requirements. It ensures that the database does not run out of space unexpectedly, which can lead to significant disruptions in database services.
Manual checking: To check this manually in the database, consider running the following SQL commands:

select spcname as "Tablespace name", round(pg_tablespace_size(spcname)/1024.0/1024.0, 1) as "Tablespace size in MB" from pg_tablespace

Job Details and Functions

Job Name: Tablespace growth and information
Description: Shows an overview of tablespace growth and statistics.
Version: 1.4
Company: dbwatch.no
Artifact ID: postgres_tablespace_growth_and_info
Default Schedule: This job is scheduled to run daily at 18:10.

Dependencies

Object	Object Type	Description	Cleanup on Fail
dbw_tablespace_growth_rate(taskId int)	2	Function that calculates growth rate	true
dbw_tablespace_growth_histr	0	Historical growth data table	true
dbw_tablespace_growth_ignore_list	0	List of tablespaces to ignore in reports	true

Report Template and Structure

Template Version: 2
Title: Database growth rate (detailed)
Description: Detailed report on the database tablespaces growth over time.

Presentations:

“Tablespace growth and information”: This visualization shows the tablespaces names and their total size measured at different dates.
“Daily tablespace growth last month”: This plot charts the change in size of tablespaces, broken down by day over the past month.
“Current tablespace size”: Simple tabular presentation showing the current size of each tablespace.

Database Queries and Operations

Installation and Setup Procedures: Creation of tables such as ‘dbw_tablespace_growth_ignore_list’ and ‘dbw_tablespace_growth_histr’, as well as a PostgreSQL function ‘dbw_tablespace_growth_rate’ to compute and log the growth rates of tablespaces.
Data Maintenance: Old data is purged from the historical table based on the ‘keep data for’ setting, ensuring that the database is not cluttered with obsolete growth data.

Additional Information

This job, while automated, includes comprehensive visual reporting that allows for easy interpretation and analysis of data trends, which is critical for effective capacity management.

By offering vital insights through systematic tracking and structured reporting, this job serves as an integral part of maintaining the health and performance of the PostgreSQL database environment.