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?
#!/usr/bin/env bash db_size=`psql dbname username -c "SELECT pg_size_pretty( pg_database_size('dbname') );"` echo $db_size free
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?