Ask HN: Is there a resource to help choose the right DB for you needs? | Hacker News Reader

Ask HN: Is there a resource to help choose the right DB for you needs?

15 pointsgetcrunk12 comments

Is there something a junior developer can use that explains the various design/philosophical tradeoffs of various databases which among other things help avoid gothchas down the road (i.e. in regards to scaling/performance)? Something that is also practical as opposed to theoretical (talks about actual dbs and not abstract).

I already understand the basic difference between sql/nosql and the CAP theorem (undergrad). But now at the beginning stage of an app I’m working on I’ve spent the last few hours looking at the indexing, querying and scalability options of various databases and feel overwhelmed.

Some areas of interest off the top of my head:

  - Sql/nosql
  - Eventual vs immediate consistency
  - Rest or http
  - Orm options
  - Sharding/presharding and clustering 
  - Scalability/redundance
  - Indexing, secondary indexes
  - Querying
  - Realtime db (what even is that)

Then there is also the performance impact at larger scale based on the effects of the previously mentioned factors. Couchdb has multi-master replication, but from my reading writes need to happen to all replicas (an understandable tradeoff, but I don’t know is this the only way. Seem like it but I don’t want to have to decide that. I want someone relatively competent resource to lean on to know this other than my own synthesis.) Mongo’s eventual consistency has problems with potential data loss (seems like a normal result of eventual consistency). Mongo can have secondary indexes but other databases cannot. Couchbase allows scaling out a lot easier than couch db (no resharding). Postgres cant scale out easily but scales up well. For a mongo query you have to pull the whole document (that's why they recommend keeping it flat)

And I’m sure this just scratches the surface, but I hope there’s something you people can refer me to that’s short of an entire or multiple db courses.

As for a practical advice:

Just use PostgreSQL. You can get really really far with just an SQL database and few indexes. It's enough for 99% of projects.

This has two big advantages:

1. PostgreSQL is a reliable, high performant database, which has been proven over time. So you are not going to deal with weird issues and can focus on your project.

2. You will learn SQL which you are going to use throughout your whole career.

And if you decide to go this way, read this: https://use-the-index-luke.com/

Pretty much this, Postgres is the right option for pretty much everything you'll come across.

As a team of just me with not much savings, trying to make something that may grow quickly (1-10s of TBs), I cant afford to scale up from the get-go. I need to be able to scale out first. It doesn't seem Postgres is conducive to that. AFAIK clustering or sharing is not yet offered by the core.

In addition, I need to look more deeply into postgres but its considerations for things such as lazy replication (unreliable connections or offline/mobile users) don't seem any less complicated than other options.

You can scale PG horizontally with read replicas. Many frameworks support this (rails, for example: https://dev.to/schwad/how-to-use-horizontal-sharding-in-rail... )

I don't know your business but scaling to 1TB of data (not just files, but actual rows in a database) seems weird. Feel free to prove me wrong (I'm just some person on the internet, after all), but I've seen many startups fool around with over architecting instead of building features users want.

If, on the other hand, you're positive you're going to need to handle that kind of data from the start, I'd read up on real life architectures that are similar to yours: http://highscalability.squarespace.com/blog/category/example

If your business grows so quickly that one Postgress instance can't cope, by that time hopefully you should have enough revenue (or investors) to add a few read replicas.

Also, are you planning on storing large blobs in the database? If so, consider storing them in a blob store (e.g. S3) and only storing their path in the DB.

think something like google keep or trello or airtable. Its just a lot of text/json. Its in a db and not blobs or object store due to sync, filtering, tagging, reporting ..... larger assets(img) will be in an object store

Maybe its not supposed to be in a DB, but using my export of google keep as a guide on average my larger files are 10-20kb of json. x that by 1000 for unlimited* storage and x that per user you're over 1 TB.

Even in that case postgres will still work and there will be enough time to scale up manually. But from the perspective of architecting something on this scale or beyond I made my original post.

And so maybe I am over architecting then both of yours advice about pg still applies. (you guys are ultimately right, practically)

This advice applies IMHO. https://www.cybertec-postgresql.com/en/postgres-scaling-advi...

"So, you’re building the next unicorn startup and are thinking feverishly about a future-proof PostgreSQL architecture to house your bytes? My advice here, having seen dozens of hopelessly over-engineered / oversized solutions as a database consultant over the last 5 years, is short and blunt: Don’t overthink, and keep it simple on the database side! Instead of getting fancy with the database, focus on your application. Turn your microscope to the database only when the need actually arises, m’kay! When that day comes, first of all, try all the common vertical scale-up approaches and tricks. Try to avoid using derivative Postgres products, or employing distributed approaches, or home-brewed sharding at all costs – until you have, say, less than 1 year of breathing room available."

I just read the original article my self before seeing your post. The last bit about "sharding" by partitioning your db from the start helped alleviate a lot of my concern I think. All my apps data doesn't actually need to be in the same DB for 90% of my use case (userid in range -> db x).

You may be able to setup a multi tenant db. I've done this in the past. A new schema was created for each tenant. It worked well. It never got to the size where it had to scale out. So not sure how easy it would have been to move to multiple databases.

Came here to say this. PG (or, imo, mysql) will solve 99% of your problems and if you are in the 1%, you can hire folks to help and/or migrate.

My cheat sheet is: For transactions, postgresql. For analytics, clickhouse. For cache, redis. For search, elasticsearch. For objects, anything s3 compatible.

Anything else, I really have to do in-depth research and comparisons because 1.) Each database has their own pros and cons, 2.) These pros and cons between dbs tend to overlap and has each of their own gotchas, and 3.) These pros and cons also improve overtime where some problems brought up earlier might be addressed already in the lastest releases.

CMU Database Group's YT channel is also worth checking out for introductions on some databases.

https://m.youtube.com/channel/UCHnBsf2rH-K7pn09rb3qvkA

Orms are generally a lot of extra work down the track. They do 80% of things very well and save loads of time but you will spend an amazing amount of time on the other 20%.

Learning SQL is worth it.

Reply on news.ycombinator.com