Postgresql – How much RAM should I get for a cloud-hosted PostgreSQL data warehouse

data-warehousememorypostgresql

I'm looking at migrating a current PostgreSQL data warehouse to a cloud host with SSD storage and RAM as one of the main sizing variables. The bulkiest data we're dealing with at the moment will live on monthly partitioned tables. Each month is about 70GB with indexes (40-ish w/o). Data is likely mostly bulk-loaded periodically, and will then be accessed by a small team of 5 researchers.

I've been trying to search for recommendations for spec'ing RAM on this site and all I've found is:

  • Fit the entire DB (>1TB, unrealistic)
  • More is better

Should there be enough RAM to at least load an entire index (16GB) to RAM? Are there any other details I should provide?

Best Answer

Estimating database server memory size by the size of tables (or indexes) is going to be misleading. On the one hand, depending on the database workload, it may not ever need to load the entire table (or index) in memory. On the other hand, there are many other possible memory consumers that have little or no correlation to the data size, such as sort buffers or client connections.

If your current server is not memory-bound, then you can use its configuration as a guideline. Otherwise you might as well buy yourself as much memory as you can afford.