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 1rpNTS-006Q2C-Ep for pgsql-hackers@arkaria.postgresql.org; Wed, 27 Mar 2024 07:13:26 +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 1rpNTQ-00BKus-Br for pgsql-hackers@arkaria.postgresql.org; Wed, 27 Mar 2024 07:13:24 +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 1rpNTQ-00BKuj-2W for pgsql-hackers@lists.postgresql.org; Wed, 27 Mar 2024 07:13:24 +0000 Received: from mail-lf1-x12c.google.com ([2a00:1450:4864:20::12c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rpNTN-006idT-0I; Wed, 27 Mar 2024 07:13:23 +0000 Received: by mail-lf1-x12c.google.com with SMTP id 2adb3069b0e04-513d717269fso7941463e87.0; Wed, 27 Mar 2024 00:13:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1711523599; x=1712128399; darn=postgresql.org; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date:from:to :cc:subject:date:message-id:reply-to; bh=R6zi019ETyOVgoFnQNqYmfET4+pEYDaIr6yD7YE5MtQ=; b=PCtDkIizzeDhytXuJ2fP3EeI4zbGXIQ1CfY7xypZjdoCxXwZgnzTnaNJ4+b+2FQzff LTswIOI+HqwSczyYQ3fCPup9VnDda8HRPeEb3KwjYXEkauVIk2Vrr/Unq35zAtkcXcZP DT3+Z522GCuav1IyXVMzfwTVFN5HjPAN/jBZqrsN5LeuMikahUS43uniEJmndxhLItao 7ewpO1BTgqViQBY9D6MbLmW+rCun8KKHsXVclsXYcuEcxx51pDZIC8/svmyNszM8vkH0 DTAaWNcBqybX695x4VuytRc21ed83qOJTTnnVt1vgdrBb/8YKwhBW9meTulVJug3Ekog Wycg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711523599; x=1712128399; h=in-reply-to:content-transfer-encoding:content-disposition :mime-version:references:message-id:subject:cc:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=R6zi019ETyOVgoFnQNqYmfET4+pEYDaIr6yD7YE5MtQ=; b=enrENkK9AlT/nBcyDC+gdHWG62+ev7Sndr6hyuydpSiRAJcAorgojLgbWio7Ck4uYk yFAL8h5NsJiHYHvn17sevR10y3m5k+OvQzmH1htOIjBmTPjfXDWVnYgVAg5RgltywqFp B8wdAKrMpDp2gWWn0pMxNs+xCWz8S8W1LQ4zOF6atr4nGQbpKhm0VxH8gpzdE6H1/+xv sjLuwsmv9UDx17qekbKRzIximy2GOSAVqSIBL0aQNXoouu/e1Mz5V8BgpVE19LiKOvyW BJ/Y8SkF0/tEag97YL9RHumhDBTesa8vUk82RxvgTYJWlVY+Qj7xR246xWaOMb6qzzdw Mtzg== X-Forwarded-Encrypted: i=1; AJvYcCV0oIxYeTa7CxYbKoxvy7PQQCrMlN9NqHC1cUYInvQh+oHNveNz+pFAUc636qZlKBjgG9wo9j3MdQjHLAgkMnQABUJeGiXQfv9r5Oe9o1bg66H5YFMag2CIWoCE6GTtrpewU3PF X-Gm-Message-State: AOJu0YwGnBXzWvDod4GHxVi7jD9MGskx6MgW73rftjd0hurvmJ11faD+ JmFf8bSCZbflLs8wuDOQ2bVWdTiR8ZnkkywH2IkADXED+px6+t+3 X-Google-Smtp-Source: AGHT+IExBLh2D5U6egiICiZbRQkQSMTUW98sJ2OMCopeAC5f1loYRJy5Icii3MkTJqY45L/SUUSM9A== X-Received: by 2002:a19:ee18:0:b0:515:a417:331 with SMTP id g24-20020a19ee18000000b00515a4170331mr1098384lfb.9.1711523598812; Wed, 27 Mar 2024 00:13:18 -0700 (PDT) Received: from ip-10-97-1-34.eu-west-3.compute.internal (ec2-15-236-134-5.eu-west-3.compute.amazonaws.com. [15.236.134.5]) by smtp.gmail.com with ESMTPSA id t13-20020adfe10d000000b00341cb49f83bsm8275067wrz.79.2024.03.27.00.13.17 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 27 Mar 2024 00:13:17 -0700 (PDT) Date: Wed, 27 Mar 2024 07:13:16 +0000 From: Bertrand Drouvot To: Bharath Rupireddy Cc: Amit Kapila , Alvaro Herrera , Nathan Bossart , Robert Haas , shveta malik , Amit Kapila , "pgsql-hackers@postgresql.org" Subject: Re: pgsql: Track last_inactive_time in pg_replication_slots. Message-ID: References: <20240326150918.GB3181099@nathanxps13> <202403261539.xcjfle7sksz7@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On Wed, Mar 27, 2024 at 12:28:06PM +0530, Bharath Rupireddy wrote: > On Wed, Mar 27, 2024 at 10:10 AM Amit Kapila wrote: > > > > On Tue, Mar 26, 2024 at 9:10 PM Alvaro Herrera wrote: > > > > > > On 2024-Mar-26, Nathan Bossart wrote: > > > > > > > FWIW I'd really prefer to have something like max_slot_xid_age for this. A > > > > time-based parameter would likely help with most cases, but transaction ID > > > > usage will vary widely from server to server, so it'd be nice to have > > > > something to protect against wraparound more directly. > > > > > > Yeah, I tend to agree that an XID-based limit makes more sense than a > > > time-based one. > > > > > So, do we want the time-based parameter or just max_slot_xid_age > > considering both will be GUC's? Yes, the xid_age based parameter > > sounds to be directly helpful for transaction ID wraparound or dead > > row cleanup, OTOH having a lot of inactive slots (which is possible in > > use cases where a tool creates a lot of slots and forgets to remove > > them, or the tool exits without cleaning up slots (say due to server > > shutdown)) also prohibit removing dead space which is not nice either? > > I've personally seen the leftover slots problem on production systems > where a timeout based invalidation mechanism could have been of more > help to save time and reduce manual intervention. Usually, most if not > all, migration/upgrade/other tools create slots, and if at all any > errors occur or the operation gets cancelled midway, there's a chance > that the slots can be leftover if such tools forgets to clean them up > either because there was a bug or for whatever reasons. These are > unintended/ghost slots for the database user unnecessarily holding up > resources such as XIDs, dead rows and WAL; which might lead to XID > wraparound or server crash if unnoticed. Although XID based GUC helps > a lot, why do these unintended and unnoticed slots need to hold up the > resources even before the XID age of say 1.5 or 2 billion is reached. > > With both GUCs max_slot_xid_age and replication_slot_inactive_timeout > in place, I can set max_slot_xid_age = 1.5 billion or so and also set > replication_slot_inactive_timeout = 2 days or so to make the database > foolproof. Yeah, I think that both makes senses. The reason is that one depends of the database activity and slot activity (the xid age one) while the other (the timeout one) depends only of the slot activity. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com