Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 38 ยท Getting Started

In-Memory and Persistent Databases

Understand where DuckDB keeps data: transient in-memory databases, single-file persistent databases, read-only mode, views over files, and how to choose between them.

Every DuckDB session is connected to one database. That database lives either in your computer's memory, where it disappears when you close the session, or in a single file on disk, where it survives. Choosing between them is the first design decision in any DuckDB project, and the answer is often "neither: just query the files directly".

In-memory databases

If you start the shell with no argument, or connect from Python with the special name :memory:, DuckDB creates a database that exists only in RAM:

Terminal
duckdb
Python
import duckdb
con = duckdb.connect()            # same as duckdb.connect(":memory:")

Anything you create here, such as tables, views or macros, is gone when the process ends. That is exactly what you want for:

  • quick exploration of a file you will not touch again,
  • unit tests that need a clean database every time,
  • pipeline steps that read inputs, transform them and write outputs to Parquet.

In-memory databases are not limited by RAM alone. When a query needs more working memory than DuckDB's limit (about 80 percent of your RAM by default), it spills intermediate results to a temporary folder, so large sorts and joins still finish, just more slowly.

Persistent databases

Give DuckDB a file name and it stores everything there:

Terminal
duckdb sales.duckdb
Python
con = duckdb.connect("sales.duckdb")

If the file does not exist, DuckDB creates it. The common extensions are .duckdb or .db, but DuckDB does not care. The whole database (all schemas, tables, views, sequences and macros) lives in that one file. You can copy it, attach it to an email, or put it in version control if it is small. While it is open you will also see a sales.duckdb.wal file: the write-ahead log, which makes writes crash-safe and is merged into the main file at checkpoints.

Where does the data live?

In memory Fast and temporary
Database file One file, survives restarts
Plain files Parquet or CSV, no database at all

Many projects need only the third one.

1 / 2

Checking where you are

Two ways to see the current database in the shell:

SQL
-- DuckDB (read-only example)
SELECT current_database();
PRAGMA database_list;

In the CLI, the dot command .databases also lists them. An in-memory database is named memory; a file called sales.duckdb is named sales.

Creating and using tables

Tables work the same in both modes. This portable example behaves in DuckDB as it does in the stand-in below:

SQL Stand-in: a small table and a view

Press Run (or Ctrl + Enter) to see the result here.

A view stores a query, not data. That matters in DuckDB because a view can sit on top of files:

SQL
-- DuckDB (read-only example)
CREATE VIEW events AS SELECT * FROM 'logs/events.parquet';
SELECT count(*) FROM events;

The view stores only the query text. Each time you use events, DuckDB reads the Parquet file again, so the view always shows the current contents. That is a clean way to build a "database" with no copied data at all.

Importing data versus querying in place

You can choose between two strategies:

Strategy How Good when
Query in place SELECT ... FROM 'data.parquet' or a view The file is read once, or changes often
Import into a table CREATE TABLE t AS SELECT * FROM 'data.csv' You query the data many times, want indexes or updates

Importing is a good idea for CSV files, since text has to be parsed every time you read it. Parquet is already columnar, so querying it in place is usually as quick as a table.

SQL Stand-in: copy a query result into a table ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

CREATE TABLE ... AS SELECT (often called CTAS) is how you "import" in DuckDB: run any query, including one over a file, and keep its result. The stand-in uses the bookstore data set.

Read-only mode and sharing

If several programs only need to read the same file, open it read-only. This is safe to do from many processes at once:

Terminal
duckdb -readonly sales.duckdb
Python
con = duckdb.connect("sales.duckdb", read_only=True)

Without read_only, DuckDB takes an exclusive lock for writing. A second process that tries to open the same file for writing fails with an error about the lock. Remember this when a notebook and a script both seem "stuck": one of them holds the file.

The checkpoint and file size

DuckDB periodically checkpoints: it writes the changes from the log into the main file and compresses the data. Deleted rows leave free space that later inserts reuse, so the file does not always shrink. To rebuild a compact copy, you can export and re-import, or create a fresh file:

SQL
-- DuckDB (read-only example)
ATTACH 'compact.duckdb' AS fresh;
COPY FROM DATABASE sales TO fresh;

You will see ATTACH properly in Transactions, files and attached databases.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to tables in a database started with plain duckdb and no file name?

    Without a file name the database is in memory only and disappears when you quit.
  2. 2. What does a view over a Parquet file store?

    A view is a saved query. It always reflects the current file contents.
  3. 3. Which statement copies a query result into a new table?

    CREATE TABLE ... AS SELECT (CTAS) runs a query and stores its result as a table.
  4. 4. How do you let several processes safely read the same database file?

    Read-only connections do not need the exclusive write lock, so several can read the file at once.

Summary

  • A session talks to one database: in memory (temporary) or in a single file (persistent).
  • duckdb or :memory: gives RAM-only; duckdb file.duckdb persists, with a .wal file while open.
  • Views over files give you a database without importing anything.
  • Use CREATE TABLE ... AS SELECT to import, especially for CSV files.
  • Open files read-only when several processes need to read them.

Next: Your first analysis