Manually Configure PostgreSQL

Truxton will work with PostgreSQL version 9.6 through 12. If you install PostgreSQL on your own, the following changes will allow connection from anywhere.

pg_hba.conf

Edit the pg_hba.conf file to tell it to allow connections from anywhere.

host all all 0.0.0.0/0 md5
host all all ::0/0 md5

Tunable Parameters

We recommend PGTune for getting tuning parameters.

Sample Parameters

The following are the settings used for a server with 36 CPUs (72 virtual), 191GB RAM and data drive is an SSD.

ALTER SYSTEM SET max_connections = '1000';
ALTER SYSTEM SET shared_buffers = '48896MB';
ALTER SYSTEM SET effective_cache_size = '146688MB';
ALTER SYSTEM SET maintenance_work_mem = '2047MB';
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET default_statistics_target = '500';
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET work_mem = '1390kB';
ALTER SYSTEM SET min_wal_size = '4GB';
ALTER SYSTEM SET max_wal_size = '16GB';
ALTER SYSTEM SET max_worker_processes = '36';
ALTER SYSTEM SET max_parallel_workers_per_gather = '18';
ALTER SYSTEM SET max_parallel_workers = '36';
ALTER SYSTEM SET max_parallel_maintenance_workers = '4';