7 ms·

dba here and I really don't get why SQL is so feared... I get that it requires very different way to think about data but it is quite simple in terms of you tel

by alliao 24d ago

dba here and I really don't get why SQL is so feared... I get that it requires very different way to think about data but it is quite simple in terms of you tell it what to do, and if it does it badly you probably told it wrong so just try something different...

mike_hearn 24d ago
Because: 1. SQL isn't composable (you can't assign fragments to variables except for CTEs) so you can't easily test out subparts and build them up incrementally without just copy/pasting stuff around. 2. Joins are an unnatural way to dereference pointers. 3. SQL is more than SELECT. Once you get into updates you encounter lots of scary edge cases and traps. How many engineers really understand isolation levels? Why doesn't skipping the column list in an INSERT substitute nulls for the nullable columns that aren't provided? What changes can you make to a schema that are 'safe' for your environment (won't take table locks)? What locks are being taken by the RDBMS behind your back - sometimes it matters! 4. Site outages caused by optimizer plan shifts are scary because people don't feel in control. Good databases have features to ameliorate these issues, but most people's experience is of databases that are merely OK and not good.
- euroderf 24d ago
  > 2. Joins are an unnatural way to dereference pointers. There's gotta be a simple & clear alternative to this obstruction. Maybe it just hasn't been invented yet.
  - mike_hearn 24d ago
    Most query languages do fix that. GraphQL is one example.
    - jedwards1211 23d ago
      GraphQL joins are entirely up to the underlying resolvers, it has no syntax for expressing different kinds of joins or filter conditions on values from multiple joined relations
- sgarland 24d ago
  > How many engineers really understand isolation levels? I feel like there’s no excuse for this one. You need to know how your data store will interact with your query and others. The problem, I think, is what the tail end of that is, and is what you hinted at when discussing locks: RDBMS interaction. I have come around on this recently (quite recently - after reading and re-reading this article, and the comments), so forgive me if any past comments in my history indicate otherwise. It is unreasonable to expect a developer to administer an RDBMS. If you're a small startup, you kind of have to out of necessity; maybe if you're lucky, you hire a dev who's also done infra work, and if the stars align, they've specifically administered an RDBMS at scale. But what counts as administration? Let's look at adding a secondary index, possibly the most common DDL. AFAIK, no ORMs / frameworks (I am assuming here that most devs are using some kind of abstraction for RDBMS access) default to "safe" builds - no `CONCURRENTLY` for Postgres, and no reducing `lock_wait_timeout` to something sane for MySQL (I've no idea about MSSQL nor Oracle, though I also assume that if you're running one of those, you probably have a DB team). So already, there is an implicit assumption that they've read the pertinent manual section[s] for their RDBMS, which seems unlikely. Even if they did, there's a chance they would also need to have read and understood the paragraphs on handling invalid index builds (Postgres), or the impact that foreign key constraints can have on metadata locks (MySQL). Let's say the line gets drawn at "devs should be able to understand that they [probably] need secondary indices," with implementing those being entirely on another team or service. OK - how much do they need to understand? I think it's reasonable to expect a developer to understand B+trees; after all, they're just a data structure. Should they need to be able to internalize that such that they can understand why doing a range scan on a column in the middle of a multi-column index removes everything to the right of it from B+tree filtering? Probably, but now we're significantly deeper into specifics. Should they know that there are different kinds of indices, like GIN? Maybe. What about different operator classes (Postgres) for them? Maybe, maybe not. What about knowing about its `fastupdate` option, and the related `gin_pending_list_limit` configuration item? I'd love to say no, those are squarely in the world of ops, but then why should they be allowed to create the index at all if it's going to increase someone else's operational burden? For all these reasons, I don't think it's prudent to have dev teams managing their own DBs. But then, you get into the fight that most places seem to be in, where the devs want to do something to the DB that the ops team knows will be a headache later, they push back, product gets mad that they aren't shipping, ops capitulates, and then the headache predictably becomes real months down the road. Rinse and repeat. I have no clue how to fix this while maintaining the modern trend of velocity dominating everything else.
- alliao 23d ago
  very interesting thanks for the insight, I feel like there are broadly two camps of developers, one like yourself probably wants to know every last detail, and the other that are somewhat prefer to stay naive and taking the "declarative" part very broadly... there's a bit of cyclic relationship at play here, the data changes the query plan, and the query plan affects the performance, while the performance is being optimised by the engine. if the architecture is bad then sooner or later the engine runs out of tricks and performance suffers but then nobody never is quite sure whether the current architecture's good enough. I totally get the whole nosql that was the rage for a while... quick to prototype but I do believe once it's somewhat settled, moving the consolidated parts back to SQL is much easier to manage and optimise
- ako 23d ago
  1. Can be done using views. Creating named views is really not that different than building named functions or methods.
  - mike_hearn 23d ago
    They aren't the same. Views are persistent objects, not like local variables that exist only for the scope of an operation. CTEs are the closest equivalent but you can't factor out predicates that way.
    - ako 23d ago
      Why does it matter? It’s like a named operation, and if you want local scope you can create CTEs, which is like an anonymous local operation. So you have both options.

by alliao 24d ago

mike_hearn 24d ago

Because: 1. SQL isn't composable (you can't assign fragments to variables except for CTEs) so you can't easily test out subparts and build them up incrementally without just copy/pasting stuff around. 2. Joins are an unnatural way to dereference pointers. 3. SQL is more than SELECT. Once you get into updates you encounter lots of scary edge cases and traps. How many engineers really understand isolation levels? Why doesn't skipping the column list in an INSERT substitute nulls for the nullable columns that aren't provided? What changes can you make to a schema that are 'safe' for your environment (won't take table locks)? What locks are being taken by the RDBMS behind your back - sometimes it matters! 4. Site outages caused by optimizer plan shifts are scary because people don't feel in control. Good databases have features to ameliorate these issues, but most people's experience is of databases that are merely OK and not good.

euroderf 24d ago
> 2. Joins are an unnatural way to dereference pointers. There's gotta be a simple & clear alternative to this obstruction. Maybe it just hasn't been invented yet.
- mike_hearn 24d ago
  Most query languages do fix that. GraphQL is one example.
  - jedwards1211 23d ago
    GraphQL joins are entirely up to the underlying resolvers, it has no syntax for expressing different kinds of joins or filter conditions on values from multiple joined relations
sgarland 24d ago
> How many engineers really understand isolation levels? I feel like there’s no excuse for this one. You need to know how your data store will interact with your query and others. The problem, I think, is what the tail end of that is, and is what you hinted at when discussing locks: RDBMS interaction. I have come around on this recently (quite recently - after reading and re-reading this article, and the comments), so forgive me if any past comments in my history indicate otherwise. It is unreasonable to expect a developer to administer an RDBMS. If you're a small startup, you kind of have to out of necessity; maybe if you're lucky, you hire a dev who's also done infra work, and if the stars align, they've specifically administered an RDBMS at scale. But what counts as administration? Let's look at adding a secondary index, possibly the most common DDL. AFAIK, no ORMs / frameworks (I am assuming here that most devs are using some kind of abstraction for RDBMS access) default to "safe" builds - no `CONCURRENTLY` for Postgres, and no reducing `lock_wait_timeout` to something sane for MySQL (I've no idea about MSSQL nor Oracle, though I also assume that if you're running one of those, you probably have a DB team). So already, there is an implicit assumption that they've read the pertinent manual section[s] for their RDBMS, which seems unlikely. Even if they did, there's a chance they would also need to have read and understood the paragraphs on handling invalid index builds (Postgres), or the impact that foreign key constraints can have on metadata locks (MySQL). Let's say the line gets drawn at "devs should be able to understand that they [probably] need secondary indices," with implementing those being entirely on another team or service. OK - how much do they need to understand? I think it's reasonable to expect a developer to understand B+trees; after all, they're just a data structure. Should they need to be able to internalize that such that they can understand why doing a range scan on a column in the middle of a multi-column index removes everything to the right of it from B+tree filtering? Probably, but now we're significantly deeper into specifics. Should they know that there are different kinds of indices, like GIN? Maybe. What about different operator classes (Postgres) for them? Maybe, maybe not. What about knowing about its `fastupdate` option, and the related `gin_pending_list_limit` configuration item? I'd love to say no, those are squarely in the world of ops, but then why should they be allowed to create the index at all if it's going to increase someone else's operational burden? For all these reasons, I don't think it's prudent to have dev teams managing their own DBs. But then, you get into the fight that most places seem to be in, where the devs want to do something to the DB that the ops team knows will be a headache later, they push back, product gets mad that they aren't shipping, ops capitulates, and then the headache predictably becomes real months down the road. Rinse and repeat. I have no clue how to fix this while maintaining the modern trend of velocity dominating everything else.
alliao 23d ago
very interesting thanks for the insight, I feel like there are broadly two camps of developers, one like yourself probably wants to know every last detail, and the other that are somewhat prefer to stay naive and taking the "declarative" part very broadly... there's a bit of cyclic relationship at play here, the data changes the query plan, and the query plan affects the performance, while the performance is being optimised by the engine. if the architecture is bad then sooner or later the engine runs out of tricks and performance suffers but then nobody never is quite sure whether the current architecture's good enough. I totally get the whole nosql that was the rage for a while... quick to prototype but I do believe once it's somewhat settled, moving the consolidated parts back to SQL is much easier to manage and optimise
ako 23d ago
1. Can be done using views. Creating named views is really not that different than building named functions or methods.
- mike_hearn 23d ago
  They aren't the same. Views are persistent objects, not like local variables that exist only for the scope of an operation. CTEs are the closest equivalent but you can't factor out predicates that way.
  - ako 23d ago
    Why does it matter? It’s like a named operation, and if you want local scope you can create CTEs, which is like an anonymous local operation. So you have both options.