3 ms·

Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard po

by kyrra 12y ago

Corruption issues are fairly true with MyISAM engine. InnoDB did a lot to help reliability of tables (it can auto-correct them in cases where you had a hard poweroff or other related issues). No more needing to manually run table scans to detect corruption. What "advanced SQL features" do other DB products have that you cannot do with MySQL?

pilif 12y ago
CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.
- kbenson 12y ago
  It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect... 1: http://stackoverflow.com/questions/17147920/postgres-window-function-lag-equivalent-query-in-mysql http://stackoverflow.com/questions/17147920/postgres-window-...
  - ldng 12y ago
    I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable ! https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/ https://fosdem.org/2015/schedule/event/youd_better_have_test...
- meritt 12y ago
  As a long-time and fairly technical MySQL user and only light experience with PostgreSQL, can you suggest the best reading material (in addition to the user manual) on the subject? Comparatively, for MySQL, my recommendation would be 'High Performance MySQL'.
  - parfe 12y ago
    The postgres manual. Seriously. It's all you need to forget mysql existed. http://www.postgresql.org/docs/9.4/interactive/index.html http://www.postgresql.org/docs/9.4/interactive/index.html
- porker 12y ago
  > CTEs Coming from a MySQL background I didn't understand the fuss over CTEs ...until I worked with a SQL Server guy. That completely opened my eyes to how _sane_ things could be, vs my MySQL work-arounds. Made me wish I'd chosen a Microsoft route!
  - JohnBooty 12y ago
    Yeah, once you've used CTEs it's impossible to go back. Same with window functions. Good news is that Postgres has great CTE support and it was ahead of MSSQL in window functions last I checked!
neverminder 12y ago
The list is long (if compared to Postgres particularly), but this is a rough summary: http://www.sql-workbench.net/dbms_comparison.html http://www.sql-workbench.net/dbms_comparison.html
NhanH 12y ago
Very minor thing, but funny enough, I was working with MySQL just earlier and need to use my one in a blue moon quota of Full Join ... turned out MySQL doesn't have it :( (yes, I know how to emulate it with union).

by kyrra 12y ago

pilif 12y ago

CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) Now I can totally accept that this might have been a one-time fluke, but I personally really have trouble trusting a DBMS once it has lost data due to causes other than user-error or hardware faults.

kbenson 12y ago
It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed up. I don't care if it's a $100k oracle installation, It's not safe until it's backed up. Even then it's suspect... 1: http://stackoverflow.com/questions/17147920/postgres-window-function-lag-equivalent-query-in-mysql http://stackoverflow.com/questions/17147920/postgres-window-...
- ldng 12y ago
  I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable ! https://fosdem.org/2015/schedule/event/youd_better_have_tested_backups/ https://fosdem.org/2015/schedule/event/youd_better_have_test...
meritt 12y ago
As a long-time and fairly technical MySQL user and only light experience with PostgreSQL, can you suggest the best reading material (in addition to the user manual) on the subject? Comparatively, for MySQL, my recommendation would be 'High Performance MySQL'.
- parfe 12y ago
  The postgres manual. Seriously. It's all you need to forget mysql existed. http://www.postgresql.org/docs/9.4/interactive/index.html http://www.postgresql.org/docs/9.4/interactive/index.html
porker 12y ago
> CTEs Coming from a MySQL background I didn't understand the fuss over CTEs ...until I worked with a SQL Server guy. That completely opened my eyes to how _sane_ things could be, vs my MySQL work-arounds. Made me wish I'd chosen a Microsoft route!
- JohnBooty 12y ago
  Yeah, once you've used CTEs it's impossible to go back. Same with window functions. Good news is that Postgres has great CTE support and it was ahead of MSSQL in window functions last I checked!

neverminder 12y ago

The list is long (if compared to Postgres particularly), but this is a rough summary: http://www.sql-workbench.net/dbms_comparison.html http://www.sql-workbench.net/dbms_comparison.html

NhanH 12y ago

Very minor thing, but funny enough, I was working with MySQL just earlier and need to use my one in a blue moon quota of Full Join ... turned out MySQL doesn't have it :( (yes, I know how to emulate it with union).