4 ms·

Yeah, SQL in applications that need to be performant and responsive is a huge pain and I have found it to not be worth the effort. If you don't need performanc

Yeah, SQL in applications that need to be performant and responsive is a huge pain and I have found it to not be worth the effort. If you don't need performance or responsiveness though you can save a lot of effort except in very simple cases. I've seen it shine in quickly built proof of concept applications and in ad-hoc analytics queries.

tracker1 7y ago
I tend to prefer to treat SQL databases as dumb and generic as possible... you can go a LONG way supporting multiple databases until you need something more complex. For most organizations, supporting more than a single SQL variant is just painful. The only time I ever saw, for example, Microsoft Data Application Blocks in a useful scenario was an enterprise app that had to support MS-SQL and Oracle. Most of the variance was custom stored procedures for each named the same with the same signatures... It actually really sucked. I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do. I'd rather use MS-SQL or PostgreSQL than mySQL.
- evanelias 7y ago
  > I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc). There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.
  - tracker1 7y ago
    While I am glad to hear it... I've been very happy with mssql and postgres, with little chance of giving mysql/mariadb much in the way of future opportunity. I've just been burned too many times with other open and commercial options that give me what I expect.
kd5bjo 7y ago
It can also be made to work for highly-responsive but relatively small data sets: At Justin.tv, the scaling issues we hit with the first-generation video system were net splits and an increasing percentage of processor time being devoted to synchronization. The replacement system used a single PostgreSQL server (with a hot backup) to hold the real-time status of the entire system: Where every channel was being put into the system, the entire replication graph for every channel, active BGP routes, and the current load of every server and network link. Whenever a viewer showed up to the website, we’d do a relatively complicated query against this database to figure out which server to direct them to for the channel they wanted to watch. The net result was a system that showed video sooner, with less latency, and more cheaply than any of our competitors were able to achieve. I don’t know what their setup looks like these days, but this basic setup took them from before the Twitch.tv rebrand all the way to their Amazon acquisition. There was a lot of tuning that we had to do to make this work (like running off of a ramdisk, and having a pool of read-only clones), and it certainly had its hair-tearing-out moments, but in retrospect it was absolutely the right choice. We kept expecting it to fall over, but the plan to shard the database onto multiple servers collected dust for years because there was always a way to get the performance gains we needed through basic tuning.

tracker1 7y ago

I tend to prefer to treat SQL databases as dumb and generic as possible... you can go a LONG way supporting multiple databases until you need something more complex. For most organizations, supporting more than a single SQL variant is just painful. The only time I ever saw, for example, Microsoft Data Application Blocks in a useful scenario was an enterprise app that had to support MS-SQL and Oracle. Most of the variance was custom stored procedures for each named the same with the same signatures... It actually really sucked. I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do. I'd rather use MS-SQL or PostgreSQL than mySQL.

evanelias 7y ago
> I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc). There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.
- tracker1 7y ago
  While I am glad to hear it... I've been very happy with mssql and postgres, with little chance of giving mysql/mariadb much in the way of future opportunity. I've just been burned too many times with other open and commercial options that give me what I expect.

kd5bjo 7y ago

It can also be made to work for highly-responsive but relatively small data sets: At Justin.tv, the scaling issues we hit with the first-generation video system were net splits and an increasing percentage of processor time being devoted to synchronization. The replacement system used a single PostgreSQL server (with a hot backup) to hold the real-time status of the entire system: Where every channel was being put into the system, the entire replication graph for every channel, active BGP routes, and the current load of every server and network link. Whenever a viewer showed up to the website, we’d do a relatively complicated query against this database to figure out which server to direct them to for the channel they wanted to watch. The net result was a system that showed video sooner, with less latency, and more cheaply than any of our competitors were able to achieve. I don’t know what their setup looks like these days, but this basic setup took them from before the Twitch.tv rebrand all the way to their Amazon acquisition. There was a lot of tuning that we had to do to make this work (like running off of a ramdisk, and having a pool of read-only clones), and it certainly had its hair-tearing-out moments, but in retrospect it was absolutely the right choice. We kept expecting it to fall over, but the plan to shard the database onto multiple servers collected dust for years because there was always a way to get the performance gains we needed through basic tuning.