3 ms·

The parent post didn't have enough detail, but I'd assume the dump would be using COPY for inserts. AFAIK when using COPY, the indexes are only built at the end

• djk447 6y ago
  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.
• djk447 6y ago
  (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)
  • aforwardslash 6y ago
    Still, you managed to add valuable knowledge on your reply I wasn't aware of, regarding the overall topic. thank you for that :)