14 ms·
Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite,
by dk8996 9y ago
Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.
- deepsun 9y agoMmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.
- dk8996 9y agoUPDATE client set enabled = true; So something like this will rewrite the whole table because of MVCC. MySQL will update the record in place without rewriting the whole table.
- mst 9y agoMVCC is almost always a feature for my workloads but it's well remembering it's a trade-off. Thanks for reminding HN of this point.
- eloff 9y agoMyISAM I assume? InnoDB, which I hope you're using, uses MVCC too, just like pretty much all mainstream SQL databases of the last 30 years. Updates occur in place, with the older version of the columns relocated to UNDO space. Not sure what you're gaining over PostgreSQL there. Also, if the column is indexed, the index will contain all versions for each row. This is what allows index scans.
- deleted 9y ago[deleted]
- riku_iki 9y agoBecause FS operates pages, and not individual bytes, MySQL will also, for each column, read whole page with row included, and write it back, thus rewriting whole table..
- mst 9y agoYou're talking about DDL. They're talking about an in-place rewrite of the value of a single column, which, yes, InnoDB will do with way less write load than postgresql.