Set per-table autovacuum thresholds low

Fixes #20

diff --git a/src/config.rs b/src/config.rs
index 92d33c1c91e88276d4c93d6d5aef8cad4c36fd08..97417532b4a5e0d5297435b7f16e8205d692750b 100644 (file)
--- a/src/config.rs
+++ b/src/config.rs
@@ -200,6 +200,13 @@ pub(crate) async fn upgrade_db(schema: i32, client: &mut tokio_postgres::Client)
        if schema <= 1 || schema > SCHEMA_VERSION {
                panic!("Unknown schema in db: {}, we support up to {}", schema, SCHEMA_VERSION);
        }
+       // PostgreSQL (at least v13, but likely later versions as well) handles insert-only tables
+       // *very* poorly. After some number of inserts, it refuses to rely on indexes, assuming them to
+       // be possibly-stale, until a VACUUM happens. Thus, we set the vacuum factor really low here,
+       // pushing PostgreSQL to vacuum often.
+       // See https://www.cybertec-postgresql.com/en/postgresql-autovacuum-insert-only-tables/
+       let _ = client.execute("ALTER TABLE channel_updates SET ( autovacuum_vacuum_insert_scale_factor = 0.005 );", &[]).await;
+       let _ = client.execute("ALTER TABLE channel_announcements SET ( autovacuum_vacuum_insert_scale_factor = 0.005 );", &[]).await;
 }
 
 /// EDIT ME