2

I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc.

I have been reading about timescaledb and how it offers improved insert performance. However, I wonder if there is any downside to using hypertables instead of regular tables, if I only care about insert performance?

3

(Timescale person here.)

Yes, you should be able to get much higher insert rate in a TimescaleDB hypertable than a normal table.

The primary downside of hypertables is that there are a couple limitations they expose related to the way we do internal scaling. In particular:

  • We only allow a key to be marked as UNIQUE if it includes all partitioning keys (in its prefix). So if you partition on a time column, the time column could be unique, or you could build a unique composite key on (time, device_id). But this means you can't build a standard auto-increment id as the primary key (note primary keys are be definition UNIQUE). But we find that doesn't typically make sense for time-series data.

  • You can define a foreign-key constraint from your hypertable to a regular table, but we don't currently allow the opposite: a FK from a regular table to a hypertable. (But same as the UNIQUE constraint limitation above, this rarely makes sense or can be designed around.)

If you have other questions, Docs (docs.timescale.com) or community Slack (slack.timescale.com) are great resources.

  • A most suitable answer - thank you. The unique restriction is interesting. In my time series schemas so far I have used an incrementing integer to uniquely identify time series values (as my time fields are often subject to revisions etc), but they are always FK to another central identifier table anyway. As you say, its not often select queries use this to find rows anyway. However, not being able to have FK constraints to a hypertable does mean that using hypertables is out for most of my regular relational data. I will try the slack channel. – ThatDataGuy Jan 5 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.