https://wiki.debian.org/PostgreSql
https://wiki.wut.ee/int/siil_arti_ee/db
C or use --no-localeUTF8Using en_US.UTF-8 or some other real locale instead of C will change
string sorting order, do some other "fun things" and might break your indexes when next glibc update hits.
Under Debian/Ubuntu you should use pg_createcluster command that looks something like this
pg_createcluster 11 main -- --no-locale --encoding=UTF8
Normal initdb command should look something like this
initdb -D /var/lib/postgres/data --no-locale --encoding=UTF8
Authentication options are configured in pg_hba.conf
pg_createcluster will set default unix domain socket (local) authentication type to peer and tcp/ip host auth to md5 password
On other systems you can set those authentication parameters with following initdb arguments. This will also ask you for a password to give to the DB super-user.
--auth-host=md5 --auth-local=peer --pwprompt
For better security you can swap md5 with scram-sha-256. https://info.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram
Add this line to pg_hba.conf and reload postgres
# TYPE DATABASE USER ADDRESS METHOD
host all all samenet md5
atime = off # Recoding access time on file open is stupid
relatime = on # writing access time on file write is fine because we are writing anyway.
compression = lz4 # Its faster to compress/decompress on the CPU than it is to wait for the data from HDD/SSD
recordsize = 128K # postgres native 8k will give give horrible compression ratio, default 128k is fine
primarycache = metadata # maybe?
Each postgres database cluster gets its own dataset
https://pgtune.leopard.in.ua/#/
And also
full_page_writes = false
This disables double writes for data because ZFS is always consistent, ~2x insert perf boost
Set zfs commit time to 1 second
echo 1 > /sys/module/zfs/parameters/zfs_txg_timeout
echo 'options zfs zfs_txg_timeout=1' >> /etc/modprobe.d/zfs.conf
And disable postgres synchronous_commit
synchronous_commit = off
This should give ~20x perf boost but in case of system chrash you will lose up to 1 second of data
Data will still always be consistent thanks to ZFS