31 ms·
How to Efficiently Choose the Right Database for Your Applications
- tangjurine 6y agoI wish I saw something like this during my internship
- pg_bot 6y agoI believe I can simplify the flow chart for "How to efficiently choose a relational database" +----------------+ | | | Use PostgreSQL | | | +----------------+
- beckingz 6y agoMariaDB is fine too
- orhmeh09 6y agoA database that doesn’t allow you to interface with views externally is fatally flawed because it means you cannot use views. This is one of a few reasons why we are moving away from MariaDB: https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-17124/comment/118979 https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-1...
- tankenmate 6y agoDo you run into the same issue with materialised views? Or does materialisation force mariadb to treat the view as a separate table? I'm also assuming that materialised views may not be useful for you due to update / synchronisation issues.
- 0xbadcafebee 6y agoI would phrase it more like: How much time and money do you have? "None" -> Use PostgreSQL "A little" -> Pick a database which matches your application use cases "A lot" -> Use PostgreSQL "FAANG" -> Roll your own With "A lot" of time and money, you quickly spend way too much time on databases, and the sprawl of the work will eat up time and budget that could be better spent on improving products / your organization. Just get the enterprise standardized on Postgres and move on with life.
- spacemanmatt 6y agoI wonder what FB is using now, having abandoned Cassandra in 2010.
- selljamhere 6y agoThis flowchart is difficult to screw up.
- haneefmubarak 6y agoI mean for straightforward usecases if Postgres will work, CockroachDB isn't a bad choice to get used to early on.
- freyir 6y agoFor the last decade or so, since I first became acquainted with databases, the HN crowd has said “Use PostgreSQL.” And every few weeks there’s a company blog post mentioning that they use MySQL. Why is this?
- jniedrauer 6y agoI think it's inertia. A lot of people have been using MySQL for 10+ years and it's still Good Enough. It takes them an extra 10 minutes to figure out how authentication or permissions work in postgres, and that extra 10 minutes isn't worth it.
- pg_bot 6y agoMySQL is certainly more popular, but that doesn't necessarily mean it is a better database from a technical standpoint. MySQL is a fantastic piece of technology and it will likely serve most of your needs if you are building websites. If you have used it before and are familiar with it there may not be a great reason to change. You also have to consider that databases are "sticky" products. Once the choice is made it is unlikely to change for the lifetime of the project due to high switching costs so there is not really an incentive to go out of your way to learn about multiple databases. MySQL was basically the default database for developing web applications from 1998-2013. (It is the "M" in the LAMP stack) It gained this position by being free to use, reliable and stable. This began a virtuous cycle where more companies catered to MySQL users. Deploying, managing, and MySQL was easier since everyone catered to the audience which drove a virtuous cycle of more developers using MySQL. A lot PostgreSQL's popularity can be traced to Heroku where it was the default choice for a database. Heroku made it even easier for developers to deploy their applications to the internet. Instead of having some janky build process you would just type "git push heroku master" and you changes would be live in a matter of minutes. This ease of deployment drove the virtuous cycle for PostgreSQL. For the web it's unlikely that choosing either database would be a mistake. They both are great options. Going into the technical reasons why I believe PostgreSQL is a superior if you don't know what to choose would be a separate post in and of itself, but I've already gone on long enough.
- skunkworker 6y agoThere are some tools that make horizontal sharding much easier, like Vitess which is MySQL only. But most businesses won't come close to needing these kinds of capabilities. I would say for anyone starting out and worrying about how large a single node can scale, Postgres can run well on aa 64/128core server, 2TB ram and 20TB ZRAID6 with a chain of read replicas. This can be done out of the box on Postgres without much issue and can get many businesses quite far, but once you go to the lots and lots of TB, or you have specific write latency, consistency, or other requirements, you have to evaluate multiple databases against your company's specific usage patterns and data, as no benchmark will give you a good idea.
- tybit 6y agoIs there a gold standard for hosted Postgres? I’m impressed with AWS Aurora Postgres but it’s still high enough maintenance that for something simple I’ll go with DynamoDB most of the time.
- pg_bot 6y agoIf you are already on AWS, I would suggest checking out RDS. You pay a slightly higher price, but the time savings are well worth the extra cost.
- stevekemp 6y agoThere are big players such as AWS/GCP who have hosted instances. Smaller companies such as aiven.io offer more dedicated services and are pretty awesome.
- cipher_system 6y ago+1 for aiven.io, works fantastically well and you don't need a DBA any more.
- bryanrasmussen 6y agoI didn't see Postgres in the article?
- orhmeh09 6y agoIts conspicuous absence alongside so many obscure alternatives is baffling.
- bryanrasmussen 6y agomy interpretation after reading more is basically the article is actually - out of the list of databases we use, how to determine which one to use for any particular task.
- pjmlp 6y agoDepends, on the projects I work on, it usually goes with either Oracle or SQL Server. Occasionally PostgreSQL gets used, as kind of staging database for small teams on a department level, with a db link for the big boy database used at corporate level.
- pg_bot 6y agoI mean no disrespect, but I've never met another developer/organization that was happy using Oracle. I'm genuinely curious as to why someone would prefer it over PostgreSQL if they were familiar with both had the option to not use it.
- mrweasel 6y agoWe use it and sell Oracle DB consulting services. Support from Oracle is pretty good. Postgresql also cannot compete with Oracle in HA setups. Don’t get me wrong, we love Postgresql, and MariaDB, but Oracle is still a great database, with all the features and stability you could possibly want, just at a hefty price.
- smarx007 6y agoI am pretty sure at least some of this is not 100% true. Otherwise "Russian Gmail" would not migrate 300TB from Oracle to Postresql https://news.ycombinator.com/item?id=12489055 https://news.ycombinator.com/item?id=12489055
- mrweasel 6y agoI think that’s an edge case, most businesses don’t have the scale where the investment in building a Postgresql setup like that is cheaper than just paying Oracle.
- pjmlp 6y agoI am quite certain that Oracle being an high profile US company, and possibility of export restrictions, also played a role.
- boffinism 6y agousername checks out?
- jasonkester 6y agoRemove the word “relational” from your title and it’s still accurate nearly all the time. There’s maybe a 1% case down at the bottom for cases that had reached a hard limit in production where you could fit a box entitled “read the linked article”
- yamrzou 6y agoI'm not sure PostgreSQL is the right choice for large analytical workloads. Unless you considser TB size datasets less than 1% of the cases? https://news.ycombinator.com/item?id=26186955 https://news.ycombinator.com/item?id=26186955 I'd appreciate if anyone could share their experience with using PostgreSQL for large enough data.
- brianoconnor 6y agoA rough figure : 100gb is still fine with postresql but even then the problem is not the database (query) but rather your pipeline to get data into it. At this point you'll probalby optimize both.
- sradman 6y agoTraditionally, many OLTP operational databases are connected via ETL to an OLAP data warehouse; they are not mutually exclusive. PingCap is the company behind TiDB, an HTAP NewSQL engine that competes with CockroachDB and YugabyteDB; the OP is content marketing for TiDB. TiDB distinguishes itself with HTAP; transparently incorporating OLTP/ETL/OLAP in a single cluster. You have to specify the ETL layer and data warehouse in addition to PostgreSQL to make an apples to apples comparison; that is the core of HTAP positioning. SAP HANA is the poster child for HTAP, a data warehouse with good enough OLTP performance to replace Oracle RDBMS; a single system is used for both SAP app tiers, Business Suite and Business Warehouse. The same value proposition applies to cloud apps. Independent OLTP/ETL/OLAP is still robust and is more modular while HTAP is more tightly integrated and simpler to operate.
- glogla 6y agoWe tried to deploy HANA with BW in a larger-ish life sciences company (200k employees) and so far it was a huge waste of money. It's not actually performing well and the support team had to stop replicating some of our most important data becase "there's too much". I'm not convinced HTAP can actually work - the they OLTP and OLAP works internally seems too different.
- PurpleFoxy 6y agoI have found that for many personal projects, sqlite is more than good enough and the simplification of infrastructure makes it worth it over pg.
- roenxi 6y agoIf you have already made your choice, you do not need a flow chart. If you are uncertain, use postgres.
- sradman 6y agoSQLite is ubiquitous as an embedded database in client-side apps, especially iOS and Android. This ubiquity and simplicity make it a viable alternative to PostgreSQL during development. This is why all popular ORM/QueryBuilder frameworks support SQLite and fits with your appreciation of it. SQLite as an embedded server-side database requires extra work and configuration to make it a viable alternative to PostgreSQL. It lacks good write concurrency and recoverability by default. It is, however, continually improving but gaps remain. Since it does not have a wire protocol, SQLite is rarely connected to a data warehouse via ETL so it does not fit well as an alternative to TiDB.
- wayneftw 6y ago> SQLite as an embedded server-side database requires extra work... No, it depends on the server application. An all-read or read-mostly server requires nothing special. Same for any server that expects a low number of users or database per user(s). Mozilla uses it on servers for documentation sites. You can also run your own Firefox sync server using SQLite.
- forinti 6y agoSQLite is surprisingly good. You can even run a small site or intranet from it. It has the advantage of requiring zero administration. All you need is a file: backups or copies are just a matter of copying the file.
- PurpleFoxy 6y ago
- bullen 6y agoI would go even further and say: +------------+ | Use a file | +------------+ Personally I use JSON over my own async. HTTP (server and client).
- jtsiskin 6y agoimport json json = json.dumps(database) with open("database.json","w") as f: f.write(json) :) - 0 dependencies - easily inspectable and editable with any text editor or cli via jq - backup and diff - language agnostic
- chousuke 6y agoThis "simple" approach will also lead to all kinds of problems quite quickly once you grow past one concurrent user. If you really must use JSON, at least use SQLite in place of open().
- bullen 6y agoA actually use one file per value! I have to partition the ext4 filesystem with type small othervise I run out if inodes before diskspace! Here is what it looks like in action: http://root.rupy.se/link/type/task/847068548006606746 http://root.rupy.se/link/type/task/847068548006606746 The front end: http://talk.binarytask.com http://talk.binarytask.com
- uyt 6y agoI tried this approach for scraping a REST API returning json (on a few tens of thousands of ids). Since it was a long running job with many failures (throttling and connectivity issues), I had to constantly kill and restart the script. I needed to know what ids to skip over on restart but for some reason listing a folder with a few tens of thousands of files is very slow. So restarting takes forever. I also ran into issues with nonatomic file writes where even though a json file was written, it was incomplete or empty. I think if I had just inserted them as json strings into sqlite it would've been more robust? I am okay with losing writes (since I will just redownload them), but it was the incomplete writes and long time to reload the set of seen ids on restart that drove me crazy.
- aaronharnly 6y agoPostgres is our goto, but in particular we have been pretty satisfied lately with AWS so-called “serverless” Aurora Postgres, which allows utilization to scale down during quiet periods, which is very helpful in a lots-of-microservices context, which can otherwise have a pretty high cost floor from having many Postgres DBs that have to be sized to accommodate their peak load. DynamoDB, which I was slow to come around on, is attractive for the same reason, although it only works if the use case fits, obviously.
- eugenejen 6y agoEven you use DynamoDB, you still need to remember to have backupss. I have seen a recent mishap when devops by accident dropped the production tables and restoration requires high IOPs in order to restore it soon enough. The IOPs was not that high for usual use case. But when you wants to reduce MTTR, you need to increase the IOPs (which means $$$, too) Eventually your dropping of the database is consistent. So back it up no matter what.
- droobles 6y agoI decided for my application to use MariaDB because I have some MySQL experience and can get it up and running quickly with best practices, and when the app reaches a certain mass we can migrate to PostgresSQL or a solution like it as we find need for the kitchen sink features. Right now the app is one view and 5 tables, only one table supporting a JSON type column. edit: grammar
- beckingz 6y agoOne of the flowcharts says that TokuDB is an alternative to MySQL. TokuDB is a MySQL engine, not the actual database...
- deepsun 6y agoI kinda disagree with separate branch for "document database" for Mongo. Mongo is a key-value storage, with a thin wrapper that converts BSON<->JSON, and indices on subfields. You can achieve exactly the same thing with PostgreSQL tables with two columns (key JSONB PRIMARY KEY, value JSONB), including indices on subfields. With way more other functionality and support options.
- gqewogpdqa 6y agoNot really true. MongoDB natively supports sharding, multiple indexes, high availability, arrays, sub documents, array traversal, etc - all able to be accessed in your native language with get/set functionality (or via MQL if you want). While PostgreSQL is a really powerful database, the JSON support is really painful to program against.
- tankenmate 6y agoPostgres supports sharding (and partitioning) also with some limitations by expression and multi level as well. HA obviously exists for Postgres in a number of forms, obviously there are limits, but then CAP is an issue for all forms of distribution regardless of DB type. Postgres supports arrays, JSON arrays, JSONB arrays, also indexes on arrays, traversal and looping on arrays, etc. And you get almost all of this via SQL (which you can then map onto your dev language choice via various libraries, personally I don't use ORMs if I can help it, I used SQL in my models as it eases performance tuning. I do realise that a lot of devs don't do or understand SQL, but then I find that a case of the dev not knowing their craft well enough; you need to understand not just logic but data as well). Also more recent version of postgres (12+) have proper support for JSON path queries. Using Postgres with JSON operators isn't that difficult, of course there are some pitfalls and corner cases due to Postgres's architectural choices but then you'll get that with just about any DB choice. And if Postgres's JSON operators aren't to your taste there are JSONPath queries you can use too.
- tehlike 6y agoThis. Postgres as a document db is more capable than mongodb.
- harikb 6y agoFuture generations will look back at us and wonder - 'Wait, they called it NoSQL? why couldn't they come up with a better name to denote what it does do, instead of what it doesn't do'
- gqewogpdqa 6y agoTotally agree. It should have been called “Scalability and availability (even degraded) first, with as much durability and consistency as possible”. After all, that was the motivation behind the “NoSQL” movement, when databases like Oracle just couldn’t scale, even at infinite cost, so things like Amazon went down for Christmas 2006 (?) and Amazon decided to write DynamoDB. A different set of requirements created a different product. And then MongoDB came along, initially to power DoubleClick, after relational had failed there too. Neither one had anything remotely to do with SQL but with the lack of scalability in the backend relational databases - a problem that still plagues single-primary-writer relational databases to this day. And then other non-relational databases came along, like Couch and Cassandra and Scylla - all focused (again) on things other than “SQL vs something else”. And now they are all adding transactions and secondary indexes and all that stuff - but starting from a much more solid base of a distributed architecture rather than the antiquated monolithic architectures of the relational leaders. Those same relational leaders (Oracle, SQL Server, MySQL, and even the current leader on the dance card, PostgreSQL) are all multi-million line monoliths which are incredibly hard to distribute, scale, and make available and operate at scale - much less easy to develop and improve. The name is just so sad
- vosper 6y ago“No rules” would have been a better way to describe how people actually use Mongo. Though, we should remember that Mongo wasn’t the only “NoSQL” database at the time when that term was taking off - there were others competing for mindshare, like Cassandra (dead[0]), HBase (dead), Riak (dead), and CouchDb (dead). [0] Obviously not actually dead - I’m taking a bit of license here. People are still running these things, and maybe they’re occasionally still the right choice
- _benedict 6y ago
- mrmonkeyman 6y agoNo postgres. Is this a joke? SQL Server, really?
- enriquto 6y agoEasy. Most likely you don't need any database.
- spacemanmatt 6y agoThe last time I had a professional task not database-involved was 2007, and that's because my role was technical marketing. When I got started in the 90s, databases were weird entities you'd see in corporate IT settings but not regular embedded/consumer app development roles. Now they're common to embedded applications and ubiquitous in server applications.
- enriquto 6y agoThere is this creepy fad of shoehorning databases into all applications, even when they are not needed at all, and architecting the whole app around the database constraints. It's weird.
- spacemanmatt 6y agos/database/data, and you have actual application design spot-on. Applications are designed around the data constraints. Full stop. Or you are just pretending.
- enriquto 6y agoYes, I agree! My point is that using a "database" for storing your data is often an unnecessary abstraction, and sometimes even pernicious.
- mattashii 6y agoMost applications expect their data to be stored somewhere. To be able to give some guarantee of data consistency, correctness and persistence, that somewhere is usually a database, to alleviate the costs of needing to reinvent the wheel and inhousing the development-, education- and maintenance costs costs of such storage layer.
- Zardoz84 6y agoAnd what happened with Postgresql, MariaDB, Oracle or Graph NoSQL databases ?
- say_it_as_it_is 6y agoThe author omits Postgresql yet includes MySQL. I don't trust the author's expertise, or motives, about what database to use.
- forinti 6y agoAs I also have to manage databases, I also take into careful consideration how much work it is to manage them. So, at one end of the spectrum is SQLite (zero administration) and at the opposite is Oracle (a major PITA). Postgresql/MariaDB lie in the middle.
- indymike 6y agoLeaving out Postgres, and SQLite seem like a pretty efficient way to end at a bad decision. Turns out his article's title is misleading as it is about a HTAP databases.
- CuriousNinja 6y agoThis article is just marketing material for one of their products, TiDB.