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/