5 ms·

Yeah, differences like that are why I ditched MySQL years ago in favor of PostgreSQL. I was using MySQL at the time, and did a performance comparison of how lo

Yeah, differences like that are why I ditched MySQL years ago in favor of PostgreSQL. I was using MySQL at the time, and did a performance comparison of how long it took each database to dump and load a snapshot of a 100GB database. MySQL took 3 days. PostgreSQL took 30 minutes. I posted on a MySQL forum asking why the drastic difference, and they tried to hand-wave it away by saying I had a lot of indexes. Well yeah, databases have indexes. I shouldn't expect my MySQL database transfers to be quick if it has an index? Features shouldn't cripple the application for routine tasks.

eloff 6y ago
That's surprising to me since Postgres does less efficient writes compared to MySQL in order to optimize for read queries[1] - a sensible tradeoff since most of the time reads are more common than writes in OLTP workloads. TimescaleDB essentially solves this problem for the specific case of time-series inserts. [In Postgres] if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row.[1] [1] https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/
- aforwardslash 6y ago
  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 of the command, not on each insert.
  - 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 :)

eloff 6y ago

That's surprising to me since Postgres does less efficient writes compared to MySQL in order to optimize for read queries[1] - a sensible tradeoff since most of the time reads are more common than writes in OLTP workloads. TimescaleDB essentially solves this problem for the specific case of time-series inserts. [In Postgres] if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row.[1] [1] https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/

aforwardslash 6y ago
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 of the command, not on each insert.
- 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 :)