I have found that for many personal projects, sqlite is more than good enough and the simplification of infrastructure makes it worth it over pg.

SQLite 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.

> 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.

SQLite 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.

Yep, I think too many people fall in to the trap of thinking they are Google or Netflix when the reality is their site could probably be run on a potato. I wouldn’t use sqlite for a commercial web app but even a good chunk of those would be just fine on it.

If you have already made your choice, you do not need a flow chart. If you are uncertain, use postgres.

I 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.

I wonder what FB is using now, having abandoned Cassandra in 2010.

For 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?

MySQL 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.

There 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.

Vertical scaling and scaling out via replicas is only excusable in a world where sharding isn't a solved problem.

With a bunch of read replicas you have to deal with eventual consistency when you get replication lag as well as the inability to scale writes.

If you shard out horizontally you get write scaleability, and read scalability with much less chance of replication delay. You like likely be reading from the master node most of the time. You also gain a bunch of operational flexibility, and smaller failure domains.

Don't you still have replication delay when sharding horizontally?

How does horizontal sharding help with HA/DR concerns?

Multi-master replication was also a MySQL-only functionality that came built in, while on Postgres you had to pick a third-party solution (I think...)? Did that change in recent times?

I have been looking for how to set up multi-master replication, and as far as I know you still need a third-party solution.

> There are some tools that make horizontal sharding much easier, like Vitess which is MySQL only.

I am just curious, how does Vitess do that?

Apart from the other reasons people have mentioned - the headline “we switched from postgres to $database (for example MySQL) is so unusual it warrants a justification. PG is so good that you really need to be a very niche case for there to be objective reasons to do such a switch. And granted the bigger the company the more intricate and unique its requirements.

I personally advise people to just start with PG and if they encounter very unique requirements that for some reason PG can’t be tuned to - then do the switch.

Legacy. MySQL used to be very popular in the late-90s and early-2000s as part of a broader MySQL+PHP web stack. But that was before PostgreSQL got good enough (including wrt. performance) for that sort of simple use case. Nowadays, there's really no reason not to go for Postgres.

I also think it was because MySQL didn’t require vacuuming so small hosting providers and resellers didn’t have to deal with support issues around that. I know when I was looking around in the late 90s for a database it was a deciding element for me.

I use MySQL. From what I’ve read Postgres is better.

As a developer I’ve used MySQL a lot, installed it myself in local environment and used it with fairly large tables. It’s very reliable and I’m generally comfortable with it. Once it’s running I don’t give it much thought frankly, so I’m not itching to change.

I 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.

Remove 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”

I'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

I'd appreciate if anyone could share their experience with using PostgreSQL for large enough data.

Traditionally, 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.

We 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.

That’s interesting and makes sense. SAP introduced HANA NLS (nearline storage) based on Sybase IQ to address your use case (I think). HANA HTAP is in-memory while IQ works with shared storage clusters so it’s ideal for offloading massive amounts of historical BW data that doesn’t fit in memory.

In-Memory HANA is freakishly good for running OLAP BW queries against fresh data. Column stores like HANA and IQ are both good at this, but to be honest, I don’t know how BW systems were typically configured before HANA/IQ.

I have to admit I'm not super involved with the system in question, and it's possible the team is underfunded or just not that great.

But we definitely ended up in a situation where it was hard to get to some important data, since the team couldn't SLT it to BW because "it's too many transactions" and trying to get it from Sidecar was blowing up, because it's too much data. And that was already with S/4.

But again, I don't know why it was the case. It would have been great and saved us a lot of work if it worked out and we could've done stuff in HANA directly, instead of copying data to different OLAP system daily. Especially now, when everyone is trying to get on the realtime-train.

A 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.

OLAP is kind of a different world - none of the typical Postgres "competition" (like MySQL or Oracle) works really any better in this domain.

There really isn't a very good free one part solution here - so either you pay big bucks for the likes of Google BigQuery or Snowflake so they can become gatekeepers to your own data, or you end up burning a lot of engineering time to get the likes of Hadoop or Spark on K8s or Trino working.

In common usage does OLAP automatically imply data that is too large for a single node to handle? I'm wondering if a lot of OLAP workloads couldn't be handled with some parquet files (or some other column based storage)?

I realize parquet is used by the hadoop/spark ecosystem, but do you really need those systems? I'm thinking that a lot of companies reach for a hadoop cluster when some parquet files in a regular for system would be much simpler. I've done things like this and in my experience it works quite well. But only for personal projects.

No, of course you're right, I'm just bit too comfortable in my own domain.

For smaller data (and here that would mean few hundreds GB which is pretty huge by normal standards) OLTP databases would cover you pretty well. Oracle has some fancy bitmap indexing, MS SQL even has columnar tables, and pretty much anything has partitioning.

Parquet is really cool - especially there's like 30 x ratio between "generic oracle table" and gzip compressed parquet file so scan time are really in a different world.

But by itself parquet doesn't solve a whole lot - where are the files stored and what scans them? What happens if someone is updating the files while someone else is reading them?

Yeah the storage size was a real clincher for me, as much as I appreciate a SQL database.

Re: concurrent reading and writing, for my use case the files are immutable so that isn't a concern. But I agree, I don't think pure parquet is a good fit there.

Postgres 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.

Even 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.

I mean for straightforward usecases if Postgres will work, CockroachDB isn't a bad choice to get used to early on.

I didn't see Postgres in the article?

Its conspicuous absence alongside so many obscure alternatives is baffling.

my 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.

Depends, 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.

I 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.

IDE development tooling straight out of Oracle without going to hunt for third parties, including proper debugging of stored procedures with everything that I expect from a modern language.

Ada flavoured PL/SQL.

The Java and .NET drivers, with support for advanced stuff like distributed clustered transactions and direct mappings of UDTs into source language types.

Support for nice stuff like OLAP cubes, bare metal databases and APEX provides a nice way to quickly build database frontends.

As mentioned I am familiar with PostgreSQL, and honestly other than a couple of SQL extensions that are easier to use, I don't see much value when I compare everything that is on the box, and usually at the project scale I work on (just yet another cog on the enterprise wheel), license costs aren't the biggest hurdle to care about, there are other pain points where money matters more.

We 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.

I 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

I 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.

I am quite certain that Oracle being an high profile US company, and possibility of export restrictions, also played a role.

I don't know for sure but my guess is that Oracle gets sold to managers in large orgs where such decisions are not necessarily made by engineers.

Is 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.

There 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.

+1 for aiven.io, works fantastically well and you don't need a DBA any more.

If 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.

I would go even further and say:

  +------------+
  | Use a file |
  +------------+

Personally I use JSON over my own async. HTTP (server and client).

import 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

This "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().

A 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

The front end: http://talk.binarytask.com

- occasionally accidentally truncates the database

If you're going to do this, at least write to a temporary file, fsync the file, rename into place, and fsync the directory. But I recommend SQLite any time someone is tempted to write these lines.

I 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.

MariaDB is fine too

A 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-1...

Do 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.

I 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

username checks out?

Nope, it's a java/javascript thing.

Yeah, I was disappointed when your comment history wasn't just a bunch of Postgres recommendations

This flowchart is difficult to screw up.