Others

Performance

The following tips can help to improve the performance for processing large PBF files with OSMNames.

Database Configuration

For better performance, the database needs to be configured according to the resources of the host system, the process runs on. A custom configuration can be added by creating a file /docker-entrypoint-initdb.d/alter_system.sh inside the postgres container and marking it as executable. The script is executed when restarting the database container.

Here is an example for the content of the script:

#!/bin/bash
set -o errexit
set -o pipefail
set -o nounset

function alter_system() {
    echo "Altering System parameters"
    PGUSER="$POSTGRES_USER" psql --dbname="$POSTGRES_DB" <<-EOSQL
    alter system set autovacuum_work_mem = '4GB';
    alter system set checkpoint_completion_target = '0.9';
    alter system set checkpoint_timeout = '20min';
    alter system set datestyle = 'iso, mdy';
    alter system set default_statistics_target = '500';
    alter system set default_text_search_config = 'pg_catalog.english';
    alter system set dynamic_shared_memory_type = 'posix';
    alter system set effective_cache_size = '96GB';
    alter system set fsync = 'off';
    alter system set lc_messages = 'en_US.utf8';
    alter system set lc_monetary = 'en_US.utf8';
    alter system set lc_numeric = 'en_US.utf8';
    alter system set lc_time = 'en_US.utf8';
    alter system set listen_addresses = '*';
    alter system set log_checkpoints = 'on';
    alter system set log_temp_files = '1MB';
    alter system set log_timezone = 'UTC';
    alter system set maintenance_work_mem = '96GB';
    alter system set max_connections = '20';
    alter system set random_page_cost = '1.1';
    alter system set shared_buffers = '96GB';
    alter system set synchronous_commit = 'off';
    alter system set temp_buffers = '120MB';
    alter system set timezone = 'UTC';
    alter system set track_counts = 'on';
    alter system set wal_buffers = '16MB';
    alter system set max_wal_size = '5GB';
    alter system set work_mem = '6GB';
    alter system set log_statement = 'all';
EOSQL
}

alter_system

Determining the best configuration for a host is not easy. A good starting point for that is PgTune.

tmpfs

To improve the performance of OSMNames the database can be hold in the RAM while processing. The easiest way to do this, is by adding following line to the docker-compose.yml file:

...
postgres:
  ...
  tmpfs: /var/lib/postgresql/data:size=300G

This only makes sense if the necessary amount of RAM is available. Additionally keep in mind that the data will be lost when restarting the docker container.