Capacity planning for a database server involves a lot of variables. We must account for table count, user activity, compliance storage requirements, indexes, object bloat, maintenance, archival, and more. We may even need to consider application features that do not yet exist. New functionality often brings additional tables, extra storage standards, and increased archival needs. Planning done now may have little relevance to future usage.

So how do we produce functional estimates for disk space with so many uncertain or fluctuating elements? We primarily want to avoid a scenario where we lack sufficient capacity to continue operating. Exhausting disk space results in ignored queries at best, and a completely frozen and difficult to repair database at worst. Neither are the ingredients of a highly available environment.

In this recipe, we will explore a possible approach to determine minimum storage for long-term operations. Our goal is to provision enough to avoid outright catastrophe, though it's in our best interest to allocate more than the bare minimum.

Since there are numerous variables that contribute to the volume of storage we want, we need information about each of them. Gather as many data points as possible regarding things such as the largest expected tables and indexes, row counts per day, indexes per table, desired excess, and anything else imaginable. We'll use all of it.

We can collect some of the information we want from PostgreSQL if we have a running instance already. If not, we can use baseline numbers. Follow these steps if you already have a PostgreSQL database available:

1. Submit this query to get the amount of space used by all databases:

SELECT pg_size_pretty(sum(pg_database_size(oid))::BIGINT) 
  FROM pg_database; 

2. Wait for one week.
3. Perform the preceding query again.
4. Subtract the first reading from the second.

If we don't have an existing installation and are working with a project that has yet to start development, we can substitute a few guesses instead. Without a running PostgreSQL instance, consider using these sample values obtained from a real-world system:

• Our databases have a total size of 100 GB.
• After 1 week, our install grew by 1.5 GB.

Next, we can calculate our growth needs for the next 3 years. Perform the following steps:

1. Multiply the data size delta by four.
2. Apply the following formula, where x is the most recent size of the databases, and y is the value from the previous step: x * (1 + y/x)^36.
3. Multiply the previous result by two.

The process we illustrate here is the magic of compounding interest. If we have an existing database directory, it can tell us not only how much space it currently consumes, but also how quickly it's currently growing. If not, we can start with a medium-size configuration and substitute a growth assumption that will cause the cumulative total to double in size every year. Remember, we begin by working with worst-case scenarios, and modify the numbers afterward.

The PostgreSQL query we provide takes advantage of the system catalog and known statistics regarding the database contents. The pg_database_size function always returns the number of bytes a database uses, so we must use the pg_size_pretty function to make it more human-readable.

Once we know the size of the database instance and its growth rate, we can apply a simple compounding interest function to estimate the volume at any point in the future. This not only accounts for the current growth rate, but also incorporates additional accumulation caused by increases in clients, table counts, and other unspecified sources. It's extremely aggressive, since we take the weekly growth rate, translate that to a monthly rate, and apply the compounding monthly instead of yearly.

And then we use a standard engineering tactic and double the estimate, just in case. Using the provided values—that of a 100 GB database that grows at 1.5 GB per week—we would have an 815 GB database install in 3 years. With a system that large, we should allocate at least 1,630 GB.

Alternatively, if our growth rate were more linear, we could simply add the 1.5 GB weekly growth rate for 3 years. In that case, the final tally after 3 years of accumulation would only be 334 GB, and we could be safe with a total capacity of 668 GB.

Don't let our formulas define your only path. Let's explore how they apply in a real-world situation, and how we can modify them to better fit our systems.

There are quite a few very large databases using PostgreSQL. Whether or not they have thousands of tables and indexes, billions of rows, or handle billions of queries per day, statistics help us plan for the future. Let's apply the previous steps to an example database that actually exists:

• The database is currently 875 GB.
• The database was 865 GB last week.
• The database grows by 10 GB per week.
• Thus, the database grows by 40 GB every 4 weeks.
• Using the formula we discussed in step two of this recipe, our size estimate after 3 years is: 875 * (1 + 40/875)^36 = 4,374 GB.
• Doubled, this is 8,748 GB.

Keep in mind that this estimation technique may grossly exaggerate the necessary space. If we take the existing 40 GB monthly growth rate, the database would only be 2,315 GB in 3 years. Of course, 2.3 TB is still a very large database; it's just half as large as our estimate.

We already mentioned that the growth curve used here is extremely aggressive. We can't risk ever running out of space in a production database and still consider ourselves highly available. However, there is probably a safe position between the current growth rate of the database, and the compounded estimate, especially since we are doubling the allocation anyway.

In the preceding real-world example, the database is likely to have a size between 2,315 GB and 4,374 GB. If we split the difference, that's 3,345 GB. Furthermore, we don't necessarily have to double that number if we're comfortable having a disk device that's 70% full 3 years from now, instead of 50%. With that in mind, we would probably be safe with 5 TB of space instead of 9 TB. That's a vast saving if we're willing to make those compromises.

At the beginning of this chapter, in the Planning for redundancy recipe, we created a hardware cost spreadsheet to estimate the total cost of a highly available server. If we were following the chapter, our spreadsheet already accounts for the minimum number of devices necessary to provide the IOPS we want.

Suppose we needed 5,000 IOPS, and decided to use 2.5-inch drives. That would require about 33 devices. Even at only 300 GB each, that's 9 TB of total available space. Yet the case for SSDs is the opposite. For our previous example, we would need at least five 1 GB SSD drives, or one very large PCIe SSD to provide 5 TB of space for the adjusted sample.

Whichever solution we finally choose, we can take the advice from every section so far. At this point, the spreadsheet should have a device count that should satisfy most, if not all, of our space and IOPS requirements.