12 ms·

It is one thing when a junior does this because they haven't learned better. It's quite another when experienced seniors ban the use of SQL features because it

by dagss 4y ago

It is one thing when a junior does this because they haven't learned better. It's quite another when experienced seniors ban the use of SQL features because it's not "modern" or there is an architectural principle to ban "business logic" in SQL. In our team we use SQL quite heavily: Process millions of input events, sum them together, produce some output events, repeat -- perfect cases for pushing compute to where the data is, instead of writing a loop in a backend that fetches events and updates projections. Almost every time we interact with other programmers or architects it's an uphill battle to explain this -- "why can't just just put your millions of events into a service bus and write some backend to react to them to update your aggregate". Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute -- instead of a new microservice or whatever and some minutes of compute. People bend over backwards and basically re-implement what the databases does for you in their service mesh. And with events and business logic in SQL we can do simulations, debugging, inspect state at every point with very low effort and without relying on getting logging right in our services (because you know -- doing JOIN in SQL is not modern, but pushing the data to your service logs and joining those to do some debugging is just fine...) I think a lot of blame is with the database vendors. They only targeted some domains and not others, so writing SQL is something of an acquired taste. I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation).

joeblubaugh 4y ago
How easy is it to verify that the configuration of your database matches a checked-in configuration or source file these days? My beef with a lot of installed procedures in a SQL database comes down to deployment and rollback difficulty.
- mh- 4y ago
  Depends on the DBMS, but pt-config-diff works well for MySQL. edit: This will only help for configuration, not procedures. https://docs.percona.com/percona-toolkit/pt-config-diff.html https://docs.percona.com/percona-toolkit/pt-config-diff.html
- Wicher 4y ago
  For Postgres views, materialized views, functions and triggers I've made a tool that can do that, DBSamizdat: https://sr.ht/~nullenenenen/DBSamizdat https://sr.ht/~nullenenenen/DBSamizdat
civilized 4y ago
A lot of people are not comfortable learning languages beyond the ALGOL-like paradigm. SQL's building blocks are incredibly odd if you're used to the idea that work gets done via variables, conditionals, and loops. Personally I think it's a wonderful (if imperfect), ultra-powerful, and easy language to learn. But if it doesn't click for you and you're under the gun at the job, I bet it's very easy to develop a bad attitude towards SQL.
- fifilura 4y ago
  And when it does click for you all the other things that do not involve for/while loops (dicts, map/reduce/group by) become very obvious.
- dagss 4y ago
  I'm curious about resources to learning SQL well. In our company I've done a series of teaching session (we're on about 10th hour now). It's working OK, but wish I knew of good material / blog posts to point at. I feel like there's no good community to learn from like for many backend languages. E.g. after learning about "cross apply" (T-SQL; "lateral join" in postgres) everything got 10x easier to express in SQL. But: How is a developer who's just getting started in SQL going to know that? And where is the community that can teach a backend developer getting started with SQL to ignore some of that advice from the DBA and Data Analytics communities? E.g. the advice to "not pin indexes" -- which I believe is 100% wrong advice for a typical backend application where reproducability across environments is key, and where any query not supported directly by an index is probably a bug anyway.
  - tqi 4y ago
    Mode's SQL school is the best I've seen: https://mode.com/sql-tutorial/ https://mode.com/sql-tutorial/
  - jerf 4y ago
    "E.g. after learning about "cross apply" (T-SQL; "lateral join" in postgres) everything got 10x easier to express in SQL." I feel like even in the past few years the database community has still been learning about what you need the databases to be able to do in order to make good code. I personally don't like the "declarative" memeset and think it set the community back literally decades, but with recent Postgreses (by which I mean, the whole last five years or so... lots of people still running older things) all the functions and functionality is technically there to arbitrarily convert between rows, arrays, columnsets, etc., and more and more you can use them arbitrarily as well, so you can JOIN against a columnset you bodged together from two other queries that you pulled into an array and then put that array into a columnset, without it having to ever be turned into a full "table". Cross apply is another example of that, where IIRC a row can be turned into multiple rows. The problem is that while all the functionality I've wanted on this front does now seem to exist, it's all incredibly haphazard. Cross joining is an SQL keyword, but arrays look more like a data structure, and I can't remember what all was going on but I recall having more hassle turning arrays into columnsets for some reason. If I were going to be doing this full time I think I'd build myself a matrix cheat sheet of how to convert between all these things, and I bet there's still holes in the matrix even today (is there an opposite of a cross join? dunno, but I wouldn't be surprised the answer is "no"). I feel like I'm doing a lot less "work around things missing in SQL (that I have access to)" than I did 15-20 years ago, but rather than a cohesive and well-organized toolset for dealing with all these things, I've got a haphazard set of Bob's Custom Tool for This and A Semi-Standard, Modestly Extensible Tool for that, neither of which were ever designed with the other in mind, and yeah, in the end I can do everything I want quite nicely but it's up to me to notice that what this tool calls a 1/8 inch quartzic turns out to be the same as a Number Seven smithnoczoid and so in fact they do work together perfectly despite the fact the documentation for neither of them suggests that such a thing is possible, etc.
fifilura 4y ago
Oh this reminds me a lot of a familiar kafka vs SQL battle. Yes it is possible to do it in kafka, but everything is obscured in the sense that you can't peek at what you are doing, you spend your precious CPU on serializing/deserializing and things like backfill is a mess.
- lmm 4y ago
  I find the opposite; SQL databases work a lot like Kafka underneath, but hide it from you, burning all your CPU to generate this illusion of a globally consistent state of the world that you don't actually want or need and doing everything they can to avoid ever showing you what they're actually doing.
  - ttfkam 4y ago
    Hold up. I need some clarification here. SQL databases are opaque with regard to resource usage but Kafka is completely transparent in this regard? That is your personal experience and assertion?
    - lmm 4y ago
      Yes. I once saw an outage caused by an SQL database server collapsing because of a query that had been issued 23 days earlier. I saw another one gradually get slower and slower over a period of weeks because a data scientist had left a command prompt open and forgotten about it. Kafka brokers are a lot more consistent and predictable IME.
Beltiras 4y ago
I interact with SQL on a daily basis but never write a single query by hand. It's all abstracted by the Django ORM. I do have to be mindful of what I do of course but mostly I just plow away with the abstractions offered. Once in a while I have to take a look at the SQL or the query plan but those are few and far between.
- pphysch 4y ago
  Sure, but there's a world of difference between someone who knows SQL and uses a ORM for productivity and someone who uses an ORM because it's the only tool in their box.
  - ttfkam 4y ago
    Exactly. Effective use of an ORM requires knowledge of the ORM's API in addition to SQL, not in lieu of it. Using an ORM without a firm understanding of SQL is a recipe for disaster (or at least glacial performance).
ptrik 4y ago
This depends on use case. SQL is the king for batching process - queries are declarative, decades of effort put into optimization. For real-time / streaming use cases, however, there is yet a mature solution in SQL yet. Flink SQL / Materialize is getting there, but the state-of-the-art approach is still Flink / Kafka Streams approach - put your state in memory / on local disk, and mutate it as you consume messages. This actually echoes the "Operate on data where it resides" principle in the article.
- fifilura 4y ago
  Do you find flink SQL immature? For me it looks a lot like syntactical sugar on top of the datastream api. Same thing, less code?
- dagss 4y ago
  We do mini-batch processing in SQL. Some hundred milliseconds latency, some hundred events consumed from to the inbound event table per iteration. Paginate through using a (Shard, EventSequenceNumber) key; writers to table synchronize/lock so that this is safe. Kafka-in-SQL if you wish. Or, homegrown Flink. (There are many different uses for the events inside our SQL processing pipelines, and have to store the ingested events in SQL anyway) I am sure real Kafka+Flink has some advantages, but...what we do works really well, is simple, and feels right for our scale. It is enough batching in SQL to real speed/CPU benefits on inserts/updates into SQL (vs e.g. hitting SQL once per consumed event which would be way worse). And with Azure SQL the infra is extremely simple vs getting a Kafka cluster in our context.
mcrad 4y ago
[dead]
jason2323 4y ago
> It's quite another when experienced seniors ban the use of SQL features because it's not "modern" or there is an architectural principle to ban "business logic" in SQL. While I agree with the idea of pushing heavy compute to where the data resides, I wholeheartedly disagree with the statement quoted above. Concentrating business logic in SQL makes it effectively untestable. Its not easy to "compartmentalize" SQL code such that each individual piece is testable on its own. Often, this lets major issues go unnoticed until the SQL query executes in production on some unexpected input data and people are woken up at 3AM. Adding on top of this the fact that SQL exceptions are a PITA to debug on a normal day, and it probably isn't any easier at 3AM
- vkazanov 4y ago
  It's totally possible to validate data before pushing to a table, and have normal tests around sql massaging it. Saying this as somebody looking at 1000s of transformations done by my teams. Admittedly, It took a while for data engineers in the industry to accept these practises though.
  - vkazanov 4y ago
    PS but stored procedures and udss are evil, yes
- wccrawford 4y ago
  At my first real programming job, I found an error in the SQL functions that the company had written. After pointing it out, the CEO bet me that I couldn't fix it. Apparently their best programmers had tried and failed. I did end up fixing it, but it took me a couple weeks. Beyond being untestable, it's also hard to version control and roll back if there's a problem. We had some procedures around SQL updates that were kind of a pain because of that.
  - zvrba 4y ago
    > Beyond being untestable, it's also hard to version control and roll back if there's a problem. Recently, I've learned that SQLServer supports synonyms. So you version functions / procedures (like MySP_1, MySP_2, etc...) and establish a synonym MySP -> MySP_1. Then you test MySP_2 and when ready, change the synonym to point to MySP_2. Of course, all code uses just the synonym.
miiiiiike 4y ago
The main reason to do this is so they can be turned into streams that and have the source and, potentially, multiple destinations decoupled. If the source and destination will always be the same, sure, do it in the DB.
vkazanov 4y ago
As a data platform manager in a data-rich company I agree: programmers tend to prefer their favourite hammers to sql, even if a modern relational database can do the thing much better. But! As somebody with a relatively good understanding of a history of sql, relational dbs and related concepts I have to add: SQL is often to blame. All the the amazing engineering that goes into database engines, clean and coherent ideas of relational algebra, optimisability of a declarative approach to computaion - all of that gets bad rep because of the nightmare of sql-the-language. The standard, the syntax, every little bit that could go wrong is just wrong from the point of view of a language designer. Composability, modularity, predictability, even core null-related defaults. But it's everywhere. We just have to accept it.
- jonhohle 4y ago
  It’s not just the language, it’s schema evolution, data distribution, and exposed APIs. I don’t want to give other teams direct access to a DB and have them Not only take a dependency on the schema, but have the ability to run arbitrary queries that may exhaust resources in ways that impact normal operations. If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. If other teams need a replica to perform their arbitrary queries, I’d much rather have them using a richer data model that they can normalize into whatever form suits their needs than have to conflate that into a source of truth data store. If you have a single business unit and can get away with commingling concerns within a small team, great, throw it all in a single DB. If it makes sense to split, however, do it quick and early to avoid a decoupling hell that is more expensive then having split in the first place.
  - andylynch 4y ago
    That’s not a new problem though? The classic way to solve this in database is to expose the data API as stored procedures/views and restrict query access on the actual tables to just the DBAs - I think even MySQL which was late to the party here has had this ability for some time now.
  - marcosdumay 4y ago
    > If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. And then they will depend on your API data schema... Yes, schema evolution is hard, but databases have many tools to help here that you will either have to recreate on your APIs or live without and have a harder time. Either way, all the trouble comes from data evolution, and any schema-only change is trivial to deal with. Data distribution is something that varies from one DB to another, they usually have very good performance that is hard to replicate on your application layer, but are very hard to setup and keep running. But the point about control of resource usage is a good one.
treebeard901 4y ago
This debate has been happening forever and really the fundamentals haven't changed. It doesn't matter if you pull data from the DB to an old middle tier or to a nice modern microservice architecture, you're almost always losing the performance game at that point. The database already has most of your data cached in memory, it already built statistics on the best methods to use to join the data, and the data is always local to the DB. Reading lots of a data from a DB to do the same operation in a microservice means you incur a cost of data retrieval, memory for a copy of the dataset and enough to do a join, the network speed to transfer the data over, and then you're giving up all the indexing and statistics that a database provides. There is almost never a reason for this unless you're needing to do some kind of data analysis that isn't supported by the DB. Maybe most of all this copy the data to somewhere else to do a thing is never going to scale well. Don't mind me, I'm just a retired former DB nerd.
- feoren 4y ago
  > the fundamentals haven't changed I believe they have, because we've gotten so much better at ORMs. ORMs get a bad rap because people use them terribly (and it's largely the ORM's fault because they encourage their own terrible use). But they are a true "change in fundamentals" that can finally end this debate. Business logic doesn't belong in the database layer, because the database layer doesn't support the level of abstraction, composability, testing, and type safety that modern programming languages afford. However, that does not mean that business logic doesn't belong in SQL. It just means you have to treat SQL as an output of your actual business layer. A good use of an ORM looks like a metaprogramming environment for conveniently building syntax trees that get converted into intelligent SQL. You know it's working well if the SQL looks somewhat like you'd write yourself and you can build one SQL statement with multiple layers that are abstracted from each other (in C#, think about passing IQueryables around). The structures are parsed into SQL and executed very explicitly only at the end of the chain, do a lot of work, and never produce SELECT N+1s. A good ORM user is thinking in SQL but writing in C# (or whatever your business layer is in). A bad use of an ORM is trying to pretend like SQL doesn't exist, or is too scary for regular programmers to think about. It has SELECT N+1s everywhere. A bad ORM user is thinking in C# and hoping the database will roughly do the correct thing.
jjice 4y ago
> "why can't just just put your millions of events into a service bus and write some backend to react to them to update your aggregate". Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute Do people actually do this? I feel like this is classic Occam's Razor. This is one of the biggest things I do in SQL and I couldn't imagine the time and effort to do it with a separate service.
pbowyer 4y ago
> I think a lot of blame is with the database vendors. They only targeted some domains and not others, so writing SQL is something of an acquired taste. I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation). There's EdgeQL https://www.edgedb.com/blog/we-can-do-better-than-sql#lack-of-orthogonality https://www.edgedb.com/blog/we-can-do-better-than-sql#lack-o... which I like the look of - but as it adds one more layer over a database I haven't used it yet. Edit: I hadn't seen PRQL before, reading the site now https://prql-lang.org/ https://prql-lang.org/
rlawson 4y ago
This, a thousand times this. A hundred lines of Java/Python/C# can save you at least 10 lines of a stored proc :) Also why don't they teach SQL in most schools???
jasonhansel 4y ago
I'm a fan of relational databases, but I think we should concede three points: 1. Databases should accept queries in a structured machine-readable format rather than a plaintext language. 2. SQL in particular is a poorly designed language: it isn't very composable, it has lots of annoying edge cases (like NULLs), and it has a number of annoying limitations (in particular, its historically limited support for structured data within fields). 3. Given how most RDBMSs are designed, you often need to handle denormalization and caching manually. This requires doing a lot of excess data management in a middleware layer--for instance, querying a cache before accessing the DB, or storing data in multiple places for denormalization. Some of this can be done in SQL (e.g. denormalization through TRIGGERs), but since SQL is not a very good language (see (2)) that can be tough.
- asalahli 4y ago
  Also, caching can be done with materialized views
  - jasonhansel 4y ago
    IIRC in Postgres you need to refresh an entire materialized view all at once, effectively recreating the entire table; you can't just have it update incrementally whenever the underlying data changes. I think SQL Server can do this, but...then you have to use SQL Server.
    - mnahkies 4y ago
      I think it's being worked on for postgres but probably another major release or two away, quick Google came up with this https://pgconf.ru/en/2021/288667 https://pgconf.ru/en/2021/288667 but I'm sure I've come across discussion in postgres mailing lists / wiki in the past. Would definitely be a nice feature to have, without it I find the main use case I have for materialised views is batch processing where you want to prepare a complex result set and then stream process it in a Cronjob or similar
      - 4y ago
cryptonector 4y ago
I couldn't have put it better. Use the SQL, Luke.
taeric 4y ago
I feel this has gotten worse with the latest round of data science graduates wanting everything in python. Probably just perspective. I ducked out of the push for everything to be in Hadoop. And while I can appreciate the foot gun that is indexing everything so that ad hoc queries work, I also have to deal with folks thinking elastic search somehow avoids that trap. I think I've seen the same claims and push for graphql. :(
nonethewiser 4y ago
I have run into similar attitudes. In my case, it's an unfamiliarity with SQL, fear of the database, and a desire to utilize strongly typed ORM's for everything. Implementing these sorts of queries often requires using raw sql which is seen as taboo by such people. They think it's unsafe. Meanwhile we're pulling more records than we need, searching them, then firing off more queries in a for loop.
irrational 4y ago
I've met so many developers who seem to be willing to do anything, except use sql. I remember one case where we were dealing with clearly relational data. One of the senior developers was adamant that we use a non-relational database. When pushed as to why, he said it was because it would have better performance. I pointed out that this for a process where data would be sent out and we wouldn't expect to receive it back for 3 days or so. A few milliseconds performance boost was hardly beneficial over 3 days. But he was insistent and was senior, so we did it. Shockingly, it turned out to be the wrong decision. Later we learned that the senior developer didn't like sql.
KronisLV 4y ago
I've seen both extremes of this. On one end, you have applications that execute thousands of SQL queries for each page load, for populating a table with some data or something like that, which has bunches of rules for what should be displayed, all implemented as nested method calls (e.g. the service pattern) in your application. It's a performance nightmare when you get more data, or more users. On the other end, you have an application where your back end acts just as a view for a DB that has all of the logic in it. There will rarely be proper tests for it. There will rarely be any sort of logging or observability solutions for this in place, the discoverability will be pretty bad, debugging will often be really bad, versioning of changes will also be pretty awkward, but at least the performance will typically be okay. Just look at the JetBrains Survey from 2021: https://www.jetbrains.com/lp/devecosystem-2021/databases/#Databases_do-you-debug-stored-procedures https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da... Do you debug stored procedures? 47% Never 44% Rarely 9% Frequently Do you have tests in your database? 14% Yes 70% No 15% I don't know Do you keep your database scripts in a version control system? 54% Yes 37% No 9% I don't know Do you write comments for the database objects? 49% No 27% Yes, for many types of objects 24% Yes, only for tables If something that most would consider to be a "good practice" isn't done, then clearly that's a bit of a canary about the state of the technology and the ecosystem around it. Consider that databases are very important for most types of systems, and yet about half of people don't debug their stored procedures, most people don't have tests for them, only about half version their scripts and about half don't bother with comments. I'm pretty much convinced that it's possible to write bad software regardless of the approach that's used. In my mind, the happy path for succeeding in even sub-optimal circumstances is a bit like this: - make liberal use of DB views for querying data, if you have a table in your app, it should have a matching DB view, which will also make debugging easier - make use of in-database processing only when it makes a lot of sense and anything else would be a horrible choice (e.g. ETL or batch processes/pipelines), have log tables and such regardless - for most other concerns (e.g. typical CRUD), write app logic: most back end languages will have better support for observability and tracing, logging and debugging, as well as scaling for any expensive operations - still, be wary of the N+1 problem, that might mean that you don't have enough views, or that you're not using JOINs for your queries properly - also, look into using something like Redis for caching, S3 (or something compatible, like MinIO) for binary blobs and something like RabbitMQ for task queues, just because you can shove everything into the DB doesn't mean that you should, sometimes these specialized solutions will have better support and more standardized libraries, than whatever you can concoct
mamcx 4y ago
> I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation). That is nice, but much better will be language that is relational itself. I'm working on one (https://tablam.org https://tablam.org) and you can even do stuff like: for p in cross(products, qty) ?limit 10 do print(p.products.price * p.qty) end The thing is be functional/relational only is too mind-bending and is very nice to work with procedural construct. BTW my plan is that the query (?) section will compile to optimal executions defined per storage engine (memory, sqlite, mysql, redis, etc) `products ? price = 10.0` is executed on the server, not on the client.
- jasonhansel 4y ago
  Interesting! Have you looked at Datalog? In many ways it's the ideal purely relational language.
  - mamcx 4y ago
    Yes. I think is not friendly for end-users* and making this on Rust make it easy to plug-in iterators/generators so i go that way. (Also never wrap my head about how use it internally!)
bb88 4y ago
> Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute -- instead of a new microservice or whatever and some minutes of compute. This works until your database falls over in production. Recently someone started appending to a json field type over and over in our production database. And then on some queries, postgres crashed due to lack of memory. The fix was to remove the field and code that constantly appended to it, and do something else. No, the database should not be the answer to all your data problems. Yes a microservice may be the best answer. But for structured data and queries that can run with normal amounts of memory the standard SQL DB is fine.
- mike_hock 4y ago
  > No, the database should not be the answer to all your data problems. Yes a microservice may be the best answer. Or to put it another way: No, microservices should not be the answer to all your data problems. Yes, using the DB may be the best answer.
  - bb88 4y ago
    In one case it might just be slow. In the other your db will fall over. Unfortunately, you may find the answer when your production environment fails.
    - ttfkam 4y ago
      If you have a single DB instance in production, something has already gone wrong. Read replicas.
- ttfkam 4y ago
  > Recently someone started appending to a json field type over and over in our production database. And then on some queries, postgres crashed due to lack of memory. The fix was to remove the field and code that constantly appended to it, and do something else. So your choices are doing something manifestly non-optimal in the database in violation of best practices or not use the database for non-trivial business logic? Sounds like a false dichotomy to me. Maybe just find a better solution? I mean, if someone writes an O(n^3) algorithm in Python, is the solution to use a better strategy or to swear off Python for anything non-trivial?
lmm 4y ago
IME SQL and relational databases have the framework problem, where they expect to be used in a particular way and won't let you use the internals. It's like when a language (ADA?) famously built a high-level version of concurrency into the language, where you were meant to use their "rendezvous" (I've probably mistaken the same), and instead what happened is that programmers implemented mutexes on top of this "rendezvous" and then reimplemented high-level concurrency on top of that. What we need is databases that expose more of their internals, that are designed to be embedded in applications and used as libraries. All serious databases use MVCC these days, but none of them expose it to the user. All serious databases separate updating the data from updating the index, but few of them expose that to the user. All serious databases know the difference between an indexed join and a table scan, but good luck figuring it out by just looking at an SQL query. Etc.
deleted 4y ago
[deleted]

by dagss 4y ago

joeblubaugh 4y ago

How easy is it to verify that the configuration of your database matches a checked-in configuration or source file these days? My beef with a lot of installed procedures in a SQL database comes down to deployment and rollback difficulty.

mh- 4y ago
Depends on the DBMS, but pt-config-diff works well for MySQL. edit: This will only help for configuration, not procedures. https://docs.percona.com/percona-toolkit/pt-config-diff.html https://docs.percona.com/percona-toolkit/pt-config-diff.html
Wicher 4y ago
For Postgres views, materialized views, functions and triggers I've made a tool that can do that, DBSamizdat: https://sr.ht/~nullenenenen/DBSamizdat https://sr.ht/~nullenenenen/DBSamizdat

civilized 4y ago

A lot of people are not comfortable learning languages beyond the ALGOL-like paradigm. SQL's building blocks are incredibly odd if you're used to the idea that work gets done via variables, conditionals, and loops. Personally I think it's a wonderful (if imperfect), ultra-powerful, and easy language to learn. But if it doesn't click for you and you're under the gun at the job, I bet it's very easy to develop a bad attitude towards SQL.

fifilura 4y ago
And when it does click for you all the other things that do not involve for/while loops (dicts, map/reduce/group by) become very obvious.
dagss 4y ago
I'm curious about resources to learning SQL well. In our company I've done a series of teaching session (we're on about 10th hour now). It's working OK, but wish I knew of good material / blog posts to point at. I feel like there's no good community to learn from like for many backend languages. E.g. after learning about "cross apply" (T-SQL; "lateral join" in postgres) everything got 10x easier to express in SQL. But: How is a developer who's just getting started in SQL going to know that? And where is the community that can teach a backend developer getting started with SQL to ignore some of that advice from the DBA and Data Analytics communities? E.g. the advice to "not pin indexes" -- which I believe is 100% wrong advice for a typical backend application where reproducability across environments is key, and where any query not supported directly by an index is probably a bug anyway.
- tqi 4y ago
  Mode's SQL school is the best I've seen: https://mode.com/sql-tutorial/ https://mode.com/sql-tutorial/
- jerf 4y ago
  "E.g. after learning about "cross apply" (T-SQL; "lateral join" in postgres) everything got 10x easier to express in SQL." I feel like even in the past few years the database community has still been learning about what you need the databases to be able to do in order to make good code. I personally don't like the "declarative" memeset and think it set the community back literally decades, but with recent Postgreses (by which I mean, the whole last five years or so... lots of people still running older things) all the functions and functionality is technically there to arbitrarily convert between rows, arrays, columnsets, etc., and more and more you can use them arbitrarily as well, so you can JOIN against a columnset you bodged together from two other queries that you pulled into an array and then put that array into a columnset, without it having to ever be turned into a full "table". Cross apply is another example of that, where IIRC a row can be turned into multiple rows. The problem is that while all the functionality I've wanted on this front does now seem to exist, it's all incredibly haphazard. Cross joining is an SQL keyword, but arrays look more like a data structure, and I can't remember what all was going on but I recall having more hassle turning arrays into columnsets for some reason. If I were going to be doing this full time I think I'd build myself a matrix cheat sheet of how to convert between all these things, and I bet there's still holes in the matrix even today (is there an opposite of a cross join? dunno, but I wouldn't be surprised the answer is "no"). I feel like I'm doing a lot less "work around things missing in SQL (that I have access to)" than I did 15-20 years ago, but rather than a cohesive and well-organized toolset for dealing with all these things, I've got a haphazard set of Bob's Custom Tool for This and A Semi-Standard, Modestly Extensible Tool for that, neither of which were ever designed with the other in mind, and yeah, in the end I can do everything I want quite nicely but it's up to me to notice that what this tool calls a 1/8 inch quartzic turns out to be the same as a Number Seven smithnoczoid and so in fact they do work together perfectly despite the fact the documentation for neither of them suggests that such a thing is possible, etc.

fifilura 4y ago

Oh this reminds me a lot of a familiar kafka vs SQL battle. Yes it is possible to do it in kafka, but everything is obscured in the sense that you can't peek at what you are doing, you spend your precious CPU on serializing/deserializing and things like backfill is a mess.

lmm 4y ago
I find the opposite; SQL databases work a lot like Kafka underneath, but hide it from you, burning all your CPU to generate this illusion of a globally consistent state of the world that you don't actually want or need and doing everything they can to avoid ever showing you what they're actually doing.
- ttfkam 4y ago
  Hold up. I need some clarification here. SQL databases are opaque with regard to resource usage but Kafka is completely transparent in this regard? That is your personal experience and assertion?
  - lmm 4y ago
    Yes. I once saw an outage caused by an SQL database server collapsing because of a query that had been issued 23 days earlier. I saw another one gradually get slower and slower over a period of weeks because a data scientist had left a command prompt open and forgotten about it. Kafka brokers are a lot more consistent and predictable IME.

Beltiras 4y ago

I interact with SQL on a daily basis but never write a single query by hand. It's all abstracted by the Django ORM. I do have to be mindful of what I do of course but mostly I just plow away with the abstractions offered. Once in a while I have to take a look at the SQL or the query plan but those are few and far between.

pphysch 4y ago
Sure, but there's a world of difference between someone who knows SQL and uses a ORM for productivity and someone who uses an ORM because it's the only tool in their box.
- ttfkam 4y ago
  Exactly. Effective use of an ORM requires knowledge of the ORM's API in addition to SQL, not in lieu of it. Using an ORM without a firm understanding of SQL is a recipe for disaster (or at least glacial performance).

ptrik 4y ago

This depends on use case. SQL is the king for batching process - queries are declarative, decades of effort put into optimization. For real-time / streaming use cases, however, there is yet a mature solution in SQL yet. Flink SQL / Materialize is getting there, but the state-of-the-art approach is still Flink / Kafka Streams approach - put your state in memory / on local disk, and mutate it as you consume messages. This actually echoes the "Operate on data where it resides" principle in the article.

fifilura 4y ago
Do you find flink SQL immature? For me it looks a lot like syntactical sugar on top of the datastream api. Same thing, less code?
dagss 4y ago
We do mini-batch processing in SQL. Some hundred milliseconds latency, some hundred events consumed from to the inbound event table per iteration. Paginate through using a (Shard, EventSequenceNumber) key; writers to table synchronize/lock so that this is safe. Kafka-in-SQL if you wish. Or, homegrown Flink. (There are many different uses for the events inside our SQL processing pipelines, and have to store the ingested events in SQL anyway) I am sure real Kafka+Flink has some advantages, but...what we do works really well, is simple, and feels right for our scale. It is enough batching in SQL to real speed/CPU benefits on inserts/updates into SQL (vs e.g. hitting SQL once per consumed event which would be way worse). And with Azure SQL the infra is extremely simple vs getting a Kafka cluster in our context.

mcrad 4y ago

[dead]

jason2323 4y ago

> It's quite another when experienced seniors ban the use of SQL features because it's not "modern" or there is an architectural principle to ban "business logic" in SQL. While I agree with the idea of pushing heavy compute to where the data resides, I wholeheartedly disagree with the statement quoted above. Concentrating business logic in SQL makes it effectively untestable. Its not easy to "compartmentalize" SQL code such that each individual piece is testable on its own. Often, this lets major issues go unnoticed until the SQL query executes in production on some unexpected input data and people are woken up at 3AM. Adding on top of this the fact that SQL exceptions are a PITA to debug on a normal day, and it probably isn't any easier at 3AM

vkazanov 4y ago
It's totally possible to validate data before pushing to a table, and have normal tests around sql massaging it. Saying this as somebody looking at 1000s of transformations done by my teams. Admittedly, It took a while for data engineers in the industry to accept these practises though.
- vkazanov 4y ago
  PS but stored procedures and udss are evil, yes
wccrawford 4y ago
At my first real programming job, I found an error in the SQL functions that the company had written. After pointing it out, the CEO bet me that I couldn't fix it. Apparently their best programmers had tried and failed. I did end up fixing it, but it took me a couple weeks. Beyond being untestable, it's also hard to version control and roll back if there's a problem. We had some procedures around SQL updates that were kind of a pain because of that.
- zvrba 4y ago
  > Beyond being untestable, it's also hard to version control and roll back if there's a problem. Recently, I've learned that SQLServer supports synonyms. So you version functions / procedures (like MySP_1, MySP_2, etc...) and establish a synonym MySP -> MySP_1. Then you test MySP_2 and when ready, change the synonym to point to MySP_2. Of course, all code uses just the synonym.

miiiiiike 4y ago

The main reason to do this is so they can be turned into streams that and have the source and, potentially, multiple destinations decoupled. If the source and destination will always be the same, sure, do it in the DB.

vkazanov 4y ago

As a data platform manager in a data-rich company I agree: programmers tend to prefer their favourite hammers to sql, even if a modern relational database can do the thing much better. But! As somebody with a relatively good understanding of a history of sql, relational dbs and related concepts I have to add: SQL is often to blame. All the the amazing engineering that goes into database engines, clean and coherent ideas of relational algebra, optimisability of a declarative approach to computaion - all of that gets bad rep because of the nightmare of sql-the-language. The standard, the syntax, every little bit that could go wrong is just wrong from the point of view of a language designer. Composability, modularity, predictability, even core null-related defaults. But it's everywhere. We just have to accept it.

jonhohle 4y ago
It’s not just the language, it’s schema evolution, data distribution, and exposed APIs. I don’t want to give other teams direct access to a DB and have them Not only take a dependency on the schema, but have the ability to run arbitrary queries that may exhaust resources in ways that impact normal operations. If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. If other teams need a replica to perform their arbitrary queries, I’d much rather have them using a richer data model that they can normalize into whatever form suits their needs than have to conflate that into a source of truth data store. If you have a single business unit and can get away with commingling concerns within a small team, great, throw it all in a single DB. If it makes sense to split, however, do it quick and early to avoid a decoupling hell that is more expensive then having split in the first place.
- andylynch 4y ago
  That’s not a new problem though? The classic way to solve this in database is to expose the data API as stored procedures/views and restrict query access on the actual tables to just the DBAs - I think even MySQL which was late to the party here has had this ability for some time now.
- marcosdumay 4y ago
  > If I expose an API, I control the access patterns and can evolve the schema separately to suit the workload. And then they will depend on your API data schema... Yes, schema evolution is hard, but databases have many tools to help here that you will either have to recreate on your APIs or live without and have a harder time. Either way, all the trouble comes from data evolution, and any schema-only change is trivial to deal with. Data distribution is something that varies from one DB to another, they usually have very good performance that is hard to replicate on your application layer, but are very hard to setup and keep running. But the point about control of resource usage is a good one.

treebeard901 4y ago

This debate has been happening forever and really the fundamentals haven't changed. It doesn't matter if you pull data from the DB to an old middle tier or to a nice modern microservice architecture, you're almost always losing the performance game at that point. The database already has most of your data cached in memory, it already built statistics on the best methods to use to join the data, and the data is always local to the DB. Reading lots of a data from a DB to do the same operation in a microservice means you incur a cost of data retrieval, memory for a copy of the dataset and enough to do a join, the network speed to transfer the data over, and then you're giving up all the indexing and statistics that a database provides. There is almost never a reason for this unless you're needing to do some kind of data analysis that isn't supported by the DB. Maybe most of all this copy the data to somewhere else to do a thing is never going to scale well. Don't mind me, I'm just a retired former DB nerd.

feoren 4y ago
> the fundamentals haven't changed I believe they have, because we've gotten so much better at ORMs. ORMs get a bad rap because people use them terribly (and it's largely the ORM's fault because they encourage their own terrible use). But they are a true "change in fundamentals" that can finally end this debate. Business logic doesn't belong in the database layer, because the database layer doesn't support the level of abstraction, composability, testing, and type safety that modern programming languages afford. However, that does not mean that business logic doesn't belong in SQL. It just means you have to treat SQL as an output of your actual business layer. A good use of an ORM looks like a metaprogramming environment for conveniently building syntax trees that get converted into intelligent SQL. You know it's working well if the SQL looks somewhat like you'd write yourself and you can build one SQL statement with multiple layers that are abstracted from each other (in C#, think about passing IQueryables around). The structures are parsed into SQL and executed very explicitly only at the end of the chain, do a lot of work, and never produce SELECT N+1s. A good ORM user is thinking in SQL but writing in C# (or whatever your business layer is in). A bad use of an ORM is trying to pretend like SQL doesn't exist, or is too scary for regular programmers to think about. It has SELECT N+1s everywhere. A bad ORM user is thinking in C# and hoping the database will roughly do the correct thing.

jjice 4y ago

> "why can't just just put your millions of events into a service bus and write some backend to react to them to update your aggregate". Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute Do people actually do this? I feel like this is classic Occam's Razor. This is one of the biggest things I do in SQL and I couldn't imagine the time and effort to do it with a separate service.

pbowyer 4y ago

> I think a lot of blame is with the database vendors. They only targeted some domains and not others, so writing SQL is something of an acquired taste. I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation). There's EdgeQL https://www.edgedb.com/blog/we-can-do-better-than-sql#lack-of-orthogonality https://www.edgedb.com/blog/we-can-do-better-than-sql#lack-o... which I like the look of - but as it adds one more layer over a database I haven't used it yet. Edit: I hadn't seen PRQL before, reading the site now https://prql-lang.org/ https://prql-lang.org/

rlawson 4y ago

This, a thousand times this. A hundred lines of Java/Python/C# can save you at least 10 lines of a stored proc :) Also why don't they teach SQL in most schools???

jasonhansel 4y ago

I'm a fan of relational databases, but I think we should concede three points: 1. Databases should accept queries in a structured machine-readable format rather than a plaintext language. 2. SQL in particular is a poorly designed language: it isn't very composable, it has lots of annoying edge cases (like NULLs), and it has a number of annoying limitations (in particular, its historically limited support for structured data within fields). 3. Given how most RDBMSs are designed, you often need to handle denormalization and caching manually. This requires doing a lot of excess data management in a middleware layer--for instance, querying a cache before accessing the DB, or storing data in multiple places for denormalization. Some of this can be done in SQL (e.g. denormalization through TRIGGERs), but since SQL is not a very good language (see (2)) that can be tough.

asalahli 4y ago
Also, caching can be done with materialized views
- jasonhansel 4y ago
  IIRC in Postgres you need to refresh an entire materialized view all at once, effectively recreating the entire table; you can't just have it update incrementally whenever the underlying data changes. I think SQL Server can do this, but...then you have to use SQL Server.
  - mnahkies 4y ago
    I think it's being worked on for postgres but probably another major release or two away, quick Google came up with this https://pgconf.ru/en/2021/288667 https://pgconf.ru/en/2021/288667 but I'm sure I've come across discussion in postgres mailing lists / wiki in the past. Would definitely be a nice feature to have, without it I find the main use case I have for materialised views is batch processing where you want to prepare a complex result set and then stream process it in a Cronjob or similar
    - 4y ago

cryptonector 4y ago

I couldn't have put it better. Use the SQL, Luke.

taeric 4y ago

I feel this has gotten worse with the latest round of data science graduates wanting everything in python. Probably just perspective. I ducked out of the push for everything to be in Hadoop. And while I can appreciate the foot gun that is indexing everything so that ad hoc queries work, I also have to deal with folks thinking elastic search somehow avoids that trap. I think I've seen the same claims and push for graphql. :(

nonethewiser 4y ago

I have run into similar attitudes. In my case, it's an unfamiliarity with SQL, fear of the database, and a desire to utilize strongly typed ORM's for everything. Implementing these sorts of queries often requires using raw sql which is seen as taboo by such people. They think it's unsafe. Meanwhile we're pulling more records than we need, searching them, then firing off more queries in a for loop.

irrational 4y ago

I've met so many developers who seem to be willing to do anything, except use sql. I remember one case where we were dealing with clearly relational data. One of the senior developers was adamant that we use a non-relational database. When pushed as to why, he said it was because it would have better performance. I pointed out that this for a process where data would be sent out and we wouldn't expect to receive it back for 3 days or so. A few milliseconds performance boost was hardly beneficial over 3 days. But he was insistent and was senior, so we did it. Shockingly, it turned out to be the wrong decision. Later we learned that the senior developer didn't like sql.

KronisLV 4y ago

I've seen both extremes of this. On one end, you have applications that execute thousands of SQL queries for each page load, for populating a table with some data or something like that, which has bunches of rules for what should be displayed, all implemented as nested method calls (e.g. the service pattern) in your application. It's a performance nightmare when you get more data, or more users. On the other end, you have an application where your back end acts just as a view for a DB that has all of the logic in it. There will rarely be proper tests for it. There will rarely be any sort of logging or observability solutions for this in place, the discoverability will be pretty bad, debugging will often be really bad, versioning of changes will also be pretty awkward, but at least the performance will typically be okay. Just look at the JetBrains Survey from 2021: https://www.jetbrains.com/lp/devecosystem-2021/databases/#Databases_do-you-debug-stored-procedures https://www.jetbrains.com/lp/devecosystem-2021/databases/#Da... Do you debug stored procedures? 47% Never 44% Rarely 9% Frequently Do you have tests in your database? 14% Yes 70% No 15% I don't know Do you keep your database scripts in a version control system? 54% Yes 37% No 9% I don't know Do you write comments for the database objects? 49% No 27% Yes, for many types of objects 24% Yes, only for tables If something that most would consider to be a "good practice" isn't done, then clearly that's a bit of a canary about the state of the technology and the ecosystem around it. Consider that databases are very important for most types of systems, and yet about half of people don't debug their stored procedures, most people don't have tests for them, only about half version their scripts and about half don't bother with comments. I'm pretty much convinced that it's possible to write bad software regardless of the approach that's used. In my mind, the happy path for succeeding in even sub-optimal circumstances is a bit like this: - make liberal use of DB views for querying data, if you have a table in your app, it should have a matching DB view, which will also make debugging easier - make use of in-database processing only when it makes a lot of sense and anything else would be a horrible choice (e.g. ETL or batch processes/pipelines), have log tables and such regardless - for most other concerns (e.g. typical CRUD), write app logic: most back end languages will have better support for observability and tracing, logging and debugging, as well as scaling for any expensive operations - still, be wary of the N+1 problem, that might mean that you don't have enough views, or that you're not using JOINs for your queries properly - also, look into using something like Redis for caching, S3 (or something compatible, like MinIO) for binary blobs and something like RabbitMQ for task queues, just because you can shove everything into the DB doesn't mean that you should, sometimes these specialized solutions will have better support and more standardized libraries, than whatever you can concoct

mamcx 4y ago

> I wish there was a modern language that compiled to SQL (like PRQL, but with data mutation). That is nice, but much better will be language that is relational itself. I'm working on one (https://tablam.org https://tablam.org) and you can even do stuff like: for p in cross(products, qty) ?limit 10 do print(p.products.price * p.qty) end The thing is be functional/relational only is too mind-bending and is very nice to work with procedural construct. BTW my plan is that the query (?) section will compile to optimal executions defined per storage engine (memory, sqlite, mysql, redis, etc) `products ? price = 10.0` is executed on the server, not on the client.

jasonhansel 4y ago
Interesting! Have you looked at Datalog? In many ways it's the ideal purely relational language.
- mamcx 4y ago
  Yes. I think is not friendly for end-users* and making this on Rust make it easy to plug-in iterators/generators so i go that way. (Also never wrap my head about how use it internally!)

bb88 4y ago

> Yes we CAN do that but why do that it's 15 lines of SQL and 5 seconds compute -- instead of a new microservice or whatever and some minutes of compute. This works until your database falls over in production. Recently someone started appending to a json field type over and over in our production database. And then on some queries, postgres crashed due to lack of memory. The fix was to remove the field and code that constantly appended to it, and do something else. No, the database should not be the answer to all your data problems. Yes a microservice may be the best answer. But for structured data and queries that can run with normal amounts of memory the standard SQL DB is fine.

mike_hock 4y ago
> No, the database should not be the answer to all your data problems. Yes a microservice may be the best answer. Or to put it another way: No, microservices should not be the answer to all your data problems. Yes, using the DB may be the best answer.
- bb88 4y ago
  In one case it might just be slow. In the other your db will fall over. Unfortunately, you may find the answer when your production environment fails.
  - ttfkam 4y ago
    If you have a single DB instance in production, something has already gone wrong. Read replicas.
ttfkam 4y ago
> Recently someone started appending to a json field type over and over in our production database. And then on some queries, postgres crashed due to lack of memory. The fix was to remove the field and code that constantly appended to it, and do something else. So your choices are doing something manifestly non-optimal in the database in violation of best practices or not use the database for non-trivial business logic? Sounds like a false dichotomy to me. Maybe just find a better solution? I mean, if someone writes an O(n^3) algorithm in Python, is the solution to use a better strategy or to swear off Python for anything non-trivial?

lmm 4y ago

IME SQL and relational databases have the framework problem, where they expect to be used in a particular way and won't let you use the internals. It's like when a language (ADA?) famously built a high-level version of concurrency into the language, where you were meant to use their "rendezvous" (I've probably mistaken the same), and instead what happened is that programmers implemented mutexes on top of this "rendezvous" and then reimplemented high-level concurrency on top of that. What we need is databases that expose more of their internals, that are designed to be embedded in applications and used as libraries. All serious databases use MVCC these days, but none of them expose it to the user. All serious databases separate updating the data from updating the index, but few of them expose that to the user. All serious databases know the difference between an indexed join and a table scan, but good luck figuring it out by just looking at an SQL query. Etc.

deleted 4y ago

[deleted]