What is the best way to be store, query & index a column containing around 100k bits?

I am attempting to do it creating a table like so:

CREATE TABLE public.search_bf
    id serial,
    bf integer ARRAY[3125]

So column bf would have 3125 32bit integers which would give me the neccessary bits. I'm now trying to find a way of indexing and querying this.

I would like to use intarray but this doesn't work for bits.

I could use intarray if I just created a column integer ARRAY[100000] but this would be really inefficent in terms of storage.

  • How do you want to query the table? – Laurenz Albe Jul 13 '20 at 15:59
  • I'll be testing a single bit of the data making sure that it equals 0 – owlbear Jul 14 '20 at 10:56

The solution I'm moving ahead with is to have an smallint[] which esentially gives me a total of 65k unique values / bits. I will then store the index of the bits that are 'on'. This won't be very efficent in terms of storage if densely populated. I could use int4 for higher indexes but this would consume even more space.

I can query it for off bits using @> operator.

I should be able to index this with a GIN index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.