Manually Configure PostgreSQL

Truxton will work with PostgreSQL version 9.6 through 17. 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 scram-sha-256
host all all ::0/0 scram-sha-256

Finding pg_hba.conf

The way to find which configuration file is being used is to go to the services control panel. Select the Postgres service, right button and select Properties from the context menu. There will be a text box labelled "Path to executable", scroll all the way to the right and notice the folder parameter after the -D argument. This is the folder where pg_hba.conf is stored.

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 = '6258kB';
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';

Here's a laptop with 8 CPUs (16 virtual), 64GB RAM.

ALTER SYSTEM SET max_connections = '250';
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '16GB';
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 = '100';
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET work_mem = '3495kB';
ALTER SYSTEM SET min_wal_size = '100MB';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET max_worker_processes = '8';
ALTER SYSTEM SET max_parallel_workers_per_gather = '4';
ALTER SYSTEM SET max_parallel_workers = '8';
ALTER SYSTEM SET max_parallel_maintenance_workers = '4';