Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rEFTm-004kPB-OO for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Dec 2023 21:12:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rEFTl-005boZ-Bz for pgsql-hackers@arkaria.postgresql.org; Fri, 15 Dec 2023 21:12:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rEFTk-005bnI-U1 for pgsql-hackers@lists.postgresql.org; Fri, 15 Dec 2023 21:12:17 +0000 Received: from mail-lj1-x235.google.com ([2a00:1450:4864:20::235]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rEFTh-00CBmw-J4 for pgsql-hackers@postgresql.org; Fri, 15 Dec 2023 21:12:16 +0000 Received: by mail-lj1-x235.google.com with SMTP id 38308e7fff4ca-2c9f72176cfso12814641fa.2 for ; Fri, 15 Dec 2023 13:12:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1702674732; x=1703279532; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=A2kZnzm2js3kRCyJole8kpc9s69nbl6OA3LzSqyBpoE=; b=gtkTz1jwnlnIEAX15Pa3L26W33NbEs5rlAEEREULvwTS/Dpr6KlqC+dScq5az2+qpB Eg1/61jk8WTvSbwxgWvSEcWUKns7B/amc4izS+vbBhDtpSFBODadxpAKOH9PxrpkZgWf 1hfgfnb0X75UtGrBS5a7iiCYFsvo7qiUXvVJHh8xijSa1NGUGE9nNlLrB8nETzmSRoix r60U6S4w/FBRNGgry9sB7LDF3YNQMiQzmZA1opklnRIcQjs/u6f3fgCeaxCjCL5PaHyU SOkf652zEslkvShBcZaWBzZ6nQ7XgA/ubwop2dqAiQJUtDFqGcRgyOfOakmwxZHMKIjx d5uw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1702674732; x=1703279532; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=A2kZnzm2js3kRCyJole8kpc9s69nbl6OA3LzSqyBpoE=; b=INhKoV0BFKLt9vPPwFh2Yze26bQa8JO60Fcp0hfOihLBnrSGp9Gwh7C0Qt+wXFqmpH EZZflTCVBp+MIvwXn1NlyccR2ySIKPyeiK23k2TBfuHIO38BCS+tnVfJPHIkb+MDRuFI b4SyLnVyFPcQzARIoH6onS4QQzI+4G09hzOONpkE5JaTJVIfbBC8UmeoIucGE/8a/o7b e/hS8awrJc0fS+nt40veKwkPY3ARhRfOzTWvoVhH0L5lodKQqK5mCpXmkC5Nq21jjF1z 3Vvlk24MAYlz/VHlob76/hfI8Jxc3cHdoq0A8GsWdnr8kCff5yDPXeOdGOrrlAqvjgB/ LAzg== X-Gm-Message-State: AOJu0YzO5L1QUZ0OoxW11jcSNJ9BeJfNTG0MWwOK6ANc02nW60Bt1z4j yyg9BJJdB/nFzinCbxJyV5vavupYyoNHwADmqy0= X-Google-Smtp-Source: AGHT+IEQIyV1zJfU+FaqvbZn/WwHlwSmUilDCkJGwK8RNDJBfg1aIHb6/VUdVqBZfFRJtdr77SIiKdNiEl/P2EaqsbM= X-Received: by 2002:a2e:9954:0:b0:2cc:5945:4e22 with SMTP id r20-20020a2e9954000000b002cc59454e22mr89948ljj.85.1702674731609; Fri, 15 Dec 2023 13:12:11 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Matthias van de Meent Date: Fri, 15 Dec 2023 22:11:59 +0100 Message-ID: Subject: Re: Revisiting {CREATE INDEX, REINDEX} CONCURRENTLY improvements To: Michail Nikolaev Cc: PostgreSQL Hackers , Alvaro Herrera Content-Type: multipart/alternative; boundary="0000000000008bb58a060c92da57" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008bb58a060c92da57 Content-Type: text/plain; charset="UTF-8" On Fri, 15 Dec 2023, 20:07 Michail Nikolaev, wrote: > Hello, hackers! > > I think about revisiting (1) ({CREATE INDEX, REINDEX} CONCURRENTLY > improvements) in some lighter way. > > Yes, a serious bug was (2) caused by this optimization and now it reverted. > > But what about a more safe idea in that direction: > 1) add new horizon which ignores PROC_IN_SAFE_IC backends and standbys > queries > 2) use this horizon for settings LP_DEAD bit in indexes (excluding > indexes being built of course) > > Index LP_DEAD hints are not used by standby in any way (they are just > ignored), also heap scan done by index building does not use them as > well. > > But, at the same time: > 1) index scans will be much faster during index creation or standby > reporting queries > 2) indexes can keep them fit using different optimizations > 3) less WAL due to a huge amount of full pages writes (which caused by > tons of LP_DEAD in indexes) > > The patch seems more-less easy to implement. > Does it worth being implemented? Or to scary? > I hihgly doubt this is worth the additional cognitive overhead of another liveness state, and I think there might be other issues with marking index tuples dead in indexes before the table tuple is dead that I can't think of right now. I've thought about alternative solutions, too: how about getting a new snapshot every so often? We don't really care about the liveness of the already-scanned data; the snapshots used for RIC are used only during the scan. C/RIC's relation's lock level means vacuum can't run to clean up dead line items, so as long as we only swap the backend's reported snapshot (thus xmin) while the scan is between pages we should be able to reduce the time C/RIC is the one backend holding back cleanup of old tuples. Kind regards, Matthias van de Meent Neon (https://neon.tech) > [1]: https://postgr.es/m/20210115133858.GA18931@alvherre.pgsql > [2]: https://postgr.es/m/17485-396609c6925b982d%40postgresql.org > > > --0000000000008bb58a060c92da57 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, 15 Dec 2023, 20:07 Michail Nikolaev, <michail.nikolaev@gmail.com> wrote:
Hello, hackers!

I think about revisiting (1) ({CREATE INDEX, REINDEX} CONCURRENTLY
improvements) in some lighter way.

Yes, a serious bug was (2) caused by this optimization and now it reverted.=

But what about a more safe idea in that direction:
1) add new horizon which ignores PROC_IN_SAFE_IC backends and standbys quer= ies
2) use this horizon for settings LP_DEAD bit in indexes (excluding
indexes being built of course)

Index LP_DEAD hints are not used by standby in any way (they are just
ignored), also heap scan done by index building does not use them as
well.

But, at the same time:
1) index scans will be much faster during index creation or standby
reporting queries
2) indexes can keep them fit using different optimizations
3) less WAL due to a huge amount of full pages writes (which caused by
tons of LP_DEAD in indexes)

The patch seems more-less easy to implement.
Does it worth being implemented? Or to scary?
<= div dir=3D"auto">
I hihgly doubt this is worth t= he additional cognitive overhead of another liveness state, and I think the= re might be other issues with marking index tuples dead in indexes before t= he table tuple is dead that I can't think of right now.

I've thought about alternative so= lutions, too: how about getting a new snapshot every so often?=C2=A0
<= div dir=3D"auto">We don't really care about the liveness of the already= -scanned data; the snapshots used for RIC are used only during the scan. C/= RIC's relation's lock level means vacuum can't run to clean up = dead line items, so as long as we only swap the backend's reported snap= shot (thus xmin) while the scan is between pages we should be able to reduc= e the time C/RIC is the one backend holding back cleanup of old tuples.

Kind regards,

Matthias van de Meent

--0000000000008bb58a060c92da57--