Choose SQL
stateofprogress.blog
stateofprogress.blog
Also PostgreSQL is much more than a SQL database, it rivals an application server with its flexibility through extensions and supported languages.
https://groups.google.com/forum/#!msg/redis-db/DHBqQ7x4sOU/e...
NoSQL doesn't reduce development effort. What you gain from not having to worry about modifying schemas and enforcing referential integrity, you lose from having to add more code to your app to check that a DB document has a certain value. In essence you are moving responsibility for data integrity away from the DB and in to your app, something I think is quite dangerous.
NoSQL has its place, but I do feel that it is a bit more hyped up than it should be.
This × 100. The app I'm working on has so many `if model.value` calls scattered throughout the code that it becomes difficult to follow and make sure no spots were missed. Simply adding validation to the app before writing data isn't sufficient since there are other ways to persist data and NoSQL provides no sense of data integrity.
Why is this a bad thing? The code in my app can be tested and is much more expressive than some contraints in the DB.
When validation criteria are changed on an SQL server, it revalidates all the rows - either immediately, or at the end of the transaction. With the DB doing the validation, the default is that all rows are valid.
Also, do you ever side-step your database abstraction layer to send SQL directly? If you ever insert/update/delete rows that way, perhaps for performance reasons, now you have an extra place in your app to make sure the relevant validations are kept up-to-date.
https://robots.thoughtbot.com/validation-database-constraint...
Relatedly, this article suggests that your app should only be validating user input - so there should be no app validation of fields not set by end users - whilst the database itself should validate what your app gives it.
This is only necessarily true of specific "instances" of NoSQL. MongoDB has supported server-side schema enforcement since Dec 2015, for example. Most likely other relatively mature NoSQL systems have similar features. There's certainly nothing about NoSQL which makes server-side schemas impossible.
The issue is social, not technical. I believe that people that want to use NoSQL dislike schemas - or believe that their use case is too hard to describe with a schema. Most NoSQL databases won't have schema validation set up even if the feature is available.
Only by talking to each other can we find the balance that mutually optimizes our joint effort level. A little more effort up-front to log data in a format with well-defined schemas, translates into huge savings in processing, consuming, and understanding the data down the road.
Note that this is a fundamental principle in software development, not just for data science:
Prefer data structures that are simple to interpret over those simple to generate.
1. Data is usually produced by one application, but consumed by many different applications.
2. Writing a serializer is almost always easier than writing a parser. [1]
However, it is not always clear how to apply this principle. For example, as a library or web service author, you define the API on your own, rather than having your users define it. It is extra effort to get feedback from your library users, and to design your API accordingly. That's why most APIs are geared towards the data producers rather than consumers.
There is even an enterprise design pattern for this:
https://martinfowler.com/articles/consumerDrivenContracts.ht...
[1] The former usually involves a template system and a few helper functions. The latter usually involves regexes embedded in ugly code, parser generators with all their shortcomings (and the fact that most programmers don't bother to learn using them), complex DOM or JSON traversals that fail to handle all special cases, or masses of autogenerated classes automatically derived from some XML schema (or JSON schema).
YouTube, mentioned in the article, also uses MySQL, but the author forgets to mention those big names don't use a plain simple Vanilla SQL. In the case of YouTube, it's Vitess [1]. It would be nice for the author to mention this.
The point is just fighting the perception that MySQL can't scale.
Which is a misplaced point that'd misleading an entire generation of engineers.
MySQL doesn't scale. The companies mentioned had to throw huge amount of engineering resources to make it work, whatever the costs and the drawbacks. They had no choice left, hack it or die out of your own traffic.
Cassandra, ElasticSearch, Riak, RedShift, BigQuery, DynamoDB, BigTable, Spanner, Terradata, S3...
if you're trying to keep your hosting overhead down though none of those is available (except S3 I suppose, which is super cheap, but also not really a datastore solution) but a basic app server running MySQL (or similar) is. now, I'm not saying anybody should expect scaling for free. I'm just saying you're comparing apples to oranges here.
The apples to apples comparison is basic app server running MySQL vs. basic app server running MongoDB. the myth is that the MongoDB instance inherently scales better than the MySQL instance. it doesn't, particularly. it has better write performance, but not much else that gives it better scalability.
and the real point of the article is to combat some of the magical thinking that has lead to many bad decisions from misinformed developers who heard a rumor that a NoSQL datastore has orders of magnitude better performance characteristics than an RDBMS.
> MongoDB
MongoDB is a pile of crap and a shame to all the NoSQL datastores. Please don't bring MongoDB in a talk about databases. If that is your only experience of NoSQL, it is perfectly normal that you think of NoSQL as a mistake to never use ;)
My best experience with NoSQL (in terms of it working ok and not otherwise fucking up the system) has been with HBase on a cluster running map/reduce jobs. That was well suited for the purpose (mostly). I'm sure glad nobody had any illusions about using that as a production datastore for a user facing web app though.
I usually consider the "I had to migrate from DB X to DB Y" a success story. You discovered what was required and later in the process, improved your system. Painful, but probably unavoidable.
Databases are not easy to handle and operate. Is easy to underestimate them. We used to have people just devoted to them (can you remember DBAs?). It is worth to spend time learning them and knowing their goods and bads. And sometimes making mistakes in unavoidable...
VERY true.
> Databases are not easy to handle and operate.
I think instead are the easiest of all available options. With a SQL database you have at least available some kind of GUI to build the schema and do query and stuff.
And have see not-very-technical people building databases just fine.
Try the same with Cassandra "Hey, secretary, please build a Cassandra database for the contacts, please!".
Yep, not easy.
------
You only get into trouble when:
- You schema is super-bad. But is the same with NoSql
- You don't create that 1-2 indexes per-table (and some Guis/RDBMS auto-create them for the ID and surrogate keys, that is almost enough)
- You have a very large database. Or, you have a bad schema and is leaking as a very large resultset (or SQL query) that is overcomplicated everything.
- You forgot you have a powerfull RDBMS and insist to use it below the capabilities of Acces, yet, have proud in learning C++. Because C++ is powerful. But why dedicate a few hours learning the basic of Sql??
Most people have very easy problems to solve, and in contrast with the nightmare that is debug a NoSql store that badly try to re-create what a RDBMS have and you know some data is lost but good look finding it...
---
Where the situation get out of proportion is with the startup-mindset of "scalability". A problem that few have, even startups.
You have more than 10 TB of data.
(I've also got many many more stories of my team saying "We'll need to use $NoSQL-de-jour here for performance and scalability!" where I've talked everyone into "Let's just go with MySQL/RDS/Postgres as a prototype, and see where the speed or scaling problems arise before we rewrite things." and never coming close to bumping into scale or performance problems...)
I don't mean to single you out in this comment. The reality is that your comment just really reiterates so many of the same arguments that are regularly trotted out as justification.
NoSQL already came and went. It was called ISAM. It has a use case, but it's not the silver bullet everyone wants it to be.
Oh yeah, as a DBA, I've loved hearing how NoSQL was going to do away with my job and that I am a 39 year old dinosaur who'd better catch up and get on board with DevOps. Guess who runs data CI where I am? Guess who manages our Mongo and Redis servers. Yep. The ancient DBAs.
You know what the #1 feature of nosql is? NoDBA.
I can hear you screaming you'll still need a DBA. You don't get it. You won't need an Enterprise DBA.
Enterprise DBAs are almost invariably horrible blockades to data storage in any enterprise. All they do is lock away the database from developers, cram Oracle down their throats, do things as slowly as possible behind ticket walls. You lock the data away pining for your halcyon mainframe days of white coats and clean rooms, and force your slow ponderous overtenanted infrastructure.
Your profession, since you have proudly identified as a DBA, you are an Enterprise DBA, is a disgrace.
How about not? Bad actors can be found in all domains, DBAs included. Painting all with such sweeping brushes does little to suggest people should take your comment seriously or advance the conversation constructively. There are also plenty of DBAs who know the strengths and weaknesses of many different data models, engines, and platforms, and work with developers to make decisions to ensure efficient data storage and IO for a successful application.
Edit to add:
Your profession, since you have proudly identified as a DBA, you are an Enterprise DBA, is a disgrace.
If you identify yourself as a DBA you must be an enterprise DBA? There are plenty of DBAs who are happy to dig into backend or frontend code as well, but have a lot of experience working with database systems. Many frontend and backend devs have experience working with databases, and similarly describe themselves to focus on their strengths. Are they enterprise frontend and backend devs?
DBAs are specialists which makes them inherently siloed.
Using SQL requires competence too, but learning nonstandard exotic idiosyncrasies of a certain configuration of a certain bleeding edge system (the blood is yours) is much less useful than principled and somewhat standard techniques that can be readily adapted to any good RDBMS.
For example, there's a substantial difference between improving performance of a slow query by trial and error, rewriting application code to figure out what the current version of a bug-ridden interpreter/optimizer likes, and improving performance of a slow query by ENGINEERING, creating an index according to the reports of analysis tools, back-of-the-envelope estimates of size and performance and true understanding of different index types.
As an Enterprise DBA, I know I have to be ready to be an SME on any number of topics at a moment's notice. I have to understand my data structures and logical modeling, I have to understand IO patterns and concerns (RAM and physical storage are the ones that crop up the most often for me), I have to understand security, I have to understand networking. I'm sure that you also have to consider many of these things as well so I'm not better than anyone, I'm just doing my job.
On top of that, I also have to make sure that my organization's best interests are considered. So if I'm slow to fulfill your request, or even if I say, "no, I can't do that" please don't take it personally. It's just that there are other things to consider than what you need right now and what you think is what you need to do what you need to do as fast as possible. Some times the "fast and easy" way to do things isn't the correct way to do them. Let's figure out a way to work together to do what needs to be done correctly the first time so that we're not plastered on an HN article that says, "why I moved from MySQL to Mongo to PostgreSQL."
If you are taking the time and (someone else's) money to build something do it right and research how various data stores work up front. You should be at the point where it is obvious what the right decision is in your mind and if it isn't...get your learn on until it is.
At Goldman Sachs there is almost every imaginable type of data store in use. All carefully vetted decisions that are largely the right choice much of the time. Almost all of it flows into some form of web application at some point in time in a giant interconnected system.
I am very happy we don't use a single class of data store. If we did we'd be fucked.
The "thesis" may apply very narrowly to a small web app of a certain size. But honestly if you are building something that cookie cutter are you really building something unique? What you are doing has probably been commoditized.
Is it just me, or does this seem rather misleading? My understanding is that they can use MySQL because they've carefully designed their architectures to direct most hits to caches or Memcache/Redis, which minimizes the load on the database.
When it comes time to cache and optimize for things like search, that's when you use the additional technologies.
It's a huge mistake to think that companies that are using Redis/ElasticSearch/MapReduce are doing so to cover up for the wrong decision they made of going with an RDBMS. The RDBMS is their source of truth, everything else is just an indexing mechanism.
First, to be sure, in the year of 2017, it's definitely the safe path to go with Postgres or MySQL. Traditional relational databases aren't distributed out of the gate, which is both a good thing and a bad thing. They're mature, which means that lots of bugs have been found and fixed, and there's a Stack Overflow answer for every basically error possible. And yes, Postgres will remain the "best" choice probably for at least another decade.
Very broadly, traditional relational databases are chosen because of two advantages: features (rich data model, query language, ACID transactions, etc.), and maturity. NoSQL datastore generally sacrifice the former for the sake of scalability, and lack the latter simply because they're too new.
First, the features. I think most people are in agreement that stripping down your data model to fit into a basic but highly scalable key-value store ultimately hurts productivity. But it's definitely not impossible for a scalable distributed database to provide most or all of the features that a traditional relational database provides; that's exactly what Google did[1], and what some open source projects[2] are trying to emulate.
Sharding Postgres is safe, but it's also hard. It does messy things to your application code, and also hurts productivity. And at the end of the day, you still don't get ACID transactions and joins if you ever have to do anything that touches more than one shard. We really do need a better solution that combines the best of both worlds.
Sure, traditional relational databases will probably always be the most mature databases around. But there will come a time when those new databases are mature enough. And I think along the way, we can rethink some of the properties of traditional SQL that make it hard to work with sometimes.
1: https://static.googleusercontent.com/media/research.google.c...
2: http://db.cs.cmu.edu/papers/2016/pavlo-newsql-sigmodrec2016....
>In the first case there is a language using words that humans use to talk to each other, while in the second one there is pure JavaScript, where you have to build your query using a JSON object and even convert the 24 hours to milliseconds on your own.
I have never personally implemented anything using either MySQL or Mongo. But even I can see that this is a bad example. In the first case, you have an SQL query raw. This obviously has to be passed to some sort of API to be used in code, so it's an incomplete example. In the second case, you're blaming what appears to be a weakness in Javascript's "Date" library on MongoDB. In Go this becomes time.Now().AddDate(0,0,1) which is relatively straightforward. nb. Go time lib becomes less pretty whenever there's an error involved and I sort of hate it honestly.
>First, scaling is not and most probably won’t be your problem. Unless you start having a few thousand of queries per second and terrabytes of data in your database, this won’t be a problem at all.
...true...
>And in case it does, you can go with a fully managed solution like AWS’ RDS.
...but the freshman delusion is "I have scaling problems", so the sophomore delusion is "I will never have scaling problems" :p
The job of a businessperson includes managing things. You can't outsource all of the management. It's how you add value after all. I've seen plenty of cases where people choose not to go with AWS because they can save money by doing more work and they made off with some dough.
But this is the point: that SQL is a better query language than Javascript (which is what MongoDB uses).
Sometimes RDB is better. For other things it's easier and more scalable with document store.
They need to know about each other using your application logic (join by GUID for instance), but you're doing that anyway even if you religiously staying in one camp or the other.
No JOINs, no natural way to express a ORDER BY x LIMIT y,z... no way to e.g. mirror your back-end database in order to implement stuff like full-featured offline/online apps... and don't get me started on the fact that IndexedDB is async which makes even emulation of the features I stated an open invitation into callback hell. Or, basically, ANYTHING more complex than a SELECT x FROM y.
How could this ever end up being a freakin' standard and WebSQL being dropped?!
(If anyone here decides to implement a WebSQL equivalent backed by IndexedDB, here are my monies, take them! Please!)
Which is total bananas, given the fact that there's only one (free, ultra portable, tiny) embeddable SQL database - SQLite. Which every browser except IE/Edge ships, anyways, for internal data storage.
What remains of WebSQL is the commonly implemented SQL client driver API for Node.js which isn't so good a fit IMHO.
That wouldn't have helped. Google Chrome (and, by extension, Safari and Opera) and Firefox would just use the already present SQLite library in the engine... and I think MS would also have chosen SQLite instead of risking to develop a full SQL database (and opening a rich source of bugs and security issues, in contrast with using battle-tested SQLite). Again, just one implementation of the standard.
Mozilla published some blog posts about why they didn't like WebSQL and backed IndexedDB instead: https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
If someone else had put in the work to write a "proper specification" of an SQL dialect, shown that it works with (modified) SQLite and was reasonable to implement in a different implementation, WebSQL might have passed nevertheless.
It happens that W3C Working Groups release standards without 2 implementations, so this isn't some strict rule that can't be broken, but then everybody involved agrees.
In the other direction, I think it would have been good to demonstrate by implementation that you can build good SQL-like APIs on top of IndexedDB during the standards process, to make sure it fulfills that need.
It was killed by the standards committee because a key part of the standard was essentially "do exactly what SQLite version x.yy does", because the only implementation was by embedding that version of SQLite in the browser and putting a thin API on top of it.
And using databases designed in the nineties just cannot get you to fast resilient services or to any relevant experience necessary to build them at some point in the future. But it will waste just as much time if not more. CRDTs and eventual consistency are actually easier to understand and reason about, than transactions. Don't get fooled by familiarity. Don't choose MySQL, choose Cassandra, Riak, DynamoDB, don't choose POSIX storage for the same reasons, choose object storages, Swift, S3, etc.
I suspect we’re going to have to agree to disagree on this one. Without spending too much time on this, it’s pretty easy to cite the Google F1 paper (http://static.googleusercontent.com/media/research.google.co...):
"The system must provide ACID transactions, and must always present applications with consistent and correct data. Designing applications to cope with concurrency anomalies in their data is very error-prone, time-consuming, and ultimately not worth the performance gains."
The only problem I have with SQL (I use Postgres, in the form of Redshift almost daily: it's excellent when it works as expected) is the not-great readability of the code and the abysmal testing/debugging/checking of SQL code. Have had to write lots of boilerplate (Python, mostly) for that, and it feels like we are reinventing a wheel someone else is using, somewhere.
Isn't that more of a poor-SQL-programmer problem, than a SQL-Database-technology problem?
> and the abysmal testing/debugging/checking of SQL code
I haven't used Postgres, but MS Sql Server has a ton of testing, debugging tools including SQL Trace, Explain Plans, and even a Stored Procedure debugger wherein you can set breakpoints and step through your code.
Of course, one cannot expect the same level of rich debugging features that one gets with traditional IDE for programming languages, since SQL programming is a somewhat different beast in that sense.
You are only limited if you try to use a higher-end feature (like analytics) but the overall tooling is all there.
I don't mean testing/debugging as in stepping through code that has been just written, but code that has been automatically submitted to the database during the night and for some reason has failed. The goal is to never be in that situation: with SQL you need to be way more careful than with other languages.
This. Exactly this is the problem. SQL as a language fails to provide you with simple means of composition.
And this is where libraries like Python's SQLAlchemy come into play. Note that it is not necessary to use SQLAlchemy as an ORM. Even if you use it "just" as a query builder, it will simplify a lot.
Composition is really where the syntax of SQL fails utterly. This is quite surprising, because the foundation of SQL, relational algebra, excels at composability: You have many operations which all operate on the same type of data (namely, sets of tuples, or in math/cs speak: "relations"). As such, you can combine them in any way you want. For example:
* "Filter" takes a relation and returns a relation, just with fewer rows.
* "Projection" takes a relations and returns a relation, just with fewer columns
* "Cross Join" takes two relations and returns one relation.
* "Inner Join" is really just a combination of "Cross Join" and "Filter"
* ... and so on.
For example, assume you have an existing query (perhaps more complex than this one):
select a, b from t where a > 0
(select a, b from t where a > 0) where b > 0
select a, b from t where a > 0 and b > 0
select * from (select a, b from t where a > 0) as temp where b > 0
Projection[a,b](Filter[a>0](t))
t > Filter[a>0] > Projection[a,b]
t > Filter[a>0] > Projection[a,b] > Filter[b>0]
t > Filter[a>0 and b>0] > Projections[a,b]
t > Filter[a>0] > Projection[a,b] > Name[temp] > Filter[b>0] > Projection[*]
Let's make it simple. Imagine a database with no tools whatsoever.
Welcome to postgre!
What kind of boilerplate?
---
RDBMS have a less-than-ideal programming API, and also is de-coupled from the front-end (good) but also mean it requiere to re-implement some stuff in the client (because is a agnostic api). ORMs make this task harder than it must.
--
For testing/debugging not help that a Database is a huge mutable store, and thigh testing was not a thing before.
But more of this is a trouble with the disconnect between the database guys and the app guys.
For example, Firebird (http://firebirdsql.org/) allow to embed the database similar to sqlite and also use it in a separate server process. It mean is easy to create a test environment easily without docker-alike craziness.
Another disconnect is that is impossible to avoid sql. SQL is a poor developer API and is not decoupled so we can use something else.
The advantage of some NoSql is that them not recreate SQL (at first) so them can do some nice stuff as post a JSON and call some basic commands.
Anyway, reporting is a very hard problem. I don't know if exist a truly good way to solve it in a general case.
---
Is part of this not solve with views? Any example to showcase the trouble?
Also, the biggest chunks of SQL we handle are essentially "computational": take this, join with that, mangle a lot what we have now, repeat several times and get an output we can use. Can't avoid the big part in the middle
(See also my other comment: https://news.ycombinator.com/item?id=13929111 )
There are times where NoSQL is a better choice. When massive scale is involved I understand that NoSQL can come into it's own. I'm sure there are other cases as well. But when it gets used for CRUD, it tends to be a bad call.
Also, NoSQL is not a database, it's 10 different databases which have little in common with each other. A rare speciality pays more.
One other thing I'd add - I read a very good mantra for software development, that I've adapted a bit in my head.
Your data will outlast your application
Your application will outlast your framework
Your framework will outlast your developers.
For many of the apps I write, the database is the core of the application. One question I like to ask myself is: if my users were well versed in UNIX, SQL, and a programming language, how well could they get along without the web application?
The answer to this question reveals how well I've designed my app.
For instance, is the backend database useful on its own? Or is it merely a persistence tier for scattered bits of information that only become useful once assembled by code? If it's the latter, then no wonder people start questioning the value of SQL. It isn't functioning as it should, there's no relational structure to the data. Persistence for scattered bits of objects that need to be reassembled through code means that the relational database is almost just getting used to serialize objects back and forth to disk. If you're doing that, then of course you start to see SQL as just a bunch of stuff that complicates things! I mean, at that point, why not just write and retrieve objects? There's almost no difference. Thing is, this sometimes reveals a mismatch between SQL and the problem at hand, but just as often, it reveals a developer who doesn't really think in terms of databases and relations, and who just sort of crams things into a SQL database because it is the default persistence tier.
Is the code that does essential analysis and logic on the data easy to run and understand on its own? If my users were capable of logging onto the machine, running this code as command line scripts, and reading the output, how useful would it be to them? How clear and concise is the code?
If both tiers are clear and easy to understand for someone grounded in SQL, Unix, and a programming language, I have control over my code base. If not, there better be a really good reason. Eventually, the developer will leave, the framework will be out of date, and someone new will need to work on the code base. If you can't make sense of the database, in particular, you are in serious trouble.
> Let’s get straight to the point; choose an SQL database for your web application. I think I can’t make my self clearer.
Yeah there is better qualification later, but there are whole paragraphs of railing against nosql databases for all kinds purposes that they actually CAN make sense in, for certain applications.
If you happen to have an exception to this rule, you are a VERY VERY rare case indeed.
Order in syntax for SELECT is weird. I usually look FROM then WHERE then the fields being selected. Feels like reading backwards.
You will be AMAZED at how compact SQL actually is by comparison!
That said, do try to rewrite a moderately complex query without SQL. Pick one with multiple joins, several where conditions, and a group by. You will generally wind up with a fragile program that is many times as big as the equivalent SQL, and is a lot harder to read and maintain.
An often overlooked point is that good SQL optimizers generate different versions of queries depending on the current state of the database and the actual parameters to the query. That is, the optimal query plan for finding the sales to "school teachers" is probably different than for "astronauts" and the optimal query plan for reporting on "todays orders" is probably different at the start of the day than late in the afternoon. SQL query planners try to do the right thing with this sort of data variability and generally (but not always!) do a good job. It would take an immense amount of work and insight into the data and all the potential use cases to approach this with custom coded queries.
Before there were relational databases these sorts of access path, query strategy decisions had to be made by developers and were reflected in the schema and physical design of the database. This was inflexible and very labor intensive and it made many kinds of applications and or even routine changes uneconomical.
That said, there is little point discussing this subject people who clearly have no experience about how to do what a little bit of SQL does. If you're convinced that manipulating data is easy, you're not going to appreciate that manipulating data efficiently is even more tricky.
If you're storing state rather than information (append-only), you'll have a hard time in the future regardless of query language.
Redshift and SQLite are pretty different.
That's not to say they lose usefulness at scale or should be avoided because they'll be a "bottleneck" at some point (if a SQL database is ever a bottleneck, you're super lucky!) but there are definitely cases where a non-SQL database is the right choice.
In some cases, it's not a scaling but just a use-case difference (Elasticsearch)