PostgreSQL on SAP BTP - How to estimate the remaining storage free space?

[antoniojmnunes]

Many users of PostgreSQL on SAP BTP, hyperscaler option service find hard to estimate the consumption of storage to be able to scale it before a problem of 'no free storage' occurs.

In this blog post we will try to help you on how to determine the data volume used by each PostgreSQL database component in an instance to estimate the used and available free space.

If you want to monitor the consumption of resources of your instance, including the % of storage space at any moment, you can use the below extension APIs:

Retrieve the instance status and monitoring metrics:

• /postgresql-db/instances/:id/status
• /postgresql-db/instances/:id/metrics

The above extension APIs allows to retrieve status of an instance and the below metrics:

• CPU Utilization (%)
• Memory Utilization (%)
• Disk Utilization (%)
• Max Connections (%)

For more details on how to use the above APIs please check the product SAP Help Portal official documentation:

• Extension API Reference: Instance Monitoring 

But if you want to know what is the storage consumed by each database component you can follow the below steps.

As prerequisite's you need to:

1. ✅ Request a admin user access, please follow the steps document in the blog post: PostgreSQL on SAP BTP - Request an 'admin' user access valid for 'x' days
2. ✅ Setup a SSH tunnel connection to the database instance and connect to the instance database using pgAdmin tool, please follow the steps document in the blog post: PostgreSQL on SAP BTP - Activate (create) an extension using pgAdmin tool (Topic 2)

How to check the volume of data being used by each database of an instance and calculate approximately the used and free space using pgAdmin tool?

You need to connect to the PostgreSQL instance using the SSH tunnel as described in the pre-requisites  and run the below query, using for example PSQL or the pgAdmin query editor.

SELECT db.datname AS "Database Name", pg_catalog.pg_get_userbyid(db.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(db.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(db.datname))
ELSE 'Not possible to get size | No authorization'
END AS "Used SIZE"
FROM pg_catalog.pg_database db
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(db.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(db.datname)
ELSE NULL
END DESC;

The above SQL output shows the instance databases and their used size.

The storage spaced used by the databases is around 187,1GB and the storage free space size will be the difference to 200GB minus the size of the temporary files (PostgreSQL use some storage space for temporary files: see below).

 Our database has 200GB of storage allocated, as per the configuration parameters:

Note: The total allocated storage to the instance is the full 200GB, they correspond to 40 units of ‘Storage In 5GB Blocks (High Availability Variant)’, the full 200GB of storage is allocated and reserved to the instance, all 200GB (40 units) should be considered always as your storage billing cost, even you are not fully use it.

How to check the temporary files used by PostgreSQL instance when trying to calculate the free space available for DB growth?

Run the below query:

SELECT datname, temp_files AS "Temporary files",
temp_bytes AS "Size of temporary files",
pg_size_pretty(temp_bytes) as "GB"
FROM pg_stat_database;

The above SQL output shows the temporary files used for each database; these temp files also consume storage.

In PostgreSQL, the data volume and temporary files size are related in the context of query execution, sorting, and other operations that may require temporary storage.

☝️When sizing your database, you need to consider your data volume but also consider temporary use of storage by PostgreSQL. 

Another important point for sizing the space of your DB is the transaction logs growth in situations of high loads during the defined logs retention period.

Please refer always to the product official documentation at SAP Help Portal.

Check also other interesting blogs regarding PostgreSQL:

• PostgreSQL on SAP BTP - Are you looking for official blog posts? Blog INDEX

Thanks for your reading,

SAP BTP Backing Services – Product Management