Install TImescaleDB extension on production database for partitionning

[ndevilleBE]

Dear all,
I'm investigating the possible installation of the timescaleDB extension for speeding up queries on my sensorthings DB.
FYI, I have a Postgresql 14 sensorthings BD populated with +550millions data over 45 years. I expect the DB to increase further dozens of millions points each year.
I think it would be worthy to make use of timescaleDB functions for partitionning the Observations table (hypertable creation) and make use of the included function (eg. bucket for sub-sampling). Reading the documentation it looks straighforward but I'm a bit suspicious :)
As anyone experience with this implementation? My two main worries are primary/foreign keys integrity and indexes.
Many thanks,
Nicolas de Ville

[hylkevds]

Someone else experimented with TimescaleDB and documented his experiences in #105

[jkreft-usgs]

Did this end up working? did you have to do any query optimization to handle the partitioning?

[ndevilleBE]

Unfortunately it didn't make much change to the performance I got. I believe the hardware I have access to is not powerful enough to handle such amount of data in a performant way.
What I end up doing is create a replica of the sensorthings database with a sub-selection of the data. The complete database is kept for archival or specific requests and the simplified one is exposed via the API. This solved most of my problems.