Use pgconfigurator

1 files changed, 50 insertions, 15 deletions

diff --git a/host/Rory-nginx/services/postgres.nix b/host/Rory-nginx/services/postgres.nix
index 8912542..96d6785 100755
--- a/host/Rory-nginx/services/postgres.nix
+++ b/host/Rory-nginx/services/postgres.nix
@@ -23,24 +23,59 @@
     # '';
     dataDir = "/mnt/postgres/data";
     settings = {
-      "max_connections" = "100";
-      "shared_buffers" = "512MB";
-      "effective_cache_size" = "8192MB";
-      "max_wal_size" = "1GB";
-      "min_wal_size" = "80MB";
-      "work_mem" = "16MB";
-      "maintenance_work_mem" = "256MB";
-      "checkpoint_segments" = "64";
-      "default_statistics_target" = "250";
+      # https://pgconfigurator.cybertec.at/
+      max_connections = 100;
+      superuser_reserved_connections = 3;
+
+      shared_buffers = "8192MB";
+      work_mem = "64MB";
+      maintenance_work_mem = "420MB";
+      huge_pages = "try";
+      effective_cache_size = "22GB";
+      effective_io_concurrency = "1";
+      random_page_cost = 4;
 
       # can use this to view stats: SELECT query, total_time, calls, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
-      "shared_preload_libraries" = "pg_stat_statements";
-      "pg_stat_statements.max" = "10000";
-      "pg_stat_statements.track" = "all";
+      shared_preload_libraries = "pg_stat_statements";
+      track_io_timing = "on";
+      track_functions = "pl";
+      "pg_stat_statements.max" = "10000"; # additional
+      "pg_stat_statements.track" = "all"; # additional
+
+      wal_level = "replica";
+      max_wal_senders = 0;
+      synchronous_commit = "on";
+
+      checkpoint_timeout = "15min";
+      checkpoint_completion_target = "0.9";
+      max_wal_size = "1GB";
+      min_wal_size = "512MB";
+
+      wal_compression = "on";
+      wal_buffers = "-1";
+      wal_writer_delay = "200ms";
+      wal_writer_flush_after = "1MB";
+      checkpoint_segments = "64"; # additional
+      default_statistics_target = "250"; # additional
+
+      bgwriter_delay = "200ms";
+      bgwriter_lru_maxpages = "100";
+      bgwriter_lru_multiplier = "2.0";
+      bgwriter_flush_after = "0";
+
+      max_worker_processes = "14";
+      max_parallel_workers_per_gather = "7";
+      max_parallel_maintenance_workers = "7";
+      max_parallel_workers = "14";
+      parallel_leader_participation = "on";
 
-      #we're on virtualised HDDs... 
-      "random_page_cost" = "2.0";
-      "synchronous_commit" = "off";
+      enable_partitionwise_join = "on";
+      enable_partitionwise_aggregate = "on";
+      jit = "on";
+      max_slot_wal_keep_size = "1GB";
+      track_wal_io_timing = "on";
+      maintenance_io_concurrency = "1";
+      wal_recycle = "on";
 
     };
   };