# How to store years of minute bars locally

Parquet and DuckDB, ArcticDB or a time-series server. What breaks is a re-run backfill that lands beside old rows, and bars stamped on two clocks.

*https://stockmarketstack.com/how-to/store-minute-bars-locally · next to Tick Data Storage & Time-Series Databases*

**Answer:** Store unadjusted bars in UTC, in one of two shapes: Parquet files by year or month read in place by DuckDB, or ArcticDB, which versions every write. QuestDB and TimescaleDB are for when one process appends while another reads. The engine is the easy part. The work is making a re-run of the backfill replace the rows it already wrote instead of adding a second copy, and agreeing what a bar's timestamp means.

## The tools that do this

*In the order this page recommends trying them. Paid placement does not affect this order.*

1. [DuckDB](https://stockmarketstack.com/tools/duckdb.md) — Parquet files per year or month, queried where they sit. MIT, no server, nothing to buy — but its APPEND option writes new files, so a re-run doubles rows.
2. [ArcticDB](https://stockmarketstack.com/tools/arcticdb.md) — A DataFrame per symbol, every write versioned. update() replaces a date range, so re-runs are safe. The vendor says business use needs a paid licence.
3. [QuestDB](https://stockmarketstack.com/tools/questdb.md) — A server with DEDUP UPSERT KEYS on timestamp and symbol, so a re-sent bar replaces the old one. Apache-2.0, one machine in the open build.
4. [TimescaleDB](https://stockmarketstack.com/tools/timescaledb.md) — Postgres with hypertables. A unique index on symbol and time plus ON CONFLICT makes loads repeatable, and upserts reach compressed chunks.
5. [ClickHouse](https://stockmarketstack.com/tools/clickhouse.md) — Columnar and compact, but ReplacingMergeTree drops duplicates only when parts merge, so a re-sent day double-counts until you query with FINAL.

## The short way

For one machine and a research workload, the shortest thing that holds up is Parquet files by
year, written by [DuckDB](https://stockmarketstack.com/tools/duckdb) and read by it in place. Nothing runs in the background,
and the files stay readable by pandas, Polars or whatever you move to next.

```python
import os
import duckdb

con = duckdb.connect()

def write_year(year: int) -> None:
    out_dir = f"store/minute/year={year}"
    os.makedirs(out_dir, exist_ok=True)
    tmp = f"{out_dir}/bars.parquet.tmp"
    # raw files: symbol, ts (already UTC), open, high, low, close, volume, unadjusted
    con.sql(f"""
        COPY (
            SELECT symbol, ts, open, high, low, close, volume
            FROM read_csv('raw/{year}/*.csv')
            ORDER BY symbol, ts
        ) TO '{tmp}' (FORMAT parquet)
    """)
    os.replace(tmp, f"{out_dir}/bars.parquet")   # a re-run replaces the year, never adds to it
```

```sql
SELECT ts, open, high, low, close, volume
FROM read_parquet('store/minute/*/bars.parquet', hive_partitioning = true)
WHERE symbol = 'AAPL'
  AND year BETWEEN 2019 AND 2020
ORDER BY ts;
```

Three decisions are baked into those twenty lines, and each one is on this page because the
obvious alternative fails.

The **unit of rewrite is the partition**, not the row. A year is rebuilt whole from the raw files
and swapped in with a rename, so running the job twice leaves the same file, not two. That is
deliberately not DuckDB's own `PARTITION_BY ... APPEND`, which the documentation describes as
writing each batch under a new UUID filename: correct for a live feed, and a duplicate of every
row for a backfill you ran twice.

**Sorted by symbol, then time.** A query for one name over a year reads one contiguous run of each
file instead of every block in it.

**Year, not day and not symbol.** DuckDB's documentation says writing many small partitions is
expensive and puts the floor at about 100 MB each. A regular US session is 390 minutes, so one
symbol-year is about 98,000 rows — a few megabytes — and a day of 500 symbols under 200,000. Both
are far below that floor. For ten thousand symbols, about a billion rows a year, a month per
partition is the better split.

## What the options are

**Files plus an embedded engine.** [DuckDB](https://stockmarketstack.com/tools/duckdb) over Parquet as above: MIT-licensed, no
server and no paid edition. A DuckDB database file has one writing process at a time, which is a
real limit for a feed and none at all for history held in Parquet.

**An embedded store with versions.** [ArcticDB](https://stockmarketstack.com/tools/arcticdb) keeps a pandas DataFrame per
symbol in a library on local disk (LMDB) or S3, and every `write`, `append` and `update` creates a
new version, which you can read back later. For bars that versioning is the useful part: last
month's research can be re-run against last month's data after you have repaired this month's. It
is Python only, and source-available under BUSL 1.1. The licence text, the README and the FAQ
disagree about commercial use, and the FAQ, the strictest of the three, says business use needs a
paid licence.

**A time-series server.** [QuestDB](https://stockmarketstack.com/tools/questdb) is the answer when something keeps appending
while something else queries. Its SQL has `SAMPLE BY` to build five-minute or hourly bars from the
minute table and `ASOF JOIN` when you add quotes later. [TimescaleDB](https://stockmarketstack.com/tools/timescaledb) is the
same job inside Postgres. Continuous aggregates keep coarser bars current, and every Postgres tool
already speaks to it.

**A columnar analytics engine.** [ClickHouse](https://stockmarketstack.com/tools/clickhouse) stores bars very compactly and
scans them fast. It is more machine than most bar stores need, and its deduplication model is the
one below that needs the most care.

## Where this breaks

**Re-running the backfill writes the rows twice.** Every backfill gets re-run: a vendor corrects
a day, a job dies halfway through 2019, you add fifty symbols. Whether the second run replaces or
duplicates is an engine property, and the defaults are rarely the safe ones.

- [ArcticDB](https://stockmarketstack.com/tools/arcticdb)'s `append` accepts only data whose first row is at or after the
  last stored row. It refuses data that starts earlier and accepts data that starts *on* the last
  stored timestamp, so a
  resumed job that re-sends its last bar writes that minute twice. `update` is the repeatable
  operation, and its documentation is exact about the cost: the whole range between the first and
  last index of the new data is replaced. A re-sent day with a gap in it deletes the stored bars
  inside the gap.
- [QuestDB](https://stockmarketstack.com/tools/questdb) deduplicates only when a table is created with `DEDUP UPSERT KEYS`, and
  the designated timestamp must be one of the keys. With `(ts, symbol)` as keys, a re-sent bar
  replaces the old one: last write wins. Keys added later do not clean up what is already there:
  the documentation says turning deduplication on affects new inserts only.
- [TimescaleDB](https://stockmarketstack.com/tools/timescaledb) needs a unique index for `ON CONFLICT` to do anything, and on a
  hypertable that index must include the time column. Once it exists, `ON CONFLICT DO UPDATE`
  repairs rows in place, including in compressed chunks.
- [ClickHouse](https://stockmarketstack.com/tools/clickhouse)'s `ReplacingMergeTree` removes duplicates only when parts merge,
  at a time the documentation does not promise. Until then a sum over a re-loaded day counts it
  twice unless the query says `FINAL`.

Test this before the store holds anything you care about: load one week, load it again, count.

**The timestamp is a convention.** A minute bar stamped 09:30 covers 09:30 to 09:31 if the vendor
labels by the start of the interval, and 09:29 to 09:30 if it labels by the end. Vendors do not
agree, and not all of them say. [FirstRate Data](https://stockmarketstack.com/tools/firstrate-data) documents the start of the
bar in US Eastern time. Alpaca's bar reference documents the timestamp's format — RFC-3339 with
nanosecond precision — and not which edge of the minute it marks. Load an archive from one and
top it up from the other and you can get a series that is quietly off by a minute, or by several
hours, at the seam.

The hours are the part that bites. US daylight saving time begins on the second Sunday of March and
ends on the first Sunday of November, so the 09:30 Eastern open is 13:30 UTC for most of the year
and 14:30 UTC for the rest. A store that mixes local and UTC stamps, or one that stores Eastern time
with no zone at all, puts the open at two different UTC times depending on the season. Convert
everything to UTC at load time and keep the vendor's convention in a column or a note. QuestDB's own
documentation says it stores every timestamp in UTC without zone information and tells you to
convert local-time sources with `to_utc` before they are written.

To check a vendor's convention, compare one day they both cover. Find the bar that contains the
opening print in each and see which label it carries.

**Minute bars do not add up to the official daily bar.** FirstRate Data states that its daily bars
are a separate series built from the exchange's official figures and cannot be reproduced by
aggregating its minute bars. Store the daily series as its own table rather than deriving it from the
minute table and trusting the result to match a broker statement.

**Adjusted bars go stale in storage.** The next split puts every adjusted row already on disk out
of step with a fresh pull, so store unadjusted bars beside the
[corporate action](https://stockmarketstack.com/glossary/corporate-action) table and adjust when you read. [How to backfill
years of minute bars](https://stockmarketstack.com/how-to/backfill-minute-bars) makes that case at the download end, and [why
adjusted close differs](https://stockmarketstack.com/guides/why-adjusted-close-differs) is the long version.

**A nightly append fragments the store.** ArcticDB slices a symbol into segments of 100,000 rows
by default, so a year of one symbol's minute bars fits roughly one segment. Appending one day at a
time instead writes 252 segments of 390 rows each. Its API has a compaction call for exactly this,
`compact_data`, which replaces the deprecated `defragment_symbol_data`. The Parquet equivalent is a
directory of daily files that should have been one annual file. Either way, the fix is to rewrite
the period in bulk once it is complete.

## If you outgrow this

**When bars stop being enough**, the next step is trades and quotes, which is two to three orders of
magnitude more data and a different question about engines: [choosing a database for tick
data](https://stockmarketstack.com/guides/storing-tick-data). The as-of join matters there and barely at all here.

**When the bars are not yet on disk**, getting them is its own purchase with its own traps —
survivorship, adjustment and the licence. That is [how to backfill years of minute
bars](https://stockmarketstack.com/how-to/backfill-minute-bars).

**When somebody else will query the store**, read the data licence before the storage licence.
FirstRate Data, Kibot and Alpaca all restrict redistribution of what they sell, and a shared
database is where internal use quietly stops being internal.

**When a live feed joins the history**, the embedded options stop fitting. DuckDB has one writing
process, and ArcticDB does not support concurrent writers to one symbol outside its staging calls.
[QuestDB](https://stockmarketstack.com/tools/questdb) and [TimescaleDB](https://stockmarketstack.com/tools/timescaledb) are built for concurrent writes and
reads.

The rest of the field is in [tick data storage](https://stockmarketstack.com/categories/tick-data-storage).

## FAQ

### Should minute bars be partitioned by symbol or by date?

By time, and coarser than a day. DuckDB's own guidance is at least 100 MB per partition. A day of 500 symbols is under 200,000 rows, around 10 MB even before compression, and one symbol for a whole year is about 98,000 rows. Both are far below that floor, so partitioning on either multiplies small files. A year or a month per partition, sorted by symbol then time inside it, keeps single-name reads fast without shredding the store.

### Can I just append each night's bars to the same store?

You can, and it works until the night the job runs twice. ArcticDB's append refuses data that starts before the stored data ends but accepts data that starts on the same timestamp, so the boundary minute can be written twice. DuckDB's APPEND writes a new file under a fresh UUID every time. Use an operation that replaces a range, or a key the engine deduplicates on.

### How do I check that a store has no duplicate bars?

Group by symbol and timestamp and keep the groups with more than one row. On a clean store that query returns nothing, and it is the test to run after every backfill that was interrupted and resumed. Then count bars per symbol per day against the session length. A regular US session is 390 minutes, so a day with more than 390 regular-session bars has been loaded twice, while fewer can simply be minutes with no trades.

### Is ArcticDB free to use for this?

For personal, academic or non-commercial use, by the vendor's own licensing FAQ. It is source-available under BUSL 1.1 rather than open source. The vendor's documents disagree about how far the commercial restriction reaches, and the most restrictive of them says any business use needs a paid licence. DuckDB, QuestDB's open build and ClickHouse are Apache-2.0 or MIT.

## Sources

1. [Partitioned Writes — DuckDB documentation](https://duckdb.org/docs/current/data/partitioning/partitioned_writes.html) — DuckDB Foundation, read 2026-09-26
2. [Library API — ArcticDB documentation](https://docs.arcticdb.io/latest/api/library/) — ArcticDB, read 2026-09-26
3. [Arctic and LibraryOptions API — ArcticDB documentation](https://docs.arcticdb.io/latest/api/arctic/) — ArcticDB, read 2026-09-26
4. [Deduplication — QuestDB documentation](https://questdb.com/docs/concepts/deduplication/) — QuestDB, read 2026-09-26
5. [Timestamps and time zones — QuestDB documentation](https://questdb.com/docs/concepts/timestamps-timezones/) — QuestDB, read 2026-09-26
6. [Upsert data — Tiger Data documentation](https://www.tigerdata.com/docs/use-timescale/latest/write-data/upsert) — Tiger Data, read 2026-09-26
7. [Enforce constraints with unique indexes — Tiger Data documentation](https://www.tigerdata.com/docs/use-timescale/latest/hypertables/hypertables-and-unique-indexes) — Tiger Data, read 2026-09-26
8. [Historical stock bars — API reference](https://docs.alpaca.markets/reference/stockbars) — Alpaca, read 2026-09-26
9. [ReplacingMergeTree table engine — ClickHouse documentation](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree) — ClickHouse, read 2026-09-26
10. [Daylight Saving Time](https://www.nist.gov/pml/time-and-frequency-division/popular-links/daylight-saving-time-dst) — National Institute of Standards and Technology, 2026-02-09

*Last updated 2026-09-26. A reference page, corrected in place — not a dated post.*