Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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? –  Jack Douglas Dec 5 '11 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. –  François Beausoleil Dec 5 '11 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. –  Scott Marlowe Dec 9 '11 at 18:30
add comment

1 Answer

up vote 3 down vote accepted

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).

share|improve this answer
    
Good thought. Haven't had a chance to try it yet, but it might be the way to go for me. Thanks! –  François Beausoleil Jan 4 '12 at 13:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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