Embedding DuckDB in Spring Boot: Ad-hoc Parquet/CSV Analytics with Multi-tenant Isolation

Why This Requirement Is Hard

Backend systems frequently face ad-hoc analytical requests: operations need GMV and refund rates by city and channel with changing dimensions; finance must reconcile third-party CSV bills against internal orders; support or business users want to run SQL against their own Excel/CSV files joined with historical data. These queries are infrequent, one-off, range from millions to hundreds of millions of rows, and their SQL shape is unpredictable — indexes cannot be pre-built.

Why MySQL/PostgreSQL Fail

OLTP engines are poor at full-table aggregation. A 100-million-row GROUP BY takes tens of seconds on InnoDB and pollutes the buffer pool, impacting live traffic.

Temporary data has no good home: loading CSVs into temp tables pollutes the primary database and requires cleanup; writing aggregation logic in Java streams is costly and error-prone.

Indexes cannot keep up with ad-hoc dimension combinations.

ClickHouse, Doris, or StarRocks offer performance but require a full cluster, ETL pipelines, a DBA, and introduce sync latency — unjustifiable for "a few reports per week."

Technology Selection: Why DuckDB

What It Is

DuckDB is an in-process columnar analytical database — "SQLite for Analytics." It has no separate server process; it's a library linked into the application via JDBC.

Key capabilities:

Columnar storage with vectorized execution saturates multiple cores; aggregation performance matches dedicated OLAP engines.

Table functions read_parquet(), read_csv_auto(), read_json_auto() query files directly without loading.

Rich SQL support: window functions, CTE, QUALIFY, PIVOT, LIST/STRUCT.

Parquet reads leverage row-group min/max statistics for predicate pushdown and partition pruning.

Comparison with Alternatives

MySQL temp tables: familiar, strong transactions, high concurrency, but poor columnar aggregation and no native Parquet/CSV query; data must be loaded first.

ClickHouse: excellent aggregation, Parquet support via S3 table functions, strong concurrency. But it's a separate cluster requiring deployment, operations, data sync, and has weaker transactions/writes. Operational cost is prohibitive for low-frequency reporting.

DuckDB: deployment is a single JAR alongside the app; near-zero ops cost; native columnar aggregation and direct file query. Concurrency is limited (single-machine, dozens of concurrent queries contend for resources); transactions and writes are weak.

Conclusion: Do not use DuckDB for high-frequency writes, strong transactions, or high-concurrency point lookups. For single-machine, read-heavy, analytical workloads with concurrency in the dozens, it offers the best cost-performance.

Spring Boot Integration

DuckDB provides a standard JDBC driver org.duckdb:duckdb_jdbc:1.1.3. Three critical points:

Avoid JPA/Hibernate. DuckDB lacks auto-increment PKs and SELECT ... FOR UPDATE; forcing ORM causes pain. Use JdbcTemplate or raw JDBC.

The JDBC driver is a multi-platform uber-JAR bundling native libraries for Linux x86_64/arm64, macOS, Windows — runs out of the box.

Connection pooling must be managed manually. DuckDB's connection semantics differ from MySQL; misconfiguration causes subtle bugs (detailed later).

Core Implementation

Overall Architecture

┌──────────────────────────────────────────────┐
│         Spring Boot Application              │
│                                              │
│  Controller → SqlGuard → QueryService        │
│                        │                     │
│              TenantDuckDbRegistry            │
│               ┌─────────┴─────────┐          │
│        HikariPool(t1)         HikariPool(t2) │
│             │                       │        │
│      analytics.duckdb         analytics.duckdb│
│             │                       │        │
│        /data/t1/               /data/t2/     │
└──────────────────────────────────────────────┘
                 │
      ┌──────────┴──────────┐
      │  S3 / MinIO (Parquet)│
      └─────────────────────┘

Configuration

@Component
@ConfigurationProperties(prefix = "analytics.duckdb")
@Data
public class DuckDbProperties {
    /** Root directory for each tenant's database file */
    private String baseDir = "/data/analytics/duckdb";
    /** Per-tenant connection pool size */
    private int maxPoolSize = 4;
    /** Threads per query */
    private int threads = 4;
    /** Per-database-instance memory limit */
    private String memoryLimit = "2GB";
    /** Per-query timeout */
    private Duration statementTimeout = Duration.ofSeconds(30);
    /** Max rows for synchronous return; beyond this use export */
    private int maxRows = 200_000;
    /** Allowed dataset path prefixes (whitelist) */
    private List<String> allowedLocationPrefixes = List.of("/data/datasets/", "s3://analytics/");
}

YAML equivalent:

analytics:
  duckdb:
    base-dir: /data/analytics/duckdb
    max-pool-size: 4
    threads: 4
    memory-limit: 2GB
    statement-timeout: 30s
    max-rows: 200000
    allowed-location-prefixes:
      - /data/datasets/
      - s3://analytics/

Tenant Database Registry & Connection Pool

Early pitfall: jdbc:duckdb: (no path) behaves inconsistently across driver versions. Early versions created a new anonymous in-memory database per getConnection(), so HikariCP pooled connections pointed to different databases — views created on one connection were invisible to another, causing "table not found" errors.

Fix: use file-backed mode, one .duckdb file per tenant.

@Slf4j
@Component
public class TenantDuckDbRegistry implements AutoCloseable {
    private final DuckDbProperties props;
    private final ConcurrentHashMap<String, HikariDataSource> pools = new ConcurrentHashMap<>();
    private final Path baseDir;

    public TenantDuckDbRegistry(DuckDbProperties props) throws IOException {
        this.props = props;
        this.baseDir = Paths.get(props.getBaseDir()).toAbsolutePath().normalize();
        Files.createDirectories(baseDir);
    }

    public HikariDataSource dataSource(String tenantId) {
        return pools.computeIfAbsent(tenantId, this::createPool);
    }

    private HikariDataSource createPool(String tenantId) {
        // 1. Strict tenant ID validation to prevent path traversal
        if (!tenantId.matches("[a-zA-Z0-9_-]{1,32}")) {
            throw new IllegalArgumentException("Invalid tenant ID: " + tenantId);
        }
        Path tenantDir = baseDir.resolve(tenantId).normalize();
        if (!tenantDir.startsWith(baseDir)) {
            throw new IllegalArgumentException("Tenant directory traversal attempt");
        }
        Path tmpDir = tenantDir.resolve("tmp");
        try { Files.createDirectories(tmpDir); }
        catch (IOException e) { throw new IllegalStateException("Cannot create tenant dir", e); }

        String dbUrl = "jdbc:duckdb:" + tenantDir.resolve("analytics.duckdb");
        String posixTmp = tmpDir.toString().replace('\\', '/');

        HikariConfig cfg = new HikariConfig();
        cfg.setPoolName("duckdb-" + tenantId);
        cfg.setJdbcUrl(dbUrl);
        cfg.setDriverClassName("org.duckdb.DuckDBDriver");
        cfg.setMaximumPoolSize(props.getMaxPoolSize());
        cfg.setMinimumIdle(1);
        cfg.setConnectionTimeout(5_000);
        cfg.setIdleTimeout(300_000);
        cfg.setMaxLifetime(1_800_000);
        // Instance-level settings applied on each new connection
        cfg.setConnectionInitSql(""
                + "SET threads = %d;"
                + "SET memory_limit = '%s';"
                + "SET temp_directory = '%s';"
                + "SET preserve_insertion_order = false;"
                + "SET enable_object_cache = true;"
                .formatted(props.getThreads(), props.getMemoryLimit(), posixTmp));

        log.info("Creating tenant DuckDB pool tenant={} url={}", tenantId, dbUrl);
        return new HikariDataSource(cfg);
    }

    @Override
    public void close() {
        pools.values().forEach(HikariDataSource::close);
        pools.clear();
    }
}

Key settings explained: memory_limit, temp_directory, threads are database-instance-level , shared by all connections in the pool. Hence each tenant needs its own instance for true quota isolation. temp_directory must point to the tenant's own directory. Large spills to disk would otherwise interfere across tenants, causing nightmare debugging. preserve_insertion_order = false lets DuckDB drop row-order preservation during aggregation/export, significantly reducing memory for large result sets. Trade-off: without ORDER BY, output order is non-deterministic — usually fine for analytics, but watch for hidden frontend assumptions. enable_object_cache = true caches remote Parquet footer metadata. Repeated queries on the same S3 files save many HEAD requests. maxLifetime = 30 min bounds connection age. DuckDB shares the same in-process instance when the same file path is reopened, so connection recreation doesn't lose in-memory state — though we use file-backed databases anyway.

File Registration & View Management

Users must not write read_parquet('s3://...') directly. Instead, the backend registers datasets as views ; users query only view names. This controls table names, paths, and permissions.

@Service
@RequiredArgsConstructor
public class DatasetService {
    private static final Pattern OPTION_KEY = Pattern.compile("[a-zA-Z_][a-zA-Z0-9_]*");
    private final TenantDuckDbRegistry registry;
    private final DuckDbProperties props;

    public void register(String tenantId, DatasetDef def) {
        String viewName = def.name();
        if (!viewName.matches("[a-z][a-z0-9_]{0,63}")) {
            throw new IllegalArgumentException("View name must be lowercase, digits, underscore");
        }
        boolean allowed = props.getAllowedLocationPrefixes().stream()
                .anyMatch(p -> def.location().startsWith(p));
        if (!allowed) {
            throw new SecurityException("Dataset path not whitelisted: " + def.location());
        }

        String reader = switch (def.type()) {
            case PARQUET -> "read_parquet";
            case CSV     -> "read_csv_auto";
            case JSON    -> "read_json_auto";
        };

        // Validate option keys to prevent injection via option names
        Map<String, Object> opts = def.options() == null ? Map.of() : def.options();
        String options = opts.entrySet().stream()
                .map(e -> {
                    if (!OPTION_KEY.matcher(e.getKey()).matches()) {
                        throw new IllegalArgumentException("Illegal option key: " + e.getKey());
                    }
                    return "%s = %s".formatted(e.getKey(), literal(e.getValue()));
                })
                .collect(Collectors.joining(", "));

        String sql = "CREATE OR REPLACE VIEW %s AS SELECT * FROM %s(%s%s)"
                .formatted(quoteIdent(viewName), reader,
                        sqlLiteral(def.location()),
                        options.isEmpty() ? "" : ", " + options);

        try (Connection conn = registry.dataSource(tenantId).getConnection();
             Statement st = conn.createStatement()) {
            st.execute(sql);
            log.info("Registered view tenant={} view={} location={}", tenantId, viewName, def.location());
        } catch (SQLException e) {
            throw new IllegalStateException("Dataset registration failed", e);
        }
    }

    private static String literal(Object v) {
        if (v instanceof Boolean || v instanceof Number) return v.toString();
        return sqlLiteral(String.valueOf(v));
    }
    private static String sqlLiteral(String s) { return "'" + s.replace("'", "''") + "'"; }
    private static String quoteIdent(String s) { return "\"" + s.replace("\"", "\"\"") + "\""; }
}

DatasetDef

definition:

public record DatasetDef(String name, DatasetType type, String location, Map<String, Object> options) {}

Views persist inside the .duckdb file and survive restarts. If the underlying path changes, re-run CREATE OR REPLACE VIEW. DuckDB resolves read_parquet file lists at query time, so newly landed Parquet partitions are auto-detected — but schema drift in new files will cause query errors, requiring view re-registration.

Parameterized Queries & Pagination

Parameterization only works for values . Table names, column names, file paths cannot be parameterized; they must go through server-side whitelists — zero user input concatenated.

public PageResult<Map<String, Object>> query(String tenantId, String sql,
        List<Object> params, Integer page, Integer size) {
    sqlGuard.assertSafe(sql);
    int currentPage = (page == null || page < 1) ? 1 : page;
    int limit = Math.min(size == null ? 50 : size, 500);
    long offset = (long)(currentPage - 1) * limit;

    // Wrap user SQL so their LIMIT/OFFSET doesn't interfere with our pagination params
    String pagedSql = "SELECT * FROM (" + sql + ") __t LIMIT ? OFFSET ?";

    try (Connection conn = registry.dataSource(tenantId).getConnection();
         PreparedStatement ps = conn.prepareStatement(pagedSql)) {
        int idx = bindParams(ps, params);
        ps.setInt(idx, limit);
        ps.setLong(idx + 1, offset);
        ps.setQueryTimeout((int) props.getStatementTimeout().toSeconds());

        try (ResultSet rs = ps.executeQuery()) {
            return toPageResult(rs, currentPage, limit);
        }
    } catch (SQLException e) {
        throw new QueryException("Query execution failed", e);
    }
}

Convention: the query API's SQL must not contain its own LIMIT. If it does, the outer wrapper turns pagination into "truncate user's result again," confusing semantics. Let pagination parameters control it.

Deep pagination ( OFFSET 1000000) in DuckDB still computes the first 1M rows before discarding them. Mitigation: cap max page number (e.g., 100 pages) and steer users to the export endpoint for larger scans. Analytical queries aren't suited for page-by-page browsing anyway.

Streaming Export

Two approaches, chosen by scenario.

Approach A: Engine-Writes File (Preferred for Very Large Results)

Use COPY to write Parquet/CSV directly; JVM only receives a row count — constant memory.

public Path exportToParquet(String tenantId, String sql, Path targetFile) {
    sqlGuard.assertSafe(sql);
    String copy = "COPY (%s) TO %s (FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 122880)"
            .formatted(sql, sqlLiteral(targetFile.toAbsolutePath().toString().replace('\\', '/')));
    try (Connection conn = registry.dataSource(tenantId).getConnection();
         Statement st = conn.createStatement()) {
        st.setQueryTimeout((int) props.getStatementTimeout().toSeconds());
        st.execute(copy);
        return targetFile;
    } catch (SQLException e) {
        throw new QueryException("Export failed", e);
    }
}

COPY

is a write operation; this endpoint must be internal-only, and targetFile path must be server-generated — never from request parameters.

Approach B: Streaming Cursor Row-by-Row (When Pushing to Browser Incrementally)

public long exportCsv(String tenantId, String sql, List<Object> params, OutputStream out) {
    sqlGuard.assertSafe(sql);
    try (Connection conn = registry.dataSource(tenantId).getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        bindParams(ps, params);
        ps.setFetchSize(5_000); // Chunked fetch, avoid full materialization
        ps.setQueryTimeout((int) props.getStatementTimeout().toSeconds());

        try (ResultSet rs = ps.executeQuery();
             BufferedWriter w = new BufferedWriter(
                     new OutputStreamWriter(out, StandardCharsets.UTF_8), 1 << 16)) {

            ResultSetMetaData md = rs.getMetaData();
            int cols = md.getColumnCount();
            writeCsvRow(w, IntStream.rangeClosed(1, cols)
                    .mapToObj(i -> safeLabel(md, i)).toArray(String[]::new));

            long rows = 0;
            String[] buf = new String[cols];
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    buf[i - 1] = escapeCsv(rs.getString(i));
                }
                writeCsvRow(w, buf);
                if (++rows % 10_000 == 0) w.flush();
            }
            w.flush();
            return rows;
        }
    } catch (SQLException | IOException e) {
        throw new QueryException("Export failed", e);
    }
}

Controller example:

@PostMapping("/{tenant}/export")
public void export(@PathVariable String tenant,
        @RequestBody @Valid QueryRequest req,
        HttpServletResponse resp) throws IOException {
    String filename = "export-" + System.currentTimeMillis() + ".csv";
    resp.setContentType("text/csv;charset=UTF-8");
    resp.setHeader("Content-Disposition",
            "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\""));
    analyticsService.exportCsv(tenant, req.sql(), req.params(), resp.getOutputStream());
}

setFetchSize(5000)

is sufficient to smooth network/disk jitter; larger values bloat the ResultSet buffer, defeating the purpose.

Query Timeout

DuckDB JDBC supports Statement.setQueryTimeout(), but behavior varies across versions — some only time out on the client side while the query keeps running server-side. Added an external watchdog that calls cancel() on timeout, triggering underlying operator interruption.

@Service
public class QueryWatchdog {
    private final ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(2, r -> {
        Thread t = new Thread(r, "duckdb-watchdog");
        t.setDaemon(true);
        return t;
    });

    public <T> T execute(Statement stmt, Duration timeout, SqlCallable<T> action) throws SQLException {
        ScheduledFuture<?> killer = scheduler.schedule(() -> {
            try { stmt.cancel(); } catch (SQLException ignored) {}
        }, timeout.toMillis(), TimeUnit.MILLISECONDS);
        try {
            return action.call();
        } finally {
            killer.cancel(false);
        }
    }
}

Combine with Spring's TaskDecorator or @Async to map timeout exceptions to a business error code QUERY_TIMEOUT, enabling friendly frontend messages like "Query timed out, please narrow the time range" instead of a generic 500.

Security & Isolation

Embedded databases carry a special risk: SQL itself is a filesystem access vector . SELECT * FROM read_csv('/etc/passwd') is perfectly valid in DuckDB. Security cannot rely on the database; it must be enforced at the application layer.

SQL Injection Defense: Three Gates

Gate 1: Single Read-Only Statement Only

@Component
public class SqlGuard {
    private static final Pattern FORBIDDEN_KEYWORD = Pattern.compile(
            "(?i)\b(attach|detach|install|load|copy|export|import|pragma|set|call|"
            + "create|insert|update|delete|drop|alter|truncate|grant|revoke|"
            + "read_csv|read_csv_auto|read_parquet|parquet_scan|csv_scan|"
            + "read_json|read_json_auto|read_text|read_blob|glob)\b");
    private static final Pattern TABLE_REF = Pattern.compile(
            "(?is)\b(?:from|join)\s+([a-zA-Z_][\\w$]*)");

    public void assertSafe(String sql, Set<String> allowedViews) {
        String s = stripComments(sql).strip();
        if (!s.regionMatches(true, 0, "select", 0, 6)
                && !s.regionMatches(true, 0, "with", 0, 4)) {
            throw new UnsafeSqlException("Only SELECT / WITH queries allowed");
        }
        // No extra semicolons after stripping trailing one → prevents multi-statement
        String body = s.endsWith(";") ? s.substring(0, s.length() - 1) : s;
        if (body.contains(";")) {
            throw new UnsafeSqlException("Multiple statements not allowed");
        }
        if (FORBIDDEN_KEYWORD.matcher(stripLiterals(body)).find()) {
            throw new UnsafeSqlException("SQL contains forbidden keyword");
        }
        Matcher m = TABLE_REF.matcher(stripLiterals(body));
        while (m.find()) {
            String table = m.group(1).toLowerCase();
            if (!allowedViews.contains(table)) {
                throw new UnsafeSqlException("Unauthorized data source: " + table);
            }
        }
    }
    private static String stripComments(String sql) { /* implementation omitted */ }
    private static String stripLiterals(String sql) { /* implementation omitted */ }
}

Gate 2: Table Name Whitelist

allowedViews

comes from the tenant's registered view set (in-memory map or metadata table). Attempts to bypass via read_parquet etc. are caught at Gate 1 by keyword blocking.

Gate 3: Engine-Level Fallback

Newer DuckDB versions support enable_external_access and directory/path whitelists ( allowed_directories, allowed_paths) as a last line of defense. Exact parameter names vary by version; consult the official docs for your version — don't blindly copy online configs.

Admitted limitation: regex-based SQL parsing is fragile. Deeply nested CTEs, subqueries after FROM, or messy whitespace/line breaks can break TABLE_REF regex. For complex production SQL, replace with JSqlParser for AST-level table extraction. Regex version is suitable as a fast first iteration.

Tenant Data Directory Isolation

Three isolation levels, chosen per tenant scale/sensitivity:

Separate Instance: One .duckdb file + dedicated pool per tenant. Strongest isolation, independent memory quotas. Cost: each instance reserves memory_limit; many tenants exhaust machine memory. Suits few tenants with sensitive data.

Shared Instance + Schema: Single instance, CREATE SCHEMA t_xxx, SET schema on connect. Medium isolation, lower memory overhead. Risk: a cross-schema query leaks all tenants; requires extremely strict view whitelists.

Pure In-Memory + Read-Only Files: Periodically load from object storage into memory tables. Weakest isolation, moderate overhead. Suits throwaway analysis (user uploads a file to explore).

Primary path uses Level 1. Each tenant's .duckdb, tmp dir, and Parquet dataset paths live under an isolated directory tree. Even if a path-concatenation bug appears, normalize() + startsWith() checks block traversal.

Resource Quotas

Per-tenant instance settings:

SET memory_limit = '2GB';          -- Spill to disk beyond this
SET temp_directory = '/data/.../tmp';
SET threads = 4;                    -- Limit single-query parallelism
SET max_temp_directory_size = '10GB'; -- Prevent temp space from filling disk

But if 200 tenants are simultaneously active, 200 × 2GB crushes the machine. TenantDuckDbRegistry must enforce a capacity ceiling with LRU eviction of least-recently-used pools.

private final ConcurrentHashMap<String, HikariDataSource> pools = new ConcurrentHashMap<>();
private final LinkedList<String> lru = new LinkedList<>();
private static final int MAX_ACTIVE_TENANTS = 50;

private synchronized void evictIfNeeded() {
    while (lru.size() >= MAX_ACTIVE_TENANTS) {
        String oldest = lru.removeFirst();
        HikariDataSource ds = pools.remove(oldest);
        if (ds != null) {
            log.info("Evicting idle tenant pool: {}", oldest);
            ds.close(); // waits for in-flight connections to return
        }
    }
}

Two caveats: (1) On every dataSource(tenantId) hit, move the key to the LRU tail, otherwise LRU degrades to FIFO. (2) close() blocks if queries are running; mark tenant "unavailable" before eviction so new requests fail fast instead of stalling on close.

Temporary Object Cleanup

TEMP tables are session-scoped; they vanish when the connection is returned/closed — no action needed.

Persistent views are managed by DatasetService; deleting a dataset runs DROP VIEW IF EXISTS.

Temp files scatter under each tenant's tmp directory. A daily 3:30 AM cron deletes files older than 7 days.

@Scheduled(cron = "0 30 3 * * ?")
public void cleanTempFiles() {
    // Walk baseDir/*/tmp, delete files with lastModified < 7 days ago
}

Sensitive Columns

Column-level security belongs in the view layer — don't defer to runtime SQL rewriting; rewrites inevitably leak.

-- Exclude sensitive columns at registration; users never see them
CREATE VIEW v_orders_safe AS
SELECT order_id, tenant_id, city, channel, amount, created_at
FROM read_parquet('s3://analytics/orders/dt=*/part-*.parquet', hive_partitioning = true);
-- phone, id_card, address columns never enter the view

If a column must stay but show different masked values per tenant, handle in the view with CASE WHEN or custom macros:

CREATE VIEW v_orders_masked AS
SELECT order_id, city, amount,
CASE WHEN amount > 10000 THEN NULL ELSE amount END AS visible_amount
FROM v_orders_safe;

View definitions are server-generated; users lack CREATE VIEW permission (blocked by SqlGuard), so they cannot create bypass views.

Performance Optimization

Column Pruning: Highest ROI

Parquet is columnar; SELECT * reads all columns of all row groups. An 80-column, 100 GB table where only 3 columns are needed suffers massive read amplification — tens of times depending on inter-column compression.

Lightweight guard: if incoming SQL contains SELECT * and the view has >30 columns, attach a WARN in the response. Don't hard-block, but make users aware.

Predicate Pushdown & Partition Pruning

DuckDB uses row-group min/max stats to skip data blocks when WHERE predicates map directly to columns.

-- Good: predicate pushes to Parquet scan
SELECT city, sum(amount) FROM v_orders
WHERE created_at >= TIMESTAMP '2024-06-01' AND created_at < TIMESTAMP '2024-07-01'
GROUP BY city;

-- Bad: function on column blocks pushdown → full scan then filter
SELECT city, sum(amount) FROM v_orders
WHERE date_trunc('month', created_at) = DATE '2024-06-01'
GROUP BY city;

Verify pushdown with EXPLAIN ANALYZE; check PARQUET_SCAN node's Filters field — non-empty means predicate reached the scan layer.

Partition Directory Design

Use Hive-style partitioning by day/hour; path encodes partition keys:

s3://analytics/orders/dt=2024-06-01/hour=00/part-000.parquet
s3://analytics/orders/dt=2024-06-01/hour=01/part-000.parquet

Register view with hive_partitioning = true:

CREATE VIEW v_orders AS
SELECT * FROM read_parquet(
    's3://analytics/orders/dt=*/hour=*/part-*.parquet',
    hive_partitioning = true
);

dt

and hour become queryable columns. WHERE dt = '2024-06-01' prunes to that day's files via filename matching only — no data download — huge win on object storage.

Object Storage Caching

SET enable_object_cache = true;  -- Cache Parquet footers and S3 metadata

With httpfs extension and S3 Secret:

INSTALL httpfs;
LOAD httpfs;
CREATE SECRET minio (
    TYPE s3,
    KEY_ID 'xxx',
    SECRET 'yyy',
    ENDPOINT 'minio.internal:9000',
    URL_STYLE 'path',
    USE_SSL false
);

CREATE SECRET

persists in the .duckdb file; create once per tenant instance. Production tip: pre-download httpfs extension into the container image — don't let first query fetch it over network; tightened network policies will hang with cryptic errors.

Concurrency Tuning

Two dimensions: per-query parallelism via SET threads = N (typically 60-70% of CPU cores, leaving headroom for GC/other threads). Multi-query parallelism via pool size: 4 connections × 4 threads = 16 concurrent tasks; exceeding physical cores causes contention.

Rule of thumb: maxPoolSize * threads ≤ 1.5 × physical cores. Starting point only; real tuning depends on query mix — I/O-heavy can pack more, CPU-heavy needs less.

Large Result Export

Synchronous API caps at 200k rows (~20-50 MB JSON). Beyond that, go async: return 202 Accepted with a taskId; background runs COPY (...) TO 'file' (FORMAT PARQUET); user downloads from a download center.

Engine-side export avoids two bottlenecks: JVM heap peaks and JSON serialization overhead. Even 1 billion rows keep application memory constant.

Production Case: Object Storage Parquet Self-Service Analytics

Scenario

Order details: 120M rows, 12 columns, day-partitioned on MinIO, ZSTD compressed to ~6.8 GB. Flow:

Data team writes daily order snapshots as Parquet.

On tenant's first analytics access, backend ensures v_orders view is registered (views persist, no rebuild on restart).

Ops selects dimensions, metrics, time range; backend builds and executes SQL.

Results >200k rows switch to async export.

Request/response DTOs and controller shown in source.

Typical ops query:

SELECT city,
count(*) AS order_cnt,
sum(amount) AS gmv,
count(DISTINCT buyer_id) AS buyers
FROM v_orders
WHERE dt BETWEEN '2024-06-01' AND '2024-06-30'
AND channel = ?
GROUP BY city
ORDER BY gmv DESC
LIMIT 100;

Execution: partition pruning scans only June's 30 days; column pruning reads only dt, city, channel, amount, buyer_id (5 of 12 columns). Actual I/O is a tiny fraction of full scan.

Rough MySQL Comparison (8C16G, 120M rows, 8 threads)

Not a formal benchmark; only order-of-magnitude reference. count(*) full table: MySQL 38s vs DuckDB 0.3s (Parquet footer stores row count — no data scan).

Single-dimension GROUP BY (100k groups): MySQL 64s vs DuckDB 1.4s.

Two-dimension GROUP BY with sum + count(distinct): MySQL timeout at 120s (killed) vs DuckDB 2.9s.

Single-day point query: MySQL 3.1s (index) vs DuckDB 0.42s — smaller gap.

Three-table reconciliation join: MySQL 210s then OOM vs DuckDB 5.6s.

Cold start first query: DuckDB ~2.3s (metadata reads).

Conclusion: DuckDB doesn't universally "crush" MySQL. Full aggregations are an order of magnitude faster due to columnar + vectorization. But indexed point/range queries on MySQL remain competitive or better. They complement, not replace — use each where it fits.

Side note: disk footprint dropped from ~42 GB (MySQL) to 6.8 GB (Parquet).

Production Pitfalls

In-memory URL with pool: jdbc:duckdb: + HikariCP caused view disappearance. Fix: file path mode, or at least named in-memory DB ( jdbc:duckdb:mydb) per pool.

Multi-instance file lock conflict: Single .duckdb file locks to one process. Two pods on same PVC fail with "Could not set lock on file." Options: sticky routing + dedicated volumes, or drop persistent files — use pure in-memory tables reading Parquet from object storage (chosen). Read-only mode ( access_mode=READ_ONLY) allows multi-process reads of the same file.

Multi-statement in connectionInitSql : DuckDB JDBC versions differ in semicolon-separated statement support; HikariCP sends them in one execute(). If only first runs, wrap a custom DataSource to execute init statements sequentially after getConnection().

Timezone: Parquet TIMESTAMP defaults to UTC; JDBC converts to local zone, causing 8-hour shifts. Fix: write UTC at ingestion, annotate timezone semantics in view, convert at presentation.

COPY export directory permissions: Target path must be writable. In containers, mount a writable volume; path must be server-generated, never user-supplied.

Closing Rules

Tenant isolation: "one tenant, one instance + dedicated pool" — full isolation, independent quotas. Memory cost contained by LRU capacity cap; prevent unbounded pool growth.

File access: always via registered views. Users query view names only. Paths, table names, table functions all server-whitelisted — blocks read_parquet('/etc/passwd') at the root. SqlGuard 's three gates are all mandatory, especially the table-name whitelist; don't assume SQL inspection catches everything.

Large results: engine-native export via COPY ... TO — never pull data into JVM then write out. Orders-of-magnitude difference at scale.

Performance focus: make I/O read only needed columns; make WHERE push down to file scan. Everything else is detail.

Boundaries: DuckDB is not an OLTP database; high-concurrency point lookups are not its strength. Sweet spot: one commodity server, ~10-20 concurrent analytical queries, fast SQL over files. Ops analytics, reconciliation, ad-hoc reports fit. Beyond that, graduate to a cluster solution.