For membership/equality predicates (also partial, for multiple columns) you could take a look at bloom indexes: they are quite efficient in terms of space footprint, you can even choose how long is the signature for each entry and how is distributed among the columns. https://www.postgresql.org/docs/14/bloom.html g
On Wed, 8 Feb 2023 at 23:15, Siddharth Jain <siddh...@gmail.com> 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 <x...@thebuild.com> wrote: > >> >> >> > On Feb 8, 2023, at 13:17, Siddharth Jain <siddh...@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. > >
