Updated: 08/10/2022

Joshua D. Drake

In this article we will be discussing the minimum requirements for production usage of PostgreSQL whether on-prem or in the cloud. We will not be discussing proprietary forks such as Amazon RDS or other Open Source forks such as Yugabyte or Greenplum.

PostgreSQL is the Linux of databases. It provides the kernel and key features to the most critical database services available today. These include but are not limited to:

• Greenplum
• Yugabyte
• Amazon RDS for PostgreSQL
• Amazon Aurora for PostgreSQL
• Azure Database for PostgreSQL
• AlloyDB
• Cloud SQL for PostgreSQL
• NeonDB

It also provides key infrastructure to other databases such as Cockroach.

Supported Platforms

PostgreSQL is supported on a number of platforms including various cloud providers. The canonical source for the officially supported list of Postgresql.org platforms is the supported platforms page.

Cloud

The scope of this article will not allow us to discuss specific cloud instance options. Instead we are going to focus on general minimum requirements for running with the cloud. These options will be valid across all major platforms but will not include proprietary forks such as AlloyDB, Aurora, RDS or Hyperscale. That is not to say those platforms aren’t useful or good, just that they are not PostgreSQL.

An advantage to modern cloud platforms is that you can customize your instance to your needs. During your initial provision you may only need 2GB of memory and 2CPUs but suddenly your new product or service takes off… Instead of having to buy all new hardware and perform a migration you can upgrade your instance to a more powerful implementation and reboot. It becomes a very quick outage versus a long, drawn out, planned migration.

The Basics

• 1GHZ Dual Core processor
• 2GB of memory
• 2GB of disk space
• RAID 1
• Linux

Processor & Memory

In a cloud environment, you usually choose an instance configuration instead of a specific processor speed. This allows you to modify some specific memory and processor requirements within a single bundle. It also allows you to select a type of instance that is specific to your workload, for example memory favored or CPU favored.

Google Cloud

When using Google cloud the minimum instance size for GCE (Google Compute Engine) would be the e2-small which provides 2 shared core CPUs and 2GB of memory. These are shared-core instances and do provide specific limitations to be aware of, including a maximum disk space of 3TB per persistent disk. If you are looking for a similar configuration but with a non-shared core, we would recommend the e2-standard-2 which provides more reliable performance with 2vCPUs and 8GB of memory.

AWS

Using AWS for PostgreSQL can be a bit more challenging due to the AWS pricing model. If we adhere to the minimum requirements then the AWS EC2 instance m4.large might be what a good choice. It has 2vCPUs and 8GB of memory. However, it is an “EBS volume only” instance and it limits your EBS bandwidth to 450Mbps. Since it is limited to EBS volumes, it would not be difficult to reach that limitation depending on the configuration of your EBS volumes and the velocity of your database traffic.

Disk Space

In the cloud this is not as straightforward. There is a production advantage to being able to dynamically add storage to an instance. It increases uptime and the resilience of your data. However, the cloud can make disk usage overly complicated. When using lower tier instances it is common to only allow the use of network based volumes. It is also common to restrict the performance of those volumes based not only on instance size but also size of disk.

AWS

When provisioning for AWS your minimum requirements are limited by the use of only EBS volumes and having limited EBS volume bandwidth. Using the aforementioned instance type of m4.large, we can provision the appropriate size of a minimum of 2GB of disk but we will have to provision 2 EBS volumes so we achieve the minimum requirement of RAID 1. You are then limited to 450Mbps in total for the two volumes. Put another way, due to the limitations of the instance, the provisioned database would be the equivalent of having an On-Prem server with (2) SAS drives in a RAID 1. One can find further information on AWS instance options and limitations here.

Google Cloud

Provisioning for Google Cloud can be simpler as they allow you to modify the size and type of your boot disk. Though this boot disk is not redundant and does not meet the requirements of RAID 1, it can meet your performance requirements. The minimum size is 10GB. GCE also provisions the speed of a disk by type and size of disk. You can find more on this topic here.

RAID 1

The cloud provides a false sense of security when it comes to the resilience of your data. There is a plug and go mentality. Unfortunately, like any configuration there is a risk of failure. If you allow your data to reside on a single EBS (or local) volume. You are at similar risk as if you allow your data to reside on a single local disk using On-Prem. Yes, you can (and you should) have backups, snapshots, point in time recovery etc… However, nothing will beat the ability to lose a volume and continue operating while you provision a new volume and add it to an existing operating array without causing an outage.

Linux

Equal to On-Prem, one should use Linux for PostgreSQL. This is not a slight against any other operating system (looking at you FreeBSD). PostgreSQL is tested most widely on Linux and the ecosystem is centered around Linux. Further PostgreSQL takes a specifically Linux/Unix view of its configuration and administration which makes it feel foreign to Windows users (though Microsoft has proven that PostgreSQL runs well on Windows). We further recommend that you stick with widely and externally supported Linux distributions such as Debian, RHEL, SLES and Ubuntu LTS. One should try to avoid vendor centric distributions such as Amazon Linux.

On-Prem

Though a solid percentage of PostgreSQL users are migrating to the cloud, whether it be via instances such as EC2 or managed services such as RDS for PostgreSQL, they are still dwarfed in regards to the overall population of on-prem installations. If you have the staffing and expertise, on-prem can offer a lot of options that the cloud will not or cannot do in a cost effective manner. There is also an argument that by moving to the cloud you are increasing external risk.

The basics:

• 1GHZ Dual Core processor
• 2GB of memory
• 2GB of disk space
• RAID 1
• Linux

Processor

PostgreSQL is process based and this means it can do literally only one thing per process, per core, at a time. The use of technology such as context-switching helps but the inherent physical limitation still exists. Though there are applications that will work fine in that scenario, your scalability goes up significantly in a multi-core scenario. Even if your application only uses a single processor, there are other processes running in the background of PostgreSQL (wal writer, bgwriter, stats collector, autovacuum, etc.) that you want to make sure you have resources for outside of the application itself. One should also avoid anything that isn’t 64bit.

Memory

The 2GB of memory is a recommendation for memory you can allocate to PostgreSQL outside of the operating system. If you have a small data set, you are still going to want enough memory to cache the majority of your hot data (you can use pg_buffercache to determine your hot data). With 2GB of memory you could allocate 512MB of shared buffers for hot cache and leave 1.5GB for maintenance daemons, parallelism, and work_mem.

Disk space

There are some articles that suggest that you can provision with 512MB of disk space. Unfortunately, those articles are not taking into account the default configuration of current PostgreSQL installations. Consider that max_wal_size defaults to 1GB, plus you have parallelism of capabilities that use work_mem (which can spill to disk), maintenance_work_mem (which can spill to disk), and you still need at least some space for your data and indexes.

Raid 1

When speaking of disks for on-prem and in almost all circumstances for production, you need at least RAID 1. RAID 1 will not provide any write performance increase but depending on the driver/controller will provide read performance increases. Further, it provides redundancy just in case that backup is out of data, or you have specific uptime requirements that will prevent you from shutting the database down to restore a backup. Remember: though NVME Data Center drives are ridiculously fast (and reliable), if one fails – you can be in a world of hurt if you don’t have proper redundancy.

Linux

While it is true that PostgreSQL runs on a vast array of operating systems, PostgreSQL is tested most widely on Linux based operating systems. This is not to take away from the other operating systems out there (@FreeBSD: looking at you!). The use of Linux is going to provide you with the widest capability of expertise and knowledge bases for properly running PostgreSQL. We would also recommend that the installation of Linux you use is an LTS release such as Ubuntu LTS, Redhat RHEL, SLES, etc.

While not a comprehensive manual in provisioning PostgreSQL, it is the hope that this article provides someone who wants to work with PostgreSQL a starting point that is performant, reliant and sheds some light on the difficulties in just “using PostgreSQL”. In future articles we will discuss other topics such as Replication, Snapshots, how to provision in a high performance manner and connection pooling.