The myth of “joins don't scale” | Hacker News Reader

The myth of “joins don't scale”

blog.dbi-services.com

Unfortunately, the author/article seems to completely miss the meaning of "joins don't scale".

Obviously indexed joins on a single database server scale just fine, that's the entire selling point of an RDMBS!

The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance. Joins scale across rows, they don't scale across servers.

Now a lot of people don't realize how insanely powerful single-server DB's can be. A lot of people that assume they need to architect for a multi-server DB don't realize they can get away with a hugely-provisioned SSD single-server indexed database with a backup, with performant queries.

But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins. And then whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.

Even then you probably get a few years of use out of that simple setup while you work on migrating to a horizontal solution. And by then more likely than hitting Twitter-scale, your company has gone bankrupt.

And while we're making pie-in-the-sky long-term plans, the amount of RAM you can shove into a single server chassis is growing faster than the world population. So you might as well use joins -- the people maintaining your code in the year 2999 will thank you!

reality is 99% of companies out there will be just fine with a single db server

all this nosql fad is simply because SQL is hard and newbies would just rather call it old and slow because they can't be bothered to learn it

SQL is easy. Data is hard.

SQL queries that can beat NoSQL are easy but you still have to think about schema. Which you can just not do in K/V(you just need the key) or NoSQL.

As such it makes starting really easy which is the allure. Many seem to hope they can avoid doing schemas and well architected data. Or push it in to the future. The rude awakening comes when you do need to run complex queries (and you do not have SQL or joins at all). Or when the people do not understand they have to take care about data integrity in app layer now.

At the end people going for NoSQL have a much harder to manage system without the tools (like SQL, schemas, constraints)

I think NoSQL is great for very specific purposes. Caches, keeping state, logging stuff. For many things it is misused and postgre with jsonb can do everything you need (faster, easier) without rude awakenings.

Your single server is a CA service.

If you want CP or AP, then you need a "fad" database.

single server sql databases are not "CA" it doesn't make sense to talk about cap theorem like this.

I don't mean to say some companies like Google don't need NoSQL. Obviously they do.

Also, no you don't. It's possible to have distributed RDBMS.

A single DB server is a SPOF. You normally need a hot standby if you care about availability. You can also replicate transactions to read-only copies to alleviate the read load.

What most companies can do well with is a single master DB which handles updates.

(Some companies can't, though; e.g. GrubHub switched to Cassandra because no monolithic RDBMS could handle the write load.)

How many op/s was that? Bytes/s? Just curious because I was involved in migrating a write-heavy service off of C* to SQL Server. Our writes have higher latency now, but using managed SQL is worth it. (much cheaper too)

I personally enjoy writing SQL as a novice programmer. The basics are easy. Getting to know all the intricacies of what executes how fast and such is HARD.

Also: Designing a system that is prepared for unknown unknowns is practically impossible. Initial design is not too bad, but the continuous adjustments kinda suck.

Cross-database JOINs should work if the database server is written to handle that - take Spanner or CockroachDB: they’re both capable of high-performance cross-database and cross-server joins.

I think the poor reputation that cross-server joins have comes from servers like MS SQL Server that do cross-server joins through ODBC which is obviously going to be terrible for performance (though I don’t know exactly how - or even if - SQL Server handles a highly-selective INNER JOIN differently compared to a join with lower specificity on the remote table).

That’s extremely dependent on how much data needed to be passed around. Network bandwidth is extremely limited. Loading an extra 10GB of data into RAM from an array of SSD‘s is no big deal, tossing 10GB of data to each of 50 servers needs to be a rare event.

If you value speed over storage, you can just replicate all the tables you need to join to every node, do a join on a subset on each node, then merge. It would improve latency quite a lot.

The problem arises when you can't fit an entire table on a single node, no matter how big your SAN is. But this would be really big data, few companies face this problem.

If you’re using a SAN, then surely every server node has at least read-only access to every table? The only thing they’d missing is in-memory indexes and cached execution-plans.

Within the same datacenter, quite likely. Across datacenters, I'm not so sure.

It’s not just a problem of dataset sizes. You now need to send every update to every DB server. For something like an open world MMO it’s easy to start hitting billions of updates per second. Databases have hard trade offs and you need to make compromises somewhere.

AP systems are where actual scale is. Cockroach is a CP system. Spanner is probably similar, hardware clocks or not.

I don’t see how CAP applies to distributed JOINs (provided the user is aware of it)

Consistency of the data joined. That's why Spanner has both hw clocks AND notion of time of update baked into its transaction system, because it allows to both distribute the data up front and give guarantees about it being in sync

> The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance.

You're correct. However, most applications databases are fine with billions of records on a single instance. It's a rare application that needs the level of scale you're talking about. In other words, a typical startup doesn't, at least not at first ....

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day

Worth noting that both Twitter and Facebook started with MySQL.

https://dzone.com/articles/facebooks-user-db-is-it-sql-or-no...

http://highscalability.com/scaling-twitter-making-twitter-10...

> Worth noting that both Twitter and Facebook started with MySQL.

And both are still using it as primary data store.

According to Stanford prof who launched ramcloud Facebook indeed uses MySQL. But MySQL by itself was hopelessly slow. That's why Facebook has over 4000 memcache servers (this as of 2009). By the time you add ram underlying disks he estimated 75pct of all Facebook data is cached in memory. That's why it works.

Instead of Stanford as source here is an example from the FB engineering blog about migrating from HBase to MySQl for messaged in order to reach performance: https://engineering.fb.com/core-data/migrating-messenger-sto...

And yes, they certainly cache data in multiple layers using different technologies but from all I as an outsider can see MySQL still seems to be their source of truth for most services. Meaning all writes go to MySQL and at least when going deep in history or doing searches (which typically can't be served from a cache) hit MySQL.

memcached + rdbms is really super cheap and effective. Properly architected it can be tolerant of all sorts of latency/failure/CAP issues, up to some limits of course.

there was a variant library that allowed you to replace memcached servers on the fly without worrying about the size of the cluster list or having to recache.

Define primary? Are you claiming every tweet lives in MySQL?

http://highscalability.com/blog/2011/12/19/how-twitter-store...

that's nearly a decade old

if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day

Neither twitter nor facebook started with anything like the architecture they use today, nor did they have to, nor could they ever have known they'd need it or even what they would need.

> if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins.

If your company end up being as large as Twitter or Facebook, you probably can raise enough money to overhaul the entire infrastructure to make it efficient in your custom scenario, which is much better than prematurely optimizing when you have 100 users.

Indeed; the initial Twitter implementation ran on Ruby on Rails, of all things. Several hundred copies. (Remember "fail whale"?)

Then they re-engineered it to scale.

If they started not with a simple RoR prototype but with a ton of scalable infrastructure, they'd launch maybe a couple years later, and burn much, much more money before the launch.

> they'd launch maybe a couple years later

If at all; probably obvious, but important to point out that in a startup environment this kind of mistake could just outright kill the company.

I work on an open source project (Sandstorm[1]) that was started by a now-defunct startup. There's a repo that's unmaintained at this point (It might still work?) containing a high-scalability backend that had been used to manage Sandstorm Inc's hosted offering. Apparently they never got to the scale where they couldn't have thrown one giant server at it and been fine, and it pulled a lot of resources away from other (ultimately more important) development tasks.

...and now I'm stuck developing against mongo for no good reason :P

[1]: https://sandstorm.io

> whether you pick an RDBMS or NoSQL solution is mostly a tooling issue.

Eh, when the time comes to scale I’d still very likely pick a RDBMS but hand my team copies of Eric Evans and ask them to design data schema and sharding scheme such that any given entity has a bounded context that doesn’t join across the network.

Probably then using a Merkle-like structure for shard selection.

However as you say there’s no need to start like that. A big fat server can do a lot. It may be cheaper just to run a multi-TB database server and do everything in memory, than invest in re-engineering and all the flow-on operational costs of a distributed store. For sure a DBA would have an easier time tuning it, even if NUMA is in the mix.

And building out a distributed store without restricting your ability to pivot requires more experience than almost anyone but the big clouds possess.

edit/addendum: when you take a bounded-context domain-specific approach to your data persistence you will probably will still need to join things, but the option arises to do so periodically rather than on-demand, and in batches i.e it has more of a reporting flavour than OLTP, and work can be managed for quiet times or offloaded to map-reduce spot instances or whatever you like. From the outside it looks like an eventually-consistent application but only with regards to the parts that don’t need to change instantly.

> Eh, when the time comes to scale I’d still very likely pick a RDBMS but hand my team copies of Eric Evans and ask them to design data schema and sharding scheme such that any given entity has a bounded context that doesn’t join across the network.

That will only help you scale out to N servers, where N is the number of bounded contexts. And it might end up being less helpful than it sounds, if the scaling needs are dominated by data in one or two bounded contexts.

Still, it could be a useful approach.

No, the dimension is in the other axis. Otherwise there’d be no need for sharding keys.

But if you have nice natural sharding keys for your data (the most common example is something like TenantId), how do bounded contexts help?

EDIT: Perhaps you mean something different by bounded context than what is usually meant in the context of DDD, where they separate groups of entities not data within those entities.

No, I mean exactly that, but tenant_id sounds like a SaaS or a Shopify. Their pods don’t have this kind of scale to begin with, the datasets just don’t have more than a few billion entities. Assuming there’s a “nice natural sharding key” at all already limits scope to a subset of applications, most of which you can stick inside a single instance, rendering the point moot.

> The meaning of "joins don't scale" is that they don't scale across database servers, when your dataset is too big to fit in a single database instance. Joins scale across rows, they don't scale across servers.

But people have been saying "joins don't scale" for a lot longer than there have been companies that have today's giant's horizontal infrastructure. It's just been a sort of hipster anti-hipster anti-correctness brag since at least 1996, when I first heard it.

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day

The only way you’ll know this is if you are already Google or Facebook

Best just always start with a RDBMS and then split out subsets if needed down the line

> Obviously indexed joins on a single database server scale just fine, that's the entire selling point of an RDMBS!

Is it really obvious, though?

In my experience, many people consistently fail to grasp this point. It seems likely that the underlying reason is that it's easy for most people to conflate nested loop join with hash join (or with merge join). Hash join really doesn't scale in the sense that the author conveys (though this in itself many not matter, because they're not really in competition in any practical sense).

When you point out that the key to good performance is to ensure that query results are fetched rather than searched and computed, it sounds obvious. It still seems very helpful to connect the dots.

> But if you're absolutely sure you need to be planning to run at close to Twitter or Facebook scale one day, then yep, you'd better architect from the beginning not to use joins.

No, both of them started with MySQL. If you make it, you have plenty of resources to throw at the scaling problem, it's a good problem to have. A bad problem to have is running out of money and shutting down because you over engineered from the start. Don't do that. Most startups fail, and you're no exception. Build one to throw away, and if you're lucky enough to succeed, you can use your new-found resources to rebuild what needs to be rebuilt.

It’s not a myth. In the trite examples provided with tiny datasets they scale. In stark reality when you’ve got 5000+ qps of dog shit queries of several joins each and buggered table statistics (thanks optimiser!) coming in on realistic datasets they are a damage amplifier.

Really all they do is repeat work in the most costly way possible that should have been done up front once. But that incurs the cost of knowing what you’re looking for before you do it.

If you want to avoid repeating work, you can set up a materialized view. But really, quite often a few tailored indexes are enough.

People do not realize just how far industry practitioners take joins :) So my take is that users will find a way to take joins to the point where they do not scale.

Usually they scale infinitely well until someone opens a PO for a Cray sized database cluster that burns kilowatts of power.

Let me just say that I have seen queries that are megabytes in size with joins all over the place :)

I’ve seen the same ones that do 3GB of reads to return ten rows. The point I was inelegantly making is that it is terrible doing this processing on every read rather than once at write.

Correct!

you either need a relational model or you don't. if you try to build a complex enterprise application with nosql all that's gonna happen is you end up reinventing an RDBMS

there are not many companies in the world that can afford to reinvent shit

I entirely disagree with the first point on the basis that most enterprise applications are CRUD with a poorly contrived search interface. Both of which are somewhat better serviced by anything other than SQL.

The second one however is on the mark.

> It’s not a myth. In the trite examples provided with tiny datasets they scale.

The point of the article is that in general nested loop joins based on index scans/iteration scale in the sense that the amount of I/O incurred grows at logarithmic intervals, at least once you assume a fixed amount of rows are returned. The amount of I/O incurred is practically fixed past a certain point. It doesn't matter how much data you have!

It may be that there are practical difficulties with ensuring that everything runs smoothly in the real world (e.g., availability of good statistics), but that doesn't make the author's point any less valid.

> It’s not a myth.

It depends really. My approach for the last 20+ years is first build a proper database and data model and go from there. It's akin to only when you know the rules, should you break the rules. In the late 90s the db I was using didn't have 'materialized views' so we built a system that ran nightly and generated them for highly used sets of data (effectively removing joins b/c they hadn't scaled). Some systems had a nice 3rd normal tx database for the main program, but was dumped into a star/snowflake schema for reporting. Others worked fine coming right out the tx system.

As computers have gotten faster, I find the original database can often go much farther. But, when it finally breaks down we now have so many more tools to help handle the situation. Admittedly, I've never dealt with data at FB or Google scale, but honestly that's a completely different set of constraints than what most of the world deals with.

This article only tested two pages, each with millions of data. In corporate developments (and probably the original meaning of "joins don't scale" in the era of Oracle/MSSQL), it's usually the opposite - usually joining half dozen of tables, each with a few data, but their mappings and underlying relationship are usually not explicitly-defined, making joins across multiple tables super slow (RDBMS can only plan the query based on fundamental stats). In other words, joins now scale with table size, but still a challenge in terms of #tables

> but their mappings and underlying relationship are usually not explicitly-defined

wouldn't this be an issue no matter what you're using?

I'm not sure what you mean by joins scale with table size? There are few times you'd join more than say 5 or 6 tables.

> There are few times you'd join more than say 5 or 6 tables.

This is only true in new tech where data relationship isn't complicated. Any stored procedure in bank would contain joins with no fewer than that number of tables.

Really? Twenty years as a database consultant and 5 or 6 tables would be a typical upper limit, you could perhaps get up to 10 or so if you include a lot of lookup tables for codes. I've never worked in banks though, so what would you be looking at there typically?

I work at a leasing company, and we somewhat frequently have around 5-7 joins when getting data such as where to send how much money for work done at another company.

The most I saw was "only" 10 though.

>> usually joining half dozen of tables, each with a few data, but their mappings and underlying relationship are usually not explicitly-defined, making joins across multiple tables super slow

Rather than "joins don't scale" it's more like "bad data design don't scale"

Every few years I get the urge to create a RDBMS API that only allows data design using functional dependencies and defined types and possibly rules, i.e. only lets devs do "logical design". Usually happens when I discover some project dependent on EAVIL or giant master table syndrome.

I've got a legion of cynical jokes about "emergent data design" and "Plato's Cave Shadows ERD", HHOS.

I get the impression that (crucially: outside of Facebook/Google-scale applications) the strongest advocates for No-SQL databases are great software devs who just had a poor experience with SQL databases as juniors or students - or didn’t develop have the necessary experience and skills to make the most of a SQL RDBMS because it was out of their field (e.g. game devs); they’d be missing things like not understanding relational-algebra or relational-calculus - or not even knowing SQL at all - or having the DBA skills to know how to define the best indexes - or even what a cardinality-estimate in an execution plan is. If you throw all of those topics at someone and also add that they only have a week to build the thing - it’s no-wonder that people turn to No-SQL.

I’m not putting those people down either - I would have opted for No-SQL myself if I wasn’t already familiar with RDBS theory and management. What I am criticising is the large amount of manual-management needed when you do run an RDBMS because this is putting people off. Instead, things like automatic index generation and pruning, nightly index rebuilds and statistics computing, etc arguably SHOULD be part of the RDBS out-of-the-box and enabled by default. To my knowledge only IaaS databases have features like that (it’s what justifies their subscription pricing). I believe some on-prem database server products will generate recommendations but they won’t automatically implement them on their own accord.

Another “approachability” issue I have is with the design of the SQL language: it’s unnecessarily verbose and very confusing for beginners as its basis in relational-calculus instead of relational-algebra throws people off (“SELECT is evaluated after the WHERE clause?!”).

If SQL is like SGML and XML, which was replaced in many applications by the faster and lighter JSON format, can we do the same for SQL? Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implementations are incompatible with each other anyway.

In summary:

1. RDBMS servers should have an “automatic DBA”-mode enabled by default (because if it’s not the default then the exact people who need it (unsophisticated DB devs) won’t be using it, and the people who don’t want it will just turn it off anyway.

2. SQL needs to be replaced with something better: less verbose but just as expressive, and with all of SQL’s “gotchas” removed (like adding multiplicity assertions to joins, implicit joins when dereferencing a foreign-key, implicit locks, allowing comparisons to NULL, and leaning more towards relational-algebra instead of relational-calculus, and support for column macros: this is desperately needed for CRUD applications!)

3. Thank you for coming to my TED Talk.

I totally agree with this. I'm relatively new to SQL but have recently had to start messing around with it. In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue.

I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance.

> I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance.

Azure SQL and Amazon RDS do this - but they’re Cloud-hosted IaaS rather than on-prem.

On-prem SQL Server does have similar, but limited capabilities with its Query-Store feature - but I don’t recall any of it being fully automatic - at least not by default: which may or may not be a good thing.

> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue.

In mathematical modeling, it's the same. You write the model, but what happens under the hood numerically is not that transparent -- in complex cases, you normally don't get to set breakpoints and step through numerical iterations, especially for iterative solvers -- so you have to treat it as a grey box (not black box). You can analyze the problem by analyzing its inputs and outputs, and through the intermediates it exposes. SQL is the same. As are many systems in the physical world. I'd argue the situation in CS is actually more the exception than the norm.

You can do this by inspecting the query plan. In MS SQL, you can even inspect the plan live (while the query is running) and figure out where the bottlenecks are. For obvious cases, MS SQL's client profiler returns (imperfect) recommendations on which fields to index.

The trouble is because the behavior of algorithms depends so much on the characteristics of the data (which in a transactional database is changing all the time) and the underlying intention behind the query (which is not always explicitly declared in SQL -- otherwise the language would very extremely verbose), automated optimization is not always possible.

Optimizing query plans is a bit of an art -- it's like trying to give hints to an optimizing compiler via pragmas. Azure SQL does some simple analyses and adds/removes indexes as needed and performance is generally somewhat improved, but if you understand your query patterns, you can actually do far better. For instance, if you know you're going to be joining or filtering on certain fields, indexing those fields will speed up the join.

Or when you're doing a nonunique JOIN where just one to join to any one record (doesn't matter which one), you can rewrite that as an OUTER APPLY -- a query optimizer wouldn't be able to deduce your intention of "it doesn't matter which to join to" from your declarative query.

> You can do this by inspecting the query plan

That’s what I’m railing against! Most of the time the RDBMS should be self-aware enough to implement the required changes to improve query performance by itself, without needing us to do that.

SQL Server’s query-plans are nice - I won’t deny - but it’s impossible for a relative database newbie to even understand what a query-plan even is - less so to understand what the plan is saying - and considerably less to understand what changes should be made to the system to fix any performance issues. Probably 90% of the time it’s a missing index or out-of-date STATISTICS objects.

When I first learned about RDBMS indexes (this was probably about 2-3 years after I was already writing database-driven websites in Dreamweaver in secondary school) I was flummoxed that I had to manage indexes manually - I long assumed that a DBMS would just index everything because Google search engine and Windows XP’s file indexer did, I got the impression that indexing was a-given on any modern platform, that’s why I’m disappointed that it’s 2020 and a local MySQL instance will still gladly let a regularly-run query perform a full table-scan.

If Apple designed an RDBMS as a consumer product, there wouldn’t be any exposed indexes.

I point you to this discussion:

https://dba.stackexchange.com/questions/43772/why-dont-datab...

Auto update statistics is something you can turn on, but you can't (well shouldn't) arbitrarily index every field because there's a write load/performance cost. Also sometimes a table scan has the same performance characteristics as reading from an index -- it all depends on your query.

The query planner already does a ton of optimizations, but indexing is one of those things that may or may not help -- there's a stochastic element at play -- you almost have to have a model of current and future data + query mix, and which you then have to profile for any gains -- which is why most databases leave the decision to the admins.

Index creation after all is based on a priori expectations of what future data will look like based on historical patterns -- it is predictive in nature. It is akin to front-loading computation to create a derived data structure that you hope will match future query patterns.

You could do continuous profiling but it's expensive. Also finding the right combination of indexes is a combinatorial problem coupled with the fact that the data is constantly changing -- it's a very difficult optimization problem.

The situation has changed somewhat, and I've read about research on using modern machine learning methods to help databases tune/index themselves (which probably what Azure SQL is doing, as well as a few other cloud db's). Azure SQL's database tuning again is passable and better than nothing, but it's not super good -- I suspect it's very conservative in order to avoid risky indexes that may slow down corner cases. I have it turned on but I still find myself hand-tuning indexes to achieve the performance I need. (that said, I also mostly run analytic queries so converting all my fact tables to the Clustered Columnstore Index data structure pretty much speeds all of my queries up without any tuning.)

There's also an alternate approach based on linting:

https://www.eversql.com/sql-query-optimizer/

At any rate, for most of the history of RDBMSes this type of ML technology was not available or was very expensive to operate on large-scale databases. It's a heavily researched area, and the fact you don't see it implemented at scale reliably suggests that there practical difficulties.

It's not just an ease-of-use issue.

> If Apple designed an RDBMS as a consumer product, there wouldn’t be any exposed indexes.

Apple has an RDBMS product: FileMaker. It does support automatically creating indexes, but indexes are exposed and users can manually configure indexes if they choose.

> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue.

With experience, you'll learn to appreciate the lack of need to say how to do something, and the ability to just specify what needs to be done. Read more about declarative vs imperative.

Knowing exactly what happens under the hood costs a lot of mental resources (this is why assembly is considered low-level), and is often an illusion. A proven tool which knows a particular area instead of you is immensely valuable (think about compilers, for instance).

> it would automatically add an index

Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema.

Software that suggests which indexes would benefit a particular query does exist. Consideration is still needed.

> Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema.

To be clear, I’m not suggesting a DBMS should always add a new index every time it encounters a query that’s slow due to a missing index. I’m saying that DB servers should look at trends of queries - and see where real wall-clock time is being wasted, and act accordingly.

The performance cost of an extra index for insert/update/delete queries is only a concern when you get to the point where you do need to manually manage indexes - but for most database tables with less a few million rows there’s no reason for the server not to automatically add indexes on columns used in predicates. It won’t be perfect, but it’s better than allowing slow queries to always be slow.

Unfortunately every index takes time to access; it's like dereferencing a pointer. You also usually want the hottest indexes to reside in RAM. Making more indexes compete for RAM pages may and will have implications for other queries, possibly more important.

I agree that often machine resources are plentiful, and human resources are scarce, so managing indexes automatically makes sense. It's even implemented, e.g. in Oracle as a complete management tool [1], or in Postgres as a discovery and recommendation tool [2].

[1]: https://oracle-base.com/articles/19c/automatic-indexing-19c

[2]: https://www.percona.com/blog/2019/07/22/automatic-index-reco...

>> The performance cost of an extra index for insert/update/delete queries is only a concern when you get to the point where you do need to manually manage indexes - but for most database tables with less a few million rows there’s no reason for the server not to automatically add indexes on columns used in predicates. It won’t be perfect, but it’s better than allowing slow queries to always be slow.

You'd be surprised. The thing that really drives index explosion is the number of distinct queries. IME, inspecting and reducing distinct queries is one of the easiest ways to optimize and it can typically be automated. I've migrated systems from millions of distinct queries down to hundreds or dozens. However the objective of the optimization is typically driven by requirements external to the database and may vary over time. Thus difficult to align with an optimizer.

Distinct as in doing different things or distinct as in prefer batching? My English seems to be failing me here.

> Unfortunately there’s too much institutional inertia behind SQL for any of the DBMS vendors to go-it-alone, and beyond trivial queries all of the SQL implementations are incompatible with each other anyway.

I'm not sure which alternative query language is sufficiently general to match SQL. A datalog implementation on top of Postgres (via libpq?) would be really interesting though.

I’m saying we should keep SQL’s fundamental model, but overhaul the syntax and change some default semantics to eliminate nasty surprises and design it to encourage best-practices (I.e. pit-of-success).

For example, this:

``` SELECT u.UserId AS user_userid, u.UserName AS user_userName, u.DisplayName AD user_displayName, u.Etc... this is tedious, d.DocumentId AS doc_documentId, d.etc FROM dbo.Documents AS d INNER JOIN dbo.Users AD u ON d.CreatorUserId = u.UserId WHERE d.Created >= @today AND d.Foobar IS NOT NULL AND d.ReviewerUserId IS DISTINCT FROM u.UserId ```

Could be represented using this hypothetical syntax:

``` from dbo.Users u dbo.Documents d join 1:m on u = d auto where d.Created >= @today & d.ReviewerUserId != u.UserId select u.* as user_, d. as doc_* ```

* The “1:m” (and “0:m”, “m:m”, “1:1”, etc) syntax would be used to both declare the type of JOIN needed and to assert the multiplicity of the JOIN (e.g. “1:1” would be an INNER JOIN with a one-to-one multiplicity, so the query engine would reject the query if the join uses non-unique/key columns, and “1>m” would be a LEFT OUTER JOIN with a one-to-many multiplicity).

* The “auto” keyword means the exact join comparison can be omitted if there already is a foreign-key relationship between the two tables.

* The “foo.* AS foo_*” syntax allows for the prefixing of all column names without needing to manually list them. I also want to allow for saying “select all columns EXCEPT these specific columns”.

I question the sensibility of having the primary interface to a database be a query language as such; It's a pain to work with programmatically. I agree with the notion of starting from relational algebra, but rather than the interface being a human-oriented textual syntax, the protocol should be defined in terms of some widely supported serialization format (take your pick). This makes it easier to write query-builder libraries, which the humans can use. You really don't want to be doing string substitution to put queries together.

> I'm not sure which alternative query language is sufficiently general to match SQL. A datalog implementation on top of Postgres (via libpq?) would be really interesting though.

Datalog is already more general that SQL, though; even WITH RECURSIVE only gets you linear Datalog.

The "No" in NoSQL is No DBA.

A lot of stuff like Mongo got popular because devs could start fast and there was no DBA group to get in the way

It is 2020. If you comparing the performance of PostgreSQL and DynamoDB in terms of GROUP BY analytics, you have probably missed the Column Store revolution.

Now if these two authors actually focused on the trade-offs between these two architectures in terms of their sweet-spot, operational row stores, we might gain some insight.

Reply on news.ycombinator.com