Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pG7E0-0004bX-Lc for pgsql-admin@arkaria.postgresql.org; Thu, 12 Jan 2023 23:43:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pG7Dz-0002sG-4o for pgsql-admin@arkaria.postgresql.org; Thu, 12 Jan 2023 23:43:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pG7Dy-0002s6-N3 for pgsql-admin@lists.postgresql.org; Thu, 12 Jan 2023 23:43:10 +0000 Received: from mail-qt1-x82b.google.com ([2607:f8b0:4864:20::82b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pG7Dw-0001YM-6e for pgsql-admin@lists.postgresql.org; Thu, 12 Jan 2023 23:43:10 +0000 Received: by mail-qt1-x82b.google.com with SMTP id jr10so10571731qtb.7 for ; Thu, 12 Jan 2023 15:43:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=8mOt0wXWDamkl6kBdTDmYGK+pnyN0FLKcfyted4IIz4=; b=Qm0stLBgxURbgvpDccuQION5aMbWhvFuH3rIhnMV7OqKgfKesjMyDtpmpSOn5zr1gI 2eAbBg4tDXj/s5KKIO6VXBDP5qxqOjFz8n1rTWFtMeVOVYsWvG4xW+n7/Rs+wW39X5W3 qruBxVBpAj8zsELAX1ArBrmEepP4/EZQXizQI0i2I0DkD6fADDpi+acpkKomxrRNNf6H jWEhK1BoefbW1QUi6t8SLPgAVLJa+41KbKYuYSKPABA7bT4QmJqioQhQ+Wd7rZfD0HZH YXnXHZTdbf4AB0+ky3smgRuxjZkNjzzbGTVIJw2wtjhIPCJmpTFKvpOxvwu4qCMVkS45 l8VQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=8mOt0wXWDamkl6kBdTDmYGK+pnyN0FLKcfyted4IIz4=; b=hCy/IT6j6Obd1K1QoLoLjl98pDB7LTvt8ixzpt3rA4VGjF9WlHvA8ZlfOveAHZvU1v UNu5bxoAj7OiN2YbDjN7DWNktdgXLuWVGFSQI1mC/HS6KTPBuzCVCTamW7T/yPwOusff ZfZ/qPiXna+usilFzL3AvK0+NSWTqcLd2KmR/DHjdHbHYM6IsUGBG70d4qVEOgwJM615 ObfmieCkVRr72EJpvLCttgTi2OF/6Bh86hj+8cX7eQlYGZrAEdflqyOHJ/OfvdXkhAuw A1kzl6RzVGJTA+YJlDnMleZLp2+7eXPnI/KMkD4geFfNuJaNVmKsJ41AJxcFlbCeobDr +KNA== X-Gm-Message-State: AFqh2kp34meVz6p8Uuz2B1HvurG0QVZoT5fmzC+6FIBf2zHCa7Idwq4W SbXRaV/Tlh3yQ8yt+f5vgSMT7kxFpLYfSg== X-Google-Smtp-Source: AMrXdXsIorOgD9XOGGe8y+4M/iGeFMedDLFmDzhyFY73k4tEYHIs8m2GWU84avNPUnURAjmY1Iz7fw== X-Received: by 2002:a05:622a:250d:b0:3ab:5f5f:b7c4 with SMTP id cm13-20020a05622a250d00b003ab5f5fb7c4mr116207214qtb.24.1673566986555; Thu, 12 Jan 2023 15:43:06 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id bx6-20020a05622a090600b003a81029654csm9859362qtb.31.2023.01.12.15.43.06 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 12 Jan 2023 15:43:06 -0800 (PST) Content-Type: multipart/alternative; boundary="------------lN0lc3Y7tswvtI4up6ZzQnta" Message-ID: <4efb3483-dc9d-66ec-1190-25b58d5886cb@gmail.com> Date: Thu, 12 Jan 2023 17:43:05 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Synthetic keys and index fillfactor Content-Language: en-US To: "David G. Johnston" Cc: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------lN0lc3Y7tswvtI4up6ZzQnta Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/12/23 16:54, David G. Johnston wrote: > On Thu, Jan 12, 2023 at 3:45 PM Ron wrote: > > > (This mostly pertains to recreating a PK on an existing table.) > > Is there any reason to have the PK index on an ever-increasing field (for > example SERIAL, sequence or timestamp fed by clock_timestamp() at time > zone > 'UTC') be anything but fillfactor=100? > > New records will always be added to the "lower right hand corner" of the > tree, so having 20% empty space in the rest of the tree would just waste > space (mainly buffers, but disk space could even start to add up on > Very Big > Tables). > > > Yes, at least that is what I gather from the advice on the CREATE INDEX page. > > https://www.postgresql.org/docs/15/sql-createindex.html > "even a few updates or inserts will cause a sudden flood of page splits" shouldn't matter for ever-increasing PK values, since you're always filling that bottom right corner anyway. This might be a killer if the applications update many indexed fields: https://www.postgresql.org/docs/15/btree-implementation.html#BTREE-DELETION "Changing the value of only one column covered by one index during an|UPDATE|/always/necessitates a new set of index tuples — one for/each and every/index on the table." -- Born in Arizona, moved to Babylonia. --------------lN0lc3Y7tswvtI4up6ZzQnta Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 1/12/23 16:54, David G. Johnston wrote:
On Thu, Jan 12, 2023 at 3:45 PM Ron <ronljohnsonjr@gmail.com> wrote:

(This mostly pertains to recreating a PK on an existing table.)

Is there any reason to have the PK index on an ever-increasing field (for
example SERIAL, sequence or timestamp fed by clock_timestamp() at time zone
'UTC') be anything but fillfactor=100?

New records will always be added to the "lower right hand corner" of the
tree, so having 20% empty space in the rest of the tree would just waste
space (mainly buffers, but disk space could even start to add up on Very Big
Tables).


Yes, at least that is what I gather from the advice on the CREATE INDEX page.

https://www.postgresql.org/docs/15/sql-createindex.html


"even a few updates or inserts will cause a sudden flood of page splits" shouldn't matter for ever-increasing PK values, since you're always filling that bottom right corner anyway.

This might be a killer if the applications update many indexed fields:
https://www.postgresql.org/docs/15/btree-implementation.html#BTREE-DELETION
"Changing the value of only one column covered by one index during an UPDATE always necessitates a new set of index tuples — one for each and every index on the table."

--
Born in Arizona, moved to Babylonia.
--------------lN0lc3Y7tswvtI4up6ZzQnta--