Duckdb: DuckDB disk space consumption with NULLs

[dustalov]

I have a pet project that currently uses MonetDBLite as the columnar data storage for HTTP access logs.

When I discovered DuckDB, I was amazed by the opportunity to have an embedded columnar database which is actively maintained by the developers. I immediately gave it a try and it worked well for me.

Due to the nature of the problem domain, my database contains a lot of NULL values. I noticed that the DuckDB database consumed five times more disk space than MonetDBLite on the same data (roughly 19M vs. 3.5M, correspondingly; https://github.com/dustalov/ballcone/issues/2).

Schema

("datetime" BIGINT NOT NULL,
 "date" INTEGER NOT NULL,
 "host" TEXT NOT NULL,
 "method" TEXT NOT NULL,
 "path" TEXT NOT NULL,
 "status" SMALLINT NOT NULL,
 "length" INTEGER NOT NULL,
 "generation_time" DOUBLE NOT NULL,
 "referer" TEXT,
 "ip" TEXT NOT NULL,
 "country_iso_code" TEXT,
 "platform_name" TEXT,
 "platform_version" TEXT,
 "browser_name" TEXT,
 "browser_version" TEXT,
 "is_robot" BOOLEAN)

I found that currently DuckDB does not support sparse tables. Could this be related to my issue? Could you please advise me how I can tune DuckDB to get the disk footprint matching MonetDBLite?

If it helps, I can provide a dataset for reproducing the issue.

[Mytherin]

The storage in DuckDB is still very much a work in progress; there is still a lot to be done there and we have a lot of plans to work on and improve this. It is currently the "least finished" part of the system.

The way it works currently is that DuckDB stores data in blocks of 256KB. However, the data is stored rather naïvely in the blocks. That is to say, it will not combine partially filled blocks. This means that if your data is quite small, there might be a lot of mostly-empty blocks (e.g. 4KB/256KB); but these mostly empty blocks will still take up the full 256KB on disk as it is all in a single file.

For your example table, there are 16 columns. Even if the table only had a single row, that means right now the table would take up 16*256KB=4MB. Text columns can also overflow to other blocks, depending on how long the text is and how many strings there are, which can result in additional space wastage currently. This can easily result in ~8MB of storage even if you don't have many rows in the table. These are all problems we are aware of, and on the agenda for us to fix once we get around to it.

Another problem is that right now checkpoints are completed in their entirety, which means the database can be inside the file twice. The reason for that is that, during a checkpoint, the data has to be written before the old data can be deleted to ensure no data is lost. This will be fixed once we get incremental checkpointing working (#49).

Relating to many NULL values/sparse tables, we are also working on compressed storage (#575), which should drastically shrink the table size for you and also speed up query execution.

To conclude, right now there is not much you can do, unless you want to help us improve the storage yourself. In that case we would be happy to review any PRs :)

We would be happy to have an example dataset, however! This will help us a lot in benchmarking the storage footprint of DuckDB. One of our goals is definitely to have small database files.

[dustalov]

Thank you for a great response! I plan to provide the benchmarking code during the weekend.

[dustalov]

I am happy to share my benchmarking code with you. It requires a dataset I gathered in my early experiments: logs.zip; its unzipped size is roughly 13M.

Code

#!/usr/bin/env python3

import sys
from pathlib import Path
from typing import Union, List

import duckdb
import monetdblite

CREATE_TABLE = 'CREATE TABLE logs (datetime BIGINT NOT NULL, date INT NOT NULL, ' \
               'host TEXT NOT NULL, method TEXT NOT NULL, path TEXT NOT NULL, ' \
               'status SMALLINT NOT NULL, length INT NOT NULL, generation_time DOUBLE NOT NULL, ' \
               'referer TEXT, ip TEXT NOT NULL, country_iso_code TEXT, platform_name TEXT, ' \
               'platform_version TEXT, browser_name TEXT, browser_version TEXT, is_robot BOOLEAN)'

SELECT_COUNT = 'SELECT COUNT(*) FROM logs'

SELECT_LIMIT = 'SELECT * FROM logs LIMIT 1'


def size(path: Union[str, Path], glob: str) -> int:
    return sum(f.stat().st_size for f in Path(path).glob(glob) if f.is_file())


def execute(db: Union[monetdblite.Connection, duckdb.DuckDBPyConnection], sql: str) -> List:
    cursor = db.cursor()
    if isinstance(db, duckdb.DuckDBPyConnection):
        cursor.begin()
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.commit()
    cursor.close()
    return result


def first(csv: Path):
    db_duckdb = duckdb.connect('benchmark-duckdb')
    execute(db_duckdb, CREATE_TABLE)
    execute(db_duckdb, f"COPY logs FROM '{csv}' (HEADER)")

    db_monetdb = monetdblite.make_connection('benchmark-monetdb')
    execute(db_monetdb, CREATE_TABLE)
    execute(db_monetdb, f"COPY OFFSET 2 INTO logs FROM '{csv}' DELIMITERS ',' NULL AS ''")

    print('DuckDB count and row', execute(db_duckdb, SELECT_COUNT), execute(db_duckdb, SELECT_LIMIT))
    print('MonetDBLite count and row', execute(db_monetdb, SELECT_COUNT), execute(db_monetdb, SELECT_LIMIT))

    db_duckdb.close()
    db_monetdb.close()


def second():
    db_duckdb = duckdb.connect('benchmark-duckdb')
    db_monetdb = monetdblite.make_connection('benchmark-monetdb')

    print('DuckDB count and row', execute(db_duckdb, SELECT_COUNT), execute(db_duckdb, SELECT_LIMIT))
    print('MonetDBLite count and row', execute(db_monetdb, SELECT_COUNT), execute(db_monetdb, SELECT_LIMIT))

    db_duckdb.close()
    db_monetdb.close()


def main():
    csv = Path('logs.csv').absolute()

    first(csv)
    print('size of logs.csv:', csv.stat().st_size)
    print('size of DuckDB:', size(Path.cwd(), 'benchmark-duckdb*'))
    print('size of MonetDBLite:', size(Path.cwd(), 'benchmark-monetdb/**/*'))

    print()
    second()
    print('size of reopened DuckDB:', size(Path.cwd(), 'benchmark-duckdb*'))
    print('size of reopened MonetDBLite:', size(Path.cwd(), 'benchmark-monetdb/**/*'))


if __name__ == '__main__':
    sys.exit(main())

The logs.csv dataset is imported into both databases. They are then closed, reopened, and closed again. After each closing, database sizes are printed.

I ran this code on a CentOS 7 (x86_64) machine with duckdb=0.1.7 and monetdblite=0.6.3.

DuckDB count and row [(143038,)] [(1581454848, 737466, 'localhost', 'POST', '/rpc/v2/stage', 204, 0, 30.006000518798828, None, '127.0.0.1', 'XX', None, None, None, None, True)]
MonetDBLite count and row [[143038]] [[1581454848, 737466, 'localhost', 'POST', '/rpc/v2/stage', 204, 0, 30.006000518798828, None, '127.0.0.1', 'XX', None, None, None, None, 1]]
size of logs.csv: 13542591
size of DuckDB: 15614471
size of MonetDBLite: 6908586

DuckDB count and row [(143038,)] [(1581454848, 737466, 'localhost', 'POST', '/rpc/v2/stage', 204, 0, 30.006000518798828, None, '127.0.0.1', 'XX', None, None, None, None, True)]
MonetDBLite count and row [[143038]] [[1581454848, 737466, 'localhost', 'POST', '/rpc/v2/stage', 204, 0, 30.006000518798828, None, '127.0.0.1', 'XX', None, None, None, None, 1]]
size of reopened DuckDB: 21246175
size of reopened MonetDBLite: 7509896

Step 1: the size of DuckDB is 15M and the size of MonetDBLite is about 7M.
Step 2: the size of DuckDB is 20M and the size of MonetDBLite is almost the same.

[Mytherin]

Thanks, this is very helpful! Once we get to work on the storage again I will integrate it into the benchmark suite.