(Just noticed this was about dump/restore, during restores you can in fact write the data and then build the index in a separate operation and that can be faster, sorry for missing context)
I mean, I'm not sure this is the place to get into detailed schema discussions, but I think I wouldn't use a row_id here at all, certainly, there's no need for this to be unique across all of the rows, you could make a unique constraint on user_id, time, row_id and then provide all 3 of them for the deletes, if the only reason is for the deletes, then that should be fine. If there are other reasons, then you probably have a different unique constraint that has some real world meaning (and that likely involves time).
Sorry, the previous reply suggested it was on row_id, time I thought, anyway. I think the question is why should row_id be unique? What looks it up by row_id? And can they look it up by user_id/time instead? It may be impossible. On the other hand, if row_id is really what you're searching by, not by time, then just use our partitioning on row_id, assuming it's a bigint or something like that, you can just partition on that...
Why not create an index on user_id, time? Why do you need a unique reference back to the row? We find that they're often (though certainly not always) kind of meaningless and/or unnecessary for timeseries workloads.
Indexes are built during the inserts for copies and for normal inserts, in general copies are faster because many people use single row inserts that need to instantiate executor nodes and other bits for every row. I think there used to be some other differences, but in general we've seen that multi-row inserts are around as fast as copies and I'm honestly not sure which one our benchmarking tool uses. (I'm at Timescale, btw). Aside from that, the MySQL vs Postgres comment above actually has to do with updates not with inserts and the main difference here is that PG stores rows in the heap whereas MySQL has primary keys that actually organize tables (and also things around copy-on-write MVCC model). So, in some ways, PG should actually be faster for raw inserts, because you don't have to deal with as many page splits and other bits for the organization of the heap, things are just written there (and indexes are secondary). We limit the overhead of building indexes by making sure our chunks are "right-sized" so that you don't end up swapping too much as you're doing inserts into recent data.
Hi! Engineer at Timescale who’s just been working on this feature, it’s coming in our next release and going to continue to be developed over the next few releases! Would love to have you try it out and get your feedback :)