Urban Myths about SQL (2015) | Hacker News Reader

Urban Myths about SQL (2015)

docslide.us

31 pointsTomte55 comments

This seems suspiciously like a marketing presentation for VoltDB more than debunking myth about SQL.

Tl;Dr; SQL is too slow ? No, VoltDB is Fast !

It was presented in 2010 at gluecon and was the launch deck for VoltDB. A big chuck is marketing, yes. Doesn’t make it wrong as a counterexample.

Though in 2016, there are a lot more fast SQL systems and it’s unclear this myth is still as mythical as it was?

I agree. This is basically marketing material from VoltDB.

I've flagged it, not because it's not interesting (some of it is), but because marketing propaganda can't really be trusted as a source of information.

While it is clearly propaganda for VoltDB, it does a good job of adressing some of the myths of SQL-slowness that we don't see very often.

Usually a lot of people saying SQL is slow is because they don't really use it well. He doesn't talk about indexing and statistics [1] for example.

But he talks about ODBC/JDBC because, while it is usually fast enough, a lot of people overlook the fact that these interfaces hold transactions externally.

Besides, the presentations comes from Stonebreaker himself; of (Turing prize, Von Neumann Medal, ingres, postgres, cstore, vertica, hstore, SciDB, Tamr, Streambase)-fame.

[1] http://use-the-index-luke.com/ is a very good resource for this.

Yes, that slide deck in isolation looks an ad so it's probably better to get some background on Michael Stonebraker's "big picture" beliefs and core premises:

++ He believes SQL is the "right" and most proven access model. (Note that SQL does not mean RDBMS like Oracle/MSSQL.)

++ He believes in ACID

++ He believes that other db engines' (NoSQL, etc) approaches to gain speed and scaling of clusters by sacrificing SQL and ACID is wrong and his proof is that those other products end up eventually bolting on SQL and ACID anyway.

I think a better presentation to flesh out these ideas is the following youtube video: https://www.youtube.com/watch?v=KRcecxdGxvQ

It's 55 minutes long so to save time, HN readers may prefer to browse it by pressing "L" key to step through it quickly. Just read the slides shown behind him and if the bullet points look interesting, rewind and listen to his presentation.

One can dwell on those SQL/ACID merits in isolation and consider separately, the question of whether VoltDB-as-a-product fulfills those ideals.

I’m pretty sure this is Mike’s GlueCon 2010 presentation verbatim. Not sure why it says 2015.

Plenty has changed since 2010 about VoltDB. If people have any questions I’m happy to answer.

Is it still stored procedures only?

No, you can run arbitrary SQL. You can even send it over an HTTP/JSON API.

If you want a multi-statement transaction with logic, you still need to use a procedure. 1TXN = 1RndTrp is still true.

"Use stored procedures instead" - in most use cases this is prolly a really really bad idea.

Why is that?

Maintenance.

Companies really like people doing it as well, as it locks you into their platform.

> "Use stored procedures instead" - in most use cases this is prolly a really really bad idea.

This is a common recommendation. The basis for it is two fold, 1) You strictly define the inputs for your transaction as the parameters for the stored proc and 2) You have a single round trip from the app to the DB.

For a complex transaction involving says 10 separate operations (insert X, query Y, update Z, ...) that's 10 network roundtrips that are saved. Add to that eliminating the transit of data from the DB to the app server.

On the other hand cloud sql is 400 micros from web servers. 10 round trips is like 10ms.. Not even visible to an end user.

And parsing the query and generating a query plan for those 10 queries is obviously free.

You can also solve this with prepared statements which for example Active Record does for you for free.

That's often done once and cached.

It is worth keeping one eye at those things. They can slow your program down badly, and go unnoticed a lot of times. But most of the time they don't slow you down at all, and when they do it's often much better to solve the problem by rewriting your queries into something that batches more data.

Yes, once in a long while it makes sense to create a stored procedure to deal with it. But this is the exceptional situation that deserves explaining - not the other way around.

If you need to hold a lock for 10ms in order to ensure things are transactional, it can have a bigger impact.

Also, if you get a slow delivery even N packets, where N seems large, you’ve just incresed your chances of hitting that issue 10x. This is brutal on long tail latencies.

But no, not all systems need this.

Ya exactly. If you are writing something high performance, I don't think you want locks. You are more likely to do append only and sort it out when you do a read or such (or fancy magic down the Cassandra route).

For stupid webapp, meh. Most of the time when a page load takes 10 round trips to the DB it is not for a lock, it is to read a bunch of tables to render a page.

I'm just guessing here but this to me seems a bit too micro optimization.

Great when you are a huge company with massive number of data and operations where every ms counts, but in most cases there are bigger and more important things to optimize before saving some round trips from the db.

If you use sps you actually have much less coupling to the structure of your DB that let's you rearrange/optimize things on DB side without affecting the client apps. Plus you can gain serious security benefits by using sps only.

It’s totally true that this optimization only matters if you’re pushing your database.

Of course as a VoltDB employee, we have a number of smaller customers who use VoltDB because it allows a small shop to do things that would be too expensive with a traditional system. Just because you’re small, doesn’t mean you don’t have a high-throughput problem.

One example is Xacte, which tracks marathon bibs as they fly by RFID sensors in the course. The issue is when 50 people run by a sensor in one second, the de-duplification code in the sensor sometimes breaks down, and their system gets hundreds of triggers. They literally have a van full of equiptment that includes a small VoltDB cluster keeping accurate runner positioning for events like the LA Marathon.

Round trips over the network of any kind are actually the fastest way to make your application slow. So if you do optimize for performance then this is a good starting point, if you do go over the network a lot. But you are right that you should think twice and first make sure you even built the right thing.

Yes, I agree.

But, in many (if not most) situations the ms you loose by doing more round trips then necessary are not worth how much more maintainable the system is.

Especially when there is probably some programming or query optimization that will save you seconds, when reducing the round trips is a matter of ms.

It's very system dependent, but VoltDB is designed for systems that require up to a million tps. "A matter of ms" is not irrelevant in such systems.

Yes, ok I agree. 100% agree.

But micro optimization has always the same caveat:

Of course it is not irrelevant, it might even be very important, in very specific cases.

But if you are not one of those cases (and you should already know if you are) you shouldn't even bother with it.

I think that everything you just said categorizes VoltDB already, though. They're not advertising themselves as a general purpose database. You'll know if you have a use case for it.

By "stored procedures" the author seems to mean what is more usually described as "prepared statements": https://docs.voltdb.com/UsingVoltDB/DesignProcAnatomy.php#De...

VoltDB supports prepared statements AND stored procedures. It also supports ad-hoc SQL and will cache auto-parameterized intermediate representations.

SQL is only slow if the system has never seen it before and needs to plan/optimize, which typically happens rarely in an OLTP system that does the same thing over and over.

“really bad idea” seems too strong for me – I don't think it's good general advice, at least in that simple a framing, but I'd focus on the actual downsides: you're writing a lot more logic in SQL and that means you need to manage it in two places while maintaining test coverage, deployments, etc.

Many places do that terribly – no version control / copy and paste from a separate system, different groups own the code and the database, etc. – but that doesn't mean that you wouldn't have a far better existence if you followed modern practice, used a good migration framework, etc.

It's not too strong. I actually meant "in most use cases" which in fact is true, most people are slow anyways. Your database or the roundtrips are mostly not the performance factor nr. 1

I wouldn't describe something which is a minor performance delta as a “really really bad idea” — save that for things which have reliability risks, security implications, etc. This is a tool which people should know about and consider when they have the right situation.

I'm also not sure what you're basing the claim that “Your database or the roundtrips are mostly not the performance factor nr. 1” on, either – poor database usage is a notoriously common area for performance issues. Roundtrips are less common since things like unindexed or poor queries usually dominate first but it's certainly not hard to find examples of someone missing the problem when testing their app using a local database and then being reminded that latency matters the first time they try it using production-scale data on a real server.

The big issue here is the idea of moving logic to the data, rather than data to the logic. One of these things is much bigger than the other. If you have a multi-statement transaction with intersitial logic, client-side logic is going to get absolutely smoked by a stored procedure.

There are downsides; suddenly logic in your app is split between client side and server side, which is annoying.

At VoltDB, we do our best to make the situation better than it is in more traditional systems.

- Stored procedures are Java, and can be unit-tested and debugged live.

- Stored procedures can be transactionally added, removed and upgraded in a cluster-wide operation that happens at a single-logical point in time.

- Stored procedure code (and schema) is archived with all snapshots, allowing for portability.

- You can run stored procedures on your laptop development machine, then push the same bits to a pre-production cluster, then push the same bits to production.

- Stored proceudres can share code and even use inheritance, reducing putting the same logic in many places.

One way to look at it is the set of prepared statements and procedures on your VoltDB cluster is sort of a state/processing-API. It works really well when you’re processing streams of events and you have one procedure per event type.

You say JDBC is slow, but what if you're using PreparedStatements and PreparedStatement caching? Isn't that similar?

JDBC doesn’t have to be slow. VoltDB actually caches ad-hoc SQL plans so even they run pretty fast these days. You can use JDBC and get good performance.

The biggest thing you lose with JDBC over the native VoltDB clients is better cluster awareness. In our native client you can get callbacks when servers fail, when backpressure is triggered, etc…

Now Hibernate over JDBC is another issue.

Edit: As merb points out JDBC is synchronous. This is true; if you have one client and one thread, this will be limiting fast. It's not a server-side issue though. If you have enough clients and/or enough threads, then you can still get very high throughput.

I see, I was reacting to the slide that mentioned ODBC and JDBC are slow.

It makes sense that no matter the context, forcing the DB engine to parse and plan SQL it hasn't seen before could be quite a bit slower than parsing/planning it once and swapping in new parameters for each query.

Actually JDBC is synchronous, this could be a real pain. But there are alternative's to most databases around.

Not to mention, stored procedures mean having a DBA, or developer well versed in DBA, available when you want to make changes to your app, which isn't always an option.

I'm still pretty firmly in the camp which believes that a lot of logic ('business rules,' if you like that phrase) should reside in the database. IMHO DBAs are useful, in much the same way that ops folks are useful: they have a different skillset and point-of-view than do developers, and are a necessary component of any team dealing with data.

Playing fast-and-loose with one's data is as bad as playing fast-and-loose with one's processing of that data.

I completely agree. People early in their careers have often not yet realized that data is king. That flashy web app written today will be thrown away tomorrow, but the data gathered by that app will live on in other apps for as long as the company is in business (and maybe longer depending on the type of data).

Most VoltDB customers have no DBA. DBAs can sometimes be pretty hostile during POCs because with VoltDB and many other non-tradtiional systems, they often have a smaller role.

Some DBAs are great though and bring experience that can be really valueable when building an app. Depends on how open-minded they are.

Isn't one of the main reasons to move logic into the database is so that you have a dedicated DBA handling everything in the best way possible ?

Well, you often have data-centric developers. Whether their title is DBA is variable. These people are rarely divorced from the application implementation and design the way traditional DBAs are.

I see old-school DBAs as people you give logical schema to, and they make the physical schema fast and keep the system up. That role feels pretty dated.

> Not to mention, stored procedures mean having a DBA,

Not anymore than writing SQL requires a dba. If you have developers writing SQL that cannot handle creating sprocs, then you already have a huge problem.

Most developers I know aren't writing any SQL, and if they are, you might (not necessarily) have an even bigger problem[1].

ORMs are a valuable abstraction. It's no good to go about your coding life without a care in the world for SQL, just because ORMs exist, but it's even worse to write an app littered with SQL, which is a language most likely entirely unrelated to your app.

1. I audited a code base for a friend a few weekends ago, and it was littered with SQL injection vulnerabilities, because the developer didn't like ORMs.

You don't necessarily need ORMs. If the developer had used prepared statements (e.g. PDO in php), then there would probably have been a lot less vulnerabilities.

Hand concatenating SQL is also not a good thing. Prepared statements and/or something like JOOQ (which is my goto in Java nowadays) would have prevented most if not all of the injections.

Sprocs also go a long ways towards preventing SQL injection. One could still build sql statements through concatenation inside the sproc I guess. /shudder.

> "Use stored procedures instead" - in most use cases this is prolly a really really bad idea.

Of course, so when I need need to do operations on large sets of data lets pull it all into an app, operate on the data and then push it back. I would not be no snarky if I had not seen it done because someone didn't like stored procedures.

Those aren't the only two choices.

The presentation is clearly focused on high-performance use cases. In that context, they make a case that be it bad idea or good idea, it's your only choice, because you can't afford a back-and-forth conversation between the program and the database for every little query.

How true that is I can't personally vouch for, having never examined the network protocols for the DBs that closely. I'm not sure what the conversation is that they are referencing when it seems like the client can send the query + all data in one shot and the server ought to be able to start streaming responses pretty quickly. But it wouldn't be the first time that either the protocols or my understanding of the situation is suboptimal. I'm really just relaying the argument and making the point that the context matters. There's a lot of DB usage in the world where none of this matters because your performance needs are frankly blown away by what even "slow" DBs can do.

Consider the op, "put me in line if the line isn't full"

Query 1: Check if the line is full. Query 2: Conditionally put me in line.

First, this requires two trips. Second, the line could fill up between these two trips, cause the second line to put you in a full line. So, you hold a lock between the two statements. Now you have a third round trip to commit or abort, and the line is frozen for new additions while you talk over the network for two more round trips.

Some examples are better, some worse, but this kind of thing is way better if you bundle up the logic and send it in one round trip.

It's not SQL that is slow, but when you try and distribute a relational database you lose Referential integrity, Transactions, Unique Indexes and other such key features which are why you actually chose a relational database in the first place.

I'm yet to see a database which does all of that and still is truly distributed.

VoltDB lets you keep transactions, and the strong consistency lets you enforce many constraints (though some in stored procedure code).

Cluster-wide uniqueness has some limitations, but there are lots of tools we offer that are way better than nothing.

Much of what you get in a relational DB, you can keep, and you can scale.

I sometimes wonder if the "cloud first" mentality has led people to distributing databases way way before the need for it. I get that "big data" is in the in thing right now, but non-distributed databases still scale to huge amounts of data. I suspect there are plenty of services running over distributed servers instead of a couple of appropriately sized principal/witness servers.

Beyond that, it's better to distribute by splitting databases across services or uses, so instead of having a mono-database running over a cluster, it's better for example to have an OLTP database and a separate OLAP database for reporting. It's easy to split those out to different servers rather than having a mono-database distributed over a cluster.

Going beyond that, data partitioning is more sane, so you partition the data so that different sets of non-interacting data are in different places. So you can for example partition by customer, then each of those partitions can be arranged to different servers where appropriate for load. There shouldn't be a need to have transactions hitting more than one partition at once when partitioned correctly.

And none of that breaks referential integrity.

Well, even for smaller use cases, systems with peer-to-peer clustering over a lan have better high-availability profiles than primary/secondary replication systems.

Reply on news.ycombinator.com