Skip to content

Instantly share code, notes, and snippets.

@egor-romanov

egor-romanov/optimisations.md Secret

Last active February 1, 2024 15:23
Show Gist options
  • Star 5 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save egor-romanov/323e2847851bbd758081511785573c08 to your computer and use it in GitHub Desktop.
Save egor-romanov/323e2847851bbd758081511785573c08 to your computer and use it in GitHub Desktop.
Download ZIP
Supabase Optimisations for Postgres
Raw
optimisations.md

Postgres Optimizations

This is a list of Supabase optimizations for Postgres DB used on different types of our instances during the performance testing of pgvector workloads (2023-07). It may not be always up-to-date in the future.

2-core ARM (shared) | 1 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "768MB"
effective_io_concurrency          = 200
maintenance_work_mem              = "64MB"
max_connections                   =  60
max_parallel_maintenance_workers  = 1
max_parallel_workers              = 2
max_parallel_workers_per_gather   = 1
max_wal_size                      = "4GB"
max_worker_processes              = 4
min_wal_size                      = "1GB"
random_page_cost                  = 1.1
shared_buffers                    = "256MB"
wal_buffers                       = "7864kB"
work_mem                          = "3500kB"

2-core ARM (shared) | 2 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "1536MB"
effective_io_concurrency          = 200
maintenance_work_mem              = "128MB"
max_connections                   =  90
max_parallel_maintenance_workers  = 1
max_parallel_workers              = 2
max_parallel_workers_per_gather   = 1
max_wal_size                      = "4GB"
max_worker_processes              = 4
min_wal_size                      = "1GB"
random_page_cost                  = 1.1
shared_buffers                    = "512MB"
wal_buffers                       = "16MB"
work_mem                          = "5MB"

2-core ARM (shared) | 4 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "3GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "256MB"
max_connections                   = 120
max_parallel_maintenance_workers  = 1
max_parallel_workers              = 2
max_parallel_workers_per_gather   = 1
max_wal_size                      = "4GB"
max_worker_processes              = 4
min_wal_size                      = "1GB"
random_page_cost                  = 1.1
shared_buffers                    = "1GB"
wal_buffers                       = "16MB"
work_mem                          = "7MB"

2-core ARM (dedicated) | 8 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "6GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "512MB"
max_connections                   = 160
max_parallel_maintenance_workers  = 1
max_parallel_workers              = 2
max_parallel_workers_per_gather   = 1
max_wal_size                      = "4GB"
max_worker_processes              = 4
min_wal_size                      = "1GB"
random_page_cost                  = 1.1
shared_buffers                    = "2GB"
wal_buffers                       = "16MB"
work_mem                          = "12MB"

4-core ARM (dedicated) | 16 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "12GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "1GB"
max_connections                   = 240
max_parallel_maintenance_workers  = 2
max_parallel_workers              = 4
max_parallel_workers_per_gather   = 2
max_wal_size                      = "4GB"
max_worker_processes              = 6
min_wal_size                      = "2GB"
random_page_cost                  = 1.1
shared_buffers                    = "4GB"
wal_buffers                       = "16MB"
work_mem                          = "16MB"

8-core ARM (dedicated) | 32 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "24GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "2GB"
max_connections                   = 380
max_parallel_maintenance_workers  = 4
max_parallel_workers              = 8
max_parallel_workers_per_gather   = 4
max_wal_size                      = "4GB"
max_worker_processes              = 8
min_wal_size                      = "2GB"
random_page_cost                  = 1.1
shared_buffers                    = "8GB"
wal_buffers                       = "16MB"
work_mem                          = "20MB"

16-core ARM (dedicated) | 64 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "48GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "2GB"
max_connections                   = 480
max_parallel_maintenance_workers  = 8
max_parallel_workers              = 16
max_parallel_workers_per_gather   = 8
max_wal_size                      = "4GB"
max_worker_processes              = 16
min_wal_size                      = "2GB"
random_page_cost                  = 1.1
shared_buffers                    = "16GB"
wal_buffers                       = "16MB"
work_mem                          = "32MB"

32-core ARM (dedicated) | 128 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "96GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "2GB"
max_connections                   = 490
max_parallel_maintenance_workers  = 16
max_parallel_workers              = 32
max_parallel_workers_per_gather   = 16
max_wal_size                      = "4GB"
max_worker_processes              = 32
min_wal_size                      = "2GB"
random_page_cost                  = 1.1
shared_buffers                    = "32GB"
wal_buffers                       = "16MB"
work_mem                          = "64MB"

48-core ARM (dedicated) | 192 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "144GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "2GB"
max_connections                   = 500
max_parallel_maintenance_workers  = 24
max_parallel_workers              = 48
max_parallel_workers_per_gather   = 24
max_wal_size                      = "4GB"
max_worker_processes              = 48
min_wal_size                      = "2GB"
random_page_cost                  = 1.1
shared_buffers                    = "48GB"
wal_buffers                       = "16MB"
work_mem                          = "95MB"

64-core ARM (dedicated) | 256 GB

checkpoint_completion_target      = 0.9
default_statistics_target         = 100
effective_cache_size              = "192GB"
effective_io_concurrency          = 200
maintenance_work_mem              = "2GB"
max_connections                   = 500
max_parallel_maintenance_workers  = 32
max_parallel_workers              = 64
max_parallel_workers_per_gather   = 32
max_wal_size                      = "4GB"
max_worker_processes              = 64
min_wal_size                      = "2GB"
random_page_cost                  = 1.1
shared_buffers                    = "64GB"
wal_buffers                       = "16MB"
work_mem                          = "125MB"

Last updated: 2023-07-13

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment