python - Django + Postgres + Large Time Series

Question

I am scoping out a project with large, mostly-uncompressible time series data, and wondering if Django + Postgres with raw SQL is the right call.

I have time series data that is ~2K objects/hour, every hour. This is about 2 million rows per year I store, and I would like to 1) be able to slice off data for analysis through a connection, 2) be able to do elementary overview work on the web, served by Django. I think the best idea is to use Django for the objects themselves, but drop to raw SQL to deal with the large time series data associated. I see this as a hybrid approach; that might be a red flag, but using the full ORM for a long series of data samples feels like overkill. Is there a better way?

Answer 1

If I understand your thoughts correctly, you are considering storing the time series in PostgreSQL, one time series record in one database row. Don't do that.

On the one hand, the problem is theoretical. Relational databases (and I think most databases) are based on the premise of row independence, whereas the records of a time series are physically ordered. Of course, database indexes provide some order for database tables, but that order is meant to speed up searching or to present results alphabetically or in some other order; it does not imply any natural meaning to that order. Regardless how you order them, each customer is independent of other customers, and each customer's purchase is independent of his other purchases, even if you can get them altogether chronologically in order to form the customer's purchase history. The interdependence of time series records is much stronger, which makes relational databases inappropriate.

In practice, this means that the disk space taken up by the table and its indexes will be huge (maybe 20 times larger than storing the time series in files), and reading time series from the database will be very slow, something like an order of magnitude slower than storing in files. It will also not give you any important benefit. You probably aren't ever going to make the query "give me all time series records whose value is larger than X". If you ever need such a query, you will also need a hell of other analysis which the relational database has not been designed to perform, so you will read the entire time series into some object anyway.

So each time series should be stored as a file. It might be either a file on the file system, or a blob in the database. Despite the fact that I've implemented the latter, I believe the former is better; in Django, I'd write something like this:

class Timeseries(models.model):
    name = models.CharField(max_length=50)
    time_step = models.ForeignKey(...)
    other_metadata = models.Whatever(...)
    data = models.FileField(...)

Using a FileField will make your database smaller and make it easier to make incremental backups of your system. It will also be easier to get slices by seeking in the file, something that's probably impossible or difficult with a blob.

Now, what kind of file? I'd advise you to take a look at pandas. It's a python library for mathematical analysis that has support for time series, and it should also have a way to store time series in files.

I linked above to a library of mine which I don't recommend you to use; on the one hand it doesn't do what you want (it can't handle granularity finer than a minute, and it has other shortcomings), and on the other it's outdated - I wrote it before pandas, and I intend to convert it to use pandas in the future. There's a book, "Python for data analysis", by the author of pandas, which I've found invaluable.

Update (2016): There's also InfluxDB. Never used it and therefore I have no opinion, but it is definitely something that you need to examine if you are wondering how to store time series.

Update (2020-02-07): There's also TimescaleDB, an extension to PostgreSQL.

Update (2020-08-07): We changed our software (again) so that it stores the data in the database using TimescaleDB. We are already versed in PostgreSQL and it was easy to learn some TimescaleDB. The most important concrete advantage is that we can make queries like "find all locations where there was >50mm rain within 24 hours in 2019", something that would be very difficult when storing data in flat files. Another advantage is the integrity checks—over the years we had a few time series with duplicate rows because of little bugs here and there. The drawbacks are also significant. It uses 10 times more disk space. We may need to change our PostgreSQL backup policy because of that. It's slower. It takes maybe one second to retrieve a time series with 300k records. This was instant before. We needed to implement caching for retrieving time series, which wasn't needed before.