From 90bd81afd1e51d04bd37991af3ea6034ad07c25c Mon Sep 17 00:00:00 2001
From: Paul Norman <penorman@mac.com>
Date: Tue, 12 Sep 2023 02:34:27 -0700
Subject: [PATCH] Set reasonable PostgreSQL GUCs for tile servers

These are based off of the osm2pgsql.org recommendations, scaled
for the larger servers we run, as well as experience running
PostgreSQL in a large variety of environments.
---
 roles/balerion.rb  | 2 --
 roles/bowser.rb    | 2 --
 roles/culebre.rb   | 2 --
 roles/nidhogg.rb   | 2 --
 roles/odin.rb      | 2 --
 roles/palulukon.rb | 2 --
 roles/piasa.rb     | 2 --
 roles/pyrene.rb    | 2 --
 roles/tile.rb      | 6 ++++--
 roles/ysera.rb     | 2 --
 10 files changed, 4 insertions(+), 20 deletions(-)

diff --git a/roles/balerion.rb b/roles/balerion.rb
index ee5ca2be4..cfbc82ec6 100644
--- a/roles/balerion.rb
+++ b/roles/balerion.rb
@@ -21,8 +21,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/bowser.rb b/roles/bowser.rb
index a12c9887e..26e8d2d4f 100644
--- a/roles/bowser.rb
+++ b/roles/bowser.rb
@@ -21,8 +21,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/culebre.rb b/roles/culebre.rb
index c956e456c..9058b4445 100644
--- a/roles/culebre.rb
+++ b/roles/culebre.rb
@@ -32,8 +32,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/nidhogg.rb b/roles/nidhogg.rb
index 19535d2de..8b6003c8e 100644
--- a/roles/nidhogg.rb
+++ b/roles/nidhogg.rb
@@ -29,8 +29,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/odin.rb b/roles/odin.rb
index 14b1f768e..79514889c 100644
--- a/roles/odin.rb
+++ b/roles/odin.rb
@@ -32,8 +32,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/palulukon.rb b/roles/palulukon.rb
index 0a1adf68f..fb0612be7 100644
--- a/roles/palulukon.rb
+++ b/roles/palulukon.rb
@@ -22,8 +22,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/piasa.rb b/roles/piasa.rb
index 35f28a206..4b0bd0b28 100644
--- a/roles/piasa.rb
+++ b/roles/piasa.rb
@@ -22,8 +22,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/pyrene.rb b/roles/pyrene.rb
index 1b211f033..9d943e243 100644
--- a/roles/pyrene.rb
+++ b/roles/pyrene.rb
@@ -37,8 +37,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
diff --git a/roles/tile.rb b/roles/tile.rb
index 6bb3dc902..88a971145 100644
--- a/roles/tile.rb
+++ b/roles/tile.rb
@@ -34,14 +34,16 @@ default_attributes(
     :settings => {
       :defaults => {
         :max_connections => "250",
-        :temp_buffers => "32MB",
+        :shared_buffers => "16GB",
         :work_mem => "128MB",
+        :maintenance_work_mem => "8GB",
         :max_parallel_workers_per_gather => "0",
         :wal_level => "minimal",
         :wal_buffers => "1024kB",
         :wal_writer_delay => "500ms",
+        :checkpoint_timeout => "60min"
         :commit_delay => "10000",
-        :max_wal_size => "2880MB",
+        :max_wal_size => "10GB",
         :max_wal_senders => "0",
         :jit => "off",
         :track_activity_query_size => "16384",
diff --git a/roles/ysera.rb b/roles/ysera.rb
index f00192ac0..980b5da42 100644
--- a/roles/ysera.rb
+++ b/roles/ysera.rb
@@ -23,8 +23,6 @@ default_attributes(
   :postgresql => {
     :settings => {
       :defaults => {
-        :shared_buffers => "8GB",
-        :maintenance_work_mem => "7144MB",
         :effective_cache_size => "16GB"
       }
     }
-- 
2.39.5