I need to calculate reach, grouped by different criterions, and would prefer to keep everything in my database. I'm looking for either a pre-built bitset implementation for PostgreSQL, or pointers on how I may achieve my goal.

What I'm currently doing is storing serialized Java instances of EWAHCompressedBitSet in bytea columns, but this means I have to round-trip to a Java process to continue my calculations.

Ideally, I'd like to be able to do the following:

-- Unique people reached during a time window
SELECT   service_name, last_updated_at, bitset_or(followers_bs)
FROM     followers
GROUP BY service_name, last_updated_at

I have used a commercial solution (Truviso) that had such a feature, and it was based on PostgreSQL as well.

Note that I do have a table that has the data in an uncompressed format, but because the data is so huge (10M rows and counting), JOINing on it simply takes too much time than I want to devote to it.

  • 1
    JOINing is what databases are born to do - and 10M rows is not huge it is tiny. This makes me think there is something wrong with your model - can you post more details about your normalised data that is too slow? Dec 5, 2011 at 23:14
  • True enough, but on my dev machine of 4 GiB and 2 CPUs, that's causing me considerable grief. Having more RAM will help, obviously. Dec 5, 2011 at 23:56
  • 2
    Do bitstrings or arrays of bits work for you? I'm not that familiar with using those kinds of data types, but there are definitely bit operators in pgsql to let you play with things like bit strings, so it's worth a look. Dec 9, 2011 at 18:30

1 Answer 1


Can you use the "bit" type instead of "bytea", and use your own user-defined aggregate?

postgres=> CREATE AGGREGATE bitset_or ("bit")
postgres-> ( sfunc    = bitor,
postgres->   stype    = "bit",
postgres->   initcond =   '0'  );

Now bitset_or should work with your query as written (again, assuming bytea -> bit).

  • Good thought. Haven't had a chance to try it yet, but it might be the way to go for me. Thanks! Jan 4, 2012 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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