Ask HN: Why are we so fragmented in databases options?

To make it easier to think about databases, I group them into categories

- SQL Databases (Mysql, Postgres, SQL Server, SQLite)

- JSON document stores (MongoDB, etc)

- In-memory key-value stores (redis, memcache)

- key-value stores (cassandra, etc)

- Graph DBs (neo4j, dgraph)

- Search DBs (elasticsearch, Lucene)

- Timeseries DBs (timescale)

I consider the SQL databases and the JSON document stores to be the general default databases that my applications will use 90%+ of the time. SQL databases require defining the schema ahead of time, while with document stores I can just dump data into the database and then figure out what's needed afterwards. At this point, I usually use postgres as my go-to database since it's open-source, well-supported and has some additional features like the ability to use json, but if you're more familiar with a different SQL db use that instead. Most of the other database categories are specialized for specific use-cases (in-memory key-values are great for queues and caching, time-series dbs are great for metrics)

EDIT: https://db-engines.com/en/ranking currently shows 381 unique databases, which is probably a bit of an undercount but not by much.

I realized I didn't answer the `Why there are so many databases` part of your question. Essentially, many companies outgrow the general-purpose databases and need databases optimized to their read-write level and data structures. Analytics tend to use columnar-databases once their data sets are in the TB+ range so that their queries return quickly.

Project metrics & usage / design doumentation should be able to help with selecting relevant database grouping(s).

aka post data collection doing timeseries analystics; at home book library database so, no need for remote web access; doing local gis map database; database for motion sensor to record time / date motion detected.

starter ideas from searching on "database selection metrics" after:2019-12-31

short reads:

   https://medium.com/wix-engineering/how-to-choose-the-right-database-for-your-service-97b1670c5632

   https://www.pingcap.com/blog/how-to-efficiently-choose-the-right-database-for-your-applications/

   https://dzone.com/articles/introducing-database-selection

full discussion:

   https://sunnykichloo.wordpress.com/2020/09/21/database-selection-criteria/

   https://www.mongodb.com/blog/post/introducing-database-selection-matrix

Internet search engines are 'the' widely used, general purpose database, at least for things that can be shared publicly.

Obviously, doesn't work to well with embedded devices without an internet connection and/or data not in some electronic format aka traditional camera picture / computer file

Plain files will work for those collections at the kind of scale implied. Don’t overthink it.

hence the comment on project metrics / usage / design.

aka aggregating a few terabytes of motion data may not work well in a single text file for CERN collider motion data. ;)

Right... you previously listed a home library catalog, local GIS data, motion capture for what, a home Nest device? You should have mentioned you need a solution for CERN collider data or that you have a collider at home for more relevant answers.

ok, should have prefaced with, miscellaneous contrived use case examples, where project metrics/usage/design intormation would provide insites about the contrived use intent / framework.

> Why isn't out there an ultimate general-purpose database? Which fits well for 90% of cases at least?

There is. It's called PostgreSQL :)

I love PostgreSQL but it's not easy, not horizontal write scalable, not immutable.

then maybe those actually aren't requirements for 90% of use cases ;)

You've got a point!

Relational databases have dominated since the 1980s. You have a handful of options that differ mainly in terms of price and features. They all follow the same model and will imply the same or very similar application code. You can pay for Oracle, SQL Server, or DB/2, or go with the free/open source PostgreSQL or MySQL. For many applications SQLite is a good fit.

I don't know about the other tens of thousands of databases out there, I certainly have never heard of that many. You have various NoSQL options from Redis to MongoDB, Cassandra, various object-oriented databases, column-oriented, document databases, etc., all of which fall into a small number of general groups that target specific applications and requirements. Unless you work on hobby projects and like to experiment I can't think of a reason to look at hundreds or thousands of obscure databases.

> I imagine that the minimum qualities of a database are: immutable, ease of use in any language, performance, ease of querying and manipulating data, easy to configure, maintain and scale, lightweight, among others. Every time I go to choose a db I always come across some critical problem.

An "immutable" database (read only) would have limited value. "Easy" describes your subjective experience and opinion, not a quality of a database. Maybe list some of the "critical problems" you encounter. Certainly most non-trivial applications use databases without dealing with "critical" problems.

re: "immutable" database use cases:

   internet archive : https://archive.org/

   'old' news media :https://mediahistoryproject.org/ perhaps including orwell's '1984'

   version specific api documentation : https://swagger.io/blog/api-documentation/what-is-api-documentation-and-why-it-matters/

   voyager 1 & 2 golden record : https://en.wikipedia.org/wiki/Voyager_Golden_Record

Right, limited value as I wrote. Most modern database engines support read-only databases, it’s more a configuration than a different kind of database.

Ummm... would characterize an immutable database as a more as a specific use case than general application configuration aka write once, read many traditionally for transactional logging/recovery. For concurrent transactions/databases, the immutable stuff does allow for making certain assumptions to improve performance.

database management systems typically support/provide front end to multiple/different database engines.

As with most things in software world, general situations that allow for customize use case configuration setup, and specific designs/implimentation for specific situations.

Personally my favorite db for pet projects is

https://www.arangodb.com/

I think you hear very little about it because ADB users see it as a "secret weapon" to crush their competitors with. I've done large ontology work (MESH and other health ontologies) and IoT work (keep several years of sensor readings for sensors in my house) and workflow systems (select interesting HN articles or jobs I want to apply to) and it has never let me down. I haven't run a real instance serving customers in the cloud though.

For the last few years every eng manager I have worked with has been a fan of

https://www.postgresql.org/

In the early 2000s I thought it overpromised and underdelivered and called it CrashGreSlow but after MySQL got bought by Oracle the pgsql team has worked hard to improve it I think it is great today. It supports all kinds of advanced features such as stored procs, full-text search, JSON equivalent fields, etc.

> There are tens of thousands of options out there and every single day a new one appears here on HN.

Not really, realistically you have a handful of options for every use case unless you're deliberately picking really obscure stuff for the sake of it.

> Why isn't out there an ultimate general-purpose database?

Because:

1. There's many good solutions that all work well especially if we're talking about general purpose.

2. Still not everything works for every use case.

3. It's healthy to have options to pick from.

IMO the one real way for an "ultimate" to emerge is for it to be able to do a lot of heavy lifting for you: and that basically means it has to be a managed cloud service. It's just not going to be reasonable in pure software to service the vast majority of the needs of the vast majority of your applications. But in the cloud with managed services the possibility space changes massively

"I have 22 years of experience in the area of woodworking, and I always had a huge difficulty when starting a new project: choosing a tool. Do I need a chisel? Or a hammer? Or should I go for a screwdriver?"

Databases are tools like any others, and you choose them based on what you actually need for any specific time. In fact, many projects already use multiple databases - storing dynamic state in a RDBM, and static resources in the filesystem (which is absolutely a database).

Of course, there are other criteria involved, like e.g. the familiarity of the team with different database paradigms, but the core fact stays the same: database is a means to an end, which can be summarized as "finding a solution for persistent storage of some data".

Rather than throwing out nonsense like "tens of thousands of options" and "I always come across some critical problem" why don't you describe your requirements and list some of those "critical problems" that seem to uniquely afflict your work, whatever that is.

Your actual question doesn't have an answer. No such thing as an "ultimate" database for the same reason we can't objectively agree on the "ultimate" car or the "ultimate" food, or programming language, or anything else. It always comes down to it depends on the requirements.

I'd add to this comment - many options is certainly better than not many options. I personally prefer to have rich and colorful set of options to use depending on my current needs than to have to choose from, say, 2 options (presumably delivered by google and ms?)

I was just talking to someone today. If in doubt, just start with Postgres. Lowest hanging fruit if you are just getting started. Don't overthink it. Remember I said if in doubt. If you already know, then sure go ahead choose whatever you like. 99.9% of cases don't need anything beyond a decently hosted Postgres instance.

Flipping the question around, it sounds like with 22 years of experience and tens of thousands of options, you should be the one educating us. I'd say for most folk around here, they only know about 1-8 options.

I wonder this every day.

I’ve settled on:

-CockroachDB for relational -FoundationDB for key value

I’m currently looking for a full search text engine that supports pluggable storage so I can have FDB be the backing store.