# Storing lots of time series

**URL:** <https://discourse.julialang.org/t/storing-lots-of-time-series/115169>\
**Category:** Data\
**Tags:** hdf5, mmap, timeseries\
**Created:** [June 4, 2024, 1:28pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169 "2024-06-04T13:28:20Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![alemelis](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alemelis/32/208433_2.png) [@alemelis](https://discourse.julialang.org/u/alemelis)\
**Post date:** [June 4, 2024, 1:28pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/1 "2024-06-04T13:28:20Z")

</div>

Hello,  
I’ve a large number of time series as result of my simulations.

As today the dataset is made of ~1e7 time series (100 points each), stored on disk in `.csv` files (each one containing 5 of them). I’ll have more (10x more) in the future and I need to find a good way of exploring the dataset.

I am thinking of a SQL database (like timescale) but I wonder if that would be overkill for my use case.

Any recommendation along these lines?

---

<div class="post-metadata">

**Author:** ![stevengj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stevengj/32/71_2.png) [@stevengj](https://discourse.julialang.org/u/stevengj)\
**Post date:** [June 4, 2024, 1:34pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/2 "2024-06-04T13:34:37Z")

</div>

Consider using a binary format designed for scientific data, e.g. HDF5.jl.

1e8 datasets of 100 points each, in double precision, is only about 80GB (and you might also consider single precision to save 50%), which is easily possible to store in a single file. In HDF5 you can store this as a single Nx100 dataset that is “extendable” (N is an “unlimited” dimension), so that N can grow over time. And then you can read slices of this quickly, or even the whole dataset if you have enough RAM. (Unfortunately, HDF5.jl currently has [very limited documentation](https://juliaio.github.io/HDF5.jl/stable/api_bindings/#HDF5.API.h5d_extend) for extendable datasets, but the underlying code supports this.)

---

<div class="post-metadata">

**Author:** ![Satvik](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/satvik/32/20486_2.png) [@Satvik](https://discourse.julialang.org/u/Satvik)\
**Post date:** [June 4, 2024, 1:48pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/3 "2024-06-04T13:48:13Z")

</div>

I would use a SQL database. I’ve found them good even for relatively small use cases, systems like Postgres are extremely well-tested and solve many tricky problems like upserts. It often makes sense to default to using a relational db until you have a specific use case that would benefit from something else.

I haven’t used timescale specifically but I’ve heard good things.

---

<div class="post-metadata">

**Author:** ![chiraganand](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/chiraganand/32/32787_2.png) [@chiraganand](https://discourse.julialang.org/u/chiraganand)\
**Post date:** [June 4, 2024, 2:36pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/4 "2024-06-04T14:36:00Z")

</div>

Similar experience here with HDF5 files, have stored 100million+ records in a single file with 5million/sec average read time (of slices). This was way back in 2016 on an older Intel chip with data stored on a SSD.

I would have recommended SQLite also if it was not for 1e8 datasets, I don’t know how that will perform. I have experience of storing 100+ timeseries tables with 1000+ records in a SQLite file and being able to read any full table (without `WHERE` clauses) very quickly into memory (no performance concrete numbers available).

---

<div class="post-metadata">

**Author:** ![j\_u](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/j_u/32/219081_2.png) [@j\_u](https://discourse.julialang.org/u/j_u)\
**Post date:** [June 4, 2024, 7:05pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/5 "2024-06-04T19:05:06Z")

</div>

As for the databases, you may also consider `QuestDB` (TimeSeries DB, `Java + Rust`). It is an open-source alternative to `kdb+` (the financial time series standard) and is a pure joy to use (easy setup, convenient, and performant). Basic charting can be done in just a second from within the GUI, or, as it is `Postgres-compatible`, with `Grafana` or `Pulse` (`TimeStored`). The recent (8.0) open-source version supports `ZFS`, which you may extend with `BeeGFS`. However, it is, of course, not mandatory for 80GB. Reading can be done with LibPQ.jl, and writing with [QuestDBClient.j](https://github.com/Ochibobo/QuestDBClient.jl) and [QuestDB.jl](https://github.com/questdb/julia-questdb-client) (the latter looks like a work in progress but seems to be faster than the first). There is also a `REST API` and, of course, native support for `CSV` (including GUI).

---

<div class="post-metadata">

**Author:** ![stephancb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stephancb/32/14243_2.png) [@stephancb](https://discourse.julialang.org/u/stephancb)\
**Post date:** [June 4, 2024, 8:31pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/6 "2024-06-04T20:31:52Z")

</div>

For me SQLite (wrapped in Julia) handles ≈1.8e9 records in a single ≈400 GB file fine, definitely much faster than .csv. But I have not benchmarked yet against HDF5, which could be a notch faster regarding the raw reading.

What does “exploring” these 1e8 time series mean? If it is simulated data then presumably a large number of parameters with complex relations were varied for all the different time series? Then the SQL itself and the option to have multiple indices that can be changed in the course of the exploration could be handy. [HDF5 support for indexing](https://de.slideshare.net/slideshow/jxp-50716706/50716706) is limited, or requires extensions.

---

<div class="post-metadata">

**Author:** ![jling](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/jling/32/212909_2.png) [@jling](https://discourse.julialang.org/u/jling)\
**Post date:** [June 4, 2024, 8:43pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/7 "2024-06-04T20:43:14Z")

</div>

Try Arrow.jl

---

<div class="post-metadata">

**Author:** ![j\_u](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/j_u/32/219081_2.png) [@j\_u](https://discourse.julialang.org/u/j_u)\
**Post date:** [June 4, 2024, 11:38pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/8 "2024-06-04T23:38:41Z")

</div>

My `QuestDB` instance is currently quite small, consisting of `10,807 files totaling 4.3 GB`. It runs on a cloud VM with `3 ARM64` cores and `16 GB of RAM` inside a `Podman` container. `BeeGFS` is served by `6 VMs` with mixed bandwidth ranging from `0.5 Gbps to 4 Gbps`. Its storage is located on an `XFS` filesystem, with each volume providing `11,250 IOPS` (this is rather a significant number for the cloud but not particularly impressive for `SSD` or `NVMe`). Hope it helps.

**EXAMPLE TIMES:**

**1.** `SELECT * FROM table;`

**A.** 19,365,625 rows x 18 columns

 ![Screenshot from 2024-06-05 00-46-47](https://global.discourse-cdn.com/julialang/original/3X/d/b/dbc88ccee06045835d5d288bd895ab08782c8758.png)

**B.** 68,969 rows x 11 columns

 ![Screenshot from 2024-06-05 00-48-43](https://global.discourse-cdn.com/julialang/original/3X/b/f/bf866eccb28c633d271a9837169c75d94a918d7d.png)

**C.** 18,981 rows x 8 columns

 ![Screenshot from 2024-06-05 00-50-51](https://global.discourse-cdn.com/julialang/original/3X/8/0/80b574975aa1a8bc89a8e85812d374dc82e4f890.png)

**D.** 1,869,678 rows x 5 columns

 ![Screenshot from 2024-06-05 00-54-09](https://global.discourse-cdn.com/julialang/original/3X/6/4/64925b98779ef608573a414f2ba19c2354baf42b.png)

**2.** QUERY on 3 columns of table A (19,365,625 rows x 18 columns)

> […]  
> V1 ASOF JOIN V2 SAMPLE BY 15m  
> […]  
> avg(correlation) OVER (ORDER BY timestamp RANGE BETWEEN 1 HOUR PRECEDING AND CURRENT ROW) AS rolling\_corr\_1h,  
> avg(correlation) OVER (ORDER BY timestamp RANGE BETWEEN 3 HOUR PRECEDING AND CURRENT ROW) AS rolling\_corr\_3h,  
> avg(correlation) OVER (ORDER BY timestamp RANGE BETWEEN 1 DAY PRECEDING AND CURRENT ROW) AS rolling\_corr\_1d  
> […]

 ![Screenshot from 2024-06-05 00-57-16](https://global.discourse-cdn.com/julialang/original/3X/b/6/b609e9a04aa37fd7f524106ee21c16f298651f22.png)

 ![Screenshot from 2024-06-05 00-58-57](https://global.discourse-cdn.com/julialang/original/3X/d/1/d16b3640f1136b8b4be67ba3775e8702038f292c.png)

EDIT: Naturally, the filesystem’s metadata is all warmed up; however, feel free to make your own judgment. In fact, to be honest, I bet that even @stevengj could be cracking a smile right now! :- )

---

<div class="post-metadata">

**Author:** ![stevengj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stevengj/32/71_2.png) [@stevengj](https://discourse.julialang.org/u/stevengj)\
**Post date:** [June 5, 2024, 1:12am UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/9 "2024-06-05T01:12:13Z")

</div>

> [@alemelis](#):
>
> As today the dataset is made of ~1e7 time series (100 points each), stored on disk in `.csv` files (each one containing 5 of them). I’ll have more (10x more) in the future and I need to find a good way of exploring the dataset.

If the data is this regular you could also just call something like

```julia
open(f -> write(f, array), filename, "a")

```

to append each length-100 dataset to a raw binary file, and

```julia
using Mmap
f = open(filename, "r")
n = (filesize(f) ÷ sizeof(Float64)) ÷ 100
a = mmap(f, Matrix{Float64}, (100, n))

```

to open the whole dataset simultaneously as a memory-mapped matrix, at which point you can do any analysis you want (e.g. slicing out a random subset of columns) easily and efficiently.

---

<div class="post-metadata">

**Author:** ![alemelis](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alemelis/32/208433_2.png) [@alemelis](https://discourse.julialang.org/u/alemelis)\
**Post date:** [June 5, 2024, 10:23am UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/10 "2024-06-05T10:23:27Z")

</div>

Thank you all for the replies.

HDF5 looks interesting and the idea closely match my current folder structure. However, I don’t have enough RAM to hold the entire dataset, would that be a problem? This is why I was thinking of a SQL database

Regarding the post processing, I don’t know what I’ll do with the data yet.  
I’m going to heavily use metadata from each simulation. I’m simulating a virtual population of patients so I may want to look at all the 65+ yo patients with height \> 1.50m and low blood pressure. I imagine this is doable with the HDF5 approach by carefully matching the matrix indices to relevant metadata but it’s possibly easier to do that in SQL

---

<div class="post-metadata">

**Author:** ![emil\_hedevang\_sgre](https://avatars.discourse-cdn.com/v4/letter/e/b5a626/32.png) [@emil\_hedevang\_sgre](https://discourse.julialang.org/u/emil_hedevang_sgre)\
**Post date:** [June 5, 2024, 11:11am UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/11 "2024-06-05T11:11:08Z")

</div>

To use HDF5, you don’t need to be able to hold all data or even an entire dataset in memory, you can read smaller pieces. Personally, I like HDF5 to hold data in an organized manner, and I find it fairly easy and convenient to use, and the performance is good enough.

---

<div class="post-metadata">

**Author:** ![stevengj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stevengj/32/71_2.png) [@stevengj](https://discourse.julialang.org/u/stevengj)\
**Post date:** [June 5, 2024, 5:01pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/12 "2024-06-05T17:01:59Z")

</div>

> [@alemelis](#):
>
> However, I don’t have enough RAM to hold the entire dataset, would that be a problem?

No, not for HDF5. And not for the `Mmap` solution I suggested as well — the whole point of a memory mapped array is that the operating system will only “page in” those portions of the array that you are actually accessing.

---

<div class="post-metadata">

**Author:** ![stevengj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stevengj/32/71_2.png) [@stevengj](https://discourse.julialang.org/u/stevengj)\
**Post date:** [June 5, 2024, 7:56pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/13 "2024-06-05T19:56:51Z")

</div>

4 posts were split to a new topic: [Storge format for lots of strings](https://discourse.julialang.org/t/storge-format-for-lots-of-strings/115256)

---

<div class="post-metadata">

**Author:** ![stephancb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stephancb/32/14243_2.png) [@stephancb](https://discourse.julialang.org/u/stephancb)\
**Post date:** [June 5, 2024, 7:35pm UTC](https://discourse.julialang.org/t/storing-lots-of-time-series/115169/16 "2024-06-05T19:35:46Z")

</div>

Storing the actual time series directly in a file and `mmap` for access is probably the most efficient method there is, followed by HDF5.

But for the metadata an SQL database should be considered. It seems that there are at least three parameters, patient age, height, and blood pressure. Only one of them, for example height, could be monotonically “matched” to the matrix row nr (enabling binary search). Even this would not work, if you need to do more simulations after seeing initial results of the “exploration”. In a relational database indices can be freely created in any combination and order, which map to the row nr of the matrix of timeseries. The indices update automatically when more data/timeseries are added. “Carefully matching … to relevant metadata” can be done in HDF5 or directly stored tables, but it becomes rather complex for more than one parameter. SQL is kind of made for such a job.

So I would perhaps store the timeseries directly, use mmap, but design an SQL database for the metadata.