4 ms·
I have a huge amount of sympathy for people who accidentally attempt to implement a database. I believe they are usually motivated by their desire to avoid SQL
by roenxi 4y ago
I have a huge amount of sympathy for people who accidentally attempt to implement a database. I believe they are usually motivated by their desire to avoid SQL - an extremely worthy goal. The industry, by and large, puts a crazy amount of effort into avoiding SQL by writing ORMs, translation layers and similar tech because it really is that bad.
But unfortunately databases have solved common-as-mud problems to do with data, disks, multiple users (which, if your data is worth storing, you will eventually have) and the interactions thereof. It is worth putting up with SQL to get all the lessons of the 70s, 80s, 90s, 00s, 10s and now 20s in how to store data without creating buggy software. People probably have these problems and it is usually faster to solve them the usual way that works. Write some SQL and maul a stress ball, it'll be easier long term.
- JPLeRouzic 4y ago> "The industry, by and large, puts a crazy amount of effort into avoiding SQL by writing ORMs, translation layers and similar tech " In my experience (I am retired from a telecom operator) using a database made applications quite inflexible yet real life implies constant changes in their data model and as a result when there was a "database migration" there was always some information lost. On contrary Pick databases (MultiValue databases) were very useful. https://en.wikipedia.org/wiki/Pick_operating_system https://en.wikipedia.org/wiki/Pick_operating_system
- sirtaj 4y agoI'm of the opposite opinion. Database migrations force you to think through the changes in your data more explicitly. You catch logic oversights, boundary conditions and implicit transformations much more thoroughly and with fewer errors later on.
- joshka 4y agoI've seen a lot of different enterprise systems. In my experience it's not the db that's causes this inflexibility issue, but designing the architecture of systems data-first (using Data Dictionaries / ER diagrams etc.) rather than use-case-first that leads to this sort of problem. When a data schema is the foundation of a system, it's often the most difficult thing to change.
- doctor_eval 4y agoI don’t know how that works. Your data schema is the foundation of almost any system, regardless of how you generate it. I think this is a common mistake. People think their code is what’s important. But it’s the data and how it gets transformed that is the core of most applications.
- esailija 4y agoData-based architecture means that you have all data about an entity in a single place. Even though if you analyze the domain and all use cases you find that there are slices of that data that never interact together. Example is storing customers address and purchase history in same place, only because they "belong to customer" in a data sense. This is very bad because it leads to entangled architecture where everything depends on everything and you cannot extract truly independent modules. The project implemented like this simply cannot scale beyond a certain size. So no, data is not important but how it is used.
- doctor_eval 4y agoOk. Perhaps I’m not familiar with this concept of “data-based architecture”. The GP talked about designing around features, which I think is also bad. But maybe they meant more like a DDD approach? So - I certainly agree 100% with what you say about keeping thing independent. Maybe I’m just unfamiliar with the terms.
- jen20 4y agoFor this to be true (which it is in some contexts), the important question is what the boundary of the "system" is.
- doctor_eval 4y agoI agree. On reflection, I'd also add that I do design systems "data first" however to me this doesn't mean "database first". It's important to think about how data flows through the system, how it gets transformed, and what parts of the system "own" which data. All of this requires understanding of the boundaries of the various systems that make up the whole, which I think is exactly your point.
- doctor_eval 4y agoI have done, quite literally, thousands of database migrations in a telco context and we never - not one time - lost information. I don’t think there is any particular problem, or solution. Applications get old, assumptions change, the world moves in. It’s true that our database design became inflexible over time and needed to be hoisted. But so too did our authentication layer (hand built - JaaS - JWT), the UI (AWT - Swing - Web 1.0 - Web 2.0), the deployment model (on prem - cloud - docker), even the API scheme (RMI - soap - rest - graphql). Any application that lives long enough is going to go through this sort of change. The only constant in all of the above was SQL.
- ttfkam 4y agoBeware the old man in a young man's game. You will be tempted to underestimate him. As many unremembered before you have done.
- pharmakom 4y agoSQL is pretty good though. It could compose better, sure, but it’s well suited to data queries. I think people dislike learning a new language, even to the point of writing bad software. I wonder why?
- d33 4y agoI'd say that pretty much everything about SQL is bad. Apart from composability, the syntax is super annoying (the ordering of keywords), many databases have pretty much no error reporting if you get it wrong and the linters suck. You can't unit test it. It's not very well standardized and it's actually more than one language (DML vs DDL). Plus, it's not very readable. And the declarative nature of the language is eventually going to back-stab you when you discover yet another edge case of your query planner that flips a reasonable plan to one that's literally going to take ages.
- pharmakom 4y agoYou can unit test SQL by spinning up Docker containers. You should just write SQL for the database you are using (e.g. PostgreSQL). Switching databases without changing queries is unrealistic anyways. I agree with your other points, but I am yet to see a layer above SQL that improves on them enough to justify its additional complexity.
- CHY872 4y agoI know a team who used JooQ (code-generated query builder for Java) to generate SQL which worked on both Postgres and Oracle and worked well enough that they could support both in production (Oracle for top end perf, Postgres for everything else). There were certain places where they had to switch on SQL format, but over years they felt comfortable enough only unit testing on one kind of database.
- CHY872 4y agoFew things: Regarding unit testing, it's now very possible to productively unit test a SQL database - I'm familiar with a Java package that boots up an embedded PostgreSQL, obviously Docker is now in vogue, etc, etc. It's not quite 'just run a function', but you write your code in the same style as a unit test and you're running hundreds of tests in seconds. You can use transactions to automagically clear out your state, which means performance is also excellent. Regarding the standardization of the language - no, it's not totally standard (although arguably that's a good thing, we don't want 10 implementations of exactly the same thing, they'd never evolve), but because they all work sort-of-the-same, a lot of language support ends up transferrable. You can use ActiveRecord on many backing databases, for example. Query planner problem is definitely true, know folks who've spent a lot of time hinting Oracle and scaring Postgres into the same, though personally only took a few days. However, SQL is IMO the best of a bad bunch. Most non-SQL databases I've used have been extremely sharp cornered, whether this be via: unpredictable/unbounded worst case performance in pessimal storage cases (RocksDB, Cassandra), various parts of the code where unbounded memory/storage usage is required (Cassandra, Spark, Elasticsearch), extremely low level APIs making it easy to make unobservably (as in, you won't notice it and will eat the pain every read) slow APIs (RocksDB). I'm not saying there's nothing better, but it's probably not an all-rounder. There are certainly things I don't like about most SQL databases. Few SQL databases are columnar, which is _the_ key to good performance in the 2010s, and native LZ4-style data compression is also not widely used. But for most engineers they solve a lot of problems for you without having to think about them.
- kindofabigdeal 4y agoHow is SQL bad? If anything my experience with KV db's is that most of the time they'll eventually end up implementing some form of structured data storage and retrieval that in some way resembles SQL. I love KV's only when I used them as embedded db's like pebble and badger db but you can never beat a good ol' SQL server that just does what you need from it nothing more nothing less. ORM's most of the time aren't that great either you always have to keep up with their new fancy API changes and instead of just doing your work you find yourself relearning the same ORM every few years, not only that but some ORM's are extremely sensitive in how you order your operations that the order in which you write you code can potentially kill your performance or worse accidentally deleting data.
- crabbone 4y agoWhy is SQL bad? I'll take SQL over almost any language used to write the "application layer" any day... Also, why do you think that databases solved problems with disks? Do you mean disks as in block devices? If so, none of the popular databases ever touches that level, most popular SQL databases work with filesystem.
- setr 4y agoIt doesn’t compose properly, lacks type safety, you have to codegen your statements in the application anyways and is generally an utter bitch to do so because it’s horrifically inconsistent (and doesn’t compose properly). You can dump everything into stored procs and functions, but now you get worthless compiler errors, indirect version control at best, and really your whole tooling support is reverted to the 80’s. It’s a pretty shit language — its just the only one we get to have, and the value of what it interfaces with (the RDBMS itself, ACID and the relational algebra) is… invaluable. You can’t get away with not knowing it, and it’s generally dumb to avoid it (because you’re then avoiding the glory behind it) but as a language it’s basically COBOL-tier.
- crabbone 4y ago> It doesn’t compose properly Stored procedures? > lacks type safety How so? Can you give some examples? I have no idea what this is even about. > you have to codegen your statements Never happened to me. Not in any capacity that I would feel like this was a burden. The only cases where I needed to generate SQL code were some fuzzing tests intended to figure out problems with database performance, s.a. feeding a bunch of generated SQL to pgbench to see what may cause the database to slow down / use too many resources. This seems very strange in an application that is simply using SQL database rather than trying to do something weird with it (eg. testing performance). SQL is a very high-level language, which means you don't need to do as much code generation compared to, say, Assembly or even Java. I do see that people do this when they use ORM, but this is a self-inflicted pain. They shouldn't have been doing ORM to begin with. > get worthless compiler errors From what? Are you talking about writing SQL functions (eg. in Java) and then using them from SQL (eg. Oracle)? This doesn't sound like a SQL problem, more like Java compiler problem... But, yeah, I can see how this is a complication and an inconvenient thing to do, so, if that's what you mean, I'm generally with you on this one. Well, I think that the benefits are still worth doing this. > whole tooling support is reverted to the 80’s. Again, I don't see the connection. But, even if it was so... what's the problem? You get to work with an old and reliable technology, with decades of knowledge and experience to support it. And it's not like it died in the 80's. Tons of effort is being invested into this technology, it's amongst the first to get updates and integrations with new hardware and system capabilities (eg. vectorized ISAs support, even GPU access, bet you that when "smart SSD" become common enough, SQL databases will be the first to support them). What's not to like? This is like complaining about C compilers for being "old tech"... for all that's wrong with C (compilers), being old is not one of those things. > It’s a pretty shit language If you were honest, you'd write it like this: "I don't like it". And, well, you are allowed not to like whatever you want. It's subjective, and completely up to you. It just has less of a convincing power. But, really, this is your entire argument...
- eternityforest 4y agoI don't see anything particularly bad about SQL. If your problem is a recipe manager CRUD it's trivial. If it's more complex then than, any DIY thing is gonna be hard too. If you need some insane performance, SQL might not be right but any DIY thing will still be hard.