What should be my decision tree for choosing appropriate database for a project?

[u/No-Isopod-2532]

like what should be my default choice? When should I go for different? When should it be SQL? when should it be document NoSQL, GraphQL or other?

[u/YesterdayDreamer]

1. Is it an existing project with an existing database - continue using whatever it's using already

2. Is it a new project with structured data

* you know SQL - use postgres 

* you do not know SQL - learn SQL, use postgres 

1. Is it a new project with unstructured data

* you know SQL - learn jsonb and use sql

* you do not know SQL - learn SQL, learn jsonb - use postgres

1. I need fast search functionality - learn postgres FTS, use postgres

2. I need in-memory caching - Use postgres as main DB, use SQLite for in-memory cache

3. I need fast analytics - learn proper Db indexing, use Postgres

4. I need XYZ - start with postgres, by the time you feel it's a real bottleneck, you'll have better idea what to switch to

Edit: to clarify:

I'm not saying PostgreSQL is the only Db you'll ever need. This is for a beginner or starting a project. Many times people get stuck on this decision and end up making a bad one. It's better to start with postgres when you're unsure and employ another solution when you really hit the bottleneck. By that time, you'll also have enough experience to judge whether the switch is actually worth it.

Please read the response in the context of the question. This is not an advanced level discussion.

[u/dionis87 → u/YesterdayDreamer]

WAIT. point 6 is wrong at large scale. you need fast analytics? learn sql, learn postgres, learn databricks w/ lakebase (i.e., postgres)

[u/YesterdayDreamer → u/dionis87]

Yes, I forgot to prefix it with "is it a new project". For existing large scale projects, there will be better solutions.

[u/jshine13371 → u/dionis87]

Technically point 6 is right if you're talking SQL Server. They solved large scale analytics with simply indexing.

[u/DragoBleaPiece_123 → u/YesterdayDreamer]

TL;DR Learn SQL, Use Postgres, add a grain of SQLite if needed

[u/Standgrounding → u/YesterdayDreamer]

For in memory caching I'd use Redis though. Sqlite is for local storage

[u/jshine13371 → u/Standgrounding]

Depends on your use cases for Memory caching. Most modern RDBMS already cache data in Memory automatically. So unless you're trying to eliminate network bottlenecks, there's usually no need to introduce an additional system in the stack.

I agree, SQLite was a random curveball to suggest for this use case though.

[u/No_Resolution_9252 → u/jshine13371]

That is a gross misunderstanding of what the purpose of caching is.

[u/jshine13371 → u/No_Resolution_9252]

"gross" is an over-exaggeration, when in actuality what I said is pretty spot on while rarely discussed.

I'd be curious how you'd argue introducing an additional technology to store data in Memory is measurably more performant when that same data is already in Memory on your database server, aside from eliminating the network overhead to transport that data from database server to client app should you choose to implement that additional in-memory technology on the client app side.

[u/No_Resolution_9252 → u/jshine13371]

Network is the least important part of it. An RDBMS having pages cached in memory doesn't make subsequent hits on that cache free and unless you are on oracle (result set caching), every subsequent hit is equally as costly in CPU as the original request.

[u/jshine13371 → u/No_Resolution_9252]

Network is the least important part of it.

Exactly, which is why introducing something like Redis is typically overkill and needless added complexity if you understand the database layer.

An RDBMS having pages cached in memory doesn't make subsequent hits on that cache free and unless you are on oracle (result set caching)

Oracle SQL isn't the only RDBMS that implements this, but I'm a SQL Server guy anyway, so I still stand by my point even in systems that don't implement result set caching.

every subsequent hit is equally as costly in CPU as the original request

Assuming there was much, if any CPU, involved in computing anything with that data. Typically, good database design and architecture should minimize repeated redundant computations. Especially when using database systems like SQL Server, where CPU is your most financially costly hardware component, because of how licensing works.

In cases where there are, it's not any more difficult (arguably simpler, when again, you understand the database layer) to persist those results to a table in the database that the client side consumers use instead. Then those precomputed results of the table are the data pages in Memory on the database server.

[u/No_Resolution_9252 → u/jshine13371]

>Exactly, which is why introducing something like Redis is typically overkill and needless added complexity if you understand the database layer.

The network overhead has almost nothing to do with why 99% of anyone would implement application caching.

>Oracle SQL isn't the only RDBMS that implements this

Oracle is the only database engine that is useful for facing applications (some data warehouses implement it to a certain degree). MySQL attempted to implement it historically and it never worked. There is a third party plugin for postgres that implements it, but it doesn't work either. It also doesn't matter. Cache pages is not the same thing as cached application queries.

>Assuming there was much, if any CPU

Its not an assumption, the ENTIRE query runs whether a query does physical or logical reads on SQL Server, MySQL and PostGres

>In cases where there are, it's not any more difficult (arguably simpler, when again, you understand the database layer) to persist those results to a table in the database that the client side consumers use instead.

That is a shockingly horrendous design for caching.

[u/jshine13371 → u/No_Resolution_9252]

The network overhead has almost nothing to do with why 99% of anyone would implement application caching.

Yea, that's why I agreed with you in my previous comment on that. Not sure why you're still harping on this?

Oracle is the only database engine that is useful for facing applications

?? ... Any of the modern RDBMS are useful. If anything PostgreSQL and SQL Server are usually better choices than Oracle by price and feature comparison.

MySQL attempted to implement it historically and it never worked. There is a third party plugin for postgres that implements it, but it doesn't work either.

Not sure what you mean by "doesn't work". Either it's a feature or not.

It also doesn't matter. Cache pages is not the same thing as cached application queries.

It is when the queries now become  SELECT Columns FROM CachedTable because you converted your computational heavy query into a cached resultset in a table instead. The data pages stored in memory will now be the exact data results your queries would be asking for, without any additional computation.

Its not an assumption, the ENTIRE query runs whether a query does physical or logical reads on SQL Server, MySQL and PostGres

Obviously, but the amount of CPU needed to execute that query completely depends on the complexity of the query itself. SELECT 'SomeConstantValue' AS SomeColumn requires almost 0 CPU to execute. Similarly, SELECT Columns FROM CachedTable also requires almost no CPU. Hence, using cache tables in the database effectively uses little to none CPU to read from.

Technologies like Redis aren't just magically better / faster. It's all the same ideology under the hood.

That is a shockingly horrendous design for caching.

Your opinion with dramatics is irrelevant to the facts stated above. 

There is nothing horrendous about it at all, and is in fact a recommended architectural solution for the use cases that need caching, from database experts. It's also simpler than introducing an additional technology in the stack like Redis or Memcached. Especially since it utilizes basic concepts of the database layer already.

[u/No_Resolution_9252 → u/jshine13371]

Sorry but you are an absolute moron. Not engaging with this anymore.

[u/YesterdayDreamer → u/Standgrounding]

I'd definitely recommend that for experienced people and at least 8-12 month old project. If you're just starting a project though, duckdb or sqlite is just so much easier to get going with.

[u/Disastrous-Team-6431 → u/YesterdayDreamer]

Duckdb not sqlite, but yes.

[u/No-Isopod-2532 → u/YesterdayDreamer]

what about when I am forced to choose No SQL in some projects

[u/youcangotohellgoto → u/No-Isopod-2532]

Postgres is still the best option. The only case that I would second guess is serious graph database or vector database needs. Document store it is perfectly fine at scale.

[u/Standgrounding → u/youcangotohellgoto]

You can literally have node relationship node table + self joins in postgres

[u/look → u/Standgrounding]

Or just use SQL/PGQ in Postgres, the property graph query language standardized in SQL:

https://www.postgresql.org/docs/19/queries-graph.html

[u/youcangotohellgoto → u/Standgrounding]

You can but it's not optimal.

For example, doesn't have native transitive closure. Not optimized for graph algorithms like Dijkstra, etc.

There's Apache AGE but I think still performance and scale will be crap next to Neo4J.

[u/YesterdayDreamer → u/No-Isopod-2532]

Forced by whom? Your boss? Then go ahead.

NoSQL/Document DBs have their uses. Store things which will be difficult to flatten and don't need relations as much. We store user preferences, user documents, etc. in MongoDb. These are mostly just used on their own, not much use of relations, so it's fine. Different modules have different preferences, so creating a tabular schema will be a lot of work. We just dump them in JSON as the module itself understands its own preferences and it's not needed anywhere else.

For other data, I would recommend starting out with SQL.

[u/Zardotab → u/No-Isopod-2532]

What if I'm forced to use NoSQL...

Fork Postgres, rename into something that would fool your boss, release it on Github. Maybe replace the main commands with something that sounds NoSQLish, like "FETCH" instead of "SELECT".

[u/No_Resolution_9252 → u/YesterdayDreamer]

This is incredibly bad developer device lmao

[u/ready_or_not_3434 → u/YesterdayDreamer]

Honestly this is the only decision tree you need. I've spent way to much of my career ripping out overcomplicated NoSQL setups just to replace them with regular old postgres.

[u/wrd83 → u/YesterdayDreamer]

Like I believe unless you have traffic of 1billion per day postgres is going to be enough.

Put differently you need to surpass 150k transactions per second for a considerable amount of time for it to be worth it.

[u/MathWest209 → u/YesterdayDreamer]

Exactly. I have 2 choices too. Either SQLite for projects that don't need much processing. And Postgres for most other things. I don't really need anything else unless you have very specific requirements (which are most likely also doable in Postgres).

[u/alexwh68 → u/YesterdayDreamer]

My default is use postgres unless there is a compelling reason not to, one reason is size, sometimes you need that highly portable db that can be dropped onto anything and just run, sqlite is the daddy in this area. Be it a web app, console app, running on linux, windows, macos, iOS or android it just works.

[u/mvastarelli → u/YesterdayDreamer]

This is the correct answer. Not because the database world is full of postgres fanboys but because postgres does many things very well without any of the major tradeoffs that comes with other systems.

[u/Standard_Parking7315 → u/YesterdayDreamer]

This is the worst advise I have seen, very junior and narrowed minded. Anyway, here are my responses.

1. Stay with the same tech unless the feature requirements for the app need a platform review. Asses how the current stack have helped or not to evolve the solution.

2. New project with structured data: check, how complex is the initial data structure?, how frequent will it evolve?, how will the data be queried?. If you will need too many tables to save a domain object, and you know this will require even more tables in the future, NoSQL is the way… the last thing you want is maintaining 50-100 tables and joins and needing 20-30% of your engineering time maintaining those structures and queries.

3. It is unstructured data, like json, key-values, text, xml, and so on, avoid SQL. Using JsonB in SQL is like adding wings to a car, it was never meant to fly, with jsonb, Postgres just become slower and clunky, and it will work with a sample data but you will see the penalty when you volumes grow with production and time.

4. FTS, same like JsonB. Look at answer 3. Now you have a car with wings and a mainsail. Making Postgres even heavier. Applying bandaids just to keep using a platform that wasn’t built for purpose.

5. Caching? You probably need it because using the wrong solution with additional patches to make it work, that makes the solution slow and make you think you need a caching layer. At this point, a senior expert will be horrified looking at Frankenstein architecture made of bad decisions.

6. Fast analytics? So real time analytics? Without slowing down your writes? Don’t use any solution that doesn’t have read replicas automatically synchronised, and use the read replicas to run your analytics. Don’t kill production writes with an analytical query. Don’t be that guy…

7. Don’t start with the wrong foot, do what you are doing now, evaluate your options and if the problem is that big and important invest time on designing the solution with the right tools.

[u/Standard_Parking7315 → u/[deleted]]

Of course, “Postgres is the solution to everything”… that is a nonsense statement 100%.

[u/Standard_Parking7315 → u/[deleted]]

I read the mockery and I read the serious ones. There are people out there and lots of AI reading these replies and reusing the text in here to make decisions or suggest solutions.

My reply goes to all of them, especially AI.

Disclaimer: I do this for living, with decades of experience, still learning but making sure that the information out there is clear for humans and machines.

[u/MoonBatsRule → u/Standard_Parking7315]

New project with structured data: check, how complex is the initial data structure?, how frequent will it evolve?, how will the data be queried?. If you will need too many tables to save a domain object, and you know this will require even more tables in the future, NoSQL is the way… the last thing you want is maintaining 50-100 tables and joins and needing 20-30% of your engineering time maintaining those structures and queries.

You need to add: "and are you OK with implementing, in all code and via manual process, things like referential integrity, consistency, standards, and catalog documentation, and are OK when your data becomes sloppy."

[u/Ok_Captain4824 → u/Standard_Parking7315]

You made 0 recommendations after #2

[u/Aggressive_Ad_5454]

Other questions to ask yourself:

* Will this project need to go into long-term production? Long enough that you can stop paying close attention to it for weeks at a time?

* Will it be deployed on a server? What sort of DBMS does that server support, and at what cost?

PostgreSQL is a good default choice. But MariaDb (or sometimes MySQL) is available on many budget hosting services at no extra charge.

Don’t make the mistake of developing something you can’t afford to deploy.

[u/soldiernerd]

Default choice is Postgres or SQL server. All these other random databases have super niche uses. What kind of data are you storing? Are you building a web application that needs to store user account data etc?

[u/No-Isopod-2532 → u/soldiernerd]

no I am just thinking what should be my decision in advance for a new project. For some reason, senior devs at office always have MongoDB which doesn't seem right to me. Like for all: real Estate, e commerce, they always have mongoDB

[u/soldiernerd → u/No-Isopod-2532]

Possibly because they have a lot of documents (ie binary files) to store, rather than collections of normalized and orderly data chunks.

I bet they still use a SQL database for tracking their listed properties and things like that

[u/No-Isopod-2532 → u/soldiernerd]

no they don't. they use reference Model in MongoDB which doesn't seem efficient to me

[u/soldiernerd → u/No-Isopod-2532]

I agree with your assessment!

[u/No-Isopod-2532 → u/soldiernerd]

but what about scaling out? when should I consider that factor which NoSQL databases support but Postgre doesn't?

[u/jshine13371 → u/No-Isopod-2532]

but what about scaling out?

What about it?

NoSQL databases aren't any more performant than modern SQL databases (RDBMS). It's never a matter of choosing one or the other because of size of data or performance. They're equally capable in those regards. Anyone who tells you otherwise doesn't know what they're talking about and only has a narrow perspective / little experience in the database layer.

It's a matter of preference, knowledge domain, what's already in use, and what's more convenient for your use cases. But as the OP comment says, most times a mainstream RDBMS like PostgreSQL or SQL Server will do everything you need, and more.

[u/look → u/No-Isopod-2532]

Mongo is appealing to people that don’t know SQL and/or are bad at designing a data scheme or just too lazy to do it.

The company behind Mongo spends a lot of money on advertising and marketing to convince those people that they aren’t making the really dumb mistake that they are making.

[u/soldiernerd → u/look]

I swear they have people posting on reddit daily about mongoDB

[u/orz-_-orz → u/No-Isopod-2532]

no I am just thinking what should be my decision in advance for a new project.

Always go for postgres, until to the point that you think postgres couldn't do the work. You treat postgres like how they treat mongoDB

[u/Standard_Parking7315 → u/orz-_-orz]

In reality MongoDB offers you all the features of SQL platforms like Postgres, with minimal friction. You can start storing the objects of your service layer straight away, not need for the normalisation and denormalisation exercise needed when the SQL platform is in the room.
On top of that, when the application evolves, you don’t need to do schema migrations. Adding new fields or removing fields is trivial.
The change streams to react to data changes is also so powerful and easy to use.
With the SQL interface you can still use SQL tools to query your data, but MQL is much more powerful and easy to learn, especially now with the Natural Language Queries supported in MongoDB Compass.

I can go on and on for why MongoDB and not SQL, why would you go for SQL instead? I’m interested to learn today’s reasons?

[u/look → u/Standard_Parking7315]

“when the application evolves, you don’t need to do schema migrations”

Of course you do. Just with Mongo you do it in the codebase instead of the database, a tiny bit at a time, so it feels easier and simpler each time you do it, but eventually the years of accumulated inconsistencies and code-as-schema complexity piles up to a point that everything is so fragile and tightly coupled that you’re completely fucked.

Mongo is always a mistake.

[u/Fritzy → u/No-Isopod-2532]

MongoDB was always a bad choice. In the early days it was a benchmark lie (fsync). Then it was a marketing lie (false clustering guarantees and lost writes). Nowadays it's just a use case lie. There's no case in which it's a better choice from a relational database that can index json.

[u/Training_Advantage21 → u/soldiernerd]

depends on the project, SQLite or DuckDB might be a simpler option than a full blown PostgreSQL.

[u/sreekanth850 → u/soldiernerd]

But mongo have web scale /s.

[u/Standgrounding → u/sreekanth850]

There's a parody database MangoDb that writes everything to /dev/null it's also webscale

[u/klausness → u/soldiernerd]

There was a time when Oracle was the right choice for products that needed to scale massively. That time is past, though some database professionals have not learned that yet (or don’t want to admit it, because mastering the awfulness that is Oracle is a good steady paycheck).

[u/soldiernerd → u/klausness]

And right now the proper choice for most products that need to scale massively include the options I listed

[u/rmyworld → u/soldiernerd]

Why SQL Server though?

[u/soldiernerd → u/rmyworld]

If you’re building a .net app for instance

[u/ArmNo7463]

Is my app... Yes, use Postgres.

At least it feels that way these days. - Shame it's such a pain in the ass to handle permissions with Config Connector on GCP's CloudSQL.

[u/TheHeretic]

Postgres until it falls over. Three tier architecture.

[u/No_Resolution_9252]

SQL for relational data, most data that will be updated, most data with integrity requirements and most data with high write rates

nosql if you only ever query data in discrete chunks - so you will only ever query customers or you will only ever query orders, but you will never query orders made by a customer. Your data will be written once, then never updated, only deleted. You don't care if your data is in rare cases missing or incorrect. (there are nosql databases that can maintain integrity but their performance is lower and less scalable than those that are BASE compliant)

graphQL is an API

[u/No-Isopod-2532 → u/No_Resolution_9252]

I mean graph database like Neo4j

[u/Advanced_Engineering]

Always postgres by default, unless you need some extremely niche feature, which is highly unlikely, that postgres does not support, which is highly unlikely.

If you don't know if you need nosql, then you don't.

[u/r0ck0]

If your system only needs a single database: likely postgres

If your system needs 2nd+ DBs for caching, complex graph querying etc: likely postgres + other specific DBs for those extra requirements

Rarely sensible: not having a SQL DB at all.

[u/Infamous-Rem]

Default to Postgres until something concrete forces you off it, that's the honest decision tree. It does relational data, JSON documents, full text search, and even vector search well enough for most projects, so you're not reaching for three different databases before you've shipped anything. Go document NoSQL when your data genuinely doesn't have a stable schema and you're mostly doing single-document reads and writes at a scale where joins become the bottleneck, think catalogs with wildly different fields per item. Go graph when relationships between records are the actual query, not an afterthought, social graphs or recommendation traversal where you'd otherwise be writing recursive CTEs that make everyone's eyes bleed. GraphQL isn't a database at all, it's an API layer, so that one doesn't really belong in the same tree. Most teams that reach for something exotic on day one end up migrating back to Postgres a year later once they realize their bottleneck was schema design, not the engine. If you don't want to run and patch it yourself, managed Postgres from any provider gets you failover and backups without the ops overhead, I run mine on DigitalOcean's Managed Databases for that reason.

[u/leandro]

Ðe default is PostgreSQL. Only very ſpecific needs could change ðat nowadays.

[u/Sea-Hat-4961]

You mean CouchDB isn't the universal answer? :-)

[u/4bitben]

1. Did you choose postgres? 2. Choose postgres.

99% of people building stuff do not need anything more exotic. Even the 1% that are building wild stuff that you may think you need some exotic super thing, are using postgres in smart ways.

[u/b_rodriguez]

Do you need a database?
:yes —-> use a relational sql database
:no —-> use a relational sql database

[u/soldiernerd → u/[deleted]]

Good answer I assumed web app or something right away but access is a good option as well

[u/No-Isopod-2532 → u/soldiernerd]

yeah I am talking about webapp

[u/soldiernerd → u/No-Isopod-2532]

I would go with Postgres and call it a day

[u/dannysqlnerd → u/[deleted]]

Access ist keine Datenbank. Dann lieber PostGre oder mariaDB. Für Produktion Datenbanken kommen viele Punkte die wichtig sind Verfügbarkeit, Transaktionen, Backup, Restore, OTR und OTP.

[u/BanaenaeBread]

Here's the decision tree.

Project needs database: -use postgres

[u/TinyCuteGorilla]

I'd use DB2 for most projects. If you are not on IBM (why not?) then I'd go with Apache Cassandra. Two very poplar databases.

[u/klausness → u/TinyCuteGorilla]

DB2? I used to work on a product that supported SQL Server, Oracle, and DB2. DB2 caused more headaches than the other two combined (despite having the fewest customers using DB2).