Storage sizing

One of the best practices to predict the database disk storage capacity is by loading a set of sample data into the application's database, and simulating production kind of actions using pgbench over a long period. For a period of time (every 1 hour), let's collect the database size using pg_database_size() or any native command, which returns the disk usage information. Once we get the periodic intervals for at least 24 hours, then we can find an average disk growth ratio by calculating the average of delta among each interval value.

Prepare the SQL script as follows, which simulates the live application behavior in the database:

Create connection;            --- Create/Use pool connection.
INSERT operation              --- Initial write operation.
SELECT pg_sleep(0.01);        --- Some application code runs here, and waiting for the next query.
UPDATE operation              --- Update other tables for the newly inserted records.
SELECT pg_sleep(0.1);         --- Updating other services which shows the live graphs on the updated records.
DELETE operation              --- Delete or purge any unnecessary data.
SELECT pg_sleep(0.01);        --- Some application code overhead.

Let's run the following pgbench test case, with the preceding test file for 24 hours:

$ pgbench -T 86400 -f <script location> -c <number of concurrent connections>

In parallel, let's schedule a job that collects the database size every hour using the pg_database_size() function, also schedule another job to run for every 10 minutes, which run the VACUUM on the database. This VACUUM job takes care of reclaiming the dead tuples logically at database level. However, in production servers, we will not deploy the VACUUM job to run for every 10 minutes, as the autovacuum process takes care of the dead tuples. As this test is not for database performance benchmarking, we can also make autovacuum more aggressive on the database side as well.

Once we find the average disk growth per day, we can predict the database growth for the next 1 or 2 years. However, the database write rate also increases with the business growth. So, we need to deploy the database growth script or we need to analyze any disk storage trends from the monitoring tool to make a better prediction of the storage size.