Making Postgres Bloom

#1

Post by usman-m » Thu, Jul 30, 2015, 6:17 PM UTC

Re: Making Postgres Bloom

#2

Post by mamikonyana » Thu, Jul 30, 2015, 6:30 PM UTC

When you say adding more online algorithms, is that mostly going to be limited to adding more probabilistic data structures?

Re: Making Postgres Bloom

#3

Post by zallarak » Thu, Jul 30, 2015, 6:31 PM UTC

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.

Re: Making Postgres Bloom

#4

Post by usman-m » Thu, Jul 30, 2015, 6:35 PM UTC

mamikonyana wrote: post #2

When you say adding more online algorithms, is that mostly going to be limited to adding more probabilistic data structures?

Mostly that. I've also been thinking about how we could incorporate some machine learning algorithms, like online perceptrons.

Re: Making Postgres Bloom

#5

Post by pbnjay » Thu, Jul 30, 2015, 6:42 PM UTC

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!

Redis comes with both bitfields (see http://redis.io/commands/bitcount) and hyperloglog counters (see http://redis.io/commands/pfcount), requires almost no setup and has very minimal overhead.

Re: Making Postgres Bloom

#6

Post by Zikes » Thu, Jul 30, 2015, 6:58 PM UTC

pbnjay wrote: post #5

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! Redis comes with both bitfields (see http://redis.io/commands/bitcount ) and hyperloglog counters (see http://redis.io/commands/pfcount ), requires almost no setup and has very minimal overhead.

Foreign Data Wrappers make that even easier: http://www.craigkerstiens.com/2012/10/18/connecting_to_redis...

Re: Making Postgres Bloom

#7

Post by ahachete » Thu, Jul 30, 2015, 7:28 PM UTC

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 (http://www.postgresql.org/docs/9.4/static/logicaldecoding.ht...) interface. Thank you

Re: Making Postgres Bloom

#8

Post by gleb » Thu, Jul 30, 2015, 7:39 PM UTC

The example doesn't quite make sense:

  SELECT user_id IN (SELECT DISTINCT user_id FROM user_actions);

is not valid SQL. You may mean something like:

  SELECT 123 IN (SELECT DISTINCT user_id FROM user_actions);

which is a strange query, as it's equivalent to:

  SELECT 123 IN (SELECT user_id FROM user_actions);

Re: Making Postgres Bloom

#9

Post by usman-m » Thu, Jul 30, 2015, 7:50 PM UTC

ahachete wrote: post #7

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 ( http://www.postgresql.org/docs/9.4/static/logicaldecoding.ht... ) interface. Thank you

ahachete, I'm not sure if I totally understand your question.

Continuous views are consumers for streams. You can think of them as high throughput real-time materialized views. The source of data for the stream can be practically anything. Logical decoding on the other hand is a producer of streaming data--it's basically a human readable replication log. So you could potentially stream the logically decoded log into PipelineDB and build some continuous views in front of it.

Re: Making Postgres Bloom

#10

Post by usman-m » Thu, Jul 30, 2015, 7:52 PM UTC

gleb wrote: post #8

The example doesn't quite make sense: SELECT user_id IN (SELECT DISTINCT user_id FROM user_actions); is not valid SQL. You may mean something like: SELECT 123 IN (SELECT DISTINCT user_id FROM user_actions); which is a strange query, as it's equivalent to: SELECT 123 IN (SELECT user_id FROM user_actions);

Oops, it was meant to be:

  SELECT  FROM (SELECT DISTINCT user_id FROM user_actions);

You're absolutely right that both those queries will give the same result. I guess I was trying to motivate the basic problem of finding whether some user exists in a set of users, and `SELECT DISTINCT` is the SQL way of representing a set.

Fixed the post, thanks!