bug: slower performance of INSERT with 1.2.881

[rad-pat]

Search before asking

• I had searched in the issues and found no similar issues.

Version

1.2.881-nightly

What's Wrong?

Upgrading from 1.2.790 to 1.2.881, I find that INSERT is running slower than previously. I attach logs for 1.2.790 and 1.2.881.
Please let me know what else to provide to assist. I also logged #19479 which is addressing system tables issues.

1.2.790 - 0.49s
1.2.881 - 1.05s

logs 1.2.790_insert.txt
logs 1.2.881_insert.txt

How to Reproduce?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[wubx]

Thanks for the report! We analyzed the two query logs and found the root causes of the performancedifference.

Query comparison:

• Query 019c8f25... (v1.2.790) — completed in 478ms
• Query 019c8f15... (v1.2.881) — completed in 920ms

Root causes:

1. Partition pruning cache miss (biggest factor)

The v1.2.790 query hit the partition pruning cache and completed pruning in microseconds. The v1.2.881 query had a cold cache and had to perform actual block pruning, costing ~45ms
per table (~90ms total).

2. Metadata/SQL binding cold start

SQL binding took 134ms in the v1.2.881 query vs only 8.7ms in the v1.2.790 query — roughly 15x slower — because table metadata was not yet cached.

3. Vacuum & Analyze (enterprise feature)

This is worth highlighting: the v1.2.881 query ran vacuum and analyze hooks after the insert, which added meaningful overhead (~40ms+). These are enterprise features that
automatically reclaim storage space and keep table statistics up to date. In the v1.2.790 query, we can see this log line:

No vacuum handler available for auto vacuuming, please verify your license

This means the enterprise license had expired, so vacuum was silently skipped. While this made the individual query appear faster, it means storage is no longer being
automatically cleaned up — old snapshots and orphan files will accumulate over time, which can degrade long-term performance and increase storage costs.

Recommendation

If you'd like to keep vacuum running automatically and maintain optimal performance, we'd recommend reaching out to us about an enterprise license. Please contact us at
hi@databend.com or visit https://www.databend.com/contact-us/ — we'd be happy to help.

[rad-pat]

Thanks @wubx, I maybe have another report for COPY INTO statement - I'm just analysing and gathering logs now.

Interesting that the cache is empty - for reference, for each analysis - I ran a set of SQL (which includes truncation of target table) 3 times for each version after swapping the docker image, so would have presumed the cache would be populated as necessary.

[wubx]

You can also provide the logs in this issue directly.

[rad-pat]

There is actually not much difference in timing, but here are the logs, just in case something stands out.

logs 1.2.790_copy.txt
logs 1.2.881_copy.txt

[rad-pat]

Another set of logs, this time for TRUNCATE:

logs 1.2.790_truncate.txt
logs 1.2.881_truncate.txt

[dantengsky]

@rad-pat Hi,

A major reason for the increased execution time is that auto vacuum was triggered while the query was running.

You can check this with SHOW SETTINGS like 'enable_auto_vacuum'. If the value column shows 1, auto vacuum is on.

Each vacuum operation has a fixed cost, even if there's nothing or very little to clean up. If the query itself is light, this delay becomes very obvious.

For the above cases, I'd suggest turning auto vacuum off:

SET GLOBAL enable_auto_vacuum = 0;

This way, queries don't pay the auto vacuum cost every time they run.

---

For cleaning up historical data, you can periodically run:

OPTIMIZE TABLE db_name.tbl_name purge;

Before your enterprise license expires, you can also use:

call system$fuse_vacuum2(); — for all tables.
-- or
call system$fuse_vacuum2('db_name', 'tbl_name'); — for a specific table.

These tend to be faster, especially when cleaning up large amounts of historical data.

[rad-pat]

Hi @dantengsky, we don't have enterprise license due to high cost of such. We can definitely set enable_auto_vacuum = 0 if it will help, but I thought it was doing nothing anyway.

[dantengsky]

@rad-pat

Got it. Please try set enable_auto_vacuum = 0 (session level) or setting it globally with SET GLOBAL enable_auto_vacuum = 0, and see how query performance is affected.

Happy to help if you need further assistance.

[rad-pat]

@dantengsky, would this show a difference between the versions - the auto_vacuum is presumably attempted (but not available) in both 1.2.790 and 1.2.881. I will try anyway.

[dantengsky]

@rad-pat

Hi, according to the logs:

In version 1.2.790, auto vacuum was attempted but aborted due to license expiration. The log shows: "No vacuum handler available for auto vacuuming, please verify your license". So it was not actually executed.

In version 1.2.881, not only was auto vacuum triggered, but it was actually executed. The log shows: "Vacuuming table: temp_0ae38cea-92a1-4aba-8faa-bde343239ff7, ident: table_id:32487328, ver:32487335".

The binary of version 1.2.881 you downloaded likely includes an embedded trial license, so with auto vacuum enabled, it will actually be executed.

---

How has the performance been since you set enable_auto_vacuum = 0?

[rad-pat]

Hi @dantengsky , I still see TRUNCATE take ~0.6s on 1.2.811 and ~0.1s on 1.2.790

[dantengsky]

@rad-pat

I still see TRUNCATE take ~0.6s on 1.2.811 and ~0.1s on 1.2.790

Could you share the TRUNCATE logs after the settings change? And has COPY execution time changed significantly?

[dantengsky]

Hi @rad-pat , just checking in on this. Did you have a chance to collect the logs? Let me know if you need any further assistance.

[rad-pat]

Hi @dantengsky, apologies - I was on vacation a few days. I will attempt to gather some logs