ncc/hosts/best/postgresql.nix

{ self, ... }: {
  imports = [(self + /modules/postgresql.nix)];

  # postgres -C shared_memory_size_in_huge_pages -D /var/lib/postgresql/<version>.
  boot.kernel.sysctl."vm.nr_hugepages" = 5265;

  # Generated by <https://pgconfigurator.cybertec.at/>.
  services.postgresql.settings = {
    # Connectivity:
    max_connections                = 100;
    superuser_reserved_connections = 3;

    # Memory Settings:
    shared_buffers           = "10240 MB";
    work_mem                 = "64 MB";
    maintenance_work_mem     = "620 MB";
    huge_pages               = "try";
    effective_cache_size     = "45 GB";
    effective_io_concurrency = 100;
    random_page_cost         = 1.25;

    # Monitoring:
    shared_preload_libraries = "pg_stat_statements";
    track_io_timing          = "on";
    track_functions          = "pl";

    # Replication:
    wal_level          = "replica";
    max_wal_senders    = 10;
    synchronous_commit = "on";

    # Checkpointing:
    checkpoint_timeout           = "15 min";
    checkpoint_completion_target = 0.9;
    max_wal_size                 = "1024 MB";
    min_wal_size                 = "512 MB";

    # WAL writing:
    wal_compression        = "on";
    wal_buffers            = -1;
    wal_writer_delay       = "200ms";
    wal_writer_flush_after = "1MB";
    wal_keep_size          = "3650 MB";


    # Background writer:
    bgwriter_delay          = "200ms";
    bgwriter_lru_maxpages   = 100;
    bgwriter_lru_multiplier = 2.0;
    bgwriter_flush_after    = 0;

    # Parallel queries:
    max_worker_processes             = 20;
    max_parallel_workers_per_gather  = 10;
    max_parallel_maintenance_workers = 10;
    max_parallel_workers             = 20;
    parallel_leader_participation    = "on";

    # Advanced features:
    enable_partitionwise_join      = "on";
    enable_partitionwise_aggregate = "on";
    jit                            = "on";
    max_slot_wal_keep_size         = "1000 MB";
    track_wal_io_timing            = "on";
    maintenance_io_concurrency     = 100;
    wal_recycle                    = "on";
  };
}