Analytics queries inside your Node.js service without a data warehouse — DuckDB + S3 Parquet + MotherDuck

Have you ever wondered exactly when a dedicated data warehouse becomes justified? If you have thousands of concurrent dashboard users or petabytes of data to aggregate, a dedicated platform like Snowflake or BigQuery is the right fit. But the stage before that — a SaaS backend with tens to hundreds of GB of event logs — is a different story.

In that range, running a dedicated warehouse 24/7 incurs compute costs even when no queries are running. In contrast, embedding DuckDB directly into a Node.js process means zero additional cost when there are no queries. If analytical queries run only tens to hundreds of times per day, the infrastructure cost profile changes completely.

DuckDB embeds in-process like SQLite, but it's an OLAP engine optimized for aggregation and scanning. While PostgreSQL handles CRUD, heavy aggregations are offloaded to DuckDB, which reads Parquet files stored on S3 directly. The core of this pattern is on-demand aggregation — reading only the partitions needed at query time, with no separate ETL pipeline.

This article covers everything from attaching DuckDB to a Fastify server with @duckdb/node-api, to querying S3 Parquet directly, Lambda packaging, MotherDuck serverless deployment, and concurrency limiting — with real code for each scenario.

Core Concepts

Why DuckDB Is Suited for OLAP — Differences from SQLite

True to its nickname "the SQLite for analytics," both DuckDB and SQLite use an in-process embedding model with no separate server daemon. The difference starts with internal storage layout.

The key advantage of columnar storage is that a query like SUM(revenue) only needs to read the revenue column — minimizing I/O even across millions of rows. On top of that, the vectorized execution engine processes multiple values at once using SIMD instructions. This combination is what creates the tangible performance difference over row-oriented RDBMSes for aggregation and scan queries.

Hybrid Architecture — OLTP and OLAP in a Single Service

This pattern keeps PostgreSQL's write path intact while exporting only event logs or aggregation-target data to S3 as Parquet.

Both engines coexist within the same app process — PostgreSQL handles CRUD queries, and DuckDB + S3 Parquet handles analytical queries. The structure of this pattern is pure role separation without adding any extra infrastructure.

@duckdb/node-api — Which Package to Choose

Two packages exist on npm: duckdb and @duckdb/node-api.

• duckdb (legacy): Callback-based C bindings. Support for new versions will be discontinued; officially planned for deprecation.
• @duckdb/node-api (officially recommended): Promise-native, more stable async support.

This isn't a version-based cutover — @duckdb/node-api has replaced the other as the officially recommended package. For new projects, this is the right choice.

Practical Implementation

Scenario 1: Attaching DuckDB as a Singleton to Fastify

pnpm add @duckdb/node-api fastify

The recommended pattern is to initialize the DuckDB instance once at server startup and reuse it. Creating a new instance per request adds hundreds of milliseconds of overhead from httpfs installation and initialization.

// src/analytics/duckdb.ts
import { DuckDBInstance } from '@duckdb/node-api';
 
let instance: DuckDBInstance | null = null;
 
export async function getDuckDB(): Promise<DuckDBInstance> {
  if (instance) return instance;
 
  instance = await DuckDBInstance.create(':memory:');
  const conn = await instance.connect();
 
  await conn.run(`INSTALL httpfs; LOAD httpfs;`);
 
  // Secret is stored at the instance level — persists even after this connection is closed
  await conn.run(`
    CREATE SECRET IF NOT EXISTS s3_cred (
      TYPE s3,
      KEY_ID '${process.env.AWS_ACCESS_KEY_ID}',
      SECRET '${process.env.AWS_SECRET_ACCESS_KEY}',
      REGION '${process.env.AWS_REGION ?? 'ap-northeast-2'}'
    );
  `);
 
  // threads is a global setting that affects the entire instance — specify it once here
  await conn.run(`SET threads = 4;`);
 
  // Metadata cache to reduce S3 LIST costs for repeated queries
  await conn.run(`SET enable_object_cache = true;`);
 
  conn.close();
  return instance;
}

SET threads, SET enable_object_cache, and CREATE SECRET are all instance-level settings. Closing the conn used for initialization does not affect these settings — they persist for the lifetime of instance.

In route handlers, open a connection per request and always close it.

// src/routes/analytics.ts
import { FastifyInstance } from 'fastify';
import { getDuckDB } from '../analytics/duckdb.js';
 
export async function analyticsRoutes(app: FastifyInstance) {
  app.get('/api/analytics/revenue-by-region', async (req, reply) => {
    const db = await getDuckDB();
    const conn = await db.connect();
 
    try {
      const result = await conn.runAndReadAll(`
        SELECT
          region,
          date_trunc('day', ts)  AS day,
          SUM(revenue)           AS total_revenue,
          COUNT(*)               AS order_count
        FROM read_parquet(
          's3://my-bucket/events/year=*/month=*/*.parquet',
          hive_partitioning = true
        )
        WHERE year >= 2025
        GROUP BY region, day
        ORDER BY day DESC, total_revenue DESC
      `);
 
      return result.getRowObjects();
    } finally {
      conn.close();
    }
  });
}

Placing conn.close() in the finally block is critical. Failing to explicitly close a connection causes memory leaks.

Scenario 2: Querying S3 Parquet Directly — Reducing Costs with Hive Partitioning

Assume event data is partitioned on S3 as follows:

s3://my-bucket/events/
├── year=2025/
│   ├── month=11/
│   │   └── data.parquet
│   └── month=12/
│       └── data.parquet
└── year=2026/
    └── month=01/
        └── data.parquet

With the hive_partitioning=true option enabled, when a WHERE year=2026 AND month=01 condition is present, DuckDB skips reading 2025 partition files entirely. This dramatically reduces the number of S3 GET requests.

const result = await conn.runAndReadAll(`
  SELECT
    event_type,
    COUNT(*)         AS cnt,
    AVG(duration_ms) AS avg_duration
  FROM read_parquet(
    's3://my-bucket/events/**/*.parquet',
    hive_partitioning = true,
    filename = true
  )
  WHERE year = 2026 AND month = 1
  GROUP BY event_type
  ORDER BY cnt DESC
`);

S3 Parquet Performance Optimization Checklist

Scenario 3: Packaging DuckDB for AWS Lambda

The DuckDB binary is approximately 40–50 MB. Given the Lambda package size limit (250 MB unzipped), separating it into a Lambda Layer is recommended.

// handler.ts
import { DuckDBInstance } from '@duckdb/node-api';
 
const DB_PATH = '/tmp/analytics.db';
 
export const handler = async (event: {
  startDate: string;
  endDate: string;
}) => {
  const db = await DuckDBInstance.create(DB_PATH);
  const conn = await db.connect();
 
  await conn.run(`INSTALL httpfs; LOAD httpfs;`);
  await conn.run(`
    CREATE SECRET IF NOT EXISTS s3_cred (
      TYPE s3,
      PROVIDER credential_chain
    );
  `);
 
  const result = await conn.runAndReadAll(
    `SELECT date_trunc('week', ts) AS week, SUM(revenue) AS total
     FROM read_parquet('s3://my-bucket/events/**/*.parquet', hive_partitioning=true)
     WHERE ts BETWEEN $1 AND $2
     GROUP BY 1 ORDER BY 1`,
    [event.startDate, event.endDate]
  );
 
  conn.close();
  return result.getRowObjects();
};

Date values are bound using $1, $2 placeholders and an array — not string interpolation. Interpolating values from external event objects directly into SQL exposes you to SQL injection. Using PROVIDER credential_chain automatically retrieves credentials from the Lambda IAM Role, so AWS keys don't need to be hardcoded.

Scenario 4: Serverless Deployment with MotherDuck

If managing the DuckDB binary directly is impractical, MotherDuck is worth considering. MotherDuck supports the PostgreSQL Wire Protocol, so you can connect with the pg package directly.

// MotherDuck — connect with the pg package (no DuckDB binary required)
import { Pool } from 'pg';
 
const pool = new Pool({
  connectionString: `postgresql://${process.env.MOTHERDUCK_TOKEN}@motherduck.com/my_database`,
});
 
export async function queryRevenue(startDate: string) {
  const { rows } = await pool.query(
    `SELECT region, SUM(revenue) AS total
     FROM events
     WHERE ts >= $1
     GROUP BY region
     ORDER BY total DESC`,
    [startDate]
  );
 
  return rows;
}

The md: prefix is the DuckDB native client connection string format. When using the pg package (PostgreSQL Wire Protocol), use the postgresql:// form. Mixing the two formats will result in a connection failure.

This is also the only way to use DuckDB analytical queries in environments that cannot run Node.js native binaries, such as Vercel Edge Functions or Cloudflare Workers.

Scenario 5: Concurrency Limiting — Introducing a Query Queue

By default, DuckDB uses all CPU cores on the machine. If multiple concurrent requests arrive, each query competes for all cores, which can slow the entire server.

Two settings should be used together.

① Limit thread count at the instance level — in Scenario 1's getDuckDB() initialization, SET threads = 4 is already applied. Since threads is a global setting that affects the entire instance — not individual connections — resetting it per request is redundant and misleading. Setting it once at initialization is the correct pattern.

② Limit concurrent executions with a query queue:

// src/analytics/queue.ts
import PQueue from 'p-queue';
 
// threads=4, concurrency=2 → max 8 threads used simultaneously (adjust to core count)
const analyticsQueue = new PQueue({ concurrency: 2 });
 
export async function runAnalyticsQuery<T>(
  fn: () => Promise<T>
): Promise<T> {
  return analyticsQueue.add(fn) as Promise<T>;
}

// Usage example
const result = await runAnalyticsQuery(async () => {
  const conn = await db.connect();
  try {
    const rows = await conn.runAndReadAll(query);
    return rows.getRowObjects();
  } finally {
    conn.close();
  }
});

Total concurrent thread usage is estimated as threads × concurrency. On an 8-core server, starting with threads=4 and concurrency=2 and adjusting based on monitoring is a practical approach. Install with pnpm add p-queue.

Limitations and Practical Considerations

Advantages

Disadvantages and Caveats

The two most common mistakes:

1. Creating a DuckDB instance per request — httpfs installation, Secret creation, and instance initialization can take hundreds of milliseconds. Create it once as a singleton at server startup and reuse it.

2. Omitting conn.close() — failing to explicitly close a connection causes memory leaks. Always close with a try/finally pattern.

What to Choose and When

Closing Thoughts

DuckDB is a practical choice for teams that "need analytical queries but can't afford to run a dedicated warehouse." The pattern of directly reading Parquet files accumulated on S3 at query time — without touching PostgreSQL's OLTP role — covers everything from small SaaS products to hundreds of GB of data without additional infrastructure.

If you need petabyte-scale data or thousands of concurrent dashboard users, a dedicated warehouse is still the right choice. Before you reach that point, DuckDB offers the lowest barrier to entry for embedding analytical queries directly inside your service.

3 Steps to Get Started:

1. Install and validate locally: Install with pnpm add @duckdb/node-api and test queries against local Parquet files. Starting in :memory: mode keeps the barrier low.
2. Isolate an analytics endpoint: Pick the heaviest aggregation query in your existing service and replace it with a DuckDB-based endpoint. Compare query execution times directly — you'll feel the difference.
3. Integrate S3 Parquet: Load event logs onto S3 in a Hive-partitioned structure and attach an httpfs pipeline to query them directly. Don't forget enable_object_cache=true and explicit partition key conditions.

References

• DuckDB Official Documentation
• Node.js API — DuckDB Official Documentation
• S3 Parquet Import — DuckDB Official Guide
• Querying Parquet Files — DuckDB Official Guide
• Announcing DuckDB 1.5.0
• duckdb/duckdb-node (GitHub)
• MotherDuck Official Documentation — Architecture and capabilities
• MotherDuck Vercel Integration Guide
• 5 DuckDB + S3 Patterns: Multi-Part, S3 Select, Costs (Medium)
• DuckDB vs Postgres for Embedded Analytics (MotherDuck Blog)
• Customer-facing analytics with DuckDB and MotherDuck (codecentric)
• Running analytics queries from AWS S3 using DuckDB (Engineer's Notes)
• tobilg/duckdb-nodejs-layer (GitHub)