50-60GB database on aurora, everythings fine on writes/updates and transactional operations. We hooked the db to be used as analytics dashboard pulling a fair amount of data but disk i/o is extremely slow from query plan around 5-15mb/s. Bumping up instance ram and loading all working database into shared_buffers cache everything works fine. So I came up to the following conclusions:

• aws aurora is basically unusable for olap workloads as i/o timings are terribly slow (and couldn't find an official figure from aws)

• aws aurora storage layer uses a storage attached network observed disk i/o is very low and there is no OS level cache so they recommend loading up all databse in memory

• does aws rds postgres with provisioned ssd suffer from the same problem? iops and throughput are 100x better on paper

• anyone had similar experiences with aurora and working with relatively large datasets?

• considering moving to redshift

We're currently looking at Aurora (PostgreSQL edition) as a db solution on AWS. We're not dealing with big data, so Redshift is out. S3 + a query engine like Athena isn't quite suitable either because most of our data is relational, structured and static when it comes to the schema. We're quite attracted to a serverless solution, so RDS is out as well. That basically leaves Aurora. Now, AWS says that Aurora is optimized for OLTP workloads. But is there anything really missing when it comes to OLAP functionality? We come from Oracle database, which we use for both OLTP and OLAP workloads. This works fine and is performant.

The question now is: Is there anything missing in Aurora to handle OLAP workloads, or is it just 'less optimized' but still very useable for OLAP workloads on modestly sized data (let's say <1 billion rows per table)?

I can't seem to find a SQL function reference for Aurora or anything about it's ANSI SQL compliance, so I presume that it's the same as PostgreSQL's. That would imply that Aurora supports 'OLAP functions' (like analytical and window functions). Am I on the right track here?

Hi everyone,

I've been reflecting on the limitations of Delta in OLAP environments and struggling to fully grasp its use cases given some of its constraints. The primary purpose of OLAP systems is to handle large, complex analytical queries at scale, but Delta introduces certain constraints that make me question its suitability for these workloads.

For instance, Delta's maximum number of columns is 4,600, and the maximum record size is capped at 20MB. While that might seem sufficient on paper, when you're not operating at true petabyte-scale workloads for every operation, these become more of a bottleneck than a benefit.

I understand that there was a time when running 1TB Postgres clusters was far more challenging, and the scaling limitations of traditional relational databases may have justified alternative solutions like Delta. However, with the advancements in database technology, particularly around partitioning, indexing, and parallel query execution, and the availability of massive amounts of memory, it's now much easier to model data without needing to perform a full "everything by everything" comparison every time.

With that in mind, the meat and bones of my argument and biggest concern is with the 20MB maximum row size. In a scenario where you're not pre-aggregating results in Postgres as data comes in, you could easily scale your compute to 24TB of memory and process millions of rows in a parallel query, because, each row, under the same constraints (20MB per row), can be handled in-memory quite efficiently.

Now, I could see the case for Delta if each record were over a gigabyte in size. Right? In that case, you'd need to process huge amounts of data per row, with arbitrary queries that don't neatly fit into a B-Tree like semantic structure, and parallelizing the compute in Spark over these large records would eventually make much more sense. But that's simply not the reality we're dealing with — at Delta Table's 20MB max, you're well within the capacity of modern relational databases like Postgres to handle the load with its parallalized compute without needing Delta’s architecture.

If your queries are designed to leverage these optimizations, the per-row workload on a Postgres cluster is significantly reduced, even with large datasets. This begs the question: if you're not regularly working with multi-petabyte datasets, does Delta still make sense in today’s landscape, because of its 20MB record limit, especially when traditional RDBMS systems can handle large-scale operations so efficiently?

Would love to hear others' thoughts on this, in 2024/2023, can the community kindly outline cases they have used personally especially if there are workloads where Delta truly shines that I'm overlooking?