RDRYUSLOG DEV
Back to Blog
Python

Python DuckDB: In-Memory and Persistent Databases

python duckdb in memory and persistent databases: Learn how to create and use DuckDB databases in Python, covering in-memory and persistent file-based modes, their beh...

DuckDBIn-Memory DatabasePersistent DatabasePython SQLAnalytics
Illustration of Python code connecting to both an in-memory DuckDB instance and a persistent DuckDB file on disk, showing the two modes side by side.

python duckdb in memory and persistent databases requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When you call duckdb.connect() without arguments, DuckDB creates an in-memory database that lives entirely in RAM. That is the default mode, and it is useful for analytical workloads where data fits in memory and does not need to survive the process. To work with a persistent database, you pass a file path to duckdb.connect(), and DuckDB stores the database on disk. Understanding how these two modes differ affects everything from connection lifecycle to performance and data durability. This article explains how to use both modes in Python, what changes between them, and how to decide which one fits your use case.

Creating an In-Memory Database

The simplest way to start with DuckDB is to create an in-memory database. The duckdb.connect() function returns a connection object that manages the database. When you call it without arguments, DuckDB uses a temporary in-memory database that is destroyed when the connection closes.

import duckdb

conn = duckdb.connect()
conn.execute("CREATE TABLE t (id INTEGER, name VARCHAR)")
conn.execute("INSERT INTO t VALUES (1, 'Alice'), (2, 'Bob')")
print(conn.execute("SELECT * FROM t").fetchall())
conn.close()

After conn.close(), the database and all its data are gone. The in-memory mode is ideal for quick analysis, prototyping, or when you are processing data that is already loaded in the same process. You can also explicitly pass ":memory:" to duckdb.connect() to make the intent clear, though the behavior is identical.

Creating a Persistent Database

To persist data across connections and process restarts, pass a file path to duckdb.connect(). DuckDB creates the file if it does not exist and opens it if it does. The database is stored in a single file on disk, and all changes are written to that file.

import duckdb

conn = duckdb.connect('analytics.db')
conn.execute("CREATE TABLE events (event_id INTEGER, value DOUBLE)")
conn.execute("INSERT INTO events VALUES (1, 12.5), (2, 7.25)")
conn.close()

When you close the connection, the data remains in analytics.db. Later, you can open the same file again and query the data:

conn = duckdb.connect('analytics.db')
print(conn.execute("SELECT * FROM events").fetchall())
conn.close()

Persistent databases are the right choice when you need to share data between runs, keep historical records, or work with datasets larger than available memory. DuckDB uses a buffer manager to move data between memory and disk, so you are not limited by RAM, though performance depends on the storage layer.

Key Behavioral Differences Between the Modes

The most obvious difference is durability. An in-memory database loses all data when the connection closes or the process exits. A persistent database survives because it is written to a file. This difference affects how you structure your code and what guarantees you can rely on.

Another difference is connection isolation. In-memory databases are private to the connection that created them. Two separate duckdb.connect() calls without a file path create two independent databases that cannot see each other. With a persistent file, multiple connections can open the same database, but DuckDB uses file-level locking to manage concurrent access. By default, only one process can write to a persistent database at a time, though multiple processes can read if no writer is active.

Memory usage also differs. In-memory databases keep all data in RAM, which makes queries fast but limits the dataset size to available memory. Persistent databases use a buffer manager that caches pages in memory and flushes them to disk as needed. This allows you to work with datasets larger than memory, but queries may involve disk I/O and be slower for large scans.

Loading Data and Querying in Both Modes

DuckDB's Python API is the same regardless of the mode. You can load data from existing Python objects, CSV files, Parquet files, or even query external data sources directly. The mode only changes where the catalog and data live.

For example, you can create a table from a Pandas DataFrame in both modes:

import duckdb
import pandas as pd

df = pd.DataFrame({'x': [1, 2, 3], 'y': ['a', 'b', 'c']})

# In-memory
conn_mem = duckdb.connect()
conn_mem.register('df_view', df)
conn_mem.execute("CREATE TABLE t AS SELECT * FROM df_view")
print(conn_mem.execute("SELECT * FROM t").fetchall())
conn_mem.close()

# Persistent
conn_persist = duckdb.connect('data.duckdb')
conn_persist.register('df_view', df)
conn_persist.execute("CREATE TABLE t AS SELECT * FROM df_view")
conn_persist.close()

In both cases, the register method creates a temporary view that can be used in SQL. The persistent connection writes the table to the file, while the in-memory connection discards it on close.

Performance and Memory Considerations

In-memory databases avoid disk I/O entirely for reads and writes, so they are generally faster for workloads that fit in memory. If you are doing multiple queries over a dataset that is already loaded in RAM, the in-memory mode eliminates the overhead of file system access.

Persistent databases trade some speed for durability and capacity. The buffer manager controls how much memory is used for caching. By default, DuckDB tries to use available memory, but you can adjust the memory_limit configuration option to control the buffer size. For example:

conn = duckdb.connect('large.duckdb')
conn.execute("SET memory_limit='2GB'")

This limits DuckDB's memory usage to 2 GB, forcing more aggressive disk usage. The optimal setting depends on your hardware and workload. If you set the limit too low, queries may become slow due to excessive page swapping; if too high, you risk exhausting system memory.

Another performance factor is data compression. DuckDB compresses data on disk by default, so a persistent database file can be smaller than the equivalent raw data. In-memory databases do not compress data in the same way, so they may consume more memory than the on-disk representation.

Operational and Production Considerations

When you move from a script to a production service, the choice between in-memory and persistent databases becomes more critical. In-memory databases are ephemeral by design, so they are not suitable for applications that require data recovery after a crash. Persistent databases give you durability, but you must manage the file and its backups.

Concurrency is another concern. DuckDB is designed for analytical workloads, not high-concurrency OLTP. A single connection can execute queries, but multiple connections to the same persistent file are subject to locking. If you need concurrent writes, you may need to use a single writer process or consider a client-server database. In-memory databases are inherently single-connection, so they avoid locking but cannot be shared.

Transactions behave similarly in both modes. DuckDB supports ACID transactions, and you can commit or roll back changes. In persistent mode, a transaction is durable only after it commits and the data is flushed to disk. In-memory transactions are not durable, but they still provide atomicity and isolation within the connection.

Choosing Between In-Memory and Persistent Databases

Use an in-memory database when:

  • Your dataset fits comfortably in memory.
  • You do not need to persist data between runs.
  • You are prototyping or running ad-hoc analysis.
  • You are embedding DuckDB in an application that loads data fresh each time.

Use a persistent database when:

  • You need to store data for later use.
  • Your dataset exceeds available memory.
  • You want to share data across multiple processes or runs.
  • You need to recover data after a crash.

There is no strict rule that forces one mode over the other. Many projects start with in-memory for development and switch to persistent when they need to keep results or process larger datasets. The API is identical, so the change is often a single argument to duckdb.connect().

Managing the Connection Lifecycle

Whether you use in-memory or persistent mode, you should close connections explicitly when you are done. DuckDB releases file locks and flushes buffers on close. If you forget to close a persistent connection, the file may remain locked, preventing other processes from writing. Using a context manager is a clean way to handle this:

with duckdb.connect('app.duckdb') as conn:
    conn.execute("INSERT INTO logs VALUES (1, 'start')")

The connection is closed automatically when the with block exits. This pattern works for both modes and ensures that resources are released even if an exception occurs.

For long-running processes that open many connections, be mindful of file descriptors and memory. Each in-memory connection allocates its own database, so creating many connections can consume significant RAM. Persistent connections share the same file but still hold locks and buffers. In practice, you should reuse a single connection for the lifetime of a task rather than opening a new one for every query.

python duckdb in memory and persistent databases: Practical | RYUSLOG DEV