4 ms·
How'd they make it 50-100x faster than postgres? That's some serious innovation.
by metadat 4y ago
How'd they make it 50-100x faster than postgres? That's some serious innovation.
- code_biologist 4y ago*50-100x faster for analytical queries. Columnar storage. Vertica was the real innovator. Redshift, BigQuery, Snowflake, Citus, Timescale, etc just took the idea and ran with it.
- metadat 4y agoAh, so it's actually more marketing, since it's comparing completely different approaches. I wonder if there are performance tradeoffs sometimes leading to slower than vanilla postgres?
- zaphar 4y agoThe sweet spot for columnar storage is calculating aggregations across a subset of columns. It can be really fast at that. But if you what you need is select * it might actually be slower in that case.
- tehlike 4y agoIf you are reading all columns from same row in your queries that'd break columnar storage use case, and would be slower.
- sa46 4y agoThey claim 4x faster against vanilla postgres for "commercial workloads" so it sounds really impressive. > I wonder if there are performance tradeoffs sometimes leading to slower than vanilla postgres? Speculation: alloydb probably has both a row store and a column store and some fancy logic to decide which one to use. That's usually what HTAP means. In very broad strokes, columnar data stores struggle if you select many columns.
- cavisne 4y agoNot really speculation, its mentioned in the article. Theres not really any free lunch here, vanilla postgres is optimized to perform well within the constraints of a single scale-up host, minimizing storage usage as much as possible. These Aurora style systems instead take the assumption that storage is relatively cheap in a cloud environment, and likewise with any compute task that can be scaled out instead of up. So move as much as possible out of the scale-up instance to scale-out storage and compute. Additionally Google is claiming their system is better than Aurora because google has a distributed filesystem, where AWS only has block storage (tied to specific compute) and object storage (much worse performance).
- rektide 4y ago> and likewise with any compute task that can be scaled out instead of up. So move as much as possible out of the scale-up instance to scale-out storage and compute. This was very much the main lesson I got reading up on DataDog's third-gen event storage system Husky[1]. Great read. [1] https://www.datadoghq.com/blog/engineering/introducing-husky/ https://www.datadoghq.com/blog/engineering/introducing-husky... https://news.ycombinator.com/item?id=31416843 https://news.ycombinator.com/item?id=31416843 (185 points, 9d ago, 15 comments)
- trhway 4y agoDoing OLTP writes into a columnar storage is pretty complex. Also columnar storage needs to be in memory or on SSD at least, not HDD where read performance would be abysmal without serious gymnastics (thus in particular, given the RAM and SSD prices back then, you just couldn't really have a columnar db widespread adoption 15+ years ago).
- avinassh 4y agoWhat were the innovations by Vertica? Is there any link you have
- AlisdairO 4y agoThis is the c-store paper, which was evolved into Vertica: http://www.cs.umd.edu/~abadi/vldb.pdf http://www.cs.umd.edu/~abadi/vldb.pdf . It's very readable, worth a look. edit: column stores existed before c-store, but c-store did some very nifty stuff around integrating compression awareness into the query executor
- avinassh 4y agoThank you, I will read the paper!
- jerryjerryjerry 4y agoIf I remember correctly, IBM published the idea even before Vertica.
- dehrmann 4y agoHow did the timeline work with Vertica and Paraccel? I know Paraccel was around the same time, but I'm not sure who was really first, or if it's even an important distinction.
- mdasen 4y agoVertica came out of the C-Store paper (https://web.archive.org/web/20100619191833/http://db.lcs.mit.edu/projects/cstore/vldb.pdf https://web.archive.org/web/20100619191833/http://db.lcs.mit...). The C-Store paper pre-dates ParAccel being incorporated. Many of the C-Store authors were involved in Vertica's creation. If you want to be technical, C-Store was the real innovator...and those people refined and commercialized it as Vertica.
- jandrewrogers 4y agoIt is hard to overstate how poorly optimized the Postgres architecture is for modern analytical workloads on modern hardware. It is an OLTP engine with 1990s design assumptions. You can modify it heavily to make it adequate for analytics but it won't be anything close to optimal due to architectural limitations. And I say that as a big fan of Postgres. There is a longstanding heuristic that, outside of the workload for which Postgres was designed, a state-of-the-art database implementation will be 100x faster on the same hardware. That heuristic is well-grounded in reality in my experience; I often measure new database kernel implementations relative to Postgres. Postgres is still great for classic OLTP workloads and you will find that the gap is smaller there. For analytical processing, 100x gain is pretty straightforward. No one should read this as reflecting poorly on Postgres. Database architecture and the hardware it runs on has evolved enormously in the decades since Postgres was designed. Most people have no sense of just how fast and scalable a modern database kernel is compared to the designs from 20+ years ago.
- closeparen 4y agoWhat are some modern databases in this context?
- zasdffaa 4y agoI did some utterly trivial benchmarks of aggregating over a (large) single table, and found PG and MSSQL to be about the same (both using row storage). Where does your 100X slowdown figure come from, and what 'architectural limitations' are you referring to? Genuine question as DBs architectures are interesting to me. TIA
- nicoburns 4y ago> both using row storage > Where does your 100X slowdown figure come from Given they specify analytical workloads, I'm betting a lot of it comes from column storage.
- zasdffaa 4y agoI need some ELI5 explaining to: colstores allow you to pick out just the cols of data you need rather than get the full row and discard what you don't need. If that alone explained the 100X speedup that would mean the row is storing 99% of data that's not of interest to a query. That would a) be unlike pretty most table structures + queries I've ever seen, but in those cases cases such as when a fat blob is stored in the row, you hive off blobs to a separate table and only join to that when blob's wanted. I'm missing something big here.
- LoriP 4y agoThis article discusses how TimescaleDB (packaged as an extension to PostgreSQL) approaches performance improvements, though it's a bit of an old piece and things have moved on with TimescaleDB too. It gives some good insights though. https://www.timescale.com/blog/timescaledb-vs-6a696248104e/ https://www.timescale.com/blog/timescaledb-vs-6a696248104e/ There are some more recent articles on the blog about performance and benchmarks if you're tantalized by that one. Transparency: I work for Timescale
- lmwnshn 4y agoSupposedly, it is significantly slower than SingleStore on various TPC-H SF-10 queries [0]. [0] https://twitter.com/DomenicRavita/status/1529963959465435154 https://twitter.com/DomenicRavita/status/1529963959465435154