[Feature] Reduce S3 API calls

[polyzos]

Search before asking

• I searched in the issues and found nothing similar.

Motivation

Overall Apache Paimon offers low TCO, but in terms of S3 API calls it makes way more compared to Iceberg.
Using version 0.7 you can see the following in the screenshot

On the left it's the S3 API calls Apache Iceberg makes and on the right its Apache Paimon (~3k/hour I believe)

I believe @JingsongLi mentioned some improvements were made in the upcoming 08 version, but as this is important for many companies we need to keep it as low as iceberg if possible.

Solution

No response

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[polyzos]

More context on this: turns out it was the effect of not having compaction running in the background., which wasn't clear in the first place.

This means that although partition/snapshot/manifest file expiration happens automatically
https://paimon.apache.org/docs/master/maintenance/manage-snapshots/#expire-snapshots
it results in more files being generated, but not physically deleted because compaction is disabled.

This means that although the generated files keep increasing, they are also never removed, but Paimon will still have to access/list more metadata files (more and more over time), in order to find out what's still relevant and what's not.

Turning compaction, in the long run will keep the requests stable.

At the same time it is also recommended to use version 0.8+ that brings many improvements as well

[tsturzl]

@polyzos It would be great to see a write up somewhere about this. We've been struggling with the same thing. We have like 16 tables in a Paimon data lake that are maybe 8GB of compressed data in total, and it's generating several thousand S3 requests per minute, and most of these are ListObjects calls. This seems like a really high cost for running a very small datalake, we're only running this in our staging environment to offload some telemetry data, but it's currently costing over $15/day which is a lot considering the low volume of data. I'm not sure what tuning compaction might entail, because there's not a lot to guide you on how to tune things currently. Would this be a good place to start? https://paimon.apache.org/docs/master/primary-key-table/compaction/#asynchronous-compaction

[polyzos]

@tsturzl, what Paimon version are you using? @JingsongLi If I recall correctly, there were some improvements around this?

[tsturzl]

@polyzos Thanks for the quick response. We're running Paimon 1.0.1 currently, so we're on the latest release. We're running 16 tables, a few of them are getting streamed maybe a few hundred records a minute. So the throughput is pretty low, since this is a staging environment. We have about 8GB in the S3 bucket. Every table we have is primary key, but I'm not sure if that's the correct choice since we'll almost never be overwriting these records. Each table is partitioned by event time, and generally records are being written in order of that event time. So we had assumed things would be pretty efficient. I wonder if we have something misconfigured. We're using a JDBC catalog against PostgreSQL, we were hoping for a 1.1 release to bring the PostgreSQL locking feature into a stable release, but currently we have lock.enabled = false, which we thought wouldn't be an issue, given we only have one writer per table, but maybe that's part of the issue? We're currently using the Paimon Java API directly to write, because that fit our existing project best, but we can run Flink jobs to alter or query the tables as well and have been using starrocks to prove out some analytical workloads, and we may move to using Flink to write out to the Paimon tables in the future. So if there's anything you think would be beneficial to try I'd be very interested.

The current TCO is really hard to swallow, it also seems extreme and we can't help but think something isn't behaving correctly. We're really interested in Paimon, because we have a lot of potential workloads that benefit from the design, like low latency reporting, and creating comprehensive views that reflect live data, but if we can't get the cost down it's probably not something we can justify. There's not really a lot to suggest how you should setup your tables without learning a lot about how Paimon works at a fairly low level. It would be really helpful to have some kind of guide on how to tune tables for particular workloads, and some explanation to better understand the access patterns paimon might have on various warehouse options.

At $15/day for this volume of data the TCO is worse than fully managed data warehouse solutions like Redshift currently.

[polyzos]

@tsturzl This needs a closer look .. Here we are discussing a streaming context, which means operations happen live.
I haven't used the Java client to be honest, so I'm not sure if things are done differently compared to the Flink Sink.
Some things that come in mind really in a high level are quickly are: writing data too often with the client, with a compaction running too frequently. This means files might need to be listed often, or if there are any other configurations enabled, snapshot generation frequency.
Maybe @JingsongLi has more to say around this.

[tsturzl]

@polyzos Something we've recently done is reduce the amount we're calling commit, which should reduce the amount of snapshots we're creating. We have enabled on most tables some kind of delay and message count threshold to reach before a commit is performed. Currently we are setting this to ~500ms as I'm not entirely sure what the implications of this are, but from what I assumed this should reduce the rate snapshots are created, because we're not creating a snapshot for each message write. That said, I don't see a drastic drop in S3 call volume. I could increase this commit delay to several seconds and see if that helps, it doesn't obviously call commit if there is nothing to commit. Something I was going to try today was tweaking the compaction triggers like so:

'num-sorted-run.stop-trigger' = '2147483647',
'sort-spill-threshold' = '10',
'lookup-wait' = 'false',
'num-sorted-run.compaction-trigger' = '20'

[polyzos]

Typically with Flink the checkpoint is typically to the minute.. This means at most 1 snapshot every minute but you might wanna optimise for that as well.
If you are writing every ~500ms then too many snapshots and file operations are being performed so I think this might be expected.

[tsturzl]

@polyzos That's good to know. I'll try setting that to 1min instead and see if that improves things. That seems like it could make a pretty big difference. Previously we were committing ever message. I wasn't initially sure if data was being written until the commit, but it seems like the commit mostly just pertains to the snapshot.

[tsturzl]

@polyzos Per the recommendation in the documentation we partition the table based on a TIMESTAMP field which is the event time. What I'm seeing in S3 is even small tables have thousands of partitions, eg timestampMillis=2025-04-02T23%3A20%3A00.214/. It almost seems like instead of partitioning based on a range it's creating a partition for each unique event time it's seen... I thought this would be more aware of the given type. Should I be extracting the date into a string and use that instead?