The Complete Computer

My go-to multi-write database server is postgres. I like how it has JSON columns and indexes in a (somewhat) ergonomic form. This is a pretty basic setup.

nix source: :tangle ~/arroyo-nix/nixos/postgresql.nix
{ pkgs, ... }:

{
  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_14;
    settings = {
      wal_level = "replica";
      archive_mode = "on";
      archive_command = "test ! -f /srv/pgsql/archive/wal/%f && cp %p /srv/pgsql/archive/wal/%f";

      shared_preload_libraries = "pg_stat_statements";
      # log_min_duration_statement = 10000;
      # statement_timeout = 60*1000;

      shared_buffers = "4GB";
      effective_cache_size = "12GB";
      maintenance_work_mem = "1GB";
      work_mem = "4GB";
      default_statistics_target = 250;

      max_worker_processes = "40";
      max_parallel_workers_per_gather = "10";
      max_parallel_workers = "20";

      autovacuum = true;
      track_counts = true;
    };
  };
  # you fuckers silently broke my updates with a one-liner in the release notes!!!
  systemd.services.postgresql.serviceConfig.ReadWritePaths = [
    "/srv/pgsql/archive/"
  ];
  
  # backup weekly
  services.postgresqlBackup = {
    enable = true;
    backupAll = true;
    location = "/srv/pgsql/backups/";
    startAt = "Mon *-*-* 02:00:00";
  };
}