Slow performance during upgrade

[arindamg94]

Hi,

We are experiencing very slow upgrade path. example when going from v2.17 to v2.20.2, it took us approx 5 days to finish the upgrade path (migrations run from migration scripts 8-13). The umami-database being 138 GB and growing to 285 GB after upgrade was finish. After reindex/vacuuming (full), it reduced to 153 GB.

HOWEVER, It looks like umami-software is not parallized when running these scripts and with the size of our database, this becomes very slow and required a lot of days with downtime while upgrading. Correct me if I am wrong, but are there any suggestions anyone in this community can give us?

umamidb=# \dt
              List of relations
 Schema |        Name        | Type  | Owner 
--------+--------------------+-------+-------
 public | _prisma_migrations | table | umami
 public | event_data         | table | umami
 public | report             | table | umami
 public | revenue            | table | umami
 public | segment            | table | umami
 public | session            | table | umami
 public | session_data       | table | umami
 public | team               | table | umami
 public | team_user          | table | umami
 public | user               | table | umami
 public | website            | table | umami
 public | website_event      | table | umami
(12 rows)

umamidb=# select count(*) from event_data;
  count   
----------
 13085014
(1 row)

umamidb=# select count(*) from session;
  count   
----------
 23403897
(1 row)

umamidb=#  select count(*) from website;
 count 
-------
     4
(1 row)

umamidb=#  select count(*) from website_event;
   count   
-----------
 134961206
(1 row)

Here is how our server config looks like:
Ubuntu 24.04.3 LTS
RAM: 47 GB
CPU: 8

umamidb01:~# free -h
               total        used        free      shared  buff/cache   available
Mem:            47Gi        17Gi        17Gi        16Gi        29Gi        29Gi
Swap:          8.0Gi        54Mi       7.9Gi

postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

max_connections = 60                          # Reduced from 100 for better resource utilization
password_encryption = scram-sha-256
listen_addresses = '*'
track_counts = on

#----------------------------
# Memory Configuration (47 GB RAM)
#----------------------------
shared_buffers = 16GB                         # Increased to ~34% of RAM (better for analytics)
effective_cache_size = 38GB
work_mem = 256MB                              # Kept same - 256MB × 60 = 15.3GB (safely under RAM)
maintenance_work_mem = 8GB                    # Slightly reduced since fewer connections
huge_pages = try
hash_mem_multiplier = 1.5                     # Reduced from 2.0 (fewer concurrent hash operations needed)

#----------------------------
# Connection & Prepared Statements
#----------------------------
superuser_reserved_connections = 3
max_prepared_transactions = 64                # Reduced proportionally
idle_in_transaction_session_timeout = 10min   # Important for web analytics connections
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10

#----------------------------
# Parallel Processing
#----------------------------
max_worker_processes = 8
max_parallel_workers_per_gather = 4           # Reduced from 5 (better for 60 connections)
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_leader_participation = off
parallel_tuple_cost = 0.05                    # Slightly less aggressive with parallelism
parallel_setup_cost = 1000

#----------------------------
# Checkpoint & WAL
#----------------------------
checkpoint_completion_target = 0.9            # Slightly reduced for more consistent I/O
checkpoint_timeout = 30min
checkpoint_flush_after = 1MB                  # Reduced for better write performance
max_wal_size = 24GB                           # Reduced (lower connection count = less concurrent writes)
min_wal_size = 8GB
wal_compression = zstd
wal_buffers = 64MB
bgwriter_delay = 20ms                         # Increased for fewer connections
bgwriter_lru_maxpages = 800                   # Reduced proportionally

#----------------------------
# Query Planning
#----------------------------
random_page_cost = 1.0
effective_io_concurrency = 200                # Reduced for better predictability
enable_hashagg = on                           # Changed to ON - hashagg can be good for analytics
enable_sort = on
enable_material = on
jit = off
default_statistics_target = 500               # Reduced from 750 (adequate for analytics)

#----------------------------
# Autovacuum & Vacuum
#----------------------------
autovacuum = on
autovacuum_vacuum_threshold = 50
autovacuum_max_workers = 4                    # Reduced from 5 (fewer connections)
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
vacuum_cost_delay = 4ms                       # Increased delay (less aggressive vacuum)
autovacuum_vacuum_cost_limit = 1000           # Reduced (fewer workers, lower throughput needed)
autovacuum_work_mem = 1GB

#----------------------------
# Monitoring & Logging
#----------------------------
track_io_timing = on
log_min_duration_statement = 200              # Reduced to 200ms (tighter monitoring)
log_checkpoints = on
log_autovacuum_min_duration = 500             # Reduced threshold
log_temp_files = 32MB                         # Reduced threshold
#log_connections = on                          # Added - useful for connection monitoring
#log_disconnections = on                       # Added
log_lock_waits = on                           # Important for identifying contention

#----------------------------
# Additional Optimizations
#----------------------------
temp_buffers = 32MB                           # Per-session temp buffers
log_min_error_statement = error
plan_cache_mode = auto                        # Let planner decide when to use generic plans