The example doesn't quite make sense:<p><pre><code> SELECT user_id IN (SELECT DISTINCT user_id FROM user_actions);
</code></pre>
is not valid SQL. You may mean something like:<p><pre><code> SELECT 123 IN (SELECT DISTINCT user_id FROM user_actions);
</code></pre>
which is a strange query, as it's equivalent to:<p><pre><code> SELECT 123 IN (SELECT user_id FROM user_actions);</code></pre>
Don't get me wrong, I love Postgres and use it in pretty much all of my projects... but for this kind of application it's not very well suited. Leave your relational data for the database and use something more efficient!<p>Redis comes with both bitfields (see <a href="http://redis.io/commands/bitcount" rel="nofollow">http://redis.io/commands/bitcount</a>) and hyperloglog counters (see <a href="http://redis.io/commands/pfcount" rel="nofollow">http://redis.io/commands/pfcount</a>), requires almost no setup and has very minimal overhead.
Semi-related in the land of Postgres and probabilistic data structures -- Redshift supports APPROXIMATE COUNT. Much, much faster than a raw COUNT, and their stated error is +-2%<p><a href="http://docs.aws.amazon.com/redshift/latest/dg/r_COUNT.html" rel="nofollow">http://docs.aws.amazon.com/redshift/latest/dg/r_COUNT.html</a>
Internally using hashtext(), which is not a good idea for a bloom filter for a few reasons, one of which is <a href="http://www.postgresql.org/message-id/CABUevExTx2whgSpKaoMVowDxBe=pm7w4LJkb=-k8NTohQT12Kg@mail.gmail.com" rel="nofollow">http://www.postgresql.org/message-id/CABUevExTx2whgSpKaoMVow...</a>
The idea of using probabilistic data structures instead of counting every point of data (for things like customer analytics) is pretty significant -- getting caught in the weeds of managing every data point is error-prone and inefficient.
usman-m, the approach of PipelineDB seems really interesting. However, I'd like to understand how in your opinion it compares with processing the stream of data changes accessed over PostgreSQL's logical decoding (<a href="http://www.postgresql.org/docs/9.4/static/logicaldecoding.html" rel="nofollow">http://www.postgresql.org/docs/9.4/static/logicaldecoding.ht...</a>) interface. Thank you