search

endlessm / azafea

Service to track device activations and usage metrics
Mozilla Public License 2.0
10 stars 2 forks source link

Try out timescaledb #9

Open bochecha opened 5 years ago

bochecha commented 5 years ago

Timescaledb is a PostgreSQL extension: https://github.com/timescale/timescaledb

It seems like it could help manage the database going forward, adding things like automatic partitioning of time series.

It could even improve performance depending on the size of the data: https://blog.timescale.com/timescaledb-vs-6a696248104e/

However, there are two caveats to pay attention to:

  1. how does this work with SQLAlchemy?

    it's probably possible to make the two work nicely together, but it might take a bit of experimentation at first;

  2. we need to be very careful to stick to the open source edition

    timescaledb is open-core, and the source is available even for the proprietary additions, so we need to carefully check which of its features we use and not depend on the nonfree ones (at least not in Azafea itself, custom event handlers are free to do that if they are willing to pay for the proprietary features)

wjt commented 2 years ago

Timescaledb is not available on RDS.

If one wanted to stick to RDS, https://github.com/pgpartman/pg_partman also exists and is available there. Apparently recent PostgreSQL has native support for table partitioning https://www.postgresql.org/docs/current/ddl-partitioning.html but one still needs to actually create the partitions by hand, or with something like pg_partman.