4 ms·

Yes, really. I've done (and do) substantial work in SQL as well. Currently almost the entirety of our business logic is implemented in it, both to avoid the de

Yes, really. I've done (and do) substantial work in SQL as well. Currently almost the entirety of our business logic is implemented in it, both to avoid the delay of round-tripping large data sets and for security reasons. To be clear, this isn't CRUD code. There's a lot of custom ML bits, vector algebra, etc., running in Aurora Postgres where C and Python are off limits. Without PL/pgSQL many of the functions would be impractical. The optimizer isn't the main source of headaches, but it doesn't help. I've caught it doing things it shouldn't be doing -- Aurora's fault, not Postgres' -- and having so much complexity locked away behind a magical "feature" is endlessly frustrating. Not everything is visible in a query plan.

btilly 4y ago
When you're trying to run general purpose code inside of the database, you're beyond what it is good for. I've written a compiler in PL/pgSQL (for a special purpose language for time series data) and agree that there is a limit to what you want to do there. SQL is a query tool. Not a general purpose programming platform. But it is good for surprisingly complex queries on surprisingly large and complex data sets.
- FreakLegion 4y ago
  We're really just debating the meaning of "simple" from: > SQL as a query language is truly horrible once you get past simple data queries. I think things start to get dicey as soon as you're past basic SELECT, FROM, JOIN, WHERE [1], ORDER BY. CTEs, subqueries, cross joins, case expressions, etc. certainly aren't advanced, but they do surprising things at times, and it can be hard to tell what's going on under the hood with the optimizer. I'd prefer to work with a full-fledged programming language or the underlying primitives the optimizer translates SQL into, but SQL isn't a problem for me personally. The problem is that I've had teammates be utterly defeated by it. 1. And even then you can get in trouble, e.g. not knowing that OR short-circuiting isn't part of the standard, and Postgres for example doesn't do it.
  - btilly 4y ago
    I think we can agree on that resolution. :-)

btilly 4y ago

When you're trying to run general purpose code inside of the database, you're beyond what it is good for. I've written a compiler in PL/pgSQL (for a special purpose language for time series data) and agree that there is a limit to what you want to do there. SQL is a query tool. Not a general purpose programming platform. But it is good for surprisingly complex queries on surprisingly large and complex data sets.

FreakLegion 4y ago
We're really just debating the meaning of "simple" from: > SQL as a query language is truly horrible once you get past simple data queries. I think things start to get dicey as soon as you're past basic SELECT, FROM, JOIN, WHERE [1], ORDER BY. CTEs, subqueries, cross joins, case expressions, etc. certainly aren't advanced, but they do surprising things at times, and it can be hard to tell what's going on under the hood with the optimizer. I'd prefer to work with a full-fledged programming language or the underlying primitives the optimizer translates SQL into, but SQL isn't a problem for me personally. The problem is that I've had teammates be utterly defeated by it. 1. And even then you can get in trouble, e.g. not knowing that OR short-circuiting isn't part of the standard, and Postgres for example doesn't do it.
- btilly 4y ago
  I think we can agree on that resolution. :-)