Adding TimescaleDB hypertables to Django

Sun 15 August 2021

So, you have a Django app, or you want to use Django to build an application, and it just so happens that your application requires sorting and serving time-series data (if it doesn’t, then this post is going to bore you). Luckily, TimescaleDB is just a PostgreSQL extension; so using TimescaleDB is as simple as ensuring that you have psycopg2 installed, and you are good to go! However, there are no free lunches.

To spare you from some of the pain, I will just go over some of the things I did when I whipped up www.servaltracker.com, and explain why I did things this way. Of course, if you have a better way of achieving the same or better results; drop me a tweet!

Assumptions first

Okay, before we get into the weeds - I’m going to assume the following things:

You already have setup a Django project,
- If you haven’t yet, I would suggest using cookiecutter-django to start your project.
You have a working knowledge of Django, PostgreSQL, and TimescaleDB.
You have a working Django, Timescale environment setup… If you use the cookiecutter, then this is as simple as a docker-compose up

Adding a TimescaleDB Hypertable

With your Django project all setup, and you have created a Django app for these models, the first issue you will likely run into is: how the hell do you set up a simple Hypertable that has a time, parameter and value field, without a pesky id column being created too?

Let’s look at a code example inside models.py:

from django.db import models
from django.db.models.fields import DateTimeField

class Parameter(models.Model):
    parameter_name = models.CharField(max_length=200)

    def __str__(self):
        return self.parameter_name


class TimeSeries(models.Model):
    # NOTE: We have removed the primary key (unique constraint) manually, since
    # we don't want an id column.
    time = DateTimeField(primary_key=True)
    parameter = models.ForeignKey(Parameter, on_delete=models.RESTRICT)
    value = models.FloatField(null=False)

The first that you might notice is that time is a primary key, and you might also be upset by this because doing so will add a UNIQUE constraint to that field. Well noticed, and excellent point! So, the reason why I make time the primary key, is so that Django doesn’t create an id column automatically. Obviously, this will still result in a UNIQUE constraint, but we will fix that in the migrations.

Setting up the migrations

Awesome! You have just added your freshly minted time series models to Django, now lets make some sweet sweeeeet migrations. Running the following:

python manage.py makemigrations

This will now crap out a migration file into <django-project>/<django-app>/migrations/0001_initial.py.

Note, I don’t know what your django-project’s name is, nor the django-app that these models will be living in, so every time you see <django-project> or <django-app>, substitution as necessary.

Editing the Django migration

It’s now time we cracked this file open, and did a little manipulation:

# Generated by Django 3.1.12 on 2025-07-09 09:36

from datetime import datetime
from django.db import migrations, models
import django.db.models.deletion


class Migration(migrations.Migration):

    initial = True

    dependencies = []

    operations = [
        migrations.CreateModel(
            name="Parameter",
            fields=[
                (
                    "id",
                    models.AutoField(
                        auto_created=True,
                        primary_key=True,
                        serialize=False,
                        verbose_name="ID",
                    ),
                ),
                ("parameter_name", models.CharField(max_length=200)),
            ],
        ),
        migrations.CreateModel(
            name="TimeSeries",
            fields=[
                ("time", models.DateTimeField(primary_key=True, serialize=False)),
                ("value", models.FloatField()),
                (
                    "parameter",
                    models.ForeignKey(
                        on_delete=django.db.models.deletion.RESTRICT,
                        to="<django-app>.parameter",
                    ),
                ),
            ],
        ),
        migrations.RunSQL(
            "ALTER TABLE <django-app>_timeseries DROP CONSTRAINT <django-app>_timeseries_pkey;"
        ),
        migrations.RunSQL(
            "SELECT create_hypertable('<django-app>_timeseries', 'time', chunk_time_interval => INTERVAL '5 days');"
        ),
    ]

Dropping unique the constraint

So, everything here is mostly standard and should match what you have, except for the last two migration operations. Remember when I mentioned that Django will make the time field on the TimeSeries table a UNIQUE key? Well, the following migration RunSQL yeets that from under Django’s nose:

migrations.RunSQL(
    "ALTER TABLE <django-app>_timeseries DROP CONSTRAINT <django-app>_timeseries_pkey;"
),

We are doing this because we don’t actually want unique records; what we want are (time, parameter, value) tuples that can represent our time series data. You should never need to do this for regular tables, this is a cheeky and specialized trick that should almost never be used for regular use cases. Django expects there to be a unique key on the table for the case where you want this table to join onto other tables. Removing the unique key will likely break regular use.

Creating the hypertable

The next weird RunSQL you noticed (well spotted!) is this cheeky chunk:

migrations.RunSQL(
    "SELECT create_hypertable('<django-app>_timeseries', 'time', chunk_time_interval => INTERVAL '5 days');"
),

This will create the TimescaleDB hypertable. It’s important to change the chunk_time_interval to suit your needs, I set it to chunk in 5 day intervals for illustrative purposes. It must be noted that this will only ever run on an empty table!

What about compression?

Excellent point! We have not set compression! Unfortunately, I wasn’t able to add a compression policy in the same migration, so I just had to create another one! Create an empty file named: 0002_addingcompression.py, and then throw this into it:

# Generated by Django 3.1.12 on 2049-07-09 09:58

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ("<django-app>", "0001_initial"),
    ]

    operations = [
        migrations.RunSQL(
            "ALTER TABLE <django-app>_timeseries SET (timescaledb.compress, timescaledb.compress_segmentby = 'parameter_id');"
        ),
        migrations.RunSQL(
            "SELECT add_compression_policy('<django-app>_timeseries', INTERVAL '5 days')"
        ),
    ]

First, we need to add compression to the table; and we are segmenting by parameter_id, since this is the name Django gave the foreign key that linked the Parameter table to the TimeSeries table. Finally, we add a compression policy that instructs TimescaleDB to compress hypertable chunks that are older than 5 days.

Conclusion

That’s all there is to setting up TimescaleDB hypertables in your Django app!