8 ms·
I believe I can simplify the flow chart for "How to efficiently choose a relational database" +----------------+ | | | Use PostgreSQL |
by pg_bot 6y ago
I 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