pyrox.dev
1{ pkgs, config, ... }:
2let
3 cfg = config.services.postgresql;
4in
5{
6 services.postgresql = {
7 enable = true;
8 package = pkgs.postgresql_16;
9 enableJIT = true;
10 # Settings taken from [PGTune](https://pgtune.leopard.in.ua/)
11 settings = {
12 max_connections = "300";
13 shared_buffers = "2GB";
14 effective_cache_size = "6GB";
15 maintenance_work_mem = "512MB";
16 checkpoint_completion_target = 0.9;
17 wal_buffers = "16MB";
18 default_statistics_target = 100;
19 random_page_cost = 4;
20 effective_io_concurrency = 2;
21 work_mem = "2621kB";
22 huge_pages = "off";
23 min_wal_size = "1GB";
24 max_wal_size = "4GB";
25 max_worker_processes = 8;
26 max_parallel_workers_per_gather = 4;
27 max_parallel_workers = 8;
28 max_parallel_maintenance_workers = 4;
29 };
30 };
31 systemd.timers.pg-autovacuum = {
32 description = "Timer for Postgres Autovacuum";
33 timerConfig = {
34 OnCalendar = "*-*-* 01:00:00";
35 Unit = "pg-autovacuum.service";
36 };
37 };
38 systemd.services.pg-autovacuum = {
39 description = "Vacuum all Postgres databases.";
40 requisite = [ "postgresql.service" ];
41 wantedBy = [ "multi-user.target" ];
42 serviceConfig = {
43 Type = "oneshot";
44 User = "postgres";
45 Group = "postgres";
46 SyslogIdentifier = "pg-autovacuum";
47 ExecStart = "${cfg.package}/bin/vacuumdb --all --echo --jobs=6 --parallel=5 --analyze --verbose";
48 };
49 };
50}