Important database news from the last few months | Hacker News Reader

Important database news from the last few months

winand.at

123 pointsMarkusWinand81 comments

Anyone know a really good SQL book or tutorial? Every book I've ever bought is usually way too simple. I'll flip to the "advanced" section thinking I'll finally learn some tricks only to see the "advance" section just introduces the join syntax...

I'd like a good book that goes over queries common in reporting, you know a 3 page sql query that joins to sub queries that themselves are joining to sub queries. You know, the good stuff!

I'm almost finished with this SQL book. I've been working with databases at work lately and this has helped me avoid some pitfalls in the data world.

SQL Antipatterns

http://www.r-5.org/files/books/computers/languages/sql/style...

If you are looking for something advanced, you should check books written by C. J. Date. For example, his "An Introduction to Database Systems" considered as one of the best book on database theory.

Theory.

However he insists on living in his relational purist fantasy land without nulls or outer joins or other pragmatic things that people who use databases to get real work done rely on every day.

I remember "SQL for Smarties" being helpful. Looks as though it is still getting updated.

[0]https://www.safaribooksonline.com/library/view/joe-celkos-sq...

Joe Celko's Puzzles and Answers is a nice complement to the Smarties book.

Neither of them cover analytic functions, though. And they can really make a difference (for performance and succinctness). So it's worth it to look into them after going through these books.

GalaXQL http://sol.gfxile.net/g3/ is a decent, but very basic, introduction slightly held back by its reliance on sqlite.

I enjoyed "The Art of SQL". It's purposefully about higher-level strategies for relational DB management and administration, performance, tips in schema setup, SQL features to use/avoid...

Feel free to browse/download/peruse the many PDF slides of my course "Advanced SQL" (in which joins are considered basic).

https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.h...

E. F. Codd - The Relational Model for Database Management - Version 2.

Did you try SQL Cookbook?

I think that was the one I used to enjoy.

Lots of receipts. The first are quite simple but the later, more advanced ones should take you way beyond simple joins and would also have variations for the most common databases.

From the table of contents:

...

    Chapter 5 Metadata Queries
        Listing Tables in a Schema
        Listing a Table's Columns
        Listing Indexed Columns for a Table
        Listing Constraints on a Table
        Listing Foreign Keys Without Corresponding Indexes
        Using SQL to Generate SQL
        Describing the Data Dictionary Views in an Oracle Database
    Chapter 6 Working with Strings
        Walking a String
        Embedding Quotes Within String Literals
        Counting the Occurrences of a Character in a String
        Removing Unwanted Characters from a String
        Separating Numeric and Character Data
        Determining Whether a String Is Alphanumeric
        Extracting Initials from a Name
        Ordering by Parts of a String
        Ordering by a Number in a String
        Creating a Delimited List from Table Rows
        Converting Delimited Data into a Multi-Valued IN-List
        Alphabetizing a String
        Identifying Strings That Can Be Treated as Numbers
        Extracting the nth Delimited Substring
        Parsing an IP Address
    Chapter 7 Working with Numbers
        Computing an Average
        Finding the Min/Max Value in a Column
        Summing the Values in a Column
        Counting Rows in a Table
        Counting Values in a Column
        Generating a Running Total
        Generating a Running Product
        Calculating a Running Difference
        Calculating a Mode
        Calculating a Median
        Determining the Percentage of a Total
        Aggregating Nullable Columns
        Computing Averages Without High and Low Values
        Converting Alphanumeric Strings into Numbers
        Changing Values in a Running Total
    Chapter 8 Date Arithmetic
        Adding and Subtracting Days, Months, and Years
        Determining the Number of Days Between Two Dates
        Determining the Number of Business Days Between Two Dates
        Determining the Number of Months or Years Between Two Dates
        Determining the Number of Seconds, Minutes, or Hours Between Two Dates
        Counting the Occurrences of Weekdays in a Year
        Determining the Date Difference Between the Current Record and the Next Record
    Chapter 9 Date Manipulation
        Determining if a Year Is a Leap Year
        Determining the Number of Days in a Year
        Extracting Units of Time from a Date
        Determining the First and Last Day of a Month
        Determining All Dates for a Particular Weekday Throughout a Year
        Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
        Creating a Calendar
        Listing Quarter Start and End Dates for the Year
        Determining Quarter Start and End Dates for a Given Quarter
        Filling in Missing Dates
        Searching on Specific Units of Time
        Comparing Records Using Specific Parts of a Date
        Identifying Overlapping Date Ranges
    Chapter 10 Working with Ranges
        Locating a Range of Consecutive Values
        Finding Differences Between Rows in the Same Group or Partition
        Locating the Beginning and End of a Range of Consecutive Values
        Filling in Missing Values in a Range of Values
        Generating Consecutive Numeric Values
    Chapter 11 Advanced Searching
        Paginating Through a Result Set
        Skipping n Rows from a Table
        Incorporating OR Logic when Using Outer Joins
        Determining Which Rows Are Reciprocals
        Selecting the Top n Records
        Finding Records with the Highest and Lowest Values
        Investigating Future Rows
        Shifting Row Values
        Ranking Results
        Suppressing Duplicates
        Finding Knight Values
        Generating Simple Forecasts
    Chapter 12 Reporting and Warehousing
        Pivoting a Result Set into One Row
        Pivoting a Result Set into Multiple Rows
        Reverse Pivoting a Result Set
        Reverse Pivoting a Result Set into One Column
        Suppressing Repeating Values from a Result Set
        Pivoting a Result Set to Facilitate Inter-Row Calculations
        Creating Buckets of Data, of a Fixed Size
        Creating a Predefined Number of Buckets
        Creating Horizontal Histograms
        Creating Vertical Histograms
        Returning Non-GROUP BY Columns
        Calculating Simple Subtotals
        Calculating Subtotals for All Possible Expression Combinations
        Identifying Rows That Are Not Subtotals
        Using Case Expressions to Flag Rows
        Creating a Sparse Matrix
        Grouping Rows by Units of Time
        Performing Aggregations over Different Groups/Partitions Simultaneously
        Performing Aggregations over a Moving Range of Values
        Pivoting a Result Set with Subtotals
    Chapter 13 Hierarchical Queries
        Expressing a Parent-Child Relationship
        Expressing a Child-Parent-Grandparent Relationship
        Creating a Hierarchical View of a Table
        Finding All Child Rows for a Given Parent Row
        Determining Which Rows Are Leaf, Branch, or Root Nodes
    Chapter 14 Odds 'n' Ends
        Creating Cross-Tab Reports Using SQL Server's PIVOT Operator
        Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator
        Transposing a Result Set Using Oracle's MODEL Clause
        Extracting Elements of a String from Unfixed Locations
        Finding the Number of Days in a Year (an Alternate Solution for Oracle)
        Searching for Mixed Alphanumeric Strings
        Converting Whole Numbers to Binary Using Oracle
        Pivoting a Ranked Result Set
        Adding a Column Header into a Double Pivoted Result Set
        Converting a Scalar Subquery to a Composite Subquery in Oracle
        Parsing Serialized Data into Rows
        Calculating Percent Relative to Total
        Creating CSV Output from Oracle
        Finding Text Not Matching a Pattern (Oracle)
        Transforming Data with an Inline View
        Testing for Existence of a Value Within a Group

    Appendix A Window Function Refresher
        Grouping
        Windowing
    Appendix B Rozenshtein Revisited
        Rozenshtein's Example Tables
        Answering Questions Involving Negation
        Answering Questions Involving "at Most"
        Answering Questions Involving "at Least"
        Answering Questions Involving "Exactly"
        Answering Questions Involving "Any" or "All"

I recently bought the author’s book to brush some cobwebs off parts of my SQLing. Over the years I’ve seen Markus’s blog posts and guides posted here on HN. I’m always impressed when I see an individual focus on a topic and build an identity / business like this. Thanks for all the blog posts, Markus!

SQL is by no means perfect. For one, t̶h̶e̶r̶e̶'̶s̶ ̶n̶o̶ ̶o̶f̶f̶i̶c̶i̶a̶l̶ ̶s̶p̶e̶c̶i̶f̶i̶c̶a̶t̶i̶o̶n̶: some dialects are meaningfully different than others, and even similar ones are often full of implementation details about the underlying database. It has a ton of quirks, and isn't as powerful as I'd always want it to be. And sometimes it can be really hard to read.

But I still haven't found a better universal "language" to talk about extracting and manipulating data. The core set of functionality of SQL (selects, joins, aggregations) is usually adequate to express powerful ideas. Most importantly, a lot of people who work with data know it, and I've found semi-technical people (including non-programmers) can learn it quickly. Anyone who has been paying attention to trends in the data world in the last 5 years knows SQL is here to stay for the long-term.

> For one, there's no official specification

I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685

> and isn't as powerful as I'd always want it to be

Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.

>I'd take ISO/IEC 9075 as an official specification

Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift.

>Do you have examples?

For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.

Ah, "dozen of extra lines" is not exactly what I was expecting for "isn't as powerful" ;)

Dozen of extra lines most probably means it's also suboptimally executed.

Declarative languages are almost always more verbose than imperative languages. Not sure why you'd use LOC to compare across them when they have readily available execution engine planners with stats and everything.

I don't know...optimizers are pretty good at rewriting and planning nowadays.

There are things I can do in SQL in one line that would take dozens in python. I don't see your point.

By that logic, is there anything at all in the realm of APIs and tools that is standardized then? HTML mark up? Regular expressions?

> > > For one, there's no official specification.

> > I'd take ISO/IEC 9075 as an official specification

> Fair enough, but I'd argue it's not a de facto standard...

It may be useful to understand the distinction between official (de jure) and common usage (de facto).

200 CHF for a programming language specification? Is it 1995?

As for myself, I wish SQL's SELECT were more expressive. It's currently organized almost exactly like a mirror of the underlying relational algebra operators that a query engine has to execute internally.

But relational operators isn't what developers like to work with. SQL is about "projecting" relations, putting them through some basic transforms, none of which compose that well because of SQL's statement-oriented syntax. For example, you can't group a group-by. A group-by takes a list of terms, but you can't funnel the output through another one without wrapping the whole query in a "SELECT FROM (...) AS ...".

I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.

kdbs q-sql does improve a lot on that. Especially expressiveness.

https://code.kx.com/q/ref/qsql/ http://www.timestored.com/b/kdb-qsql-query-vs-sql/

> I'd like a query language that more expressive, expression-based syntax where the results of each expression can be "piped" into the next step to perform transformations, joins, reductions and so on.

I do not think this can be easily fixed in SQL because the problems are not in the syntax but rather lie deeper at the level of the underlying paradigm and principles.

Yeah, this is my main beef with SQL. The syntax is just clunky when you want to compose operations. Linq does this much better.

With-clauses (aka Common Table Expressions) improves this a lot, but it would be better still if you could just write:

FROM <table> WHERE <filter> SELECT <projection> GROUP BY <groupings> WHERE <filter> SELECT <projection> and so on.

SQL uses nesting as you have shown instead of a "pipe" (think of it like functional programming).

The difference is just the syntax, not the expressiveness.

Well, I am explicitly talking about syntax.

I often use temporary tables to hold intermediate results. Does that not work for you?

Finally, an open source SQL database with history for data builtin: https://mariadb.com/kb/en/library/system-versioned-tables/

Always wondered why this feature has been ignored for so long.

Thanks for pointing that out! They appear to only support one of the two time models proposed by the research community. They support transaction time (when you write it), but not the interesting and useful 'valid time'. The difference can be explained as you are going to be paid a salary for working say from July 1, 2018 to June 30, 2019 (this is valid time), but we write the information into the db at a different time, say we write it on June 15 (this is transaction time), then we realize a mistake and update your salary record on June 20th. But your employment starts July 1.

Just FYI: The SQL standard supports both types. The other one (valid time) is called "application versioning" in SQL.

Read this paper if you'd like to learn more about it: http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeat...

Cool I hadn't heard about that.

Hum, you don't have the syntaxic sugar but I'm pretty sure you can do bitemporal tables for sometimes in PostgreSQL.

No features come for free. How do you justify the universal value of this feature?

It would be interesting to see a "new and noteworthy" section in these types of news recaps. There's always a lot of hype around new databases, and it'd be nice to see an unbiased perspective from someone with such deep knowledge of database systems, similar to Aphyr's deep dives into consistency and durability.

This seems focused on only SQL systems, and only the largest ones, which is okay, but I think there's a lot more to the story than that :)

If you are into global consistency but also want NoSQL flexibility, my employer FaunaDB has ACID transactions, joins, indexes, and object level access control.

I'm sure you guys are doing good business, but the only way we'd use FaunaDB is if it's open-sourced.

I suspect you'll have a hard time competing against Google Spanner (or even Google Cloud Datastore).

From a developer’s point of view, I absolutely hate SQL. Converting my data from and to row format is a pain. Transactions don’t really tie well with the rest of my application logic. And of course embedding multiline SQL statements in my code looks like monstrosity.

Why do you put SQL statements in the “core”? Just put them behind and interface and call it a day.

SQL and relational data is the only option, unless someone comes up with an equally powerful alternative with similar theoretical underpinnings.

Sorry, it was a autocorrect shenanigans. s/core/code

Is this the final nail in the coffin for the NoSQL hype?

Although I'm not sure the road back to sanity is to add SQL interfaces to non-relational database systems. SQL and the relational model are often conflated, both in the NoSQL hype and apparently also in the backlash. I like things like LINQ in .net which allows you to work with a relational model and use relational algebra without having to use the SQL language

I am personally playing a bit with Apache Calcite - SQL without the data storage - and it sounds very interesting. For most reporting jobs, a couple of SQL queries are all that s needed and are easy to read and easy to write, even if you are actually querying some in-memory structures.

"According to the “shoot the messenger” principle, PostgreSQL has been heavily criticized for fsyncgate. Indeed, PostgreSQL suffers from this problem more than other databases as it doesn't offer direct IO."

Not a problem on any illumos based operating system, as fsync(2) and ZFS do what they're supposed to do and do not lie about data being on stable storage when it isn't. PostgreSQL is a good database, and if you want it to be bullet proof, run it on an illumos based operating system.

This comment is currently fading into downvotitude. Can someone explain why? Is it factually incorrect? Are people upset at the implication that other systems do lie about data being on stable storage when it isn’t?

I haven’t spent much time with Illumos, but if Postgres is not impacted on Illumos, then why the downvotes?

Ultimately the Operating System doesn't (and can't) know whether the storage is actually going to retain data you've written to it.

Even if your OS goes out of its way to use all six kinds of double secret synchronization primitive, the cheap eBay clone product didn't bother implementing any of them and replies "Yup, definitely 100% synchronised" while doing nothing, then the user is disappointed because your OS promised that the data was safe and it isn't.

This isn't really just about hard disks either.

Over the years lots of manufacturers have sold "smart" network cards that corrupt data if they're left to their own devices to "speed up" networking by cutting corners, miscalculating checksums, copying things wrongly from RAM, and so on. But it's even possible to build a card where the most trivial scenario - where the OS lays out the entire packet and then explicitly does IO operations to write it to the card - still sometimes randomly corrupts data. Your OS can promise a "guarantee" that the network data is exactly what you sent... and then you find 0.1% of packets are corrupted before they even reach the switch, oops.

Ultimately all bets are off when running on real hardware. Many years ago a friend of mine had a PC where randomly sometimes basic Unix features like "cat" would segfault or misbehave. Did disk checks, re-installed the entire OS, loads of hair-pulling. Eventually he discovered that the CPU fan failed, and overheating had damaged the on-die L2 cache...

To be fair, no OS has control over the hardware, only how it interacts with it. The cheap eBay clone and random PC fan failure are vanishingly rare for a datacenter database environment.

Still, the claim that merely selecting a an OS that may not suffer from this one particular issue results in a "bulletproof" setup is rather extraordinary. There may be other issues, some as a result of that OS choice (even if hardware issues are not).

Additionally, the choice of a much less popular OS can, in and of itself, be a significant "bullet"-susceptibility factor, not just in terms of general supportability but, getting back to your point about hardware, how well one might expect it to behave with what's out there on the market.

“Ultimately the Operating System doesn't (and can't) know whether the storage is actually going to retain data you've written to it.”

illumos and ZFS do know about it, especially since ZFS will checksum every block and will perform an explicit flush for every write. Should the drive lie about it, the checksum won’t match.

To make the claims you made just because what I wrote doesn’t involve Linux is preposterous. Sun engineers spent 25 years making the OS robust and able to correctly cope with failing, flaky or otherwise unreliable hardware. Ever heard of fault management architecuture in Solaris? If you had, you wouldn’t have written what you wrote.

"illumos and ZFS do know about it"

They literally can't, as I explained previously. Everything seems fine with such hardware, up until you find that some of the data you were absolutely sure you'd written isn't there after all. The fact you can't conceive of how this goes wrong is actually _illustrative_ of how Sun got screwed. The foolish man certainly does build his house on sand.

Now, twenty five years ago when those Sun engineers apparently started making the OS robust (maybe this explains how it got the reputation for being a flaky piece of crap, they should have started sooner...) I was working for a company that I'm quite _sure_ a person who says they really care about robust systems has heard of. Tandem Computers.

Tandem had a demo that gets to the heart of the thing you think you really care about in Illumos. Do a transaction, it's committed, and then we... pull out the CPU cards from the computer. It's dead.

... and our transaction is safe and indeed other users can continue to use the system, with the committed transaction, because it was replicated to other nodes far away and those weren't destroyed.

Tandem Computers doesn't exist any more because it turns out that doing this correctly is expensive and nobody wants to pay extra for correctness. After all, Sun engineers said their OS is "able to cope with failing, flaky or otherwise unreliable hardware" and with the lower price tag a customer really wants to believe that's true, shame about their data when it isn't...

“The fact you can't conceive of how this goes wrong is actually _illustrative_ of how Sun got screwed.“

I can very well concieve of it, as I’ve been preaching this to the Linux crowd for decades and they just don’t get it. By the way I know about Tandem because yes, I’m that old.

Please, before you go preaching to the choir (me), read how ZFS works; if the drive totally completely lies about everything, if it silently corrupts data, first read from the drive will uncover it because the checksums won’t match. ZFS will still deliver good data if it has redundant copies and will attempt to overwrite the corrupt data automatically.

50% of the two memory chips in my Solaris storage server no longer function correctly; Solaris has automatically migrated the affected pages out of them. When I get new memory chips, I will off-line them and replace them. Tandem might have been even more robust, but its engineers didn’t continue working on it after the company’s demise like those Sun engineers did on illumos.

https://blogs.oracle.com/bonwick/zfs-end-to-end-data-integri...

working with the said technology and understanding it goes a long way.

I would dispute the claim that PostgreSQL has been so heavily criticized. Instead, the project was invited to send a representative to the Linux Storage, Filesystem, and Memory-Management Summit (https://lwn.net/Articles/lsfmm2018/) to discuss solutions with the kernel development community; some of the resulting kernel changes are finding their way toward the mainline now.

Indeed (I was the PG guy at LSF/MM). I didn't feel ill-treated at all. Unless you count attempted poisoning via adult beverages, that is.

What is there to discuss? 20 years into development and there is still no working fsync(2) which does what fsync(2) in a real UNIX has been reliably doing since 1985. One cannot build a robust infrastructure on an operating system where core functionality isn’t working reliably.

Maybe the NOSQL hype is dying down, but it still seems like for caching for fast reads it can be helpful. What do you think?

NoSQL is a catch all term. There is a place for key/value, document, graph, etc databases.

People got all excited cause they discovered that these exist. They've been around for a long time.

Relational DBs are a good default, but there are plenty of cases in which you'll use another type of DB.

Engineers need to stop trying to find a silver bullet.

They also exist as features within an (otherwise) relational DBMS. Perhaps the trouble is that it's mostly been in the realm of the commercial ones and only relatively recently has Postgres started adopting enough of them to make the notion popular.

On the database subject, I'm working on a new project called DBacked, basically a simple and encrypted database backups as a service. I'm not ready yet for a Show HN (more things to improve on the presentation website) but happy to get your advices ;)

easier if you show us the code

"NoSQL" took the idea of a rejection of syntax (SQL) but also, in practice, burned in with it a rejection of vast swathes of database theory that resulted in the modern RDBMSes. It was a meme that was generally easy to ignore and identify the "bad ideas" within if you understood the history of databases. SQL obviously is just an incidental syntactic detail on top of a fairly sound, generalized model for databases -- relational algebra combined with a clear semantic model for transactions (ACID, etc.)

These two things I believe will stand the test of time. Everything else, I think, is ultimately incidental complexity to meet operational goals (like indexing, or caching methods, or sharding, or replication, or denormalization) or conceptually useful models and interfaces (like graphs, or literate APIs, or query langauges, or ORMs, etc) on top of these basic facilities that need to be provided by a database system.

The trend of NoSQL databases I think can be summarized as people building systems that tilted towards letting these bits of incidental complexity dictate the design of the system as a whole. The overriding concern seemed to be "scalability and performance" with "easy APIs" a close second and so that resulted in specific data structure and API needs or access patterns dictating everything about how the database system itself worked, and how data was modeled in that system.

For example, MongoDB was hearalded as finally letting you access a hierarchical data structure without SQL JOINs, but did so not by just providing a nice abstraction on top of a sound relational schema that allowed you to "re-project" your thinking and access patterns to a document-oriented one when it was the right model contextually, but instead by burning that denormalized, document-oriented data structure into the entire conceptual and technical stack for the whole system!

The cost, of course, is self-evident since choosing to reject the relational model also chooses to reject the benefits that were explicitly recognized as a reason for the relational model to be a good choice for database systems: normalized data allows a much more rich set of transformations and projections on that data to be unambiguously expressed and fulfilled by the system. In other words, it's much more future proofed since you now have the flexibility to mix-and-match and analyze it in arbitrary ways, decoupled from your choice of schema. So the effect of choosing MongoDB meant that you had minimal future-proofing if you got your document structure wrong and suddenly needed to re-project your data in a new way. The supported path, of course, was to "just run map reduce on it" -- ie, lets force every application developer to do the job the database is supposed to do :(

It's nice to see things are moving forward on this again, hopefully the trend doesn't just reverse now and we have a flood of "SQL access" APIs to poorly grounded database systems.

> The marketing term NoSQL, which was the hippest buzzword just a few years back, is slowly becoming a synonym for a defect. Without SQL, there is something missing.

I guess RDBMS companies are too afraid of NoSQL to attack it with such silly propagandist statements. You can't win the market this way.

To me it increasingly looks like SQL is losing in distributed systems. And NoSQL may never really gain SQL beyond niche usage after all.

To me it increasingly looks like SQL is losing in distributed systems.

I’m seeing the reverse trend. Google went from nosql to sql with cloud spanner. The various flavors of SQL on hadoop are on the uptick. Cockroachdb is looking really interesting.

But do you see Spanner, CockroachDB, TiDB actually getting any ground?

Whew. Those years of enduring "SQL is dead" posts were not easy. Now if we can just rebrand "cloud" to "someone else's computer", much will be right with the world.

The conflation of SQL with consistency in this blog post is dumb. Yes Google offers SQL on Spanner but the query execution is built atop lower level Spanner primitives that are also available to developers and are similar to Bigtable concepts. Also Bigtable is still much, much larger and more important within Google than is Spanner/F1, so the death of Bigtable is much overhyped.

Point is there are strong and weak consistency models with and without SQL execution engines, and they’re all useful.

The SQL standard specifies explicit consistency semantics for statements and transactions, though. So something claiming to adhere to e.g. SQL:2011, is claiming that executing certain commands will have certain consistency properties (and if you've set up your DBMS to have weaker consistency, then it should be blaring a loud alarm that you've effectively put it in a non-SQL-standard-conformant mode.)

Other generic DBMS query protocols could specify their own consistency semantics, but I'm not aware of any that do. (I guess, if you consider REST a "DBMS query protocol", then CouchDB is an example of a DBMS "conforming" to the consistency semantics specified in the REST protocol.)

If that's your definition then it's even more dumb to hold up Google Cloud Spanner as an example of how SQL gives you consistency. There's NO WAY to mutate Spanner tables via SQL. All writes are performed via the lower-level RPC interfaces. The only thing you can do with Spanner from SQL is query it. The consistency provided by Spanner is clearly not provided by SQL.

Even if a DBMS only exposes read-only SQL support, that still has consistency-model implications—namely that, if you can execute several SQL statements in a transaction with a long delay between those statements, then all the statements in the transaction should execute against the same MVCC "version" of the affected tables.

So a write on that lower-level RPC interface shouldn't, by the SQL spec, be able to cause a sequence of SQL wire messages like this:

    BEGIN

    SELECT COUNT(*) FROM foo;

    -- 100s later
    SELECT COUNT(*) FROM foo;
    COMMIT;

...to return separate numbers for those two SELECTs.

If it could, under Spanner, then Spanner wouldn't be conformant to [the read-only subset] of whatever SQL standard it claims to obey. But, according to the Cloud Spanner docs (https://cloud.google.com/spanner/docs/transactions#read-only...), the above code will always return the same values for the two queries.

And, if you're feeling argumentative, you could interpret the reason for the particular consistency semantics, as the fact that Cloud Spanner claims to support SQL, and SQL mandates that consistency. You could suppose that, without that claim, Cloud Spanner would never have bothered building a transactional MVCC layer into their architecture.

Unfortunately, no. Spanner may garbage collect the snapshot timestamp to which your example query was bound, if the garbage collection policy was triggered by additional writes at greater timestamps, or by the simple passage of time. The result of the second query may be a permanent error indicating that your data has disappeared.

Spanner had a transactional MVCC system in their original implementation described in the 2012 paper which predates SQL support described in the 2017 paper. Therefore it is probably wrong to credit SQL with Spanner's MVCC system.

> The result of the second query may be a permanent error indicating that your data has disappeared.

That's actually okay, per SQL. Any SQL transaction may fail, for any reason, at the server's discretion. Because the user's permission to operate on the table was revoked in the middle of their transaction, for example. Or because the server is going down and wants to drop connections.

SQL clients have to be written to assume that any transaction could enter a failed state "before" the execution of a statement, even if the previous statement returned a success.

Helpfully, though, they know that 1. by the SQL standard, that failure means that no part of the transaction applied; and 2. that this failure-state is a failure of the transaction context to be valid, rather than a failure of their statement to be valid. (They don't know that their statement is valid, but they know that they haven't been told that it isn't.)

I also will never understand why people have so much trouble decoupling SQL, the query language, from classic RDBMS systems. Being able to use a SQL-like dialect in systems like Hive, Spark SQL, and now even Elasticsearch makes those systems infinitely more useful.

Now if only I didn't have to keep looking up the pandas "Comparison with SQL" page every time I have to do some manipulations to a data frame...

Also some services really get held back by their lack of SQL support. We use splunk and the lack of SQL expression comprehension hurts (especially with their lack of `IN`)

It also conflated Elasticsearches new native SQL feature too, which is also query only and sits atop a non-relational engine.

Doesn't change how much I love Elastic.

Really it should be rebranded "shared hosting".

Novel. I like it.

As much as I hate NoSQL (which is considerable), I think it was destined to happen.

Sometimes the incumbent needs a kick in the ass. It feels like the pace of improvement in boring old databases has had an uptick for the last five or so years. That feeling of slowness is probably what opened the door in the first place. If you put enough beers in me I would have admitted as much at the time. Database progress got boring.

But the other thing is that people forget the past when it's been too long since the Dark Times. I have my fingers crossed, and perhaps I will regret that naivete some day, but I have my fingers crossed anyway that there's a whole generation of young developers that will know, viscerally, why we use Relational Databases. Why concurrent access to data is hard and we should leave it to the professionals instead of rolling our own every time we're feeling bored at work. The same way we generally let other people do billing, or data viz.

[Edit to add]

One of the biggest things I'm champing at the bit for an opportunity to use is the newer event streaming stuff that builds on exporting the WAL data from ACID databases. My OLTP data set gets to be the system of record as God and Man intended, but there's a perfectly sane mechanism to support the occasionally very valid OLAP-oriented business concerns. Any time a tool can take away something that makes my life difficult or unpredictable is a good tool.

I just haven't been at the right sized company that it's an unsolved problem that we need to solve, and I have higher priorities for my own stuff.

Slowness of progress in traditional databases has absolutely nothing to do with it. Primary reason NoSQL even exists is inability of traditional databases to run in distributed environments with decent availability, scalability, latency/performance and low maintenance requirements. It was never about SQL the language or relational model (which, by the way, is not inherently better than other models).

Nothing has changed since. As there exist fundamental limitations preventing all those traditional databases from ever satisfying requirements. And new distributed relational databases designed and built from the ground up so far don't seem to be able to meet neither SQL crowd's expectations nor win people who are facing actual distributed problems. It's like progress in databases doesn't favor familiarity over other things at all, effectively resetting familiarity with relational model altogether and forcing even models to compete with each other for fresh minds.

Web. Scale.

How is realistic this article without a word about FoundationDB?

Reply on news.ycombinator.com