Experiment with Postgres, SQLite, and .parquet+DuckDB

[mark-torres10]

Issue Description

We want to experiment between 3 approaches for the data layer:

• Postgres
• SQLite
• .parquet + DuckDB

Access patterns

Postgres and SQLite are more OLTP-based, while .parquet + DuckDB will be stronger for OLAP use cases. I think we'll probably want to err on the side of more OLAP-style queries, as we generally care less about knowing specific information for a given user and we care about larger aggregation-style analytics queries.

Queries to benchmark against

Let's benchmark against the following queries:

• "Give me 100 posts from today on Bluesky" (can be filtered on timestamp and just get LIMIT 100)
• "Give me the top 100 users who posted most often in the past week."
• "How many posts had text containing the word 'Trump' in the past week?"
• "How many posts per day were there the past 3 weeks?"

For completeness, let's also review how OLTP queries would look:

• "Given an author ID, give me the last 10 posts they wrote."
• "Given an author ID, give me the last 10 posts they liked."

We'll run each of these queries n times (e.g., 5 times) and collect each of the metrics below.

We specifically really care only about read queries as these will be our most common pattern. We don't anticipate high write throughput (we'll likely adopt a pattern of writing in micro-batches, as we've found this to work well and to circumvent write concurrency problems). Given that our expected workload is read-heavy, we focus on benchmarking reads.

Functional requirements

Let's create the following tables:

• post: post_id, author_id (FK with user.user_id), created_at (str), text
• user: user_id (PK), created_at (str)
• like: like_id (PK) author_id (FK with user.user_id), post_id (FK with post.post_id), created_at (str)
• follow: follower_id (FK to user.user_id), followee_id (FK to user.user_id)

Generating mock data

Generating users

Let's create 5,000 users. Use the faker library to create fake users. Define this in a generate_users() function.

Define timestamp using the CREATED_AT_FORMAT in lib/timestamp_utils.py. Randomly vary, for the users, a created_at timestamp between 6 months and 1 month ago. Create a UserModel pydantic model and make sure that the output of generate_users() is typed as list[UserModel].

Generating posts

Define this logic in a generate_posts() function that takes as input users from generate_users().

For each user, generate a random number of posts (random normal, mean of 100, SD of 5), then clamp to a minimum of 0. Use the faker library for generating fake users + content. Use something like faker.paragraph(nb_sentences=3) to get ~300 chars. Define the created_at for a post as being a sampled timestamp, starting from when the user was created and ending 3 days ago. Then convert that timestamp to a string with format defined by CREATED_AT_FORMAT in lib/timestamp_utils.py. Create a PostModel pydantic model and make sure that the output of generate_posts() is typed as list[PostModel].

Let's set the max number of posts to 1M.

Generate likes

Define this logic in a generate_likes() function that takes as input users (from generate_users()) and posts (from generate_posts(). The function should create LikeModel pydantic records.

Then generate like records by going through each user and randomly sampling a post to like. Generate random number of posts to like (random normal, mean of 200, SD of 5) then clamp to a minimum of 0. Sample without replacement from posts not authored by that same user (so, authors can't like their own posts), and skip users if no eligible posts exist.

Define the created_at for each like as a sampled timestamp between the later of (user.created_at, post.created_at) and 3 days ago, then format it using CREATED_AT_FORMAT from lib/timestamp_utils.py. If that time window is invalid, skip that like candidate.

Create globally unique like_id values and cap the total number of generated likes at 1.5M. The function return type should be list[LikeModel]

Generate follows

Define this logic in a generate_follows() function that takes as input users (from generate_users()). The function should create FollowModel pydantic records with fields: follower_id and followee_id, matching the directed follow table schema described in the issue text.

For follow, do an O(n**2) loop that goes through for each user and randomly goes through all other users and with a fixed probability of 0.001, creates a follow record for that combination of 2 users (note: a user may follow another user and not have it be reciprocal, to match real-life dynamics).

Store follows in a set of (follower_id, followee_id) tuples while generating to guarantee uniqueness, then convert to FollowModel objects at the end. Cap the total number of generated follows at 30,000 and stop early once that limit is reached.

Putting the data generation together

Put this script in a random_data_generator.py and export the simulated data in post.parquet, user.parquet, like.parquet, and follow.parquet.

Store so that the resulting file format looks something like:

experiments/database_experiments_2026_05_23/
    random_data_generator.py
    mock_data/
       *.parquet

Creating the testing setups

For each backend (Postgres, SQLite, DuckDB), we'll separate engine-specific setup from shared benchmarking logic. All three backends will:

• Pre-load or point at the same mock Parquet data once at startup.
• Run only read queries during benchmarking.
• Use a shared harness in main.py and harness.py for concurrency, timing, and metrics aggregation.

Common benchmark harness

Implement a shared query runner in main.py and metrics.py that:

• Loads the Parquet mock data from mock_data/ and initializes each backend's schema/metadata once before any benchmarks run.
• Defines the six benchmark queries (4 OLAP, 2 OLTP) as engine-agnostic logical queries, with backend-specific SQL, or API implementations, in postgres/, sqlite/, and duckdb/.
• For the OLTP queries ("last 10 posts by author" and "last 10 posts liked by author"), sample a fixed set of 100 author IDs once from the mock data and reuse this same author ID set for all engines, to keep results comparable.
• Supports a configurable number of threads per backend (default=8). Each thread executes the same read query n times (default=3), and the harness aggregates per-thread measurements into overall latency and throughput metrics per query per backend. For example, a given query should be executed 8 * 3 = 24 times by default.
• Use a short warm-up phase before measurement: for each backend and query, run a small number of executions (e.g., 1-2 per thread) and discard these samples. The goal here is to approximate a hot-cache, steady-state environment.
• After warm-up, runs the measured phase: for each query and backend, execute the query n times per thread (default=3), collecting per-execution wall-clock timings and then computing p50/p90/p99 plus observed QPS for that query at the chosen concurrency level.
• Wraps the entire benchmark for a backend with resource sampling using psutil/resource to capture "best-effort" CPU time, peak RSS, and sustained memory usage during that backend's run.

The harness should run each backend in isolation: main.py will run Postgres first, then SQLite, then DuckDB, writing postgres_results.json, sqlite_results.json, and duckdb_results.json in data//, plus a shared metrics.json and metadata.json summarizing cross‑backend comparisons and runtime parameters (e.g., thread counts, n, mock data sizes, warm‑up length).

Per-resource setup

Postgres setup

Under postgres/, implement:

• A one‑time loader that reads the Parquet files from mock_data/ (via Python, not Postgres FDWs) and bulk‑loads them into local Postgres tables user, post, like, and follow matching the schema defined in the issue.
• Indexes appropriate for the benchmark queries, e.g., on post.created_at, post.author_id, like.author_id, like.post_id, and any text search indexes needed for the “Trump” query.
• A connection pool and single‑process, multi‑threaded query runner: each thread checks out a connection from the pool and repeatedly executes its assigned read query, returning raw timing data to the shared harness.
• Functions that implement the six logical queries in Postgres SQL, parameterized by date ranges and author IDs where relevant.
• Storage metrics collection: use Postgres functions (e.g., pg_database_size, pg_relation_size, pg_indexes_size) to record table/index sizes and WAL growth for the test database after data load and after the benchmark run.

SQLite setup

Under sqlite/, implement the “1 file per table” topology:

• Create separate SQLite files per table, e.g., user.sqlite, post.sqlite, like.sqlite, and follow.sqlite, each with a single table matching the schema defined above and using the same PRAGMA optimizations documented elsewhere (WAL mode, cache sizing, mmap, etc.).
• Write a one‑time loader that reads the Parquet files and writes into the four SQLite files, using batched executemany() transactions to keep load times reasonable.
• Implement read queries by opening the relevant SQLite files, executing table‑local queries, and doing any necessary joins in Python memory rather than via cross‑file SQL joins. For example, the “last 10 posts liked by author” query would read likes for the given author from like.sqlite and then look up the corresponding posts in post.sqlite.
• Use a single process with multiple threads, where each thread maintains its own set of SQLite connections to the per‑table files (or uses a small per‑thread connection cache), and repeatedly executes its assigned read query, returning per‑execution timing data to the harness.
• Collect storage metrics by recording the sizes of each *.sqlite file as well as any associated WAL/journal files at rest after data load and after the benchmark run.

DuckDB + Parquet

Under duckdb/, implement a “pure Parquet” configuration:

• Instead of importing data into DuckDB tables, register the Parquet files in mock_data/ as DuckDB views or tables directly backed by the Parquet files (e.g., CREATE VIEW post AS SELECT * FROM read_parquet('mock_data/post.parquet');).
• Implement the six logical queries in DuckDB SQL over these Parquet‑backed relations, using appropriate date filters, grouping, and text search to match the Postgres and SQLite semantics.
• Use a single process with multiple threads; each thread holds its own DuckDB connection (or uses a shared connection if that fits DuckDB’s concurrency model in your version) and repeatedly executes its assigned read query.
• For resource metrics, in addition to the psutil/resource‑level sampling, use DuckDB’s EXPLAIN ANALYZE or profiling APIs in a separate, single‑threaded pass for each query to approximate per‑query Parquet bytes read, temp spill usage, and scan behavior. These per‑query profile summaries can be stored alongside the main timing metrics.
• For storage metrics, record the total size and count of the Parquet files in mock_data/ and, if applicable, any DuckDB metadata files created during registration or query execution.

Result outputs

Under data/<timestamp>/, write:

• postgres_results.json, sqlite_results.json, and duckdb_results.json: For each backend and each query:
  • Per‑query latency distribution (p50, p90, p99), mean, stddev.
  • Achieved QPS for that query at the configured concurrency level.
  • Number of executions and number of threads.
• metrics.json:
  • Cross‑backend summary for each query (e.g., side‑by‑side p50/p90/p99, QPS, approximate CPU time, peak RSS).
  • DuckDB‑specific Parquet bytes read and spill metrics where available.
  • Storage metrics for each backend (DB size, index size, WAL/journal size, Parquet totals).
• metadata.json:
  • Benchmark configuration (n, thread counts, warm‑up iterations, timestamp ranges used, sample author IDs, engine versions, host machine specs).

The harness should prioritize using shared tooling across engines (e.g., one common definition of the six logical queries, shared author ID sampling, shared timing and aggregation code), keeping the engine‑specific code limited to connection management, SQL text, and any special profiling hooks.

Final directory structure

The final setup should look something like:

experiments/database_experiments_2026_05_23/
    random_data_generator.py
    data/
        <timestamp>/
            sqlite_results.json
            postgres_results.json
            duckdb_results.json
            metrics.json # aggregated results across all integrations
            metadata.json # runtime results
    mock_data/
       *.parquet
    metrics.py # common tooling for tracking metrics
    {other common shared tooling}
    sqlite/
    duckdb/
    postgres/
    main.py # main file to run the experiments. Store results in data/<timestamp>/, using the `lib/timestamp_utils.py` for timestamp.

NOTE to AI agent: do NOT add sys.path.insert or update sys.path in any way. Make all scripts runnable from root, running it with PYTHONPATH=. uv run python ... (and make this instruction clear in each docstring).

Prioritize shared tooling across

Non-functional requirements

• We want to be able to support a steady stream of max 100 QPS (average 50 QPS). We can also, if the writers have trouble, write in micro-batches on an interval and just write the immediate records as .json.
• Append-only is OK.
• We don't really need to support multiple writers unless it becomes a problem with throughput.
• For query patterns, let's support the queries below for now.
• We want this to be supported in HPC, which puts severe limitations for network connections. This means setting up Postgres is pretty prohibitive (and we'd likely end up doing it in AWS as a result).

Metrics to collect

Due to our preference to work on HPC, we'll likely be limited to working with .parquet + DuckDB or SQLite, but we still want to get benchmarks regardless.

• Latency: p50/p90/p99
• Throughput: QPS for reads/writes. We'll want to benchmark Postgres (which supports high concurrency levels) against DuckDB and SQLite (which won't really support concurrency). We'll benchmark this though it's a lower priority for us as this'll be an internal pipeline and we can artificially constrain the number of queries.
• Resource efficiency: CPU time, peak RSS, sustained memory, temp spill usage (sort/hash spilling). For Parquet: bytes read from disk per query (column pruning effectiveness proxy)
• Storage efficiency: On-disk size (raw vs stored), compression ratio, index size (Postgres), WAL size growth (SQLite/Postgres). For Parquet: file count, row group size, partition directory count

Concurrency models

Each of Postgres, SQLite, and DuckDB have difference concurrency models, which will significantly affect throughput.

SQLite concurrency

SQLite has the simple possible concurrency model, because it's designed to be simple. It implements file-level locking. Multiple processes can read from SQLite but only 1 can write.

SQLite default vs. WAL modes

By default, SQLite locking works something like this:

1. UNLOCKED: No process is interacting with the DB.
2. SHARED: Multiple processes can read data simultaneously. As long as any process holds a SHARED lock, no writes are allowed.
3. RESERVED: A process announces it intends to write to the DB. Other connections can read, but no other process can claim a RESERVED lock.
4. PENDING: The writer is ready to commit its changes. It waits for all active SHARED locks to be cleared. At this stage, no new readers are allowed.
5. EXCLUSIVE: The writer has cleared all readers and holds total control. It writes directly to the disk file. No other reads or writes can happen.

So, in short, it works something like:

1. Nothing happening.
2. Multiple processes can read the DB.
3. A process announces that it wants to write to the DB. Once it "calls dibs", nobody else can announce that they want to write.
4. The writer process says "OK, I'm ready to write now", and waits for the reader processes to disconnect.
5. Once all the reader processes have disconnected, the writer process that called dibs can now do what it pleases to the DB. No other processes can be connected at this step.

Since the writer process writes directly to the DB file (though it has a "rollback journal" for emergencies), it has to be pretty strict about locking. Once it hits the PENDING step, no readers can connect, and the existing readers have to finish work. During the EXCLUSIVE step, no reads can happen.

In practice, this leads to readers and writers tripping over each other. A long-running SELECT * reader process can block a pending writer at the PENDING step. A long INSERT writer can block readers during the PENDING and EXCLUSIVE steps.

The way around this is to separate the medium for reads and writes. Currently, this is all on the same file (the DB SQLite file). However, SQLite also supports a write-ahead log (WAL), which lets readers connect to the main SQLite file while writes (still 1 writer at a time) to a write-ahead log. This decouples readers and writers so that they don't interrupt each other (at the expense of a slight consistency vs. availability tradeoff since new readers will be reading a slightly outdated version of the DB during the time after the writer has finished its write and before the WAL is written to the DB).

Exploring SQLite speedups

Assuming you are using a single process, there are speedups that can be made. We explored these in this SQLite implementation to squeeze as much performance as possible out of a single SQLite instance (here is the writeup). For whatever SQLite implementation we use here, we should use the same optimizations used in that implementation:

• Database transaction grouping: grouping a bunch of writes into an explicit executemany() transaction block.
• Horizontal DB sharding: since everything is stored at the file level, we can consider file-level sharding and explore, e.g., 1 DB file per table. We can benchmark this against the "1 DB file" approach (which will likely not scale very well as the file gets too large).
• Pragma optimizations:

conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA cache_size=-64000")
conn.execute("PRAGMA mmap_size=268435456")
conn.execute("PRAGMA temp_store=MEMORY")

Pros/cons of splitting SQLite across multiple files

We could, as mentioned above, either store everything in 1 DB file, or store each table in 1 DB file (e.g., "posts.sqlite", "likes.sqlite", etc.).

Pros:

• Each table's size can scale independently: a large "posts" table doesn't affect the read/write access to the "likes" table.
• Isolated read/write barriers: A writer for the "posts" table wouldn't collide with a writer for the "likes" table.

Cons:

• No native SQL joins: would need to load all results from each table within the Python process and then join them in-memory.
• Loss of referential integrity: can't enforce foreign key constraints across files. Cascading deletions and references must be handled within application code.
• Loss of global transactions: you lose atomic ACID compliance across domains. If a user deletes their account, you can't delete their profile, posts, and likes into a single SQL transaction.

We'll probably go with the "1 file per table" approach, if we did use SQLite. However, at this point, we'll probably run into problems related to creating multiple files for a given table (e.g., "posts") as it gets larger. At this point, probably better to bite the bullet and just go with .parquet + DuckDB, because at least we can design the architecture up-front to assume multiple files, rather than assuming 1 file per atomic operation (e.g., "get all posts") that we'll have to change later with scale (unless we're OK with opening a 50GB file every time we need to get a post). The scaling problem that justifies splitting 1 large DB sqlite file into table-specific SQLite files can be taken to its natural conclusion since those table-specific SQLite files will themselves get very large.

Postgres concurrency

Postgres uses multi-version concurrency control (MVCC). This enables high concurrency out-of-the-box without traditional locks. It has a core design principle: "reads never block writes, and writes never block reads".

When you modify a row in Postgres, it doesn't overwrite the existing data on disk. Instead, it creates a new version of that row (called a tuple) alongside the new one. When a transaction executes a query, Postgres builds a snapshot, which collects the state of the table (including which transactions are committed, in-flight, etc.). Because different transactions can hold different snapshots, Transaction A can read an old version of a row while Transaction B can concurrently write a new version in the same row. However, Postgres can support things like checking to see if there are anomalies between queries that would cause them to conflict.

DuckDB concurrency

DuckDB occupies somewhat of a middle ground between Postgres and SQLite. DuckDB has two concurrency modes:

• Single-process read/write: one process can both read/write. However, multiple threads in that process can read/write using MVCC like Postgres.
• Multi-process read-only: multiple processes can read, but no writes allowed.

At the process level, concurrency for DuckDB is basically identical to SQLite. Within a process, DuckDB has their own version of MVCC, but specialized for OLAP purposes.

Managing differences in concurrency models

For supporting high QPS, Postgres is the clear winner. DuckDB has a single-process lock while SQLite has a DB-wide lock. However, we could possibly ameliorate this by implementing micro-batching through a single writer process. We did this in our previous Bluesky research project where we had a process that connected to the Bluesky data stream and wrote real-time records to disk as JSON while we had a concurrent process that, on a cron schedule, wrote the latest records as a micro-batch to .parquet.