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 1pPsnh-0006SC-19 for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 22:20:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPsnf-0003Ng-LA for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 22:20:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPsnf-0003N8-4n for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 22:20:23 +0000 Received: from mail-oi1-x232.google.com ([2607:f8b0:4864:20::232]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pPsnc-0002k7-Tj for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 22:20:22 +0000 Received: by mail-oi1-x232.google.com with SMTP id t5so131435oiw.1 for ; Wed, 08 Feb 2023 14:20:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=cIZ+IquXaCiGGP5P4l+cDy5d/vWbV0p/SfsE4spcUOo=; b=gys0/1PNM1uCxz4VUQZiQ6MmW5UrYZTwnXBLG7zNdmHYZrDiTDu1kShcd1KJBUCY1z UfB5a5QTQIX12rNPHDQEq84wT23MKNGWxBD+Iccf1Qhnh2CPkEJ6AzTXpLxP3mHL1jub EgvZAIFXucfmAgqo+ymFkWsPZteN6dzXPgCZmia2DjQDhW3Y59WBGV0mZQJkDLyAs3xG W+I0fN3/6MUn+Y+tZsXNMOsrijPSuit5FcnfpMVORED7hUdF+gma2KJiDaD9lvW5+VdU sx1T5/zIH6rPSoFZvOMvpJm85MJsE0lwvaTpz5LGqKqGlmATNbhVFJ8KinHkgwZAXirL PQQg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references: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=cIZ+IquXaCiGGP5P4l+cDy5d/vWbV0p/SfsE4spcUOo=; b=xFQ8Vj8GghlzzOcSAYI/8GwGQZsOVYKcisaoQH2eFQRVgoJUCprlhuXEwCuxtv/wue fA9gyE1L0APJ5pgs2dwgVTZDglO65d7SpmaMTSMt43WiaQ4XJIXFKoFa4DOAdB3NWopP 8by96A4LlA5MmgT/vf5B8hg4HCl88Vj1DoI0BXLwLy3BLR/lEN7GfUMjvmAhK7GpTea9 ynf3K6pSGx02+dMDjO9TohQ5cfPUOcwNnws17uMgXcHp137dykTmDvGtTZ9hgfizXa9h kb4jObGm74Kx82yEPQJXTYU0fRp1FhWcZCWMh/AcmoHmKqONyj42D3O9Hjzm30nRlEoG /rYg== X-Gm-Message-State: AO0yUKUehfc7PUcrwH5IA3OFOk4wBOUJSHp0rwjYdLCP/clK14LOZG5s 3ajN7d0a6MG71uKjJm3AFz2oLjrGr9o= X-Google-Smtp-Source: AK7set9QkC5rrSr8sPLJrgxyZ4MjYVfyjtRzADLSngB6LkOwy7z22j8plKg48sdocyEQU8I2NIMi6g== X-Received: by 2002:a05:6808:244:b0:378:c02:bd51 with SMTP id m4-20020a056808024400b003780c02bd51mr3785932oie.33.1675894819799; Wed, 08 Feb 2023 14:20:19 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id v7-20020a056808004700b003785996ef36sm7642213oic.19.2023.02.08.14.20.19 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 08 Feb 2023 14:20:19 -0800 (PST) Content-Type: multipart/alternative; boundary="------------hHIjKUgjJqBAFcgoNSDn0Awn" Message-ID: <7f0732ff-c739-3d42-a8e7-d9779f415f94@gmail.com> Date: Wed, 8 Feb 2023 16:20:18 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: How to use the BRIN index properly? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <4CCC40F6-D1FF-4446-B50C-26A8843E6EE4@thebuild.com> 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. --------------hHIjKUgjJqBAFcgoNSDn0Awn Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 1. The whole index does not need to fit in memory, just the parts of it you need at that time. 2. Partition the table by the primary key.  Each index will be *much* smaller, since each child will be smaller. On 2/8/23 16:14, Siddharth Jain wrote: > OK so in that case we are left with the B-Tree index. > > If the B-Tree index will be so large that it cannot fit in memory, then is > it worth creating it at all? Are there any established patterns here? > > On Wed, Feb 8, 2023 at 1:21 PM Christophe Pettus wrote: > > > > > On Feb 8, 2023, at 13:17, Siddharth Jain wrote: > > > > As I explained in my question that is indeed our dilemma. Our > insertion order will not be equal to index order. i.e., referring to > your response: > > > > > who's data is added in the same order as the key in the BRIN index > > > > does NOT hold. > > A BRIN index is not a good choice in this case.  You can CLUSTER the > data on an index, but that's a one-time operation: PostgreSQL will not > maintain that order after the CLUSTER.  If the number of rows in the > table at the time of the CLUSTER is much larger than the number that > are inserted between CLUSTER operations, then a BRIN index might be > useful, but clustering a very large table is an expensive operation, > and requires an exclusive lock on the table while it is being done. > -- Born in Arizona, moved to Babylonia. --------------hHIjKUgjJqBAFcgoNSDn0Awn Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 1. The whole index does not need to fit in memory, just the parts of it you need at that time.
2. Partition the table by the primary key.  Each index will be much smaller, since each child will be smaller.

On 2/8/23 16:14, Siddharth Jain wrote:
OK so in that case we are left with the B-Tree index.

If the B-Tree index will be so large that it cannot fit in memory, then is it worth creating it at all? Are there any established patterns here?

On Wed, Feb 8, 2023 at 1:21 PM Christophe Pettus <xof@thebuild.com> wrote:


> On Feb 8, 2023, at 13:17, Siddharth Jain <siddhsql@gmail.com> wrote:
>
> As I explained in my question that is indeed our dilemma. Our insertion order will not be equal to index order. i.e., referring to your response:
>
> > who's data is added in the same order as the key in the BRIN index
>
> does NOT hold.

A BRIN index is not a good choice in this case.  You can CLUSTER the data on an index, but that's a one-time operation: PostgreSQL will not maintain that order after the CLUSTER.  If the number of rows in the table at the time of the CLUSTER is much larger than the number that are inserted between CLUSTER operations, then a BRIN index might be useful, but clustering a very large table is an expensive operation, and requires an exclusive lock on the table while it is being done.

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