9

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?

Improve this question

1 Answer 1

Reset to default
11

(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.

Improve this answer
2
  • 1
    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, 2019 at 10:11
  • @ThatDataGuy you may not be able to have a foreign key constraint, but you could have a regular index and execute joins still. You just won't have the constraint enforced by the database.
    – Eloff
    Oct 12, 2019 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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