Benchmarking Ingestion across Analytical Database Destinations

Introduction

In a cheap storage, expensive compute world, new companies have actively embraced the ELT (Extract - Load - Transform) paradigm. The destination of your data loads can have a big impact on performance and cost, however, and getting the 'right' setup for your data volumes and access patterns may not be as straightforward a task as it first appears.

In particular, loading information extracted from a variety of sources can be fraught with high costs. We have seen clients reduce their Snowflake costs by more than 50%, just by changing the way in which they loaded data into the data warehouse. That’s impressive!

Not all data warehouses are the same, and this article aims to highlight importance of thinking through your choices on how best to ingest information into your warehouse or datalake.

The Results

We benchmarked the performance of several popular analytical backends by loading ~1000 rows of event data from PostHog using dlt, an open source python library that simplifies extraction and loading, to represent a small open source event capture and storage setup.

Here are the results:

As you can see…

1. Loading information into S3 is by far the fastest medium
2. Postgres is second, taking ~3 times as long
3. Redshift takes 12x the amount of time!

DLT also provides us with a comparison of load times, i.e., strictly looking at time taken to push information into each database without retrieval time. We use a 9 column ecommerce dataset with event data, and see the following results:

Key observations:

• Redshift is faster than Bigquery for small volumes.
• Interestingly, load time doesn't increase dramatically for bigquery, suggesting it may handle scaling better than the other platforms!
• Postgres is blazingly fast with sub-second latency for <1k rows, and it may well be an easy choice for small data

Understanding the mechanics

There are some obvious mechanics at play.

• APIs typically return information in JSON format, and loading information into S3 is also in JSON (specifically, in jsonl) format. So one can expect to incur only ingress time (into AWS).
• Next, schema creation matters. Upon a re-run of the first Google bigquery job (with PostHog data) of 1000 rows, the processing time went down to 37.86s (from 51.34s).
• Finally, the strategy involved in performing your Loading matters. An append operation may not take nearly as much time as replace since it doesn’t involve a DELETE operation. Merge operations are destination dependent, and in our case DLT creates a staging table to compare against before a merge operation, increasing the load time further (compared to database-specific merge operations without staging tables).

Caveats

Any analysis of this nature comes with a plethora of caveats.

• Loading in S3 can be quick, but is often not in a query-able format to start with. Files often need some processing into a “clean” layer that can then be queried. This is typically accomplished through tools like AWS Glue (if operating within the AWS platform).
• Specifications of the cluster matter also matter. The lowest-spec Redshift cluster tells you nothing of how production systems might work.
• Note that load times may not be correlated to the month-end cost. Each of the databases listed above follow a different pricing model

Conclusion

We saw the results of a simple benchmarking exercise, and assessed the variety of ways in which load times could be affected. Ingestion times can quickly stack up your costs, and is therefore a metric you should closely monitor. Furthermore, organizational SLAs in delivery of key data assets may be affected by how well quickly your connectors can pump information into your data warehouse.

Of course, many more factors go into selection of an analytical warehouse. If you need help exploring some of these ways, book some time with us!

---

FAQ

Why use S3 as benchmark?

Using S3 serves a few functions - it highlights the speed of dumping into a filesystem vs into a database. Furthermore, a datalake or lakehouse paradigm - you will often need to push data into S3 before processing it further. Finally, S3 provides a cheap and convenient way to back up information that may or may not eventually be used, a vital capability in organizations that may want to rely less on 3rd party vendors and own their data beyond a vendor’s retention period.

How large are network ingress/egress costs?

This is a topic that we’ll cover in detail in the future. In a nutshell, the easiest way to quantify it is to use S3 or any other Cloud Storage filesystem as a benchmark.

Are there are any configurations I can tweak to reduce load times?

Absolutely! If this is something you’re interested in, leave us a note at <hello@cruxdata.co>! We can do another benchmarking article to compare configurations for your warehouse of choice.