One point of clarification for readers of @neslinesli93's post is that Timescale does not create "heavy" indexes.
We do create some default indexes that PostgreSQL does not, but these defaults can be turned off. We also allow you to create indexes after bulk loading data, if you want to compare apples-to-apples.
But to be clear, the indexes Timescale creates are the same, or can often times be cheaper, than PostgreSQL (remember, TimescaleDB is implemented as a PostgreSQL extension). We're always happy to help people work through proper set up and any implementation details in our Slack community (slack.timescale.com).
As I mentioned inside the article, I tested last year version of TimescaleDB (July/August 2017) and that was my experience with it out of the box.
I am really impressed by all the progress you've made, and hopefully I'll consider TimescaleDB as my first choice on the next iteration of the product I'm working on.
Now, I'm skimming through the docs[1] and as I understand, create_hypertable is called before all the data is migrated, thus all TimescaleDB indexes are already present during the migration. What is the way to create indexes after data migration?
(1) Call create_hypertable with default indexes off (include an argument of `create_default_indexes => FALSE`) [1]
(2) Then just use a standard CREATE INDEX command on the hypertable at any time. B-Tree, hash index, GIN/Gist, single key, composite keys, etc. This DDL command will propagate to any existing chunks (and create them) and will be remembered to so any future chunks that are automatically created will also have these indexes [2]
>What is the way to create indexes after data migration?
You can migrate the data and then do the normal PostgreSQL `CREATE INDEX` syntax to create the indexes on the hypertable. It's not an option to create_hypertable or anything, but that's how you would achieve it.
How does Timescale solve the problem of retention. In InfluxDB, old data is thrown out at every tick as the retention window continuously rolls. In the world of Postgres, wouldn't this mean an explicit cron-like DELETE of rows all the time?
Recently, I've gone through this very same choice and ended up with vanilla PostgreSQL (Timescale was not mature enough).
[Shameless self plug] You can read some of the details here: https://medium.com/@neslinesli93/how-to-efficiently-store-an...