CI slowdown due to PG_TEST_INITDB_EXTRA_OPTS
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
Hi,
I was once more wondering why CI is so slow. While thinking about what
measurements to take, I think I figured out a major source:
Many of the jobs use PG_TEST_INITDB_EXTRA_OPTS. Unfortunately, using that
turns out to disable the initdb template mechanism:
sub init
{
...
if (defined $initdb_extra_opts_env)
{
push @{ $params{extra} }, shellwords($initdb_extra_opts_env);
}
...
# If available, if there aren't any parameters and if force_initdb is
# disabled, use a previously initdb'd cluster as a template by copying it.
# For a lot of tests, that's substantially cheaper. It does not seem
# worth figuring out whether extra parameters affect compatibility, so
# initdb is forced if any are defined.
#
# There's very similar code in pg_regress.c, but we can't easily
# deduplicate it until we require perl at build time.
if ( $params{force_initdb}
or defined $params{extra}
or !defined $ENV{INITDB_TEMPLATE})
{
note("initializing database system by running initdb");
To see the impact of that, I just hacked up a commit that comments out all the
use of PG_TEST_INITDB_EXTRA_OPTS. That's of course not a fair comparison,
several of the options passed to PG_TEST_INITDB_EXTRA_OPTS do increase
overhead, but it's an approximation.
master: https://github.com/anarazel/postgres/actions/runs/29593721971
hack: https://github.com/anarazel/postgres/actions/runs/29597834725
(note that for the hack the compiles are cached, so you can't look at the
overall runtimes)
master hack
autoconf 14m52s 8m45s
meson-32 8m13s 6m52s
meson-64 19m19s 13m27s
macos 19m24s 9m07s
mingw 15m41s 19m05s
vs-slice-1 15m59s 14m19s
vs-slice-2 10m39s 13m55s
Note that mingw and vs did not use PG_TEST_INITDB_EXTRA_OPTS, so their test
timing is unrelated. Which also shows that the rest of the numbers are not
entirely to be trusted.
But I can also reproduce decent improvements locally, so I'm pretty confident
not using PG_TEST_INITDB_EXTRA_OPTS will yield significant improvements:
To make the comparison fairer, I'm comparing not using
PG_TEST_INITDB_EXTRA_OPTS
with using
PG_TEST_INITDB_EXTRA_OPTS='-c debug_copy_parse_plan_trees=off'
which shouldn't change any runtime behaviour.
PG_TEST_INITDB_EXTRA_OPTS='-c debug_copy_parse_plan_trees=off':
1794.54user 366.10system 2:11.37elapsed 1644%CPU (0avgtext+0avgdata 153580maxresident)k
52160inputs+69971904outputs (612major+26216385minor)pagefaults 0swaps
without:
435.36user 293.50system 1:48.81elapsed 669%CPU (0avgtext+0avgdata 153508maxresident)k
429472inputs+34543792outputs (780major+20764791minor)pagefaults 0swaps
So that's a ~3x increase in CPU time, no wonder that slows down the tests.
And that's without even using sanitizers, which make initdb a good bit slower!
Now, the slightly harder question is how to do this without just ripping out
the PG_TEST_INITDB_EXTRA_OPTS. Looks like we started using that for CI with:
commit a292c98d62ddc0ad681f772ab91bf68ee399cb4b
Author: Peter Eisentraut <peter@eisentraut.org>
Date: 2024-08-01 09:37:44 +0200
Convert node test compile-time settings into run-time parameters
I guess the reason Peter didn't use TEMP_CONFIG is that it's kinda annoying to
do properly, due TEMP_CONFIG already being set to src/tools/ci/pg_ci_base.conf.
Without adding new pg_regress.c and Cluster.pm code, it seems we would have to
create a conf file that includes src/tools/ci/pg_ci_base.conf and also adds
the other options. But that's somewhat annoying to do :(
We could add an include_if_exists to src/tools/ci/pg_ci_base.conf, but I'm not
sure what absolute path would be portable enough, and I don't think we allow
environment variables or such.
Any better ideas?
On macos we probably also could get a decent further boost by using cp -c when
copying the template data directory (which uses copy on write). On linux the
default is --reflink=auto, so it'd get used automatically, but CI doesn't run
on a CoW/reflink capable filesystem (locally I see pretty decent gains from
it) . On an m4 mac mini, I see about a 10% gain, but it's quite plausible
this would be larger in CI. But that's a separate thread, I guess.
Greetings,
Andres Freund
Andres Freund <andres@anarazel.de> writes:
I was once more wondering why CI is so slow. While thinking about what
measurements to take, I think I figured out a major source:
Many of the jobs use PG_TEST_INITDB_EXTRA_OPTS. Unfortunately, using that
turns out to disable the initdb template mechanism:
...
Any better ideas?
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?
regards, tom lane
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?
Yeah, that seems to work pretty well. See attached.
I'm getting similar test timings in CI with this as Andres got for the
Linux runs that he did when removing PG_TEST_INITDB_EXTRA_OPTS.
For MacOS I see basically no improvement though, I guess that's probably
because the debug_xxx options actually incur significant overhead. Maybe
it's worth trying to move them to one of the faster runs. But that seems
like a separate discussion.
Attachments:
v1-0001-Keep-using-the-initdb-template-when-PG_TEST_INITD.patchtext/x-patch; charset=utf-8; name=v1-0001-Keep-using-the-initdb-template-when-PG_TEST_INITD.patchDownload+108-17
Hi,
On 2026-07-20 23:47:16 +0200, Jelte Fennema-Nio wrote:
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?
That seems like a somewhat complicated mechanism. But mainly I feel like it's
a workaround around the config-customization-via-env-var having to go through
exactly one file. None of the uses of PG_TEST_INITDB_EXTRA_OPTS in CI actually
needs it to be an initdb option, it's just a way to set configuration options
for the server via an environment variable.
I wonder if the best thing would be to add PG_TEST_SERVER_POSTGRES_OPTS or
such, which Cluster.pm and regress.c would add to postgresql.conf.
For MacOS I see basically no improvement though, I guess that's probably
because the debug_xxx options actually incur significant overhead. Maybe
it's worth trying to move them to one of the faster runs. But that seems
like a separate discussion.
Hm. I see very consistent improvements locally on an m4 mac mini. On CI the
runtime of the macos job is crazily variable, e.g. the last few runs on master
had these runtimes for macos' "Test world":
14m59s, 17m24s, 12m13s, 18m38s, 13m15s, 12m22s
No idea what causes that degree of variability. I guess the effect of
neighboring tasks is the most likely explanation...
In contrast, meson-64 has these "Test world" times for the same commits:
19m46s, 19m40s, 18m38s, 18m14s, 19m15s, 18m23s
(meson-64 is slow due to asan)
With the configuration for meson-64 instead done via:
- name: 'configure extra opts'
run: |
echo "io_method=io_uring" >> src/tools/ci/pg_ci_base.conf
cat src/tools/ci/pg_ci_base.conf
I see ~13m30s for meson-64.
Greetings,
Andres Freund
Hi,
On Tue, 21 Jul 2026 at 03:56, Andres Freund <andres@anarazel.de> wrote:
On 2026-07-20 23:47:16 +0200, Jelte Fennema-Nio wrote:
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?I wonder if the best thing would be to add PG_TEST_SERVER_POSTGRES_OPTS or
such, which Cluster.pm and regress.c would add to postgresql.conf.
Attached is an attempt to implement 'PG_TEST_SERVER_POSTGRES_OPTS'.
Like Jelte, I didn't see any improvement for MacOS.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Attachments:
v2-0001-Add-PG_TEST_SERVER_POSTGRES_OPTS-to-inject-server.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Add-PG_TEST_SERVER_POSTGRES_OPTS-to-inject-server.patchDownload+60-9
On Tue, 21 Jul 2026 at 02:56, Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2026-07-20 23:47:16 +0200, Jelte Fennema-Nio wrote:
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?That seems like a somewhat complicated mechanism. But mainly I feel like it's
a workaround around the config-customization-via-env-var having to go through
exactly one file. None of the uses of PG_TEST_INITDB_EXTRA_OPTS in CI actually
needs it to be an initdb option, it's just a way to set configuration options
for the server via an environment variable.I wonder if the best thing would be to add PG_TEST_SERVER_POSTGRES_OPTS or
such, which Cluster.pm and regress.c would add to postgresql.conf.
I don't think it's that complicated. But I'd be fine with
PG_TEST_SERVER_POSTGRES_OPTS as well. That has the downside that it
won't work for actual initdb options, but at least CI doesn't use that
(for now). Some buildfarm members might though.
On Tue, Aug 11, 2026 at 2:39 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
On Tue, 21 Jul 2026 at 02:56, Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2026-07-20 23:47:16 +0200, Jelte Fennema-Nio wrote:
On Sat, 18 Jul 2026 at 20:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It looks like any one job mostly uses the same value of
PG_TEST_INITDB_EXTRA_OPTS. Could we record what was used to set up
the template, and allow using it if that matches?That seems like a somewhat complicated mechanism. But mainly I feel like it's
a workaround around the config-customization-via-env-var having to go through
exactly one file. None of the uses of PG_TEST_INITDB_EXTRA_OPTS in CI actually
needs it to be an initdb option, it's just a way to set configuration options
for the server via an environment variable.I wonder if the best thing would be to add PG_TEST_SERVER_POSTGRES_OPTS or
such, which Cluster.pm and regress.c would add to postgresql.conf.I don't think it's that complicated. But I'd be fine with
PG_TEST_SERVER_POSTGRES_OPTS as well. That has the downside that it
won't work for actual initdb options, but at least CI doesn't use that
(for now). Some buildfarm members might though.
Hi,
I've scheduled 3 runs on 3 consecutive days (w/o and with v2 patch by Nazir),
[in minutes, from/master -> branch/w patch]
autoconf 23 -> 17, 21 -> 10, 20 -> 16
meson32 16 -> 14, 16 -> 9, 15 -> 13
meson64 29 -> 22, 28 -> 18, 28 -> 22
macOS 28 -> 25, 21 -> 17, 21 -> 20
mingw 22 -> 27?,25 -> 23, 28 -> 26
warnings 32 -> 30, 23 -> 3?, 27 -> 24
winvc1 18 -> 19, 19 -> 26, 16 -> 16
winvc2 17 -> 17, 17 -> 14, 17 -> 16
Take the results with some degree of doubt (I have no idea what is
infrastructure underneath; how overscribed the CPUs were etc.), but those
are consistent, real gains for Linux builds (-33% autoconf, -27% meson32,
23% meson64) during 3 days.
I was once more wondering why CI is so slow. [..]
Cheer up gents, altough it's like ~ < 25min, I find the full CI of PostgreSQL
still the fastest thing I have ever worked with and it's free. From what
I've worked with, heard or read in terms of complex software/systems it's
leaving the rest of world in the dust in terms of how lightweight/fast it
is ... [one can have it even faster on your hardware if one wants -- using
runners].
-J.
On Tue, Aug 11, 2026 at 8:39 AM Jelte Fennema-Nio <postgres@jeltef.nl>
wrote:
I don't think it's that complicated. But I'd be fine with
PG_TEST_SERVER_POSTGRES_OPTS as well. That has the downside that it
won't work for actual initdb options, but at least CI doesn't use that
(for now). Some buildfarm members might though.
But they can still use PG_TEST_INITDB_EXTRA_OPTS, right? We are not
replacing the var, just adding a new one, I thought. Probably should
eventually look into caching things for people that use true initdb
options, but I suspect the number of things not handled by the new var will
be very close to zero, so maybe not worth it.
Cheers,
Greg
On Thu, Aug 13, 2026, Greg Sabino Mullane wrote:
But they can still use PG_TEST_INITDB_EXTRA_OPTS, right? We are not
replacing the var, just adding a new one, I thought.
Yes. I read Nazir's v2. It keeps PG_TEST_INITDB_EXTRA_OPTS
available for actual initdb options. Both Cluster.pm and pg_regress
handle the new variable consistently, and the CI changes move only
server GUCs to it. The patch LGTM.
I ran CI quite a few times today and spent some time waiting for it,
so I started looking for ways to make it faster.
I also tried moving one-use pg_rewind backups instead of copying them,
and caching the dummy_seclabel availability check across pg_upgrade
transfer modes. They save a few percent in the affected tests. I can
send those patches too if that sounds worthwhile.
The lowest-hanging fruit I found is splitting the Linux Meson 64-bit
ASAN tests across two runners, as we already do for the Windows Visual
Studio job. This does not reduce the total work and actually
duplicates the configure and build steps, but it does reduce how long
we wait for CI.
In one run, the two Test world slices took 8:33 and 12:08. Two
unsliced jobs running at the same time took 23:45 and 25:05. Together
the slices ran the same 412 tests. In a complete run, the gain is
naturally limited by the next-slowest job.
The CI image currently has Meson 1.7, so the patch emulates Meson 1.8's
round-robin --slice using the test list.
PFA. WDYT?
Best regards, Andrey Borodin.
Attachments:
v1-0001-Split-Linux-ASAN-tests-across-two-runners.patchapplication/octet-stream; name=v1-0001-Split-Linux-ASAN-tests-across-two-runners.patch; x-unix-mode=0644Download+48-4
Hi,
On Sun, 6 Sept 2026 at 21:12, Andrey Borodin <x4mmm@yandex-team.ru> wrote:
On Thu, Aug 13, 2026, Greg Sabino Mullane wrote:
But they can still use PG_TEST_INITDB_EXTRA_OPTS, right? We are not
replacing the var, just adding a new one, I thought.Yes. I read Nazir's v2. It keeps PG_TEST_INITDB_EXTRA_OPTS
available for actual initdb options. Both Cluster.pm and pg_regress
handle the new variable consistently, and the CI changes move only
server GUCs to it. The patch LGTM.
Thank you for looking into this!
The lowest-hanging fruit I found is splitting the Linux Meson 64-bit
ASAN tests across two runners, as we already do for the Windows Visual
Studio job. This does not reduce the total work and actually
duplicates the configure and build steps, but it does reduce how long
we wait for CI.In one run, the two Test world slices took 8:33 and 12:08. Two
unsliced jobs running at the same time took 23:45 and 25:05. Together
the slices ran the same 412 tests. In a complete run, the gain is
naturally limited by the next-slowest job.The CI image currently has Meson 1.7, so the patch emulates Meson 1.8's
round-robin --slice using the test list.
First question that comes to mind is why we are doing this only for
Linux 64 Meson and not for all jobs. I think there are two answers:
1. We can't do this for Linux Autoconf as far as I know, so it doesn't
make sense for tasks that take less or similar time.
2. The free concurrency limit for GitHub Actions is 20, and our main
CI tasks use 8 (9 with your patch). It would be best to not exceed 10
so that we can run two CI tasks simultaneously.
So, I think this change makes sense. Also we can apply the same change
to the next longest running task, which is MinGW 64 depending on my
testing.
My review:
+ readarray -t test_names < <(
+ meson test ${{env.MTEST_ARGS}} ${{env.MTEST_TARGET}} \
+ --list --no-suite setup |
+ awk -F ' - ' \
+ -v slice=${{ matrix.slice}} \
+ -v slices=${{ matrix.num_slices}} \
+ '(NR - 1) % slices == slice - 1 {
+ name = $NF
+ sub(/:[^ ]+ \/ /, ":", name)
+ print name
+ }'
+ )This is complicated but I don't have a better solution. One point is
that 'meson test --list' doesn't guarantee the order of the returned
list, so we might miss tests if the order differs between tasks. I
recommend sorting the list names before slicing.
- - *upload_logs_step
+ - name: Upload logs
+ if: failure() && !cancelled()
+ uses: actions/upload-artifact@v7
+ with:
+ name: logs-${{ github.job }}-${{ matrix.slice }}-${{
github.run_id }}-${{ github.run_attempt }}
+ path: |
+ **/*.log
+ **/*.diffs
+ **/regress_log_*
+ **/crashlog-*.txt
+ build/meson-logs/**
+ **/config.log
+ if-no-files-found: ignoreWe have the same problem for the Windows VS job; otherwise, two
artifacts could end up having the same name. I remember sending a
patch to fix this, but I don't recall where it is. I think we can save
this step as something like 'upload_logs_step_slice' and use it in the
Windows VS task as well.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Hi Nazir,
Thank you for the review.
'meson test --list' doesn't guarantee the order of the returned
list, so we might miss tests if the order differs between tasks.
Agreed. Since the two runners build their lists independently, a
different order could produce both a missing test and a duplicate. V2
sorts the names before applying the round-robin selection.
I think we can save this step as something like
'upload_logs_step_slice' and use it in the Windows VS task as well.
Agreed. V2 defines the slice-aware upload step in the Linux job and
reuses it for the existing Windows Visual Studio matrix. This also
avoids the existing artifact-name collision when both Windows slices
fail.
I also tried splitting MinGW. In six recent full runs the unsliced job
took 28:36-31:29 and was the slowest job each time. The two slices
completed in 15:05 and 17:32, so v2 includes this too.
PFA v2.
Best regards, Andrey Borodin.
Attachments:
v2-0001-Split-Linux-ASAN-and-MinGW-tests-across-runners.patchapplication/octet-stream; name=v2-0001-Split-Linux-ASAN-and-MinGW-tests-across-runners.patch; x-unix-mode=0644Download+67-7
Hi,
On Mon, 7 Sept 2026 at 16:15, Andrey Borodin <x4mmm@yandex-team.ru> wrote:
Agreed. V2 defines the slice-aware upload step in the Linux job and
reuses it for the existing Windows Visual Studio matrix. This also
avoids the existing artifact-name collision when both Windows slices
fail.I also tried splitting MinGW. In six recent full runs the unsliced job
took 28:36-31:29 and was the slowest job each time. The two slices
completed in 15:05 and 17:32, so v2 includes this too.
+ - name: Distinguish ccache slice
+ run: echo "CACHE_SUFFIX=${CACHE_SUFFIX}:${{ matrix.slice }}""$GITHUB_ENV"
I would remove these steps; ccache don't differ between builds so
there is no need to separate them. Other than that, LGTM.
--
Regards,
Nazir Bilal Yavuz
Microsoft
Hi Nazir,
On Thu, Sep 10, 2026, Nazir Bilal Yavuz wrote:
I would remove these steps; ccache don't differ between builds so
there is no need to separate them. Other than that, LGTM.
Agreed. V3 removes the slice suffix from the ccache keys for both
Linux ASAN and MinGW.
For context, here are the timings from v3 run, without your
PG_TEST_SERVER_POSTGRES_OPTS patch.
Test world Complete job
Linux Autoconf 15:04 22:00
Linux Meson 32-bit 9:53 16:29
Linux ASAN slice 1/2 12:05 17:17
Linux ASAN slice 2/2 11:52 17:04
macOS 20:29 24:29
MinGW slice 1/2 8:51 19:13
MinGW slice 2/2 7:29 17:57
Visual Studio slice 1/2 13:01 16:48
Visual Studio slice 2/2 11:45 16:33
CompilerWarnings - 22:13
Further splitting any job would not help much.
Your patch is still much needed: it reduces the work itself.
Thank you for the review!
PFA v3.
Best regards, Andrey Borodin.