Snapshot of a database for readers

A writing script keeps a read-only snapshot of its database so that reports and analyses can read while it runs:

• db_snapshot_path() gives the path of the snapshot (<name>_snapshot.duckdb next to the database);

• db_refresh_snapshot() copies the database to it, between two write batches;

• db_remove_snapshot() deletes it at the end of the writing script, since readers can then open the database itself.

Usage

db_snapshot_path(path)

db_refresh_snapshot(con, path, min_interval_hours = 0, allow_full_copy = FALSE)

db_remove_snapshot(path)

Arguments

path

Character. Path of the database file.

con

A DBI connection that writes to path, with no open transaction.

min_interval_hours

Number. Do nothing if the snapshot is younger than this; lets a loop call db_refresh_snapshot() after every batch at no cost. 0 always refreshes.

allow_full_copy

Logical. When the file system does not support copy-on-write ("reflink") copies, copy the whole file instead (it doubles the disk space used).

Value

db_snapshot_path(): the path. db_refresh_snapshot(): TRUE if the snapshot was refreshed, invisibly. db_remove_snapshot(): NULL, invisibly.

Details

db_refresh_snapshot() first writes the write-ahead log into the database file (CHECKPOINT), so that the file alone is complete, then copies it to a temporary file and renames it: a reader that has the old snapshot open keeps reading it until it disconnects. On file systems that support it (Btrfs, XFS), cp --reflink makes the copy almost free in time and space; elsewhere the copy is refused unless allow_full_copy = TRUE. Set options(coRpusdb.snapshot = FALSE) to disable snapshots in a project.

See also

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

Examples

path <- tempfile(fileext = ".duckdb")
db_snapshot_path(path)
#> [1] "/tmp/RtmpKqItGK/file79980758bbb7e_snapshot.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_refresh_snapshot(con, path, allow_full_copy = TRUE)
#> ℹ Read-only snapshot refreshed: /tmp/RtmpKqItGK/file79980758bbb7e_snapshot.duckdb
db_disconnect(con)
db_remove_snapshot(path)
#> ℹ Read-only snapshot removed.