7 ms·

Vitesse Data: Postgres + LLVM

257 points by yangyang 12y ago

willvarfar 12y ago
Interesting and upvoted even if just a sales pitch. But a keeping-the-sauce-sectret aproach won't win over techies! We want to know how it works, why it works, what it can do and what it can't do! Looking forward to the white papers and talks :) PS name is very close the Google's Vitess?
- Zikes 12y ago
  I also thought this was somehow related to Google's Vitess. I was rather excited at the prospect of Postgres compatibility, which I know they've been working on, and which we use in our business.
- Keats 12y ago
  Vitesse means speed in french, so it's not a made up word
- justinsb 12y ago
  To be fair, they've made it very clear how it works: 1) They have optimized the (existing?) CSV import code to use SSE instructions, for faster CSV import 2) For a sufficiently complicated query, they will compile it to native code using LLVM. They presumably precompile most of Postgres (or at least the execution parts) to LLVM IR, and then convert enough of the execution plan into code so that the LLVM optimizer can optimize it (inlining, branch prediction, dead code elimination etc). If they are able to persuade LLVM to optimize the per-row decoding, I think that could be a huge win. It's a great accomplishment; I do wish they had contributed it to Postgres, but I can't blame them for not doing so (they need to eat!).
  - ris 12y ago
    "I do wish they had contributed it to Postgres, but I can't blame them for not doing so (they need to eat!)." Stop this. The two are not mutually exclusive. Postgres developers are not starving.
Thaxll 12y ago
From my experience I/O is more important than CPU for DB, I've never seen the 24 cores of our servers having problems, however the raid 10...
- justinsb 12y ago
  It really depends on your workload. If your working-set fits into memory and is read-mostly, your I/O is basically irrelevant. Even if you're not in-memory, if you are doing mostly sequential reads (like the OLAP queries they are benchmarking), each drive can do ~200MB/second, so it is not too difficult to become CPU bound if you are doing any significant processing on each row (particularly if your row size is small!) For OLTP queries, particularly with working-set > RAM or lots of writes, your disk I/O is probably the bottleneck (probably your IOPS, actually, which is why SSD can be so valuable). Pretty sure they're not targeting that use-case though!
  - swasheck 12y ago
    in my experience (which isn't the sum-total of all human experience), our OLAP queries have a bottleneck at the fiber interconnect between the storage controller and the OS. cpu still isn't the problem for our OLAP systems. your point that great strides have been made in storage such that we could be back at cpu as a bottleneck is well taken though.
- jcampbell1 12y ago
  I assume you are using cheap spinning disks, and not something like a fusion-io drive. How big is your dataset?
  - swasheck 12y ago
    that's a narrow assumption. why not get more information from the person instead of simply attacking a straw man.
    - jcampbell1 12y ago
yawniek 12y ago
isn't usually the transfer of data between memory and cpu the bottle neck and not the cpu itself? e.g. http://www.pytables.org/docs/CISE-12-2-ScientificPro.pdf http://www.pytables.org/docs/CISE-12-2-ScientificPro.pdf
- analogAndroid 12y ago
  Usually yes, but that is why you take advantage of specialized CPU instructions for bulk loading and operating on data. From what it says that is part of the optimization that these folks are taking advantage of (see comment mentioning SSE instructions).
- spacemanmatt 12y ago
  The most common bottleneck in my work experience has been disk I/O when the data set would not fit into memory. And every professional data set I've worked with exceeds 1TB. Perhaps there are other bottlenecks, but disk seeks and (for one place) their iSCSI over gbit ethernet nonsense ruled the performance challenges.
vtuulos 12y ago
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/#/ http://tuulos.github.io/pydata-2014/#/ https://www.youtube.com/watch?v=xnfnv6WT1Ng https://www.youtube.com/watch?v=xnfnv6WT1Ng
- yangyang 12y ago
  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.
  - vtuulos 12y ago
    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.
    - yangyang 12y ago
      Thanks. I'm not involved with it, just a Postgres user and thought it was of interest.
    - bredman 12y ago
      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 https://github.com/citusdata/postgres_vectorization_test As discussed there it definitely showed promise in improving performance for us.
- 12y ago
florianfunke 12y ago
If you're interested in query compilation, check out the HyPer DBMS prototype at [1]. It efficiently compiles SQL (SQL92++) to super-fast LLVM code (faster than Vitesse). It's not open source, but you can download a binary [4] or try it out online using the "WebInterface" link and read about the query compiler here [2]. It also has lots of other goodies, like SIMD-powered CSV parsing (about 4x faster than Postgres) [3]. Rumor has it that the prototype will be commercialized soon ;) EDIT: It also has NUMA-aware multi-threaded query execution. [1] http://www.hyper-db.com http://www.hyper-db.com [2] http://www.vldb.org/pvldb/vol4/p539-neumann.pdf http://www.vldb.org/pvldb/vol4/p539-neumann.pdf [3] http://www.vldb.org/pvldb/vol6/p1702-muehlbauer.pdf http://www.vldb.org/pvldb/vol6/p1702-muehlbauer.pdf [4] http://databasearchitects.blogspot.de/2014/05/trying-out-hyper.html http://databasearchitects.blogspot.de/2014/05/trying-out-hyp...
- sbrakerm 12y ago
  Looks like the Vitesse product was inspired by this research. Even though the HyPer looks a lot more mature (though it's still a research project). Looking forward to see the product.
peatmoss 12y ago
I wonder how well this approach would extend to Postgres add ons like PostGIS and PGRouting?
- cordite 12y ago
  As someone that has used PostGIS on every city in the US to map to every county in the US over the last two centuries for genealogical mapping purposes, it would probably make it nicer. (Some of the results are already in the library of congress)
mfenniak 12y ago
There are a few posts on the pgsql-hackers mailing list that have some more detailed information, for anyone interested in reading more about the approach taken: http://www.postgresql.org/message-id/5CFE0CA1-E5CC-4CD1-9D0B-8D72143D81C2@vitessedata.com http://www.postgresql.org/message-id/5CFE0CA1-E5CC-4CD1-9D0B... I think this is a fascinating approach to improving query speed. It certainly won't be applicable to every use-case, but it seems like there's a lot of value in it.
hendzen 12y ago
And what is old, is new again. For reference, the System R database used this technique of compiling SQL queries to machine language in the 70's: "The approach of compiling SQL statements into machine code was one of the most successful parts of the System R project. We were able to generate a machine-language subroutine to execute any SQL statement of arbitrary complexity..." See: http://www.cs.berkeley.edu/~brewer/cs262/SystemR-comments.pdf http://www.cs.berkeley.edu/~brewer/cs262/SystemR-comments.pd...
bfrog 12y ago
I would love to see how its done one day, though I get the need to make back the investment in time!
jeffdavis 12y ago
Menu button doesn't seem to work in Firefox on android.
inzax 12y ago
As shown on https://www.facebook.com/pages/Hacker-News/1558365107716690 https://www.facebook.com/pages/Hacker-News/1558365107716690
uberneo 12y ago
Shard-Query does the same work of utilising all the available CPUs but its for MySQL - https://github.com/greenlion/swanhart-tools/tree/master/shard-query https://github.com/greenlion/swanhart-tools/tree/master/shar...
twic 12y ago
There was a vaguely similar thing a while ago called PGStrom, which compiled qualifiers into CUDA code which ran on the GPU: https://wiki.postgresql.org/wiki/PGStrom https://wiki.postgresql.org/wiki/PGStrom Someone got similar performance just using multithreaded native code on the CPU: http://blog.notapaper.de/article5.html http://blog.notapaper.de/article5.html

257 points by yangyang 12y ago

Interesting and upvoted even if just a sales pitch. But a keeping-the-sauce-sectret aproach won't win over techies! We want to know how it works, why it works, what it can do and what it can't do! Looking forward to the white papers and talks :) PS name is very close the Google's Vitess?

Zikes 12y ago
I also thought this was somehow related to Google's Vitess. I was rather excited at the prospect of Postgres compatibility, which I know they've been working on, and which we use in our business.
Keats 12y ago
Vitesse means speed in french, so it's not a made up word
justinsb 12y ago
To be fair, they've made it very clear how it works: 1) They have optimized the (existing?) CSV import code to use SSE instructions, for faster CSV import 2) For a sufficiently complicated query, they will compile it to native code using LLVM. They presumably precompile most of Postgres (or at least the execution parts) to LLVM IR, and then convert enough of the execution plan into code so that the LLVM optimizer can optimize it (inlining, branch prediction, dead code elimination etc). If they are able to persuade LLVM to optimize the per-row decoding, I think that could be a huge win. It's a great accomplishment; I do wish they had contributed it to Postgres, but I can't blame them for not doing so (they need to eat!).
- ris 12y ago
  "I do wish they had contributed it to Postgres, but I can't blame them for not doing so (they need to eat!)." Stop this. The two are not mutually exclusive. Postgres developers are not starving.

Thaxll 12y ago

From my experience I/O is more important than CPU for DB, I've never seen the 24 cores of our servers having problems, however the raid 10...

justinsb 12y ago
It really depends on your workload. If your working-set fits into memory and is read-mostly, your I/O is basically irrelevant. Even if you're not in-memory, if you are doing mostly sequential reads (like the OLAP queries they are benchmarking), each drive can do ~200MB/second, so it is not too difficult to become CPU bound if you are doing any significant processing on each row (particularly if your row size is small!) For OLTP queries, particularly with working-set > RAM or lots of writes, your disk I/O is probably the bottleneck (probably your IOPS, actually, which is why SSD can be so valuable). Pretty sure they're not targeting that use-case though!
- swasheck 12y ago
  in my experience (which isn't the sum-total of all human experience), our OLAP queries have a bottleneck at the fiber interconnect between the storage controller and the OS. cpu still isn't the problem for our OLAP systems. your point that great strides have been made in storage such that we could be back at cpu as a bottleneck is well taken though.
jcampbell1 12y ago
I assume you are using cheap spinning disks, and not something like a fusion-io drive. How big is your dataset?
- swasheck 12y ago
  that's a narrow assumption. why not get more information from the person instead of simply attacking a straw man.
  - jcampbell1 12y ago

yawniek 12y ago

isn't usually the transfer of data between memory and cpu the bottle neck and not the cpu itself? e.g. http://www.pytables.org/docs/CISE-12-2-ScientificPro.pdf http://www.pytables.org/docs/CISE-12-2-ScientificPro.pdf

analogAndroid 12y ago
Usually yes, but that is why you take advantage of specialized CPU instructions for bulk loading and operating on data. From what it says that is part of the optimization that these folks are taking advantage of (see comment mentioning SSE instructions).
spacemanmatt 12y ago
The most common bottleneck in my work experience has been disk I/O when the data set would not fit into memory. And every professional data set I've worked with exceeds 1TB. Perhaps there are other bottlenecks, but disk seeks and (for one place) their iSCSI over gbit ethernet nonsense ruled the performance challenges.

vtuulos 12y ago

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/#/ http://tuulos.github.io/pydata-2014/#/ https://www.youtube.com/watch?v=xnfnv6WT1Ng https://www.youtube.com/watch?v=xnfnv6WT1Ng

yangyang 12y ago
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.
- vtuulos 12y ago
  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.
  - yangyang 12y ago
    Thanks. I'm not involved with it, just a Postgres user and thought it was of interest.
  - bredman 12y ago
    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 https://github.com/citusdata/postgres_vectorization_test As discussed there it definitely showed promise in improving performance for us.
12y ago

florianfunke 12y ago

If you're interested in query compilation, check out the HyPer DBMS prototype at [1]. It efficiently compiles SQL (SQL92++) to super-fast LLVM code (faster than Vitesse). It's not open source, but you can download a binary [4] or try it out online using the "WebInterface" link and read about the query compiler here [2]. It also has lots of other goodies, like SIMD-powered CSV parsing (about 4x faster than Postgres) [3]. Rumor has it that the prototype will be commercialized soon ;) EDIT: It also has NUMA-aware multi-threaded query execution. [1] http://www.hyper-db.com http://www.hyper-db.com [2] http://www.vldb.org/pvldb/vol4/p539-neumann.pdf http://www.vldb.org/pvldb/vol4/p539-neumann.pdf [3] http://www.vldb.org/pvldb/vol6/p1702-muehlbauer.pdf http://www.vldb.org/pvldb/vol6/p1702-muehlbauer.pdf [4] http://databasearchitects.blogspot.de/2014/05/trying-out-hyper.html http://databasearchitects.blogspot.de/2014/05/trying-out-hyp...

sbrakerm 12y ago
Looks like the Vitesse product was inspired by this research. Even though the HyPer looks a lot more mature (though it's still a research project). Looking forward to see the product.

peatmoss 12y ago

I wonder how well this approach would extend to Postgres add ons like PostGIS and PGRouting?

cordite 12y ago
As someone that has used PostGIS on every city in the US to map to every county in the US over the last two centuries for genealogical mapping purposes, it would probably make it nicer. (Some of the results are already in the library of congress)

mfenniak 12y ago

There are a few posts on the pgsql-hackers mailing list that have some more detailed information, for anyone interested in reading more about the approach taken: http://www.postgresql.org/message-id/5CFE0CA1-E5CC-4CD1-9D0B-8D72143D81C2@vitessedata.com http://www.postgresql.org/message-id/5CFE0CA1-E5CC-4CD1-9D0B... I think this is a fascinating approach to improving query speed. It certainly won't be applicable to every use-case, but it seems like there's a lot of value in it.

hendzen 12y ago

And what is old, is new again. For reference, the System R database used this technique of compiling SQL queries to machine language in the 70's: "The approach of compiling SQL statements into machine code was one of the most successful parts of the System R project. We were able to generate a machine-language subroutine to execute any SQL statement of arbitrary complexity..." See: http://www.cs.berkeley.edu/~brewer/cs262/SystemR-comments.pdf http://www.cs.berkeley.edu/~brewer/cs262/SystemR-comments.pd...

bfrog 12y ago

I would love to see how its done one day, though I get the need to make back the investment in time!

jeffdavis 12y ago

Menu button doesn't seem to work in Firefox on android.

inzax 12y ago

As shown on https://www.facebook.com/pages/Hacker-News/1558365107716690 https://www.facebook.com/pages/Hacker-News/1558365107716690

uberneo 12y ago

Shard-Query does the same work of utilising all the available CPUs but its for MySQL - https://github.com/greenlion/swanhart-tools/tree/master/shard-query https://github.com/greenlion/swanhart-tools/tree/master/shar...

twic 12y ago

There was a vaguely similar thing a while ago called PGStrom, which compiled qualifiers into CUDA code which ran on the GPU: https://wiki.postgresql.org/wiki/PGStrom https://wiki.postgresql.org/wiki/PGStrom Someone got similar performance just using multithreaded native code on the CPU: http://blog.notapaper.de/article5.html http://blog.notapaper.de/article5.html