Python DuckDB: In-Memory and Persistent Databases
Learn how to create and use DuckDB in-memory and persistent databases in Python, including connection setup, behavioral differences, and how to choose the right mode.

DuckDB can run either entirely in memory or as a persistent database stored in a file on disk. The difference is controlled by the argument you pass to duckdb.connect(). Calling duckdb.connect() with no arguments creates an in-memory database that lives only for the lifetime of the connection. Passing a file path such as duckdb.connect('analytics.db') creates or opens a database file that can be reused after the connection closes.
This article shows how to create and query DuckDB databases in both modes, explains the main behavioral differences, and gives practical guidance for choosing between them.
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. In-memory mode is useful for quick analysis, prototyping, or when you are processing data that is already loaded in the same process. You can also pass the literal string ":memory:" to duckdb.connect() to make the intent clear; the behavior is the same.
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 file on disk, and 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 a good fit 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.
Connection isolation also differs. An in-memory database created by a plain duckdb.connect() call is private to that connection. Two separate 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 locking to manage concurrent access. In practice, concurrent writes from multiple processes are not a supported workflow; if you need that, direct all writes through a single process.
Memory usage differs as well. 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 working with datasets larger than memory, but queries may involve disk I/O and can 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 query external data sources directly. The mode changes where the catalog and data live, not the SQL or Python syntax.
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 database-file I/O, so they are generally faster for workloads that fit in memory. If you are running multiple queries over a dataset that is already loaded in RAM, in-memory mode removes 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. Persistent database files use DuckDB's default storage compression, so the file can be smaller than the raw data. The exact reduction depends on the data types and values.
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. A no-argument duckdb.connect() call creates a private in-memory database, so two callers will not share the same data.
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 no-argument duckdb.connect() call creates its own in-memory 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.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.