We have been running a similar setup (Postgres -> Foreign Data Wrappers -> LLVM) at AdRoll for over a year. We keep 100TBs+ of raw data in memory, compressed.
We managed to build our solution mostly in Python(!) using Numba for JIT and a number of compression tricks. More about it here:
I saw an instance of your talk at another venue. At the time I was learning about bitmap indexes (because we needed OLAP capabilities on a another database engine that is not postgres). Your talk reminded me furiously of them, but in the end I was not able to further investigate the difference.
How does your technology differ from bitmap indexes? Have you solved the performance problem of updating random rows, for example?
Combining OLAP without OLTP (large aggregate queries + lots of real time updates) is the holy grail that Cassandra, for example, has addressed rather nicely.
Compressed bitmap indexes are awesome. Like most indexes, the updating random rows problem is best addressed using a log structured merge tree and amortizing your index updates. Just have an in-memory buffer of recently updated rows.
If you are doing mostly sums & counts type work and can deal with some level of inaccuracy, you can consider HyperLogLog...
You mentioned that there are some regularities in different data sets that can be used to increase the efficiency of their encoding. Does this mean that you need to write a different foreign data wrapper for each data set?
Did you do anything about join pushdown (which isn't supported in core PostgreSQL yet)? Apologies if this is in your talk - I looked at the slides and couldn't see anything.
Yeah, lack of aggregation function and join pushdown is annoying.
Our data model makes sure that we don't have to do huge joins on the fly, which would be a bad idea anyways. We have a workaround to distribute medium-scale joins that occur frequently. Small joins are handled fine by Postgres as usual.
I would be curious to learn more about Vitesse. I couldn't find many technical details besides the mailing list post.
It's not as easy as it could be but you can accomplish aggregate pushdown by using executor hooks in your FDW. As an example I'd point you to our (Citus Data) recent work on doing this with our columnar store foreign data wrapper:
Comments
We have been running a similar setup (Postgres -> Foreign Data Wrappers -> LLVM) at AdRoll for over a year. We keep 100TBs+ of raw data in memory, compressed.
We managed to build our solution mostly in Python(!) using Numba for JIT and a number of compression tricks. More about it here:
http://tuulos.github.io/pydata-2014/#/
https://www.youtube.com/watch?v=xnfnv6WT1Ng
I saw an instance of your talk at another venue. At the time I was learning about bitmap indexes (because we needed OLAP capabilities on a another database engine that is not postgres). Your talk reminded me furiously of them, but in the end I was not able to further investigate the difference.
How does your technology differ from bitmap indexes? Have you solved the performance problem of updating random rows, for example?
Combining OLAP without OLTP (large aggregate queries + lots of real time updates) is the holy grail that Cassandra, for example, has addressed rather nicely.
Compressed bitmap indexes are awesome. Like most indexes, the updating random rows problem is best addressed using a log structured merge tree and amortizing your index updates. Just have an in-memory buffer of recently updated rows.
If you are doing mostly sums & counts type work and can deal with some level of inaccuracy, you can consider HyperLogLog...
Our DeliRoll is used for analytics. It doesn't support random updates, it is append-only.
We use bitmap indexes in a number of places internally.
Very interesting slides and talk.
You mentioned that there are some regularities in different data sets that can be used to increase the efficiency of their encoding. Does this mean that you need to write a different foreign data wrapper for each data set?
Thanks! Encoding takes care of regularities - it is all transparent to the user and DBA.
This is really interesting.
Did you do anything about join pushdown (which isn't supported in core PostgreSQL yet)? Apologies if this is in your talk - I looked at the slides and couldn't see anything.
Yeah, lack of aggregation function and join pushdown is annoying.
Our data model makes sure that we don't have to do huge joins on the fly, which would be a bad idea anyways. We have a workaround to distribute medium-scale joins that occur frequently. Small joins are handled fine by Postgres as usual.
I would be curious to learn more about Vitesse. I couldn't find many technical details besides the mailing list post.
It's not as easy as it could be but you can accomplish aggregate pushdown by using executor hooks in your FDW. As an example I'd point you to our (Citus Data) recent work on doing this with our columnar store foreign data wrapper:
https://github.com/citusdata/postgres_vectorization_test
As discussed there it definitely showed promise in improving performance for us.
Thanks.
I'm not involved with it, just a Postgres user and thought it was of interest.