УКР Choose a server

Accounting, databases and remote work

Server for PostgreSQL and MS SQL Server: disks, memory, settings

Updated 11 min read Читати українською

Contents

This article is for administrators setting up PostgreSQL or MS SQL Server on a dedicated server for an accounting system, a website or an internal application. You will choose disks, memory and a processor for the database and apply initial settings that can be checked with commands.

What you need#

  • A dedicated server with Debian or Ubuntu (PostgreSQL) or Windows Server (MS SQL Server) and administrator rights.
  • An installed database engine. As of October 2026 the current major versions are PostgreSQL 18 (19 is still in beta) and MS SQL Server 2025. Licensing is not covered here.
  • A fresh backup and a maintenance window: some parameters take effect only after a database restart.

Hardware: disks, memory, processor#

  • Disks: mirrored NVMe. The database writes its transaction log synchronously, so disk latency directly determines how fast documents are posted. Two disks go into RAID 1, four or more into RAID 10; RAID 0 is unsuitable for a database. A mirror does not replace backups. Check the array using the article on RAID and disk health.
  • Memory: enough for the active part of the database to fit in the cache. ECC memory is preferable: it corrects single-bit errors that could otherwise silently corrupt data.
  • Processor: core speed or core count. An ordinary accounting query runs on one core, so for a few dozen users core speed matters most. Many cores are needed for hundreds of concurrent queries and heavy analytics.

Lines for a database: Turbo — maximum single-core speed for accounting databases, but no private network (keep the database and the application on one server or connect them over a VPN); Standard — high-frequency processors or server-grade AMD EPYC, with a private network on most models; Business — AMD EPYC and Intel Xeon 6, DDR5, NVMe; Power — dozens of cores and terabytes of memory for large databases and analytics. The disks, RAID type and memory of each model are listed on the server card in the catalogue.

PostgreSQL 18: first settings#

On Debian and Ubuntu the configuration is in /etc/postgresql/18/main/: postgresql.conf, pg_hba.conf and the conf.d directory, included at the end of the main file. Keep your own values in a separate file so they are easy to find and remove. pg_lsclusters shows the version, cluster and port; the parameters are described in the official documentation.

  1. Create the file /etc/postgresql/18/main/conf.d/90-tuning.conf. Example for a server with 64 GB of memory and NVMe disks running only PostgreSQL: 
    shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
checkpoint_timeout = 15min
max_wal_size = 16GB
random_page_cost = 1.1
effective_io_concurrency = 32
max_connections = 200
  2. Check the file before restarting — PostgreSQL will not start with a configuration error: 
    sudo -u postgres psql -c "SELECT f.sourcefile, f.sourceline, f.name, f.error, s.context FROM pg_file_settings f LEFT JOIN pg_settings s ON s.name = f.name WHERE f.error IS NOT NULL;"
    For shared_buffers and max_connections (context is postmaster) the message setting could not be applied is expected: they are waiting for a restart. But an invalid value is flagged the same way, so reread those two lines. Any other row is an error to fix.
  3. Restart the cluster in a maintenance window (client connections will drop): 
    sudo systemctl restart postgresql@18-main
    If the service fails to start, sudo journalctl -u postgresql@18-main -n 50 shows why. To roll back, remove your file from conf.d and start the service again.

Choosing the values (defaults in brackets):

  • shared_buffers (128MB) — the documentation suggests starting at 25% of memory on a dedicated database server; more than 40% is unlikely to help.
  • effective_cache_size (4GB) — allocates no memory, only tells the planner the cache size; in practice 50–75% of memory.
  • work_mem (4MB) — a limit per sort or hash operation, not per connection: a complex query takes it several times, in every session.
  • maintenance_work_mem (64MB) — for VACUUM and index creation; each autovacuum process may take as much.
  • checkpoint_timeout, max_wal_size (5min, 1GB) — less frequent checkpoints mean fewer full page images in the WAL, but longer crash recovery and more space for the WAL.
  • random_page_cost (4.0) — the documentation advises lowering it when data is mostly cached, but gives no figure for SSDs; 1.1 is usual on NVMe.
  • effective_io_concurrency (16) — higher values help on disks with high IOPS, excessive ones add latency; raise it gradually.
  • max_connections (100) — each connection is a separate process; instead of thousands “just in case”, add a connection pooler such as PgBouncer.

Asynchronous I/O in version 18#

PostgreSQL 18 introduced asynchronous I/O for reads. It is controlled by io_method: worker (the default; background processes do the reading, and io_workers sets their number, 3 by default), io_uring (Linux only, in builds with liburing support) or sync; a change requires a restart. Start with worker and switch to io_uring only after comparing the two under a test load.

Warning. Do not turn off fsync or full_page_writes: writes get faster, but after a power failure or system crash the database may be corrupted beyond repair. Do not turn off autovacuum either: without it tables bloat and planner statistics go stale.

If a large table bloats, lower the autovacuum threshold for that table alone: ALTER TABLE documents SET (autovacuum_vacuum_scale_factor = 0.02); (0.2 by default).

MS SQL Server: first settings#

The setup wizard suggests some of these values — check what it wrote. The recommendations come from Microsoft Learn.

  1. Memory. The default max server memory means “no limit”: SQL Server gradually takes all the memory and starves the operating system, the application server and RDP sessions. Microsoft recommends about 75% of the memory not used by other processes.
  2. MAXDOP and the parallelism threshold. One NUMA node: MAXDOP no higher than the number of logical processors and at most 8. Several nodes: no higher than the number of processors per node, and with more than 16 per node, half that number but at most 16. Microsoft calls the default cost threshold for parallelism of 5 a starting point, not a recommendation: raise it in small steps and watch a full business cycle. Example for a server with 64 GB of memory and 16 logical processors running only the database engine (30 is an initial value, not a norm); the parameters apply without a restart: 
    EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'max server memory', 49152;
EXECUTE sp_configure 'max degree of parallelism', 8;
EXECUTE sp_configure 'cost threshold for parallelism', 30;
RECONFIGURE;
  3. tempdb. As many data files as logical processors, but at most eight; all with the same size and growth increment. Set the size up front for the usual workload.
  4. Instant file initialization. Grant the SQL Server service account the Perform volume maintenance tasks right (a checkbox in the setup wizard, or secpol.msc) and restart the service: data files then grow without zero-filling. For the log this applies only to autogrowth of up to 64 MB (SQL Server 2022 and later).
  5. File placement. With several volumes, put data, logs and tempdb on different ones; on a single NVMe mirror separate folders are enough. For such volumes Microsoft guidance still specifies NTFS with a 64 KB cluster. The cluster size is set only during formatting, which destroys data, so do it only on a new, empty volume. To check: fsutil fsinfo ntfsinfo D:, the Bytes Per Cluster line should read 65536.
  6. Power plan. The Balanced plan may lower the processor frequency; on a database server switch to High performance (to revert: powercfg /setactive SCHEME_BALANCED): 
    powercfg /getactivescheme
powercfg /setactive SCHEME_MIN
  7. Recovery model. Simple: the log is truncated automatically, but you can restore only to the last backup. Full: restore to any point in time, but only with regular log backups — without them the log grows until it fills the disk.

If the database is for BAS#

In the client-server variant BAS does not work with every version and build of a database engine. Take the supported versions of PostgreSQL and MS SQL Server, the build requirements and the recommended parameters from the platform developer’s documentation, and do not upgrade the engine to a version not listed there.

Do not expose the database port to the internet#

Ports 5432 (PostgreSQL) and 1433 (MS SQL Server) are constantly scanned on the internet. By default PostgreSQL listens only on localhost; if the application runs on another server, add the private network address and allow logins only from its subnet (example addresses):

# postgresql.conf
listen_addresses = 'localhost,10.10.0.2'

# pg_hba.conf
host    all    all    10.10.0.0/24    scram-sha-256

Changing listen_addresses requires a restart; for pg_hba.conf, sudo systemctl reload postgresql@18-main is enough. For MS SQL Server, create the Windows firewall rule for port 1433 only for the private network or VPN subnet. See the articles on the private network and on WireGuard.

How to check the result#

PostgreSQL: the pending_restart column should read f; SHOW shared_buffers; shows a single value.

sudo -u postgres psql -c "SELECT name, setting, unit, source, pending_restart FROM pg_settings WHERE name IN ('shared_buffers', 'work_mem', 'io_method', 'fsync', 'full_page_writes');"

Run the pgbench test only in a separate database (the -i option drops and recreates the pgbench_* tables) and outside working hours. Compare tps and latency average of identical runs before and after the changes.

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 100 bench
sudo -u postgres pgbench -c 16 -j 4 -T 60 -P 10 bench
sudo -u postgres dropdb bench

MS SQL Server — parameters, instant file initialization, recovery models, tempdb files:

SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'max degree of parallelism', 'cost threshold for parallelism');
SELECT servicename, instant_file_initialization_enabled FROM sys.dm_server_services;
SELECT name, recovery_model_desc FROM sys.databases;
SELECT name, type_desc, size * 8 / 1024 AS size_mb FROM tempdb.sys.database_files;

Warning. Point the disk tests fio and diskspd only at a file on free space. Never give a device as the target (/dev/nvme0n1, /dev/md0, a physical disk number): a write test will destroy the data on it. Run the tests outside working hours and delete the test file afterwards.

The first command is for Linux (the fio package, 4 GB of free space needed), the second for Windows (Microsoft’s DiskSpd utility; the folder D:\disktest must exist):

fio --name=dbtest --filename=/var/tmp/fio-test.bin --size=4G --rw=randrw --rwmixread=70 --bs=8k --direct=1 --ioengine=libaio --iodepth=16 --numjobs=4 --runtime=60 --time_based --group_reporting
diskspd.exe -c4G -d60 -r -w30 -b8K -t4 -o16 -Sh -L D:\disktest\test.dat

Common mistakes#

  • Setting a large work_mem with hundreds of connections: memory runs out at the busiest moment.
  • Changing ten parameters at once and not measuring the result.

What next#