german-streets-api/postgresql.conf at public · madLinux7/german-streets-api · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
# PostgreSQL Configuration - optimized for connection stability and performance
# Equivalent to the previous MariaDB configuration

# Connection settings
max_connections = 150                    # Increased from default for concurrent requests
superuser_reserved_connections = 3       # Reserve some connections for superuser
tcp_keepalives_idle = 600                # 10 minutes - keep connections alive
tcp_keepalives_interval = 10             # Probe interval
tcp_keepalives_count = 10                # Number of probes before timeout

# Memory settings - optimized for 512MB container limit
shared_buffers = 128MB                   # 25% of available memory (equivalent to innodb_buffer_pool_size)
effective_cache_size = 384MB             # 75% of available memory
work_mem = 4MB                           # Memory for sorting/hashing operations
maintenance_work_mem = 64MB              # Memory for maintenance operations (VACUUM, CREATE INDEX)
temp_buffers = 16MB                      # Temporary table buffer
wal_buffers = 16MB                       # Write-Ahead Log buffer (equivalent to innodb_log_buffer_size)

# Query planning
random_page_cost = 1.1                   # Optimized for SSD/container storage
effective_io_concurrency = 200           # Number of concurrent I/O operations

# Write-Ahead Log (WAL) settings
wal_level = replica                      # Enable WAL archiving for backups
fsync = on                               # Ensure data is written to disk
synchronous_commit = off                 # Trade-off for better performance (equivalent to innodb_flush_log_at_trx_commit = 2)
wal_compression = on                     # Compress WAL for better I/O
checkpoint_timeout = 15min               # Checkpoint frequency
checkpoint_completion_target = 0.9       # Spread checkpoints over time
max_wal_size = 1GB                       # Maximum WAL size before checkpoint
min_wal_size = 256MB                     # Minimum WAL size to keep

# Logging - for monitoring and debugging
logging_collector = on                   # Enable log collection
log_directory = 'log'                    # Log directory (relative to data directory)
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d                    # Rotate logs daily
log_rotation_size = 100MB                # Rotate logs when they reach 100MB
log_min_duration_statement = 2000        # Log queries taking longer than 2s (equivalent to slow_query_log)
log_connections = on                     # Log connection attempts
log_disconnections = on                  # Log disconnections
log_duration = off                       # Don't log duration of all statements
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'none'                   # Don't log all statements (only slow ones)
log_timezone = 'Europe/Berlin'

# Error handling and debugging
log_min_messages = warning               # Minimum severity to log
log_min_error_statement = error          # Log statements causing errors
log_lock_waits = on                      # Log lock waits
deadlock_timeout = 1s                    # Time to wait before checking for deadlock
log_checkpoints = on                     # Log checkpoint activity

# Locale and formatting
datestyle = 'iso, mdy'
timezone = 'Europe/Berlin'
lc_messages = 'en_US.utf8'               # Locale for system error messages
lc_monetary = 'en_US.utf8'               # Locale for monetary formatting
lc_numeric = 'en_US.utf8'                # Locale for number formatting
lc_time = 'en_US.utf8'                   # Locale for time formatting
default_text_search_config = 'pg_catalog.english'

# Connection and authentication
listen_addresses = '*'                   # Listen on all interfaces (equivalent to bind_address = 0.0.0.0)
port = 5432                              # PostgreSQL port

# Performance and maintenance
autovacuum = on                          # Enable automatic vacuuming
autovacuum_max_workers = 3               # Number of autovacuum workers
autovacuum_naptime = 1min                # Time between autovacuum runs
autovacuum_vacuum_threshold = 50         # Minimum number of updated/deleted tuples before vacuum
autovacuum_analyze_threshold = 50        # Minimum number of inserted/updated/deleted tuples before analyze

# Statistics
track_activities = on                    # Track currently executing commands
track_counts = on                        # Track statistics for query planner
track_io_timing = on                     # Track I/O timing statistics
track_functions = all                    # Track function call statistics
stats_temp_directory = '/tmp/pg_stat_tmp' # Temporary statistics directory

# Statement timeout (prevent long-running queries from blocking)
statement_timeout = 0                    # 0 = disabled, set to milliseconds if needed
lock_timeout = 0                         # 0 = disabled, set to milliseconds if needed
idle_in_transaction_session_timeout = 3600000  # 1 hour - kill idle transactions

# Increase limits for large data imports
max_locks_per_transaction = 64           # Default, increase if needed
max_pred_locks_per_transaction = 64      # Default, increase if needed

# Parallel query execution
max_parallel_workers_per_gather = 2      # Number of parallel workers per query
max_parallel_workers = 4                 # Total number of parallel workers
max_worker_processes = 8                 # Maximum number of background processes

# Security
ssl = off                                # SSL disabled for local development
password_encryption = scram-sha-256      # Modern password encryption