H2 Compaction became significantly worse in terms of size in 2.2.220

I was upgrading h2 driver version from 2.1.214 to 2.3.232 and mentioned that the sizes of compacted databases doubled in size in 2.3 compared to 2.1.

Using below scenario I've tested all the versions between 2.1.214 and 2.3.232 and found out that change that impacted compacted database size was introduced in 2.2.220.

How testing was done:

1. I've generated SQL dump using 2.1
2. Loaded dump using RunScript.execute and after that executed statement SHUTDOWN COMPACT with different versions of driver
3. Compared the sizes

Uncompacted sizes of databases were approximately the same, but compacted — differed 81Mb vs 202Mb (on our dataset)

As I'm not able to provide original data, I've tried to come up with some generated data set that can evidently show the problem. Note that I was not able to come up with dataset that can show 2x increase, but 30% difference is also big enough. Also one observation: compaction quality for numeric data haven't changed, at least for types that we use: DECFLOAT(32) and FLOAT(53).

Test case consists in 2 phases

1. Generate sql dump with below script (this is js file) generate_sql_inserts_strings.txt that can be ran like this node generate_sql_inserts_strings.txt 1000000 varchar.sql
2. Run below java code java -cp h2-2.2.220.jar scratch.java and java -cp h2-2.1.214.jar scratch.java to create databases. It will create 4 files 2.1.214_VARCHAR_COMPACTED.mv.db, 2.1.214_VARCHAR.mv.db, 2.2.220_VARCHAR_COMPACTED.mv.db, 2.2.220_VARCHAR.mv.db

import java.sql.Connection;
import java.sql.SQLException;

import org.h2.engine.Constants;
import org.h2.tools.RunScript;
import org.h2.util.JdbcUtils;

class Scratch {

    public static void main(String... args) throws Exception {
        Class.forName("org.h2.Driver");
        System.out.println("Running...");
        generate("./varchar.sql", "./"+ Constants.VERSION +"_VARCHAR", false);
        generate("./varchar.sql", "./"+ Constants.VERSION +"_VARCHAR_COMPACTED", true);
        System.out.println("Done.");
    }
    
    public static void generate(String sourceFile, String targetDbFile, boolean compact) throws SQLException {
        System.out.println("Generating database file: " + targetDbFile + " from " + sourceFile + (compact ? " and compacting" : ""));
        String url = "jdbc:h2:" + targetDbFile;
        RunScript.execute(url, "root", "admin", sourceFile, null, false);
        if (compact) {
            try (Connection conn = JdbcUtils.getConnection(null, url, "root", "admin")) {
                conn.createStatement().execute("SHUTDOWN COMPACT");
            }
        }
    }
}

Start by running the supplied generate_sql_inserts.txt and scratch.java reproduction with H2 2.1.214 and 2.2.220. Trace the behavior of RunScript.execute and SHUTDOWN COMPACT, then compare compacted VARCHAR database sizes while confirming numeric compaction remains unchanged. Done means the VARCHAR compaction regression is explained and addressed without breaking the reported workflow.