Skip to contents

Open a DuckDB database

Source: R/connect.R
db_connect.Rd

Opens a connection to a DuckDB file and caps its memory. DuckDB's own default memory limit is 80% of the RAM: a large aggregation next to a local LLM or another R session can then crash the machine. db_connect() sets a limit (6 GB by default, option coRpusdb.memory_limit); beyond it DuckDB spills to disk.

Some operations cannot spill: aggregates that build a string or a list per group (string_agg(), list()) keep each group in memory. In central_bank_database, the md5 of the whole text of each document (md5(string_agg(text ORDER BY position)) over 1.5 million rows) failed under 6 GB and peaked at 11.4 GB: raise the limit for such steps (option coRpusdb.memory_limit in the project's configuration), or hash rows before aggregating them (md5(string_agg(md5(text), ''))).

A process that writes locks the file for every other process, readers included. Use db_connect_read_only() in reports and analyses, and close connections with db_disconnect().

Usage

db_connect(
  path,
  read_only = FALSE,
  memory_limit = getOption("coRpusdb.memory_limit", "6GB"),
  threads = getOption("coRpusdb.threads"),
  temp_directory = NULL
)

Arguments

path

Character. Path of the .duckdb file (created if it does not exist and read_only is FALSE).

read_only

Logical. Open read-only; several readers can then share the file.

memory_limit

Character or NULL. DuckDB memory limit (e.g. "6GB"); NULL keeps DuckDB's default. A warning is given when it exceeds the memory available (free_memory_gb()).

threads

Integer or NULL. Number of DuckDB threads (option coRpusdb.threads); NULL keeps DuckDB's default (all cores). Each thread holds its own buffers: fewer threads lower the memory peak a little, at a cost in speed (bench/results_memory.csv).

temp_directory

Character or NULL. Folder where DuckDB spills intermediate results; by default DuckDB uses <path>.tmp.

Value

A DBI connection.

See also

Other connections: db_connect_read_only(), db_disconnect(), db_snapshot_path(), resolve_db_path()

Examples

path <- tempfile(fileext = ".duckdb")
con <- db_connect(path)
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "document", data.frame(doc_id = "a:1"))
db_disconnect(con)