I am trying to answer the question, "How big is my PostgreSQL DB relative to memory on my Ubuntu server?" The goal of asking this question is to be able to alert me to when the size of my database is becoming large relative to the system's memory. An effective script would run daily and only alert me when db_size / memory >= 0.5. For example: You should to size up your Digital Ocean Droplet soon for your PostgreSQL server: db_size = 0.6 GB; system_memory = 1.0 GB;

The problem is I am not sure exactly how to accurately make this comparison. My first stab at this was to use psql pg_size_pretty and free. However, after doing some digging into PostgreSQL tuning I have gathered that giving PostgreSQL more memory to use is not as straightforward as just increasing the amount of RAM. Several variables likely need to be set to use the additional memory including work_mem, effective_cache_size, and shared_buffers.

In any case, is there a good comparison that I can make between db_size and total memory size (or another field) to alert me when I need to resize my droplet? Or are there any tools already in place that do this type of thing?

Bash Script

#!/usr/bin/env bash
db_size=`psql dbname username -c "SELECT pg_size_pretty( pg_database_size('dbname') );"`
echo $db_size



root@ubuntu-postgres:~/GitHub/scripts/cronjobs# bash postgres_db_size_check.sh 
pg_size_pretty ---------------- 6976 kB (1 row)
              total        used        free      shared  buff/cache   available
Mem:        1016000       48648      142404       54492      824948      699852
Swap:             0           0           0

In the output above the size of the PostgreSQL DB is only 6.976 MB and the server's available memory is 1,016.00 MB. So right now we are at 0.6% of RAM used (6.976 / 1,016.00). But is this even the correct comparison to make?

Your Answer

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

Browse other questions tagged or ask your own question.