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 1u8C43-00FYvn-Kl for pgsql-admin@arkaria.postgresql.org; Fri, 25 Apr 2025 05:57:32 +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 1u8C40-0079hG-6O for pgsql-admin@arkaria.postgresql.org; Fri, 25 Apr 2025 05:57:29 +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 1u8C3z-0079h7-Pk for pgsql-admin@lists.postgresql.org; Fri, 25 Apr 2025 05:57:28 +0000 Received: from mail-wm1-x330.google.com ([2a00:1450:4864:20::330]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u8C3x-001udJ-0Z for pgsql-admin@lists.postgresql.org; Fri, 25 Apr 2025 05:57:27 +0000 Received: by mail-wm1-x330.google.com with SMTP id 5b1f17b1804b1-4394a823036so17149475e9.0 for ; Thu, 24 Apr 2025 22:57:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec.at; s=google; t=1745560645; x=1746165445; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=TKV52YX4QC6t+od35cBddqT/8a1HCfJJit7oor9/cpw=; b=aABE7m+6BnYMXOnhEcQLTWrdDdA0Wsmh79dNGFzEsLnYhUGjIffehUdjTqvvVXMU3q 6u3MOASXMBfK9ceha2KzRoDy4R8dv05+oI/O/4/t6qIR+rm1h3MJ8rrYYidzDZhpc6BW C3x4gq5TCVbgZfEdTsXAsn/7h8X8IlFPEU/eAPQIpfsBiUFtgrj+rOyu254y6fq+OlQh QBhggbCAf3+LLCTX6FYRGRI1D95/8H5dsBhttSEMKvOEW6a5fa4cCSrGUHPsk+r1YQvj hYSEmOYCi8pP+04bZ4RWvcLPfM/iEf2IuQW8M8vw7sxB7OSRqiYVU3NUbNG94OioIM8k affQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745560645; x=1746165445; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=TKV52YX4QC6t+od35cBddqT/8a1HCfJJit7oor9/cpw=; b=b/vtPTwv+K0pKvFvXh2Lxo588irsDWehqyfSbcThhy+fjFKdoxbB07wdNtLQAyrZjq fl/yIfdCJO9UUcxpnBCSTYxKJCWjw1zn1JFJBxFRGLD1YLcAwoBUIbbbYQ0q5SUkG3sP y0/6QzRi2FjBrWsSBGSMjJj1D1Yo41HuCaIUFQasgNm+C8cSrTmHW7hoeLup5D0ZQ7TF +cTikT8tuNkJZqIWBF9IzP6wxGQXeGVfO2nJafr/l4YNfV0SBt4DVA6a0WQqLKBF4kyc gMUYtP/yGH8yOdvbVFhZyzaaV52StTU0FMTI7DwOMtFi8sWSJ4nu4kiQ3gZZyNw3CEEL +Fxw== X-Forwarded-Encrypted: i=1; AJvYcCVqrytptWhpokbSGsFpDlm1MafLuaU3vhEu7Q9xlxWFU9iJg8bf/xcTS+mg5lPYFWCx2ml57N0159+raw==@lists.postgresql.org X-Gm-Message-State: AOJu0Yx7R505+ufhxQ0HZCzg30VLOie7yVUn864Mi1ZwfAiVf9+1PV67 spDekp27Er1nPgy8+STR8SUWt2S6UnENTR6KnE/mGo3UMGBFkAD+CQnSvifD1AA= X-Gm-Gg: ASbGncsa1CLpBcpPMb3AyffFJlUAicPvrX03DGZ/i8nekEK2XF4J1akC494JezWfZ+i 2CWLdXDAQ0/vkkGdIY0UvJ1UwdV3yLa4g1njJyTpk8TkPcKPvRcecqdK38Vhv9MT2YK/CLDX5IN NwHbU+LB5PP2DzqMj5H/gBVVWY4MsvvjxgU8uJ+tSnZW9RLSy/8FtK7ssaoZsRlhdXbN7vE3Gnt diO/CL1NDNpvdmVV5pt2PFRd/O1AXVC7RfofIVTRRErCNccNwG4buQAbtxbGyZEHmndv1pOe5vU megyjne+cMPVAlIKQM+nZdsR9UcZGYXJ1kX2hyRjWqEUVSLTSTijXCvDpvNrwWZMkoDfMCs= X-Google-Smtp-Source: AGHT+IHRRIlB0VgAsAsL7WJW52BbVtzD7As6aCj0BdLQV8cPmk/tNnRsKv/PciJpkurouq1BHzKnrw== X-Received: by 2002:a05:600c:354a:b0:439:9b2a:1b2f with SMTP id 5b1f17b1804b1-440a64c14afmr5725605e9.3.1745560644781; Thu, 24 Apr 2025 22:57:24 -0700 (PDT) Received: from localhost.localdomain ([2001:871:260:e754:5e12:787:a896:2a2e]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-4409d29b990sm47176275e9.4.2025.04.24.22.57.24 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 24 Apr 2025 22:57:24 -0700 (PDT) Message-ID: <6a9ed5b7ca2f7c1ba61f27a969cebcbd50cff4b6.camel@cybertec.at> Subject: Re: Reindexing From: Laurenz Albe To: Wasim Devale , Pgsql-admin , pgsql-admin Date: Fri, 25 Apr 2025 07:57:23 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2025-04-25 at 10:46 +0530, Wasim Devale wrote: > I want to automate the reindexing and planning to do it concurrently twic= e a week. That's probably unnecessary. > Can someone highlight at what percentage of index bloat can decide the fr= equency of reindexing? It is perfectly normal for a B-tree index to have an "avg_leaf_density" of = 30 or so, as determined with pgstatindex(). Consider reindexing only for indexes tha= t are doing worse *and that keep deteriorating*. Yours, Laurenz Albe