DuckDB From Go for Analytics Without Running a Server

There is a gap between "a spreadsheet is too small" and "a data warehouse is too much". A CSV export of a few million rows, a Parquet file from a pipeline, the logs of a month: you want to run SQL aggregations on them, now, in a program, without standing up a server. DuckDB fills that gap. It is an in process analytical database, sometimes called SQLite for analytics: a library you link into your program, which stores data in columns, processes it in vectorised batches across all cores, and reads Parquet, CSV and JSON files as if they were tables. This guide uses it from Go 1.25.6 with go-duckdb v2.4.3 (which bundles DuckDB v1.4.1), with a lab that prints the real timings.

It belongs to the database cornerstone. The server based counterpart is ClickHouse from Go, and for transactional embedded storage see SQLite in Go.

What it is for, and what it is not

DuckDB is built for OLAP: queries that scan many rows and few columns, with aggregates, joins and window functions. It is not built for OLTP: many small concurrent writes, single row updates and lookups by key under heavy load. SQLite and PostgreSQL do that better. DuckDB supports transactions and updates, and its sweet spot is reading a lot of data, summarising it and writing the result.

Two properties shape how you use it from a program. It is embedded: there is no network, no authentication and no process to manage, and the database is either in memory or a single file. And it is single process for writes: a file can be opened for writing by one process at a time, as the last lab step shows.

Setting it up

go-duckdb uses cgo and ships the DuckDB library as static archives for the common platforms (macOS and Linux on amd64 and arm64, Windows on amd64), so you need a C compiler and no separate DuckDB install. The consequences are the usual ones for cgo: cross compiling needs a C cross toolchain, CGO_ENABLED=0 cannot work, and the first build is slow while the linker handles the large archive. It took several minutes the first time here and seconds afterwards, because the Go build cache keeps the result. Build container images with a multi stage Dockerfile that has a compiler in the builder and only the binary in the final image.

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"log"
	"os"
	"os/exec"
	"path/filepath"
	"strings"
	"time"

	"github.com/marcboeker/go-duckdb/v2"
)

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func timed(label string, fn func()) time.Duration {
	start := time.Now()
	fn()
	d := time.Since(start)
	fmt.Printf("%-52s %v\n", label, d.Round(time.Millisecond))
	return d
}

func main() {
	if len(os.Args) == 3 && os.Args[1] == "child" {
		child, err := sql.Open("duckdb", os.Args[2])
		if err == nil {
			err = child.Ping()
		}
		msg := firstLine(err)
		if strings.Contains(msg, "Could not set lock on file") {
			// The server's message also names the temporary file and the process id; keep the part that matters.
			msg = "IO Error: Could not set lock on file: Conflicting lock is held by another process"
		}
		fmt.Println(msg)
		return
	}
	ctx := context.Background()
	dir, err := os.MkdirTemp("", "duck643")
	must(err)
	defer os.RemoveAll(dir)

	// An in memory database through database/sql, like any other driver.
	db, err := sql.Open("duckdb", "")
	must(err)
	defer db.Close()
	db.SetMaxOpenConns(1) // an in memory database belongs to one connection

	var version string
	must(db.QueryRow("select version()").Scan(&version))
	fmt.Println("DuckDB", version)

	// 1. Five million rows, built inside the engine, and an aggregate over them.
	must(execAll(db,
		"create table events as select i as id, (i * 7919) % 1000 as user_id, ['click','view','buy','scroll'][1 + i % 4] as kind, (i % 977) / 10.0 as amount from range(5000000) t(i)",
	))
	var n int
	timed("group by kind over 5,000,000 rows", func() {
		rows, err := db.Query("select kind, count(*), round(avg(amount), 2) from events group by kind order by kind")
		must(err)
		defer rows.Close()
		for rows.Next() {
			var kind string
			var c int
			var avg float64
			must(rows.Scan(&kind, &c, &avg))
			n += c
			fmt.Printf("    %-7s %8d rows, average amount %v\n", kind, c, avg)
		}
	})
	timed("distinct users and a top 3 by total", func() {
		rows, err := db.Query("select user_id, sum(amount) total from events where kind = 'buy' group by user_id order by total desc limit 3")
		must(err)
		defer rows.Close()
		for rows.Next() {
			var u int
			var t float64
			must(rows.Scan(&u, &t))
		}
	})

	// 2. Write a Parquet file, then query the file without loading it.
	parquet := filepath.Join(dir, "events.parquet")
	timed("copy the table to a Parquet file", func() {
		must(execAll(db, fmt.Sprintf("copy events to '%s' (format parquet)", parquet)))
	})
	st, _ := os.Stat(parquet)
	fmt.Printf("    the Parquet file is %.1f MB\n", float64(st.Size())/1e6)
	var total float64
	timed("sum over the Parquet file, nothing loaded", func() {
		must(db.QueryRow(fmt.Sprintf("select sum(amount) from read_parquet('%s') where kind = 'buy'", parquet)).Scan(&total))
	})

	// 3. A CSV file, with the schema detected.
	csv := filepath.Join(dir, "sales.csv")
	must(os.WriteFile(csv, []byte("region,month,revenue\nnorth,2026-01,120.5\nnorth,2026-02,130\nsouth,2026-01,90.25\nsouth,2026-02,\n"), 0o600))
	rows, err := db.Query(fmt.Sprintf("select column_name, column_type from (describe select * from read_csv_auto('%s'))", csv))
	must(err)
	fmt.Print("3. detected CSV columns:")
	for rows.Next() {
		var name, typ string
		must(rows.Scan(&name, &typ))
		fmt.Printf(" %s %s;", name, typ)
	}
	fmt.Println()
	rows.Close()
	var south float64
	must(db.QueryRow(fmt.Sprintf("select sum(revenue) from read_csv_auto('%s') where region = 'south'", csv)).Scan(&south))
	fmt.Println("   revenue for south, the empty cell is NULL and ignored:", south)

	// 4. Loading rows from Go: one statement per row against the Appender.
	must(execAll(db, "create table readings (id bigint, value double)"))
	timed("20,000 single INSERT statements", func() {
		stmt, err := db.Prepare("insert into readings values (?, ?)")
		must(err)
		defer stmt.Close()
		for i := 0; i < 20000; i++ {
			_, err := stmt.Exec(i, float64(i)/3)
			must(err)
		}
	})
	conn, err := db.Conn(ctx)
	must(err)
	timed("1,000,000 rows through the Appender", func() {
		must(conn.Raw(func(dc any) error {
			app, err := duckdb.NewAppenderFromConn(dc.(driver.Conn), "", "readings")
			if err != nil {
				return err
			}
			for i := 0; i < 1000000; i++ {
				if err := app.AppendRow(int64(i), float64(i)/3); err != nil {
					return err
				}
			}
			return app.Close()
		}))
	})
	must(conn.Close()) // the pool has one connection, so give it back before the next query
	must(db.QueryRow("select count(*) from readings").Scan(&n))
	fmt.Println("    rows in readings:", n)

	// 5. A database file belongs to one process: a second pool in this process shares it, another process is refused.
	file := filepath.Join(dir, "app.duckdb")
	first, err := sql.Open("duckdb", file)
	must(err)
	must(first.Ping())
	second, err := sql.Open("duckdb", file)
	must(err)
	fmt.Println("5. a second connection pool in the same process:", firstLine(second.Ping()))
	out, _ := exec.Command(os.Args[0], "child", file).CombinedOutput()
	fmt.Println("   a second process opening the same file:", strings.TrimSpace(string(out)))
	second.Close()
	first.Close()
}

func execAll(db *sql.DB, qs ...string) error {
	for _, q := range qs {
		if _, err := db.Exec(q); err != nil {
			return fmt.Errorf("%s: %w", q, err)
		}
	}
	return nil
}

func firstLine(err error) string {
	if err == nil {
		return "no error"
	}
	s := err.Error()
	for i := range s {
		if s[i] == '\n' {
			return s[:i]
		}
	}
	return s
}

DuckDB v1.4.1
    buy      1250000 rows, average amount 48.8
    click    1250000 rows, average amount 48.8
    scroll   1250000 rows, average amount 48.8
    view     1250000 rows, average amount 48.8
group by kind over 5,000,000 rows                    17ms
distinct users and a top 3 by total                  8ms
copy the table to a Parquet file                     135ms
    the Parquet file is 22.7 MB
sum over the Parquet file, nothing loaded            6ms
3. detected CSV columns: region VARCHAR; month VARCHAR; revenue DOUBLE;
   revenue for south, the empty cell is NULL and ignored: 90.25
20,000 single INSERT statements                      1.974s
1,000,000 rows through the Appender                  81ms
    rows in readings: 1020000
5. a second connection pool in the same process: no error
   a second process opening the same file: IO Error: Could not set lock on file: Conflicting lock is held by another process

The driver registers the name duckdb with database/sql: sql.Open("duckdb", "") opens an in memory database, and a file path opens or creates a file database. After that it is ordinary Go SQL code. One caution for in memory databases: each connection in a pool gets its own empty database, so the program calls db.SetMaxOpenConns(1) to make all queries see the same data. With a file database, connections share the data.

Fast aggregates on a lot of rows

The program creates a table of 5,000,000 events with create table events as select ... from range(5000000). DuckDB generated the rows itself: range(n) is a table function, and an array subscript picks one of four kinds. Then it ran an aggregate:

• group by kind over all 5,000,000 rows, returning four groups of 1,250,000 rows each with their average amount: 17ms.
• a filtered aggregate that finds the top three users by total amount for buy events: 8ms.

Those are wall times for the whole query including the Go side reading the result. They are fast because the table is stored by column, only kind and amount are read, the data is compressed and held in cache friendly blocks, and the work runs on every core. The numbers will differ by machine, and the shape holds: scans in the hundreds of millions of values per second per core are normal. Results here came from an Apple silicon laptop.

Querying files without loading them

DuckDB can query files in place. Writing the table out is one statement, copy events to 'events.parquet' (format parquet), which took 135ms and produced a 22.7 MB file for 5,000,000 rows. Then the program queried the file without creating a table: select sum(amount) from read_parquet('...') where kind = 'buy' took 6ms. Parquet is itself columnar and carries per column statistics, so DuckDB reads only the needed columns and skips row groups that cannot match. A directory of Parquet files, even on object storage through the httpfs extension, can be queried with a glob (read_parquet('s3://bucket/events/*.parquet')).

CSV needs no setup either. read_csv_auto('sales.csv') samples the file and works out the delimiter, header and column types. For the small file in the lab it reported region VARCHAR; month VARCHAR; revenue DOUBLE, and the sum of revenue for the south region was 90.25: the last row has an empty revenue cell, which became NULL and was ignored by sum. Type detection is a guess from a sample of rows, so for a production load, pass explicit columns and types, or a sample_size large enough to see the odd rows, since a file whose first thousand rows are numbers and whose last row is the word N/A will either fail or make the column text.

This is the quickest way to answer a one off question about a data file: a Go program, or the DuckDB command line, with one query. It also makes DuckDB a good engine for a reporting or export job: read files or a transactional database, join and aggregate in SQL, and write Parquet or CSV out, without loading anything into a heavier system.

Loading rows from Go

Getting Go data into a table is where the first mistake happens. The straightforward way is a prepared insert statement in a loop. The lab ran 20,000 of them and took 1.974s, about 100 microseconds per row. DuckDB is not designed for single row inserts; each is a small transaction against a columnar store.

The fast path is the Appender. duckdb.NewAppenderFromConn takes the underlying driver connection, which conn.Raw exposes, and AppendRow(values...) adds a row to an in memory buffer that is flushed in large chunks when full and on Close. It loaded 1,000,000 rows in 81ms, a rate about 600 times higher than the single inserts. The count query afterwards showed 1,020,000 rows in the table: the 20,000 from statements and the million from the Appender.

Notes on using it: the Appender needs a single connection, so take one with db.Conn(ctx) and give it back with Close before you query again; with a pool limited to one connection, forgetting that deadlocks, as it did in the first version of this lab. Values must match the column types, and the driver converts Go types. Close the appender, or the last chunk is not written. For data that is already in files or another database, skip Go entirely: insert into t select * from read_parquet(...), or attach PostgreSQL or SQLite with the corresponding extensions, which moves the data inside the engine.

SQL that analytics needs and your database may lack

DuckDB's SQL dialect is rich, and several of its features save a lot of Go code. Window functions (row_number() over (partition by user_id order by ts), running totals, lag and lead) compute things like "each user's second purchase" or "change from the previous day" in one query. group by all groups by every non aggregated column so a long select list needs no repetition. pivot and unpivot turn rows into columns and back. qualify filters on a window function result without a subquery, which makes "the latest row per key" one line. Lists and structs are first class types, so a JSON column can be unnested and queried in place with json_extract or the ->> operator. asof join matches each row to the nearest earlier row in another table, the standard tool for joining events to prices at a time.

None of this needs Go beyond db.Query. A report that you would write as a hundred lines of loops and maps over a slice is usually twenty lines of SQL running in parallel, and easier to check, because you can run the same query in the DuckDB shell on a sample and compare.

Reading from other databases

The extensions system lets DuckDB attach other systems. install postgres; load postgres; attach 'dbname=app host=...' as pg (type postgres) makes tables in a PostgreSQL server visible as pg.public.orders, and a query can join them with a Parquet file in one statement. The sqlite and mysql extensions work the same way. That makes DuckDB a convenient engine for analytical queries on data that lives elsewhere: read what you need through the scanner, aggregate, write Parquet, and keep the heavy scans off your primary database. Two cautions: extensions are downloaded the first time unless you pre install them, which matters in locked down containers, and pushing a filter down to the remote database depends on the extension, so look at the plan to see whether a where clause runs remotely or after a full transfer.

A pattern for batch jobs

A job that builds a daily report from files in object storage fits DuckDB well. It opens an in memory database, sets memory and thread limits, runs one or more copy (select ...) to 'out.parquet' statements that read the day's input files by glob, and exits. There is no loading step, no schema to maintain and nothing left behind. The Go program is a thin wrapper that builds the file list for the day, runs the statements with a context and checks the error. For results you serve repeatedly to users, write them into a table in a PostgreSQL database the application already uses, or serve the Parquet through an API, rather than putting DuckDB behind a web server; it can do that, with the single writer limit and the memory appetite in mind.

Testing is easy: put a small CSV in the repository, run the same query against it, and compare the output to a golden file. The engine is deterministic for a given query and data, as long as you order your results, since a parallel group by returns groups in no promised order.

One writer process

A database file has a lock. The last step of the lab opens the same file twice. A second sql.Open in the same process reported no error, because the driver shares one database instance per file inside a process. A second process was refused: IO Error: Could not set lock on file: Conflicting lock is held by another process. (The real message also names the temporary path and the process id; the lab trims them.) Within a process any number of goroutines can use the database; across processes there is one writer. Several processes can open a file read only (?access_mode=read_only) when no writer holds it. If several services must write, DuckDB is the wrong place for the data; use it as a processing engine over files that a server database or object storage holds.

Practical rules

1. Use it for scans and aggregates, not for serving individual rows to a web request.
2. Prefer files and insert ... select to row by row loads, and the Appender when the data starts in Go.
3. Set SetMaxOpenConns(1) for in memory databases, and release connections you take with db.Conn.
4. Keep it to one writing process, and treat a database file as owned by that process.
5. Set limits. SET memory_limit = '2GB' and SET threads = 4 keep it from taking the machine, which it will do happily by default, and SET temp_directory decides where it spills big sorts and joins.
6. Write Parquet for results that other tools read, and keep the .duckdb file for data you query repeatedly.
7. Test with real data sizes. A query that takes 17ms on 5,000,000 narrow rows can take much longer with wide rows, string heavy columns or large joins, and explain analyze shows where the time goes.

When to choose something else

Pick PostgreSQL when the workload is transactional or has many concurrent writers, and a server database when many programs share the data. Pick ClickHouse when the data is too large for one machine and ingestion is continuous. Pick SQLite for an embedded transactional store. DuckDB is the right answer when a single machine and a single process can do the job, which covers more analytical work than people expect. The project is documented at duckdb.org, the Go driver at go-duckdb, and the file locking rules in the page on concurrency.