3 ms·

Note that I am ONLY talking about a mode to use for limited, trivial OLTP style queries. The kind where the query planner will never be in doubt -- if you just

by dagss 2y ago

Note that I am ONLY talking about a mode to use for limited, trivial OLTP style queries. The kind where the query planner will never be in doubt -- if you just have the right indices in place. The kind of simple backend software queries where people consider NoSQL instead to avoid SQL's oddities. The mode I talk about is very inappropriate for any kind of reporting or analytics query or ad hoc queries etc. > "Slow" beats "not working at all" in almost all cases. In the specific context specified above, I disagree with this. Mainly because "not working at all" will be caught during testing (because you should have test coverage of your SQL queries). Slow = undiscovered during testing. But even assume you didn't have test coverage and the code made it to production -- yes I probably want a seldomly used "DeleteUser" API call to crash if foreign keys were missing indices, instead of doing it anyway and consume lots of DB resources. > Just keeping an eye on slow query logs and/or query performance statistics is the general approach. The feature I proposed was to help newbies learn SQL. This requires expertise in the team, and easily shifts work away from the newbies in the team writing SQL (don't think properly through indices during development) to the single SQL expert in the team. Depends a bit how your work is organized etc; I like that SQL indices etc are as closely linked to the backend development process as possible; not considered a post-optimization.. If you get the error, you can either make the index you need for a perfect and trivial query plan -- or declare "non-trivial mode" and be back to today's situation.

arp242 2y ago
You don't know what the "right indexes" are, because sometimes "no index" is the "right index". Sometimes because a full table scan is faster. Sometimes because you're okay accepting the various performance trade-offs (e.g. insert speed vs. update speed, storage space on disk). Many applications don't have tests for every single last trivial SQL query, and adding those just because the SQL server may decide to bail out because it might perhaps possibly could be 100ms slower is not a good way for most teams to spend their time. In the end it's just trading one confusion for another confusion. But the current confusion has a lot less complexity overall, so that clearly the "better" one IMHO.
- dagss 2y ago
  ...yet people flock to NoSQL to avoid the complexity of having to learn SQL. Or stick JSON into a single table in SQL, because using more than one table is too complex. And so on. (This is my context where I work, if you don't have to deal with this and people around you happily embrace and learn the footguns and complexities of SQL then good for you..) Perhaps you like this idea better, a table-specific hint: create table MyTable ( ) with (assume_infinitely_large=on) Just to turn off the possibility of table scans on that individual table in all environments. That way you don't have to do this in ALL situations, only the ones where you know that full table scans in production will be out of the question. > any applications don't have tests for every single last trivial SQL query, and adding those just because Another perspective here though: If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users?
  - arp242 2y ago
    > people flock to NoSQL to avoid the complexity of having to learn SQL That is not my impression. It went through a hype cycle as many things do, before it settled down to "where it makes sense", which was quite a while ago. And sticking JSON in SQL can be perfectly fine. "NoSQL" is not magic, and requires just as much tinkering if you have large amounts of data to get a decent performance. Or it has performance characteristics geared towards very specific operations. And none of what you're proposing will fix anything about the difficulties of running a (SQL) database; it will only make things more complex, error-prone, and difficult. > If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users? Of course not. What a silly thing to say. Deploy to production → run migrations that are not easily reversible → SQL refuses to run "because bruh huh" → customers angry because downtime → your day is well fucked → fix issue → look up the ugliest words in a dictionary for the fucking idiot cunts who made your application crash even when it could have worked → double-check dictionary again to make sure you haven't missed any words. Have you even run a production service? With users? Who will should at you if it doesn't work because their business is on its arse? And having to scramble to fix it? Perhaps at 4am? And things don't "silently degrade" if you monitor it, which you should do for serious services anyway as I mentioned. PostgreSQL has pretty good facilities for this built-in, but it's easy enough to collect metrics in the application. Or users report "it's slow" and then you investigate. Or you get errors in your error log because things time out. Or your server's CPU is pinned to 100%. You can get by even without directly monitoring the DB.

by dagss 2y ago

arp242 2y ago

You don't know what the "right indexes" are, because sometimes "no index" is the "right index". Sometimes because a full table scan is faster. Sometimes because you're okay accepting the various performance trade-offs (e.g. insert speed vs. update speed, storage space on disk). Many applications don't have tests for every single last trivial SQL query, and adding those just because the SQL server may decide to bail out because it might perhaps possibly could be 100ms slower is not a good way for most teams to spend their time. In the end it's just trading one confusion for another confusion. But the current confusion has a lot less complexity overall, so that clearly the "better" one IMHO.

dagss 2y ago
...yet people flock to NoSQL to avoid the complexity of having to learn SQL. Or stick JSON into a single table in SQL, because using more than one table is too complex. And so on. (This is my context where I work, if you don't have to deal with this and people around you happily embrace and learn the footguns and complexities of SQL then good for you..) Perhaps you like this idea better, a table-specific hint: create table MyTable ( ) with (assume_infinitely_large=on) Just to turn off the possibility of table scans on that individual table in all environments. That way you don't have to do this in ALL situations, only the ones where you know that full table scans in production will be out of the question. > any applications don't have tests for every single last trivial SQL query, and adding those just because Another perspective here though: If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users?
- arp242 2y ago
  > people flock to NoSQL to avoid the complexity of having to learn SQL That is not my impression. It went through a hype cycle as many things do, before it settled down to "where it makes sense", which was quite a while ago. And sticking JSON in SQL can be perfectly fine. "NoSQL" is not magic, and requires just as much tinkering if you have large amounts of data to get a decent performance. Or it has performance characteristics geared towards very specific operations. And none of what you're proposing will fix anything about the difficulties of running a (SQL) database; it will only make things more complex, error-prone, and difficult. > If code does not have test coverage, what really happens is the code gets tested (or "hardened") in production. And isn't it then better to have things crash straight after deploy, than to have it gradually and silently degrade as you get more users? Of course not. What a silly thing to say. Deploy to production → run migrations that are not easily reversible → SQL refuses to run "because bruh huh" → customers angry because downtime → your day is well fucked → fix issue → look up the ugliest words in a dictionary for the fucking idiot cunts who made your application crash even when it could have worked → double-check dictionary again to make sure you haven't missed any words. Have you even run a production service? With users? Who will should at you if it doesn't work because their business is on its arse? And having to scramble to fix it? Perhaps at 4am? And things don't "silently degrade" if you monitor it, which you should do for serious services anyway as I mentioned. PostgreSQL has pretty good facilities for this built-in, but it's easy enough to collect metrics in the application. Or users report "it's slow" and then you investigate. Or you get errors in your error log because things time out. Or your server's CPU is pinned to 100%. You can get by even without directly monitoring the DB.