Hacker News Reader

Comment by tomnipotent | Hacker News Reader

> Every decade it bolts on support for the new technology craze

You mean they got better? I've been using Postgres, MySQL, and SQL Server professionally for over 20 years (Postgres for only 15), but they've only become better & faster over time. I don't see this "technology craze" you mention, but I have seen things like parallel processing, bitmap indexes, selective indexes, query plans that use multiple indexes, online index creation (no locking), support for complex data types like JSON or XML, partitioning, vastly improved resource usage (CPU+memory+disk) just to name a few.

This doesn't even include some of the cool vendor-specific features like foreign data wrappers (FDW) in Postgres, embedded language runtime support (SQL Server can do .NET, Python, R) and so on.

> so vendors add their own features

Of course they do, RDBMS' are products.

View on HN

> I don't see this "technology craze" you mention, but I have seen things like parallel processing, bitmap indexes, selective indexes, query plans that use multiple indexes, online index creation (no locking), support for complex data types like JSON or XML, partitioning, vastly improved resource usage (CPU+memory+disk) just to name a few.

Those all seem like pretty ad-hoc features, some of which were influenced by prevailing tech fashion trends at the time. For instance, the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing which can be integrated into query plans. There are a lot of flaws like this in SQL stores as a whole.

How are they 'ad-hoc'? With the exceptions of JSON and XML support (cf. the 'NoSQL' fad), they're all valuable enhancements to core functionalities, no?

> the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing which can be integrated into query plans

I'm no SQL guru, but this smacks of a variant on the 'sufficiently smart compiler fallacy' to me.

That the system can't always, uh, optimally optimise, isn't necessarily an indication that the system is fundamentally flawed.

GCC permits inline assembly, but that doesn't mean GCC is a failure.

If GCC required inline assembly everywhere, it would definitely be a failure.

Of course.

Given that this feature was added to the DBMS pretty late in the game, we can infer that it's only rarely worthwhile.

See my other comment [1] if you want further details as to SQL's limitations.

[1] https://news.ycombinator.com/item?id=18147388

A lot of SQL databases can definitely adhoc determine if indices are necessary; it's just not the default because predictable performance and explicit indices tend to be preferred.

Postgresql has a handful of plugins that can tell you if you had an indice; would it be used and you can see query plans before/after without actually having to add indices until you're comfortable. This same plugin could easily automate indice creation, but most people don't.

> For instance, the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing

Real life isn't about theory, it's about what works and doesn't work in production. These are not flaws, they're features. I don't need a bitmap index on my primary key, nor does it need to be selective because it will include every row. But having these features and the ability to customize based on the needs of my product is paramount.

If you were not using an RDBMS to store your data, you'd still have these same problems and you'd still end up with optimized secondary indexes based on the lookup criteria.

> You mean they got better?

Well, I mean they support new features, which is perhaps "better", though when you continue to tack on features for decades, perhaps something was wrong with the relational algebra model to begin with. Also, you're conflating "SQL" with advancements in individual implementations of a relational database. Relational databases are great; I just think we'll find a model that is more suitable for application development than "relational algebra + the kitchen sink".

Good point, I was conflating SQL & RDBMS.

On the contrary, a model that is solid enough that you can continue to improve and build features on it for decades without changing the fundamentals is an extremely rare triumph in this industry. The signal that “something is wrong” is that you have to reinvent the foundation every five years (e.g., the tottering stack of false starts inside all web browsers).

sql has stood the test of time, but we've also figured out how to extend the sql dbs and relational model so that it works efficiently in a lot more scenarios. there are column stores that do incredible compression, in-memory row stores, you can put json in a sql database and efficiently query it.

Features aren’t being “built on it”, they’re being bolted on because the model is insufficiency powerful, which is my point. To be clear, relational databases are great and important, but the relational model will have to be replaced.

There are two issues I know of with SQL where it isn't super great IMHO.

1) Hierarchical data. The relational model is fine here, but the query syntax isn't awesome.

2) Understanding remote data. This is outside the scope of the data model, but it does effect the way software is built.

Neither of these show any evidence that the relational model should be replaced.

> There are two issues I know of with SQL where it isn't super great IMHO.

There are many more issues, you've just gotten used to eating SQL turds. Hierarchical data is solved by CTEs, so that's not a big deal.

The bigger deal is that relations themselves are impoverished second-class citizens. This means you can't write a query, bind it to a variable and then reuse that query to build another query or store that query in a table column. Something like:

    var firstQuery = select * from Foo where ...
    var compositeQuery = select * from firstQuery where ...

    create table StoredQuery(id int not null primary key, query relation)
    insert into StoredQuery values (0, firstQuery)

That's something like relations as first-class values. This replaces at least 3 distinct concepts in SQL: views, CTEs, and temporary tables, all of which were added to address SQL's expressiveness limitations. Relations as first-class values not only improve the expressiveness of SQL beyond those 3 constructs, it would also solve many annoying domain problems that require a lot of boilerplate at the moment.

Then there's pervasive NULL, inconsistent function and value semantics across implementations, and a few other issues.

The schema aspects of this idea are hard for me to grasp.

How would the database engine efficiently resolve any references to the embedded relation ?

I'm also having trouble understanding how your INSERT statement would even work - is the firstQuery being inserted being treated as hierarchical data where every single relation attached to every single row has its own schema/structure ? Or is the structure fixed in the CREATE TABLE statement so that you can't use just any relation, but rather have to use a relation that conforms to the structure defined in the CREATE TABLE statement ?

I played a little fast and loose to convey the basic idea. To elaborate since you asked, introduce typed tuples/records as first-class entities, then "tables" are just a sequence of records declared with a particular lifetime.

Relations are functions over these sequences. The embedded relation is then a stored function over a set of nested sequences. So to add the concrete types:

    var firstQuery = select Id, Name, Payment from Foo where ...
    var compositeQuery = select Id, Name, Total from firstQuery where ...

    create table StoredQuery(id int not null primary key, query relation{Id int, Name Text, Payment decimal})
    insert into StoredQuery values (0, firstQuery)

Again eliding a few details, but hopefully you get the basic idea.

This particular use case sounds like a MATERIALIZED VIEW, and views in general for doing what you're asking for...

A materialized view would be an optimization technique used when compiling a schema containing first-class relations (essentially, memoization). First-class relations are more general though.

But first-class relations would also present some optimization challenges, so a subset of a relational system with a restricted form of first-class relations corresponding to materialized views that can be stored in table columns and used in queries would get pretty close.

What you're asking for is an independently evolving logical schema on top of the physical schema - this is a great idea, but performance guarantees are highly tied to the physical layout of the data.

Apache Spark is the best example I've seen of this in that you can compose schemas on the fly and it will unroll them at the end based on physical layout.

I think what you're describing is already present as of SQL 2003 in the form of multisets:

http://farrago.sourceforge.net/design/CollectionTypes.html

However, good luck finding a database engine that necessarily supports such features. Some support array types, but I haven't seen too many that support multisets.

I like the first-class-relations idea.

As for pervasive NULLs, isn't that more the fault of schema design?

There are a lot of things wrong with SQL. Little things like the fact that INSERT and UPDATE have different syntax for no good reason.

A few of its syntax quirks annoy me as they're nonstandard in today's languages, but it's only a shallow complaint: using '<>' for its inequality operator, and using single-quotes for strings.

I'm also disappointed in the implementations in various ways: the way Microsoft SQL Server sends query text over the wire unencrypted, in its default configuration. The way Firebird SQL has such a basic wire protocol that you can see significant performance enhancements by invoking TRIM on text-type fields. The way the optimisers are so damn primitive, especially compared to the baffling wizardry that goes on in today's (programming language) compilers.

Somewhat off topic further ranting:

But the core relational model makes good sense. I see little general value in the freeform graph-databases calling themselves 'NoSQL'. (Do we call functional programming languages "No-assignment"?)

Perhaps some of them can scale well, but can't SQL do that? Google Cloud Datastore, for instance - it can scale marvellously, but only because it imposes considerable constraints on its queries. Can't we do the same thing with an SQL subset?

> As for pervasive NULLs, isn't that more the fault of schema design?

I think NULL defaults are widely regarded as a bad thing by now. You should have to declare what's nullable, not declare what's not null.

> There are a lot of things wrong with SQL. Little things like the fact that INSERT and UPDATE have different syntax for no good reason.

Moreover, SELECT should be at the end not the beginning. Query comprehensions and LINQ did this right.

And yes, the implementation inconsistencies are seriously irritating as well.

> Google Cloud Datastore, for instance - it can scale marvellously, but only because it imposes considerable constraints on its queries. Can't we do the same thing with an SQL subset?

If you extend Map-Reduce with a Merge phase, then you can implement the relational algebra with joins [1]. That scales pretty well.

http://cs.brown.edu/courses/cs295-11/mapreducemerge.pdf

> Moreover, SELECT should be at the end not the beginning. Query comprehensions and LINQ did this right.

I'd one-up this and argue that all SQL statements should be in order of execution (within reason). Moving SELECT to the end is definitely a good start.

Mixing execution concerns and the language seems like a bad idea.

It's pretty much impossible to decide what an optimised query should look like on a different databases with different data.

Slow network? Fast disks? GPU optimised joins? No way to know what execution order should be, and that's a strength of the relational model.

Interesting paper, thanks.

What do you think of perhaps a logical (e.g. Prolog or Datadog) model for databases?

I’m not familiar with those. What do _you_ think about them? :)

They are what came to mind when you said perhaps the relational algebra was not sufficient as a theoretical model of databases.

Reply on news.ycombinator.com