10 ms·
AlloyDB for PostgreSQL under the hood: Columnar engine
- metadat 4y agoHow'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
- code_biologist 4y agoGreat product positioning. I hope their follow-up is better than typical Google. The ML features are a good start, but I hope they get some BigQuery ML style model-definition-in-SQL features. The list of supported extensions [1] is interesting and covers the biggies. Still, it always kills me that these hosted "Postgres+" solutions end up meaning no third party extensions. ZomboDB (easy ElasticSearch integration) is a PG extension I've always wanted to try, but haven't been able to. [1] https://cloud.google.com/alloydb/docs/reference/extensions https://cloud.google.com/alloydb/docs/reference/extensions
- trhway 4y ago>Great product positioning. I hope their follow-up is better than typical Google. Looks like AlloyDB is a result of the influx of Oracle and SAP database people into GCP.
- gxt 4y agoOr theft. Gitlab private projects don't offer confidentiality from the service provider afterall...
- nlnn 4y agoIt's very frustrating. We use ZomboDB heavily, and the lack of extension support in AWS/Google cloud offerings prevents us from switching.
- sa46 4y agoIt wasn't clear to me how it was related to Postgres so I spun up a cluster and alloydb is Postgres with a different storage engine (similar to the long awaited zedstore project). That means all Postgres features work.
- riyadparvez 4y agoYes it seems to me they have taken the same approach as AWS Aurora. Use Postgres frontend and query parsing and replace the storage layer and query execution with their own.
- jerryjerryjerry 4y agoThis is a good HTAP system for PostgreSQL ecosystem. Emm, what about a MySQL compatible HTAP system? An open source project (https://www.vldb.org/pvldb/vol13/p3072-huang.pdf https://www.vldb.org/pvldb/vol13/p3072-huang.pdf) seems a good fit in this area that process both transactional and analytical workload at real-time.
- shenli3514 4y agoThanks for mentioning TiDB here. It is mysql compatible HTAP database. But different with AlloyDB. We do not reuse the code of MySQL/PostgreSQL. So there is no legacy code and more suitable for the HTAP scenario. It is easy to scale out and available on any cloud. More information here: https://docs.pingcap.com/tidb/dev/quick-start-with-htap https://docs.pingcap.com/tidb/dev/quick-start-with-htap
- throwaway894345 4y agoHTAP?
- jerryjerryjerry 4y ago>Recently, at Google I/O, we announced AlloyDB for PostgreSQL, a fully-managed, PostgreSQL-compatible database for demanding, enterprise-grade transactional and analytical workloads. This is exactly what a HTAP(Hybrid Transactional and Analytical Processing) system does.
- jerryjerryjerry 4y agoEven the HTAP cloud service is available to try: https://en.pingcap.com/tidb-cloud/ https://en.pingcap.com/tidb-cloud/
- al_james 4y agoThe mixed column and row store HTAP approach is very similar to how single store works (https://www.singlestore.com/ https://www.singlestore.com/) which offers a mysql compatible interface with a few small differences.
- 4y ago
- polskibus 4y agoI wonder if they ever open source at least a component of AlloyDB, which could be implemented as a Postgres extension, like Citus does.
- buremba 4y agoI don't think s because it looks like they re-use some of the vectorized columnar engine that's being used for BigQuery
- finikytou 4y agowhat is the target audience. knowing google already has Spanner. to me it sounds like it is targeting only PostegreSQL on-prem users that want to migrate to the cloud? Are they many fortune 5000 companies running PGSQL at scale in a complex setup where they will have value moving to a managedd solution?
- polskibus 4y agoPgsql as an interface does not have vendor lock in that Spanner has. You can move to the cloud and back much easier.
- baridbelmedar 4y agoGoogle seems to be working on offering Postgres interface compatibility to Spanner as well: https://cloud.google.com/spanner/docs/postgresql-interface https://cloud.google.com/spanner/docs/postgresql-interface
- zoomzoom 4y agoIn general I think Spanner is more focused on availability than performance (especially across different workloads e.g. analytics/transactions). Also, the Postgres shim for Spanner is not 100% compatible and less mature than Alloy. Last, Spanner is expensive!
- baridbelmedar 4y agoImho, Spanner used to be on the expensive side. But with the introduction of "compute units" and now 4TB storage per node, the price for Spanner looks pretty great. Links: https://cloud.google.com/spanner/docs/compute-capacity https://cloud.google.com/spanner/docs/compute-capacity https://cloud.google.com/blog/products/databases/spanner-has-doubled-the-storage-per-node-to-4tb-from-2tb https://cloud.google.com/blog/products/databases/spanner-has...
- jerryjerryjerry 4y agoSpanner usually handles transactional workload, but AlloyDB as HTAP may process the analytical workload at the same time, I think.
- dmos62 4y agoFrom my layman's perspective, this is an analogue to Amazon's "Aurora with Postgres compatibility" [0]. Can anyone provide a more accurate comparison? [0] https://aws.amazon.com/rds/aurora https://aws.amazon.com/rds/aurora
- phamilton 4y agoAurora innovated by replacing the storage layer with a network store. Replicas don't need their own copy of the data (they point at the same network store) making replication lag almost non-existent. It means new replicas can be brought up in minutes. It allows really cool features like fast cloning and point in time recovery. AlloyDB takes the same approach and adds some additional optimizations in query execution. It introduces a new caching layer and claims to use ML rather than a simple LRU cache. It also introduces cache-only representations of data in a columnar format, which allows it to greatly speed up analytical queries.
- nhoughto 4y agoInteresting they touched on a couple of pain points I’ve had with spark execution, not quite the same thing but I’ve wondered why some of the way it execute isn’t smarter. Bloom filters for cheap way to filter big joins, using single scans to do multiple jobs in the plan.. I’ll have to have a look.
- onphonenow 4y agoWhere is the pricing without logging in?
- truth_seeker 4y ago100x perf sounds too good to be true in practise. At least in my experience, most of the time the analytical queries to generate some kind of business report or display statistical graph UI on admin dashboard or whatever, need not be run few times a second. If you run ad-hoc analytical queries all the time, no matter how advanced is the database, you will bring it to its knees in some way. You can use old age tricks to enhance both OLTP and OLAP performance, master-associate (3NF or maybe 4NF) table to distribute columns across various tables, individual table partitioning, advance indexing feature of Postgres and periodic refresh of Materialised View to get decent performance.
- onphonenow 4y agoTaking a look and it looks like current minimum cost (us-west-3 / Salt Lake) is around $1,090 per month (4vCPU / 32 Gig * 2 instances).