How to override default PostgreSQL server using this repository's deployment workflow

[zachsa]

I found that PostgreSQL chooses the wrong query plan by default for the following statement:

EXPLAIN DELETE FROM public.values WHERE id = n;

From a post on the dba.stackexchange network, as well as some communication with @SephyLeigh, I have subsequently learned that there are quite a lot of opportunities in terms of performance via overriding default server configuration values. To begin with, setting the random_page_cost parameter to 3 (instead of the default 4) results in the server choosing a much faster query plan. So I can see that PostgreSQL server configuration needs to take into account the VM/hardware setup. Aside from adjusting the relative cost of random page access, it looks like there are some other easy wins here: https://postgis.net/workshops/zh_Hans/postgis-intro/tuning.html.

We need a mechanism to pass configuration overrides through the SAEON PostGIS Docker image (which is the default PostGIS docker image + a layer), through the default PostGIS Docker image, and to the PostgreSQL Docker image where the details on how to override configuration are documented.

• Is is possible to specify configuration parameters in the stack.yml file that get seen on server startup? If so, what is the syntax? The stack.yml file is here: https://github.com/SAEON/postgis/blob/main/src/stack.yml.
• The PostGIS image that we use is built via a Dockerfile here: https://github.com/SAEON/postgis/blob/main/src/Dockerfile. Does this need to change at all?