diff options
| -rwxr-xr-x | host/Rory-nginx/services/postgres.nix | 65 |
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"; }; }; |