6

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?

  • 2
    if you cannot fill the entire database on memory, the ideal value it's a size of your greatest table at least. You can get an idea which parameters adjust with the pgconfig – Sebastian Webber Aug 15 '16 at 18:03
  • @SebastianWebber: Table + indices? Or just Table? Also thanks for sharing your tool! I couldn't get pgtune to work on the shared server – raphael Aug 15 '16 at 18:12
  • i recommend both if possible, but if not, try the larger one. How big are your tables? – Sebastian Webber Aug 15 '16 at 18:15
  • @SebastianWebber partitioned: 40gigs, 70 w indexes – raphael Aug 15 '16 at 18:21
  • you can expand the server memory to this 40gb? – Sebastian Webber Aug 15 '16 at 18:23
2

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.

  • Unfortunately the current server is memory constrained (8GB, and shared with other users). – raphael Aug 15 '16 at 17:54
  • 1
    Well, at least you know you need more than 8 GB. – mustaccio Aug 15 '16 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.