Redundancy means having a spare, but a spare for what? Everything. Every single part, from the motherboard to the chassis, power supply to network cable, disk space to throughput, should have at least one piece of excess equipment or capacity available for immediate use.

The intent of this recipe is to consider as many of these elements as we can imagine before committing to a final inventory purchase.

Fire up your favorite spreadsheet program; we'll be using it to keep track of all the parts that go into the server, and any capacity concerns. If you don't have one, OpenOffice and LibreOffice are good free alternatives for building these spreadsheets, as is Google Sheets. The subsequent sections will help determine most of the row contents.

We simply need to produce a hardware spreadsheet to track our purchase needs. We can do that with the following steps:

1. Create a new spreadsheet for parts and details.
2. Create a heading row with the following columns:

• Type
• Capacity
• Supplier
• Price
• Count
• Total cost

3. Create a new row for each type of the following components:

• Chassis
• CPU
• Hard Drive (3.5")
• Hard Drive (2.5")
• Hard Drive (SSD)
• Motherboard
• XPower Supply
• RAID Controller
• RAM
• SAN

4. In the Chassis row, under the Total Cost column, enter the formula: =D2*E2.
5. Copy and paste the formula into the Total Cost column for all the rows we created. The end result should look something like the following screenshot:

What we've done is prepare a spreadsheet that we can fill in with information collected throughout the remainder of this chapter. We will have very long discussions regarding each part of the server we want to build, so we need a place to collect each decision we make along the way.

The heading column can include any other details you wish to retain about each part, but for the sake of simplicity, we have stuck to the bare minimum. This also goes for the parts we chose for each column. Depending on the vendor you select to supply your server, many of these decisions will already be made. It's still a good idea to include each component in case you need an emergency replacement.

The Total Cost column exists for one purpose: to itemize the cost of each part, multiplied by how many we will need to complete the server.

We'll have discussions later as to the failure rates of different types of hardware, which will influence how many excess components to allocate. Don't worry about that for now.

We also recommend including a summary for all Total Cost columns so we get an aggregate cost estimate for the whole server. To do that with our spreadsheet example, keep in mind that the Total Cost column is listed as column F.

To add a Sum Total column to your spreadsheet in row 15, column F, enter the formula =SUM(F2:F12). If you've added more columns, substitute for column F whichever column now holds Total Cost. Likewise, if you have more than 13 rows of different parts, use a different row to represent your summary price than row 15.

There are a lot of spreadsheet options available. Many corporations supply a copy of Microsoft Excel. However, if this is not the case, there are many alternatives, including the following:

• Google Sheets: https://docs.google.com/spreadsheets/
• Open Office: https://www.openoffice.org/
• Libre Office: https://www.libreoffice.org/

All of these options are free to use and popular enough that support and documentation are readily available.

IOPS (stands for Input/Output Operations Per Second) describes how many operations a device can perform per second before it should be considered saturated. If a device is saturated, further requests must wait until the device has spare bandwidth. A server overwhelmed with requests can amount to seconds, minutes, or even hours of delayed results.

Depending on application timeout settings and user patience, a device with low IOPS appears as a bottleneck that reduces both system responsiveness and the perception of quality. A database with insufficient IOPS to service queries in a timely manner is unavailable for all intents and purposes. It doesn't matter if PostgreSQL is still online and serving requests in this scenario, as its availability has already suffered.

In this recipe, we will attempt to account for future storage and throughput needs based on monthly increases in storage utilization.

This process is more of a thought experiment. We will present some very rough estimates of I/O performance for many different disk types. We should increment the entries in our hardware spreadsheet based on the perceived need for each.

The main things we will need for this process are numbers. During development, applications commonly have a goal, expected client count, table count, estimated growth rates, and so on. Even if we must guess many of these, each will contribute to our IOPS requirements. Have these numbers ready, even if they're simply guesses.

We need to figure out how many operations per second we can expect. We can estimate this by using the following steps:

1. Collect the number of simultaneous database connections. Start with the expected user count, and divide by 50.
2. Obtain the average number of queries per page. If this is unavailable, use 10.
3. Count the number of tables used in those queries. If this is unavailable, use 3.
4. Multiply these numbers together, and double the result.
5. Multiply the previous total by 8.
6. Increment the Count column in our hardware spreadsheet for one or more of the following, and round up:

• For 3.5" hard drives, divide by 200.
• For 2.5" hard drives, divide by 150.
• For SSD hard drives, divide by 50,000, and then add two.

7. Add 10% to any count greater than 0 and then round up.

Wow, that's a lot of work! There's a reason for everything, of course.

In the initial three steps, we're trying to determine how many operations might touch an object on disk. For every user that's actively loading a page, for every query in that page, and for every table in that query, that's a potential disk read or write.

We double that number to account for the fact we're estimating these values. It's a common engineering trick to double or triple calculations to absorb unexpected capacity, variance in materials, and so on. We can use that same technique here.

But why do we then multiply by eight? In a worst- (or best-) case scenario, it's not uncommon for an application to double the number of users or requests on a yearly basis. Doubled usage means doubled hardware needs. If requirements double in one year, we would need a server three times more powerful (1 + 2) than the original estimates to account for the second year. Another doubling would mean a server seven times better (1 + 2 + 4). CPUs, RAM, and storage are generally available as powers of two. Since it's fairly difficult to obtain storage seven times faster than what we already have, we multiply the total by eight.

That gives a total IOPS value roughly necessary for our database to immediately serve every request for the next 3 years, straight from the disk device. Several companies buy servers every three or four years as a balance between cost and capacity, so these estimates are based on that assumption.

In the next step, we get a rough estimate of the number of disks necessary to serve the required IOPS. Our numbers in these steps are based on hard drive performance. A 15,000 RPM SAS hard drive can serve, under ideal conditions, roughly 200 operations per second. Likewise, a 10,000 RPM drive can provide about 150 operations per second.

Current SSDs as of the time of writing commonly reach 200,000-300,000 IOPS, and some even regularly eclipse a cool million. However, their extreme speed suggests fewer of them are necessary to reach IOPS goals, and thus failure risk is not as evenly distributed. We artificially increase the number of these drives because, again, we are erring toward availability.

Finally, we add a few extra devices for spares that will reside in long-term storage, just in case one or more drives fail. This also insulates us from the rare event that hardware is discontinued or otherwise becomes difficult to obtain.

Figuring out the number of IOPS we need and the devices involved is only part of the story. Let's take a look at an example using values we might encounter in a real application. After that, we'll discuss other things we may need to consider when adjusting these numbers.

Sometimes, these large lists of calculations make more sense if we see them in practice. So let's make the assumption that 2,000 users will use our application each second. This is how this would look:

• 2000 / 50 = 40
• Default queries per page = 10
• Default tables per query = 3
• 40 * 10 * 3 * 2 = 2,400
• 2,400 * 8 = 19,200
• 19,200 IOPS in drives:
• 5" drives: 19,200 / 200 = 96
• 5" drives: 19,200 / 150 = 128
• SSDs: 2 + (19,200 / 50,000) = 2.38 ~ 3
• Add 10%:
• 5" drives: 96 + 9.6 = 105.6 ~ 106
• 5" drives: 128 + 12.8 = 140.8 ~ 141
• SSDs: 3 + 0.3 = 3.3 ~ 4

We are not taking storage capacity into account, which would likely increase our SSD total. We will be discussing capacity soon.

Our calculations always assume worst-case scenarios. This is both expensive and, in most cases, monumentally overzealous. We ignore RAM caching of disk blocks, we don't account for application frontend caches, and the PostgreSQL shared buffers are also not included.

Why? Crashes are always a concern. If a database crashes, buffers are forfeit. If the application frontend cache gets emptied or has problems, reads will be served directly from the database. Until caches are rebuilt, query results can be multiple orders of magnitude slower than normal for minutes or even hours. We will discuss methods of circumventing these effects, but these IOPS numbers give us a baseline.

The number of necessary IOPS, and hence disk requirements, are subject to risk evaluation and cost-benefit analysis. Deciding between 100% coverage and an acceptable fraction is a careful balancing act.

Feel free to reduce these numbers; just consider the cost of an outage as part of the total. If a delay is considered as part of standard operating procedures, fractions up to 50% are relatively low risk. If possible, try to run tests for an ultimate decision before purchase.

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.

A Redundant Array of Independent (or Inexpensive) Disks (RAID) often requires a separate controller card for management. The primary purpose of a RAID is to combine several physical devices into a single logical unit for the sake of redundancy and performance.

This is especially relevant to our interests. Carnegie Mellon University published a study in 2007 on hard drive failure rates. They found that hard drives fail at a rate of about 3% per year. Furthermore, they found that drive type and interface contributed little to disk longevity, and that hard drives do not reflect a tendency to fail early as was commonly accepted. These findings were largely corroborated by a parallel study released the same year by Google.

What does this mean? For our purposes in building a highly available server, it means hard drives should be looked at with great disdain. Larger databases will depend on tens or hundreds of hard drives in order to represent several terabytes of data. With a 3% failure rate per year, a 100-drive array would lose roughly nine devices after 3 years.

This is the primary reason that all of our calculations regarding disk devices automatically assume a 10% excess inventory allotment. If a drive fails, we need an immediate replacement. Vendors are not always capable of delivering a new drive quickly enough. Having a spare on hand, ideally at the hosting facility or in the server itself, helps ensure continuous uptime.

So how does RAID figure into this scenario? If we hosted our database on several bare hard drives knowing that around 10% of these drives will fail in 3 years, outages would be inevitable. What we want is an abstraction layer that can present any number of hard drives as a single whole, keeping reserves for drive errors, handling checksums for integrity, and mirroring for redundancy.

RAID provides all of that in several convenient configurations. Good controller cards often include copious amounts of cache and other management capabilities. Instead of manually assigning dozens of drives, it's fairly common to split them into several usable array allocations that reflect much lower operational risk.

Knowing all of this, databases have special needs when it comes to RAID and the performance characteristics associated with each RAID type. This recipe will explore the selection criteria for our database, and how to simplify the process.

That was a long introduction, wasn't it? Well, we also strongly suggest taking a look at the Having enough IOPS and Sizing storage recipes before continuing. Make sure the hardware spreadsheet has a drive count for the type of drives going into the server we're designing. If we're using PCIe instead of standard SSD drives, this section can be skipped.

Only a few RAID levels matter in a database context. Perform these steps to decide which one is right for this server:

• If this is an Online Transaction Processing (OLTP) database primarily for handling very high-speed queries, use RAID level 1+0.
• If this is a non-critical development or staging system, use RAID level 5.
• If this is a non-critical Online Analytic Processing (OLAP) reporting system, use RAID level 5.
• If this is a critical OLAP reporting system, use RAID level 6.
• If this is a long-term storage OLAP warehouse, use RAID level 6.

We have made a lot of snap decisions here. There are quite a few RAID levels we simply ignored, so there should be some discussion regarding the reasoning we used.

Let's begin with RAID level 0. Level 0 stripes data across all disks at once. It's certainly convenient, but a single drive failure will lose all stored information in the array. What about RAID level 1? Level 1 acts as a full mirror of all data stored. For every set of drives, a second set of drives holds an exact copy. If a drive fails in one set, the second set is still available. However, if that set also experiences any failure, all data is lost.

When we talk about RAID 1+0, we actually combine the mirroring capability of RAID 1 with the striping of RAID 0. How? Take a look at the following diagram:

In this RAID 1+0, we have three sets, each consisting of two disks. Each of the two disks mirrors the other, and the data is striped across all three sets. We could lose a disk from each set and still retain all of our data. We only have a problem if we lose two disks from the same set since they mirror each other. Overall, this is the most robust RAID level available, and the most commonly used for OLTP systems.

RAID level 5 and 6 take a different approach. Again, let's look at six drives and see a very simplified view of how RAID 5 would operate in that situation:

The solid line shows that the data is spread across all six drives. The dotted line is the parity information. If a drive fails and the block can't be read directly from the necessary location, a RAID 5 array will use the remaining parity information from all drives to reconstruct the missing data. The only real difference between a RAID 5 and a RAID 6 array is that a RAID 6 array contains a second parity line, so up to two drives can fail before the array begins operating in a degraded manner.

Using RAID 5 or 6 offers more protection than RAID 0, with fewer costs than RAID 1+0, which requires double the amount of required devices. We selected these for non-critical OLAP systems because they usually prioritize storage over performance and are not as sensitive to immediate availability pressures as an OLTP system.

We mentioned controller cards earlier and noted that they also offer onboard cache. RAID has been around for a long time, and though disks are getting much larger, they haven't experienced an equivalent increase in speed. In scenarios that use RAID 5 or 6, writes can also be slowed since each write must be committed to several devices simultaneously in the form of parity.

To combat this, RAID controllers allow configuration of the cache to buffer writes in favor of reads, or vice versa. Don't be afraid to adjust this and run tests to determine the best cache mix. If everything else fails, start with 100% for writes, as they are the most in need of caching. Keep a close eye on write performance and give it priority if possible. Generally, the OS cache does a better job of caching reads, and has much more memory available to do so.

For more details, refer to the following link:

• Disk failures in the real world: http://www.cs.cmu.edu/~bianca/fast07.pdf
• Failure Trends in a Large Disk Drive Population: http://research.google.com/pubs/pub32774.html

In selecting a CPU for our server, we have much to consider. At the time of writing, the current trend among processors in every space—including mobile—is toward multiple cores per chip. CPU manufacturers have found that providing a large number of smaller processing units spreads workload horizontally for better overall scalability.

As users of PostgreSQL, this benefits us tremendously. PostgreSQL is based on processes instead of threads. This means each connected client is assigned to a process that can use a CPU core when available. The host operating system can perform such allocations without any input from the database software. Motherboards have limited space, so we need more cores on the same limited real estate, which means more simultaneously active database clients.

Once again, our discussion veers toward capacity planning for a three- or four-year cycle. Limited processing capability leads to slow or delayed queries, or a database that is incapable of adequately handling increasing numbers of simultaneous users. Yet simply choosing the fastest CPU with the most cores and filling the motherboard can be a staggering waste of resources. So how, then, do we know what to buy?

This recipe will attempt to answer that question.

Luckily, there are only really two manufacturers that produce commodity server-class CPUs. Furthermore, each vendor has a line of CPUs designed specifically for server use. AMD and Intel both provide similar price-to-performance curves, but that's where the comparison ends.

This is an exciting time to purchase a CPU. At the time of writing, AMD Threadripper chips compare quite favorably to Intel Xeon processors, and often provide higher core counts. Unlike previous CPU architecture iterations, choosing a CPU isn't as simple as it once was.

Before going through this recipe, it would be a good idea to visit AnandTech, Tom's Hardware, Intel, and AMD, just to get a basic idea of the landscape. There are a lot of benchmarks that compare various models of CPUs, so don't take our word for it.

Generally, however, we can count on one thing for now: AMD CPUs tend toward higher core counts, while Intel chips prioritize per-core clock speed. This may affect which we choose for each environment, so we recommend cultivating a passing familiarity with these processor architectures if possible.

We can collect some of the information we want from the database if we have one already. If we already have a PostgreSQL database available, we can execute a query to start our calculations. This works best if used at the most active time of day.

If you have PostgreSQL 9.2 or higher, execute this query as a superuser to get the count of simultaneous active users:

SELECT count(*)
  FROM pg_stat_activity 
 WHERE state = 'active'; 

Use this query if you have an older version:

SELECT count(*)
  FROM pg_stat_activity
 WHERE current_query NOT LIKE '<IDLE>%'; 

If we don't have a PostgreSQL server, we need to make an educated guess. Use these steps to approximate:

1. Work with the application developers to obtain a count of expected active clients per second.
2. Divide the previous number by 50 to remain consistent with our 20 ms query assumption.

Once we have some idea of how many queries will be active simultaneously, we need to estimate the processor count. Follow these steps:

1. If we already know how many disks will store our data, use this number. In the case of an SSD base, use 0.
2. Subtract the previous number from our count of active users.

3. Divide the previous result by 2.
4. Apply the following formula, where x is the value from the previous step:

x * (1.4)^3

Before we can even begin to decide on a processor count, we need a baseline. With a working PostgreSQL server to base our numbers on, we can just use the number of existing users during a busy period. Without that, we need to guess. This guess can actually be fairly accurate depending on how the application functions. For example, if the intent is to service 1,000 users per second, we can start with the same assumption.

After that, we apply a commonly accepted formula used by PostgreSQL administrators for a very long time. The ideal number of active connections is equal to twice the amount of available processor cores, plus the amount of disk spindles. Amusingly, the disk spindles increase the ideal number of connections because they contribute seek time, which forces the processor to wait for information. While a processor is waiting for input intended for one connection, the operating system may decide to lend the processor to another until the data is retrieved.

Thus, we apply that accepted formula in reverse. First, we subtract the number of spindles, and then divide by two to obtain how many CPUs we should have for our expected workload.

Afterward, we assume a 40% increase in active clients on a yearly basis, and increase the CPU core count accordingly for 3 years.

When purchasing CPUs, no matter how cores are distributed, the final total should be equal to or greater than the number we calculated. If it isn't, the application may require more aggressive caching than expected, or we may need to horizontally scale the database. We're not ready to introduce that yet, but keep it in mind for later.

The processor count is only part of the story. Modern CPUs have a few additional elements we need to consider.

Essentially, all modern server-class processors provide a feature that essentially splits each physical processor core into two virtual cores. Intel calls this hyper-threading, while AMD uses the standard Simultaneous Multithreading (SMT) terminology. Historically, this was not well received as benchmarks often illustrated performance degradation when the feature was enabled.

Since the introduction of Intel's Nehalem-based architecture in 2008 and AMD's Ryzen architecture in 2017, this is no longer the case. While doubling the processor count does not result in a doubling of throughput, we've run several tests that show up to a 40% improvement over using physical cores alone. This may not be universal, but it does apply to PostgreSQL performance tests. What this means is that the commonly accepted formula for determining the ideal connection count requires modification.

The current advice is to only multiply the physical core count by two. Assuming a 40% increase by enabling SMT, the new formula becomes 2 * 1.4 * CPUs + spindles. With that in mind, if we wanted to serve 1,000 connections per second and used SSDs to host our data, our minimum CPU count would be 1000 / 50 / 1.4, or 14. Half of that is seven, but no CPU has seven physical cores, so we would need at least eight. If we used the physical cores alone for our calculation, we would need 10.

Recent processors also tend to provide adaptive CPU clock boosting based on perceived workload. Some vendor motherboards disable this by default.

This is possible because the maximum speed of the core itself is increased when resources are available or demand is high. A 4.2 GHz core might operate temporarily at 4.5 GHz. For queries that are dependent on nested loops or other CPU-intensive operations, this can drastically reduce query execution times.

Intel family chips often have low voltage versions of their high-performance offerings. While these processors require up to 30% less electricity, they also run up to 25% slower. Low-power name designations are not always consistent, so when choosing an Intel processor, make sure to compare specifications of all similarly named chips.

Beware of accidentally choosing a low-power chip meant for a high-performance database. However, these chips may be ideal for warehouse or reporting database use, since those systems are not meant for high throughput or vast amounts of simultaneous users. They often cost less than their high-performance counterparts, making them perfect for systems expecting low utilization.

For more details, refer to the following links:

• AnandTech: https://www.anandtech.com/
• Tom's Hardware: https://www.tomshardware.com/

The primary focus when selecting memory for a highly available system is stability. It's no accident that most, if not all, server-class RAM is of the error-correcting variety. There are a few other things to consider that may not appear obvious at first glance.

Due to the multi-core nature of our CPUs, the amount of addressable memory may depend on the core count. In addition, speed, latency, and parity are all considerations. We also must include the number of channels reported by each CPU; failing to match this with an equal count of memory sticks can drastically degrade performance.

This recipe will help ensure our server remains fast and stable by considering memory options.

Some of the decisions we will make depend on the capabilities of the CPU. Make sure to read through the Picking a processor recipe before continuing. If we have a PostgreSQL database available, there's also a query that can prepare us for selecting the most advantageous count of memory modules. It's also a very good idea to complete the Sizing storage recipe to get a better idea for choosing an amount of memory.

We can collect some of the information we want from PostgreSQL if we have an install already. Follow these steps if there's an existing database instance that we can use:

1. Execute the following query to obtain the size of all databases in the instance:

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

2. Multiply the result by eight.

If we don't have an existing database, we should use a size estimate of the database install after 3 years. Refer to the Sizing storage recipe to obtain this estimate. Then, perform the following steps:

1. Divide the current or estimated database storage size by ten to obtain the minimum amount of memory.
2. Multiply our ideal CPU chip count by four to get the memory module count.
3. Divide the minimum memory amount by the module count to get the minimum module size.
4. Round up to the nearest available memory module size.

The important part of this recipe is starting with a viable estimate of the database size. Since a lack of RAM usually won't cause the database to crash or operate improperly, we can use looser guidelines to obtain this number. Hence, 3 years down the road, an existing database install could be eight times larger than its current size. We use eight based on previous assumptions that the database roughly doubles in size every year.

Why do we then divide that number by ten? Our goal here is to maximize the benefit of the OS-level cache, which will consume a majority of our RAM. This estimate gives us a value that is ten times smaller than the space our database consumes. At this scale, data that is frequently fetched from disk is likely to be served from memory instead. The alternative is read latency due to insufficient memory for disk caching.

Most current CPUs are quad-channel, and thus operate best when the number of modules per processor is a multiple of four. Assuming we previously determined how many processor cores would be ideal for our system in the Picking a processor recipe, we automatically know the most efficient memory module count. Why do we multiply by four, regardless of how many memory channels the CPU has? Adding more memory modules is not wasted on chips with fewer channels, and provides a possible upgrade path.

Dividing the memory amount by the module count gives our minimum module size. RAM comes in many dimensions, and our calculation is not likely to match any of the available dimensions for purchase, so we need to round up. Why not round down? The operating system will utilize all available RAM to cache and buffer important data. Unless the greater amount is extremely expensive in comparison, any excess memory will not be wasted.

Imagine we have a 200 GB PostgreSQL installation, and chose to buy a single CPU chip. If we follow the preceding procedure, we would want at least 20 GB of RAM distributed over four RAM chips. Since 20/4 = 5, we would likely want four 8 GB RAM chips, or a total of 32 GB. This is actually a fairly good starting value in general, and allows our database to grow before 10% of its current size is greater than 32 GB.

We didn't focus on memory speed, timings, or latency here. Timing and latency can affect performance, but our primary focus is stability. We're always free to order faster or better memory as our budget allows.

Memory speed, on the other hand, is a more visible factor. Memory speed works with a multiplier to match the highest compatible motherboard bus speed. This directly controls how quickly the CPU can utilize available RAM. Before buying memory, research the stated clock speed and try to match it with one of the faster settings compatible with both the CPU and motherboard.

For example, DDR4-3200 is twice as fast as DDR4-1600 since it operates at 400 MHz, as opposed to 200 MHz. Database benchmarks would be vastly different between these two memory speeds, even with the same CPU. Fast memory means that PostgreSQL can make more immediate use of cached data and produce results more quickly.

The network card enables the database server to exchange data with the outside world. This includes far more than web servers, spreadsheets, loading jobs, application servers, and other data consumers. The database server is part of a large continuum of activity, much of which will center around maintenance, management, and even filesystem availability.

Little of this other traffic involves PostgreSQL directly. Much happens in the background regardless of the database and its current workload. Yet even one mishandled network packet across an otherwise normal driver can render the entire server invisible to the outside world or, in extreme cases, even lead to a system panic and subsequent shutdown. On a busy database server, network cards can handle several terabytes of traffic on a daily basis; the margin of error for such a critical piece of hardware is exceptionally slim.

What's more, network bandwidth can easily be saturated by an aggressive backup strategy, which is something critical to a highly available database. For PostgreSQL systems utilizing streaming replication or WAL archival, that traffic contributes quite a bit of bandwidth to the overall picture. If our backups are delayed, or replicas sit idly waiting for network packets, our exposure to risk is high indeed.

That's not to say everything is doom and gloom! With the right network setup and accompanying hardware, there should be more than enough room for any and all traffic our database server needs. This recipe will explore all the copious options for connecting our database to the outside world, and making sure it stays there.

This is one of those times when it pays to do research. At the time of writing, the current high-speed network standards include 1 Gb/s (gigabits per second), 10 Gb/s, 40 Gb/s, and even 100 Gb/s Ethernet. However, 40 Gb/s network cards are still extremely rare, and 100 Gb/s is generally reserved for fiber-based switches and data center use.

This means we will be covering 1 Gb/s and 10 Gb/s interfaces. While we will do our best to outline all of the important aspects of these technologies to simplify the process, we strongly encourage using the internet to validate current availability and performance characteristics.

Let's begin with a few basic calculations. Look at these following numbers that represent an estimate of interface speed after accounting for network overhead:

• 1,000 Mb/s * B/10 b = 100 MB/s (Megabytes per second)
• 10,000 Mb/s * B/10 b = 1,000 MB/s

Next, consider how many ways this will be distributed. If we have an existing PostgreSQL setup, follow these steps:

1. Execute the following query to determine the number of existing replicas:

SELECT count(*)+1 AS streams 
  FROM pg_stat_replication; 

2. Multiply streams by 160 to find the maximum MB/s needed by replication streams.
3. Execute the following queries together in a psql connection during a busy time of day on a production database:

SELECT SUM(pg_stat_get_db_tuples_fetched(oid)) AS count1 
  FROM pg_database; 
SELECT pg_sleep(1); 
SELECT SUM(pg_stat_get_db_tuples_fetched(oid)) AS count2 
  FROM pg_database;

4. Subtract the results of count1 from count2 for the number of rows fetched from the database per second.
5. Divide the number of rows per second by 10,000 to get the MB/s used by PostgreSQL connections.
6. Add the calculated value of MB/s for streams to the value of MB/s for connections.

Without an existing database, follow these steps for some basic bandwidth numbers:

1. Multiply the desired number of PostgreSQL replicas by 160 to get the maximum MB/s needed by replication streams.
2. Assume one WAL stream for an off-site disaster recovery database copy.
3. Start with at least one live hot-streaming standby copy.
4. Include any additional database mirrors.
5. Estimate the active client count, as discussed in Picking a Processor.
6. Multiply the active client count estimate by five for MB/s used by PostgreSQL connections.
7. Add the value of MB/s for streams to the value of MB/s for connections.

No matter which checklist we follow, we should double the final tally.

If we have an existing database, there is a wealth of statistical information at our fingertips. The first query we ran gave us a slightly inflated count of PostgreSQL replicas. For each replica, data must be transferred from the database to another server. This data is based on PostgreSQL WAL output, and these files are 16 MB each. A busy server can produce more than ten of these per second, so we multiply the count of streams by 160 to produce an aggressive amount of network overhead used by database replicas. As usual, this may be overzealous; it's always best to observe an actual system to measure maximum WAL segments generated during heavy write loads.

For the next set of numbers, we need to know how much data the database connections commonly retrieve. PostgreSQL tracks the number of table rows fetched, but it's a cumulative total. By waiting until a busy time of day and asking the database how many rows have been fetched before and after a one-second wait, we know how many rows are fetched per second.

However, we still don't know how many bytes these rows consume. A good estimate of this is 100 bytes per row. Then, we only have to multiply the number of rows by 100 to find the amount of bandwidth we would need. So why do we divide by 10,000? What's 10,000 multiplied by 100? One million. On dividing by 10,000, we produce the number of megabytes per second those tuple fetches probably used.

By adding the amount of streaming traffic to the amount of connection traffic, we have a good, if slightly inflated, idea of how much bandwidth the server needs.

Without a working database to go by, we need to use a few guesses instead. Luckily, the number of streams for a reliable database infrastructure starts at two: one for a live standby, and one for an off-site archive. Each additional desired mirror should increase this total. Again, we multiply the number of streams by 160 to obtain the maximum MB/s that all these replicas are likely to require.

The amount of bandwidth client connections use is slightly harder to estimate. However, if we worked through previous chapter sections, we have a CPU estimate, which also tells us the maximum number of database clients the server can reliably support. If we take that value and multiply by five, that provides a rough value in MB/s as well.

Again, we just add those two totals together, and we know the minimum speed of our network.

Finally, we multiply the final tally by two to account for any unknown maintenance, backup, and filesystem synchronization overhead.

Besides producing an estimate through some simple calculations, we also want to make note of a few other networking details.

This may be easier to visualize with a real example. Let's start with a very active database that has one streaming replica, and one off-site archive. Furthermore, connected clients regularly fetch five million rows per second. Let's work through our steps:

1. 2 * 160 = 320 MB/s
2. 5,000,000 / 10,000 = 50 MB/s
3. 320 + 50 = 370 MB/s
4. 370 * 2 = 740 MB/s

That's a very high value! A 1 Gb/s interface can only supply 100 MB/s at most, so we would need eight of those to produce the necessary bandwidth. Yet a 10 Gb/s interface can supply 1,000 MB/s, so it can easily handle 740 MB/s and have room to spare. Would we rather have eight network cables coming out of our server, or one?

One of the first things this chapter suggested was to consider extra inventory. Yet we haven't really covered online backups. Most server-class motherboards include not just one, but two onboard network modules. Each module commonly provides four Ethernet interfaces.

Usually, each interface is considered separate, and two interfaces from each module are connected to two switches in the data center. This allows server administrators to seamlessly perform maintenance on either switch without disrupting our network traffic. Furthermore, if a switch or network module fails, there's always a backup available.

In our working example, we would need eight 1 Gb/s interfaces to avoid experiencing network congestion. However, we've already used four of our eight available interfaces simply to satisfy basic server hosting requirements. That doesn't leave enough available capacity, and as a consequence, this server would experience a network bottleneck.

This would not be the case with a 10 Gb/s interface. Each of the interfaces connected to redundant switches can carry the entire network requirements of the server.

We suggested doing research on 1 Gb/s and 10 Gb/s network cards. Well, don't do too much. It's very likely the infrastructure department already has a standard server profile for high-bandwidth systems. This is primarily due to the fact 10 Gb/s is a very complicated standard compared to 1 Gb/s or lower. There are several different cable types available along with complimentary network modules, one or more of which are probably already deployed in the data center.

Just make sure that the infrastructure knows to allocate high-bandwidth resources if our calculations call for it.

We have been working up to this for quite some time. None of our storage, memory, CPU, or network matters if we have nothing to plug all of it into.

This could have been a long section dedicated to properly weighing the pros and cons of selecting a motherboard manufacturer for maximum stability. It turns out that most server vendors have already done all the hard work in that regard. In fact, a few vendors even disclose many details about the motherboard in their servers outside the model documentation. We can't really read hundreds of pages of documentation about every potential server we would like to consider, so what is the alternative?

No matter where we decide to purchase our server, vendors will not sell—or even present—incompatible choices. If we approached this chapter as intended, we already have a long list of parts, counts, and necessary details to exclude potential offerings very quickly. These choices will often come in the form of drop-down lists for every component the motherboard and chassis will accept.

The chassis will come later. For now, this recipe will focus on CPU, RAM, RAID, and network compatibility.

This is one of the times when the hardware spreadsheet will show its true usefulness. As long as we have been keeping track of our counts through each section, this segment of server selection will be much simpler. By this point, our spreadsheet should look something like the following screenshot:

We don't care about the total cost for each part yet. It might be a good idea to create a separate tab or copy of the spreadsheet for each vendor we want to consider. This way, we can do some comparison shopping. Also, remember that the counts are inflated by at least one replacement in case of failure. So we want to look for two 10-core CPUs, eight 16 GB memory modules, and so on.

Now it's time to do some research. Follow these steps:

1. Make a list of desired server vendors. This list may even be available from the infrastructure department, if our company has one.
2. For each vendor, check their available 1U and 2U products.
3. For each 1U or 2U server, remove from consideration any options that don't fulfill your minimum CPU requirements.
4. Repeat for RAM.
5. Repeat for RAID controller cards.
6. Repeat for network interface cards.
7. Fill in the actual selections, where appropriate, with unit prices.
8. Make corrections to the spreadsheet.

While this is straightforward, it's highly time-consuming. The number of server variants available, even from a single vendor, can be staggering. This is one of the reasons we only consider 1U and 2U servers. The other reason is that 4U servers and larger are often designed for different use patterns related to vertical scaling, incorporating more CPUs, hard drives, and even multiple concurrent motherboards.

That is generally excessive for our purposes. When purchasing servers with the explicit intention to obtain multiple, redundant, and compatible examples, this becomes more difficult as the cost and complexity of the servers increase.

Although we have reduced our sample size, there is still more work to do. We can answer questions while considering CPU compatibility. If we want ten-core chips, and the motherboard only supports up to octa core chips, we can remove that option from consideration. This also applies to available memory slots and sizes. Yet there's an unwritten element to RAM: the maximum amount supported by the motherboard. If the motherboard only supports up to 768 GB, and our earlier calculations show we may eventually want 1 TB, we can immediately cross it off our list.

Since RAIDs and network cards must be plugged directly into the motherboard or an expansion daughter card, it's the number of these available slots that directly concerns us.

We need at least two to accommodate both cards, which could drastically reduce the contents of our motherboard list. In fact, higher expansion slot requirements may immediately exclude several 1U servers due to their limited expandability.

While performing this compatibility verification, it is difficult to ignore the prices listed next to each choice, or the total price changing with each selection. We might as well take advantage of that and fill in the rest of the spreadsheet and make a copy for each vendor or configuration. Some choices are likely to provide better complete matches overall, or offer more scalable future expandability or better price points, so tracking all of this is beneficial.

RAID controllers and network interfaces are somewhat special cases. Some servers integrate these directly into the motherboard in order to reduce size. This is especially true when it comes to network modules. If at all possible, try to resist relying solely upon integrated components.

The entire server will require replacement if these fail. This makes it much more difficult and expensive to fulfill our redundancy requirement. Server-class motherboards without integrated network interfaces are rare, but we can use these as our backup path if their minimum speed matches what we've configured.

For instance, if we want a 10 Gigabit Ethernet (GbE) card and the motherboard has integrated a 10 GbE module, we can reduce the number of excess cards on our spreadsheet by one. It's very likely that the integrated version is of lower quality, but it can suffice until the bad card is replaced.

Redundancy doesn't have to be expensive.

Here is a list of well-known server vendors we could consider while completing this section:

• Penguin Computing: https://www.penguincomputing.com
• Supermicro: https://www.supermicro.com/en
• Dell: https://www.dell.com
• HP: https://www.hp.com

To round out our hardware selection phase, it's time to decide just what kind of case to order from our server vendor. This is the final protective element that hosts the motherboard, drives, and power supplies necessary to keep everything running. And like always, we place a heavy emphasis on redundancy.

For the purposes of this section, we will concentrate primarily on 1U and 2U rack-mounted servers. Why not 4U or larger? Our goal is to obtain at least two of everything, with similar or matching specifications in every possible scenario. The idea is to scale horizontally in order to more easily replace a failed component or server. As the size of the chassis increases, its cost, complexity, and resource consumption also rise. In this delicate balancing act, it's safer to err toward two smaller systems with respectable capabilities, than one giant server that's twice as powerful.

This recipe will provide a quick set of calculations we can perform to estimate how many server chassis we may need for a PostgreSQL cluster.

Since the server chassis and motherboard are generally a package deal, it's a good idea to refer to the Managing motherboards recipe. We will be using a very similar process to choose a server case. This time, we will focus on adequate room for hard drives and redundant power supplies.

Now it's time to do some more research. Follow these steps:

1. Refer to the final list of servers from our motherboard selection.
2. For our ideal count of active (not replacement) hard drives, remove any choice that doesn't have enough drive slots. Use this list if it's not immediately obvious:

• The maximum number of 2.5" drives in a 2U server is 24.
• The maximum number of 3.5" drives in a 2U server is 12.
• The maximum number of 2.5" drives in a 1U server is 8.
• The maximum number of 3.5" drives in a 1U server is 4.

3. Remove from consideration any chassis that does not support dual power supplies. This should happen rarely in server-class systems.
4. As the list dwindles, give higher priority to cases with more fans or lower average operating temperatures.

This time, our job was much easier than considering motherboard constraints, as the drives themselves determine most of our decisions.

Hot-swappable hard drives are slightly larger than their standard brethren due to the swap enclosure. Yet cases exist than can hold up to 24 hot-swappable drives across the front. If we need that many storage devices, we save space by taking advantage of cases that can accommodate them. We also need to remember to reserve two drives for the operating system in a RAID-1, separate from our PostgreSQL storage. We can't diagnose problems on a server that is unable to boot.

If we need more drives than are available in any configuration, we should consider Direct Attached Storage (DAS), Network Attached Storage (NAS), or Storage Area Network (SAN). Some vendors supply drive extension cages specifically to provide more hot-swap bays for specific server models. While we want to conserve space when possible, these are relatively inexpensive and much smaller than a NAS or SAN if we haven't progressed to requiring such a device.

The dual power-supply requirement is not negotiable. Many data centers provide two power rails per server rack. The intent is to deliver two separate sources of power to the server in case the server's power supply fails, or power is cut to one of the sources. Sometimes, these power sources even have separate generators. We're not the only ones interested in redundancy; data centers want to avoid outages too.

The final, more optional element involves investigating the case itself. Many server cases have several fans inside and along the rear, and are very loud as a consequence. This won't matter when the server is in the data center, but the number of fans and the shape of the airflow will directly affect the server temperature. Higher temperatures decrease system stability. It's not uncommon for vendors to list the maximum operating temperatures of each case, so try to gravitate toward the cooler ones if all else is equal.

We use the word vendor frequently, and there's a reason for that. Short of outright accusing bare cases and motherboards of being faulty, they are simply not stable enough for our use. There are some great cases available that, in many ways, exceed the capabilities provided by established server providers.

We don't suggest the smaller vendors for a few reasons. Larger companies often have replacement policies for each server component, including the case and motherboard. Building a system ourselves may provide more satisfaction, but vendors presumably spend time testing for compatibility and failure conditions. They produce manuals hundreds of pages long detailing the viable parts, configurations, and failure conditions of the entire unit.

However, we could just as easily argue that redundant servers increase failure tolerance, as there's always an available backup. Bare cases and motherboards are usually cheaper, and user-serviceable besides. That is a completely valid path, and if your risk assessment suggests it's viable, try it out. Our particular recommendations and biases should not limit your choices.

Those familiar with the industry may have encountered NAS as well. How exactly is that different, and how is it relevant to us?

It's subtle but important. While both introduce networked storage, only a SAN grants direct block-level access, as if the allocation were raw, unformatted disk space. NAS systems operate one level higher, providing a fully formatted filesystem such as NFS or CIFS. This means our PostgreSQL database does not have direct control over the filesystem; locks, flushes, allocation, and read cache management are all controlled by a remote server.

When building a highly available server, raw I/O and synchronization messages are very important, and NFS is more for sharing storage than extending the storage capabilities of a server. What must we consider when deciding upon utilizing a SAN, and when should we do this instead of adopting an inexpensive option such as DAS?

We won't be discussing how to evaluate a SAN, which vendors produce the best hardware, or even basic configuration strategies. There are scores of books dedicated to SAN management and evaluation that are far beyond the scope of our overview.

This recipe will only consider the when and why for incorporating SAN storage, not the how.

Because we're going to cover both SAN performance and storage allocation, we recommend referring to the Having enough IOPS recipe and the Sizing storage recipe. Just like physical disks, we need to know how much space we need, and roughly how fast it should be to fulfill our transaction and query requirements.

Do we need a SAN? We can ask ourselves a few questions:

• Do our IOPS or storage requirements demand more than 20 hard drives?
• Will the size of our database reach or exceed 3 TB within the next 3 years?
• Would the risk to the company be too high if we ever ran out of space?
• Is there already a SAN available for testing?

If we answer yes to any of these, a SAN might be in our best interests. In that case, we can determine whether it would fulfill our needs.

Follow these steps if possible:

1. Request a Logical Unit Number (LUN) from the infrastructure department with the necessary IOPS and storage requirements.
2. If a SAN isn't available, many SAN vendors will provide testing equipment to encourage purchases. Try to obtain one of these.
3. Have the infrastructure department format the allocation and attach it to a testing server. Keep note of the path to the storage.

4. Create a basic PostgreSQL testing database with the following command-line operations as the postgres user:

createdb pgbench 
pgbench -i -s 4000 pgbench 

5. Drop the system caches as a user capable of performing root-level commands, as follows:

echo 3 | sudo tee /proc/sys/vm/drop_caches 

6. Test the storage read IOPS with one final command as the postgres user:

pgbench -S -c 24 -T 600 -j 4 pgbench

The first part of our process is to decide whether or not we actually need a SAN at all. If the database will remain relatively small, capable of residing easily on local hard drives for several years, we don't need a SAN just yet.

While it might seem arbitrary, setting 3 TB as a cutoff for local storage comes with a few justifications. First, consider the local drives. Even if they were capable of saturating a 6 Gb/s disk controller, 3 TB would require over an hour to transfer to another local storage device. If that weren't a bottleneck, there is still the network. With a 10 Gb/s NIC and assuming no overhead, that's 40 minutes of transfer at full speed.

That directly affects the speed of backups, synchronization, emergency data restoration, and any number of other critical operations. Some RAID cards also require special configurations when handling over 4 TB of storage, to which 3 TB is uncomfortably close if we ever need an extension. SAN devices can often perform local storage snapshots for nearly instant data copies intended for other servers. There's no transfer overhead if the other server is also attached to the same SAN.

Remember that servers have limited local disk capacity, either due to the chassis itself, or by how many directly attached storage extensions they can accommodate. RAID devices allow online storage expansion, but they can't overcome physical limitations. SAN hardware does not have any of these inherent limitations and is one way to mitigate the risk of exhausting your maximum storage capacity.

If a SAN is ever available for testing, we're still not done. Operating performance is highly dependent on the configuration of the SAN or the storage allocation itself. It's a good idea to test SAN manufacturer claims before committing all of our storage to it.

A very easy way to do this is with a basic pgbench test. The pgbench command is provided by the PostgreSQL software, and can test various aspects of a server. For our uses, we want to focus on the disk storage. We start by creating a new pgbench database with createdb, so the pgbench command has somewhere to store its test data. The -i (init) option to pgbench tells it to initialize new test data, and the -s (scale) option describes the scale of test data we want.

A scale of 4000 creates a database of roughly 60 GB in size. We recommend adjusting this scale to be larger than the amount of available RAM. This will help guarantee that the server cannot cache all of the test data and taint our performance results by inflating the numbers.

After initializing a new test database, we can use a Linux command that instructs the server to drop all available cached data. This means none of our test data is in memory before we begin the benchmark. We don't want to inflate our results, otherwise the SAN looks more capable than it really is.

The test itself comes from pgbench again. The -S (select-only) option ensures we only analyze the performance of read operations. Furthermore, we tell the benchmark to launch 24 clients with the -c (client) parameter, and to run the test for a full 10 minutes with the -T (time) option. While we used 24 clients here, consider any amount up to three times the number of available processor cores. The final -j (jobs) flag merely launches 4 concurrent benchmark threads, preventing the test itself from reducing overall performance due to CPU throttling.

This process should reveal the true capabilities of the SAN, and whether or not our production database will be safe and offer good performance while relying on remote storage.

Notice how we never ask for a specific number of disks when requesting a SAN allocation. Modern SAN equipment operates on an implied service level agreement based on installed components. In effect, if we need 6,000 IOPS and 10 TB of space, the SAN will combine disks, cache, and even SSDs if necessary, to match those numbers as closely as possible.

This not only reduces the amount of risky micromanagement we perform as database administrators (DBAs), but acts as an abstraction layer between storage and server. In this case, it's possible to modify the storage in any number of ways. We can also enhance, adjust, or copy without affecting the database installation itself.

The main problem we encounter when using a SAN rather than several servers configured with local storage is that the SAN itself becomes a single point of failure. This is something to keep in mind as our journey to high availability progresses.

Here is a list of several SAN vendors, from well-known companies to companies with great potential:

• EMC: https://www.emc.com
• NetApp: https://www.netapp.com
• VCE: https://www.vce.com
• Pure Storage: https://www.purestorage.com/

Now it's time to get serious. We have discussed all the components that go into a stable server for several pages, and have strongly suggested obtaining multiple spares for each. Well, that applies to the server itself. Not only does this mean having a spare idle server in case of a catastrophic failure, but it means having an online server as well.

Determining excess server inventory isn't quite that simple, but it's fairly close. This is where the project starts to get expensive, but high availability is never cheap; the company itself might depend on it.

Unlike the process we used in Chapter 1, Architectural Considerations, this recipe will focus on server hardware redundancy rather than cluster design. In many ways, this recipe can help augment architectural considerations.

We want to consider the overall state of the application architecture here. The database server doesn't exist in a vacuum. Work with the systems and application teams to get an idea of the other elements that may depend on our PostgreSQL server.

This won't be a very long list. In any case, follow these steps:

1. For every critical OLTP system, allocate one online replica.
2. For every two non-cached application or web servers, consider one online replica.
3. For every ten cached application or web servers, consider one online replica.
4. For every stage or QA database server analog, allocate one spare server.

OLTP systems produce a very high transactional volume by their very nature. Any disruption to this volume is extremely visible and costly. The primary goal of running a highly available service, such as a database, is to minimize downtime. So for any database instance that is a critical component, there should be a copy of the server configured in such a manner that near-immediate promotion to production status is possible.

Any server that needs direct access to the database, whether it is a queue system, application server, or web frontend, is sensitive to database overload. One method for diffusing this risk is to create one database copy for every two to four directly connected servers. These copies are only usable for reading (rather than writing) data, but a properly designed application can accommodate this limitation. Not only does this reduce contention on the database instance that must handle data writes; but it also eliminates the likelihood of one misbehaving query disrupting the entire constellation of client-visible services.

The risk to the frontend is greatly reduced when a sophisticated cache is involved. Properly designed, a failed read from the database can default to a cached copy until reads can be re-established. This means we can subsist on fewer database replicas. If the application does not provide that kind of cache, our job as database advocates becomes one of working with appropriate technical leads until such a cache is established.

The extra QA resource may seem excessive at first, but it fulfills a critical role. While the testing teams may never touch the spare server, we can use it in their stead. It's not possible to safely configure a production system for online failover without first testing that configuration on two similarly equipped systems. To do otherwise risks failure of the automatic activation of alternate production servers, which is a de facto outage. We should always test all major actions before applying them to the production system. This means we should dry-run all database migrations, upgrades, resynchronization, backup restoration, and so on in the QA environment.

Without a second server, none of this would be possible.

We have mentioned this tip previously, but this deserves special attention. PostgreSQL 9.2 and above now has the capability to stream replicated data from one database standby to another. Even with 10 GbE network cards, there is a limit to the amount of data our production server can or should transmit.

While there is still a limit to the number of replicas we can maintain with this new functionality, we also mitigate overall traffic, and therefore reduce risk. If our database is stuck on a version before 9.2, we may never realize these new benefits.

At the time of writing, PostgreSQL 12 is the latest release and versions lower than 9.4 are no longer supported. A crafty DBA can encourage the company to adopt a forward stance regarding upgrades by providing an upgrade proposal, procedural checklist, and deployment integration tests.

Now that pg_upgrade is a standard part of PostgreSQL, producing a robust upgrade plan and the associated compatibility tests is much easier than in the past. By pushing for upgrades early, we can use new features such as cascading replication, and with PostgreSQL, this can heavily influence our resulting architecture. Consider this when choosing your hardware.

Have we ever implied that mere server inventory was sufficient for high availability? The place where our servers live—the data center—also incorporates several redundancies. Extra network lines, separate power sources, multiple generators, air conditioning and ventilation—everything a server might require—are all part of most data center guarantees.

Yet some have joked that a common backhoe is the natural enemy of the internet. There is more truth to that statement than its apparent lack of gravitas might suggest. Data centers are geographically insecure. Inclement weather, natural disasters, disrupted network backbones, power outages, and of course, accidentally damaged trunk lines (from an errant backhoe?) and simple human error can all remove a data center from the grid. When a data center vanishes from the internet, our servers become collateral damage.

However, we've done everything right! We have duplicates of everything, multiple parts, cables, even whole servers. What can we possibly do about the data center?

It's somewhat complicated, but this recipe will help us in the final element of redundancy we need to always ensure that PostgreSQL remains online.

For this section, we will need a list of every database server in our proposed architecture and the desired role for each. Try to apply the recipes in the Architectural Considerations chapter before continuing.

This won't be a very long list. In any case, follow these steps:

1. For every critical OLTP operating pair, allocate at least one standby.
2. For every two online standby replicas, consider at least one standby.
3. For every other database instance, allocate one standby.

This type of scenario is known as disaster recovery. In order to truly diffuse a data center outage, we need backups of every major database server, and even minor servers. The reasoning is simple: we don't know how long we have to operate at reduced capacity. At this point, even non-critical reporting services still need analogs; otherwise, business decisions that depend on activity analysis may not be possible.

We only really need half the amount of database servers. Most disaster recovery scenarios are severe enough for raised alertness, reduced refresh times, manually extended queue timeouts, and more. Not only is this less expensive than purchasing a copy of every server at the primary data center, but it also encourages closer monitoring until everything is fully restored. Larger companies can opt for complete parity between data centers, but this is not a requirement.

As DBAs, our scenario often resembles this:

Notice that we didn't make any reservations for QA or development database servers. In the case of a disaster, the primary concern is ensuring the continued availability of the application platform. Further development or testing is likely on hold for the duration of the outage in any case.

We cannot stress the importance of this section strongly enough. Some may consider an entire extra data center as optional due to the cost. It is not. Others may think a total of three servers for every primary system is too much maintenance overhead. Again, it is not. The price of a few servers must be weighed against the future of the company itself; it is the cost of admission into the world of high availability.

By the time we begin utilizing failover nodes, or any replicas in a separate data center, the damage has already been done. In the absence of these resources, a database crash can result in hours or even days of unavailability depending on the size of our database. This can exponentially compound the effects of the original problem.

With this in mind, all critical production systems the author designs always have a minimum of four nodes: two mirrored production systems, and two mirrored disaster recovery analogs. This ensures that the disaster recovery system location remains a viable failover target even while one node is offline for maintenance. Outages are unexpected, and we must always be prepared for them.