MIME-Version: 1.0
References: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
 <CAJpy0uDLUEjHHME8om1vAf6qkXCeRR6cBvkpK8yWBAC=T0ZFLA@mail.gmail.com>
 <CAFPTHDZg1JrunGgOj332hr+gUuH_Jm7skqPpYSvd-QE3yEdRDQ@mail.gmail.com>
 <CAJpy0uBz7MCSUkvFJD9ij65vBahNmY+bfCgdGKRqXovYs+K_TA@mail.gmail.com>
 <CAJpy0uDsuNqjWd-TmGBxqSS1rnVCJ3B8=SYrtxQ=Vs8kb71QFA@mail.gmail.com>
 <CAJpy0uAMWg3KcXtVBS7B0rnchLNrCCVYBByJCzAp=u5LERgtfA@mail.gmail.com>
 <CAFPTHDZwEhxhDAeqcPi0GuYN6xBs8gFXHOMUnbg3u2Xigcz4Zg@mail.gmail.com>
 <CAE9k0PmTyCU1A9YEf+MRgfeZ8yK1bAYJu=o0bH8DNUTzXejQyQ@mail.gmail.com>
 <CAA4eK1L6czEzG4mLNZSyjYC5nX0FMSjjk3csKuxPD3Ph5-7Yvw@mail.gmail.com>
 <CAJpy0uAhGQJ=msVsn2GsqWXr+YESJK6x9NBvrUtKvtvp1OVuKQ@mail.gmail.com>
 <CAJpy0uAOuu-M6wobH2wHOdTymm-cX9+MqwPyRNoOt=sPKBdCew@mail.gmail.com>
 <CAFPTHDZiWYXoKoo4VcBYNH9a=gxDZhfkcBeXt5w6cLw4_ysyKw@mail.gmail.com>
 <OS9PR01MB12149D9054CC7F2DC3F0D26A1F577A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
 <CAA4eK1KYxQALt46k5uWOO6SUtNjvjOaXwfNjH0AU656YrcGZEw@mail.gmail.com>
 <CAFPTHDZYonM+SXG19VVjgWduXQJSuDhcOUWq0NCiiuQubCSt6g@mail.gmail.com>
 <CAFPTHDYud1zr0VyizhyhEQXfHMgXVcHrPzE56WUKGCFNskQq2A@mail.gmail.com> <CABdArM6X0RzKnQcT46VTht2=btW7EVU7hUWNNX0eTY7hQfK8bA@mail.gmail.com>
In-Reply-To: <CABdArM6X0RzKnQcT46VTht2=btW7EVU7hUWNNX0eTY7hQfK8bA@mail.gmail.com>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Fri, 27 Feb 2026 11:31:14 +0530
Message-ID: <CAA4eK1+_4z-y4NuhqmdUqYnsN1fMqG8KpDXqVbx28__WhVpVRw@mail.gmail.com>
Subject: Re: [PATCH] Support automatic sequence replication
To: Nisha Moond <nisha.moond412@gmail.com>
Cc: Ajin Cherian <itsajin@gmail.com>, 
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, shveta malik <shveta.malik@gmail.com>, 
	Ashutosh Sharma <ashu.coek88@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA4eK1%2B_4z-y4NuhqmdUqYnsN1fMqG8KpDXqVbx28__WhVpVRw%40mail.gmail.com>
Precedence: bulk

On Thu, Feb 26, 2026 at 7:43=E2=80=AFPM Nisha Moond <nisha.moond412@gmail.c=
om> wrote:
>
> On Thu, Feb 26, 2026 at 1:07=E2=80=AFPM Ajin Cherian <itsajin@gmail.com> =
wrote:
> >
> > I also did some performance testing on HEAD to see how long REFRESH
> > SEQUENCES takes for a large number of sequences.
> > I ran these on a 2=C3=97 Intel Xeon E5-2699 v4 (22 cores each, 44 cores
> > total / 88 threads) 512 GB RAM. I didn't see much value in
> > differentiating between cases where half the sequences were different
> > or all the sequences were different as REFRESH SEQUENCES updates all
> > sequences after changing the state of all of them to INIT, it doesn't
> > matter if they drifted or not.
> >
> > On HEAD:
> > time to sync 10000 sequences: 1.080s (1080ms)
> > time to sync 100000 sequences: 12.069s (12069ms)
> > time to sync 1000000 sequences: 139.414s (139414ms)
> >
> > testing script attached (pass in the number of sequences as a run time
> > parameter).
>
> Hi Ajin,
> Thanks for sharing the performance results. I ran the same tests using
> your scripts on a different machine with the configuration:
>  - Chip: Apple M4 Pro, 14 CPU cores
>  - RAM: 24 GB
>  - Postgres installation on pg_Head - commit 77c7a17a6e5
>
> For these tests, I used shared_buffers =3D 4GB. The time taken for 1M
> sequences is increased significantly:
>   time to sync 10000 sequences: .994s (994ms)
>   time to sync 100000 sequences: 11.032s (11032ms)
>   time to sync 1000000 sequences: 426.850s (426850ms)
>
> I also tested with shared_buffers =3D 8GB, and the time for 1M sequences
> was 441.794s (441794 ms)
>

IIUC, the tests by Ajin and Nisha show the time to sync sequences
varies from a few seconds to minutes depending on the number of
sequences and machine configuration. This indicates that having an
autosync worker can avoid downtime before upgrade, otherwise, users
always need to use REFRESH SEQUENCES before upgrade.

--=20
With Regards,
Amit Kapila.