22 ms·

Ask HN: It's 2023, how do you choose between MySQL and Postgres?

• MrThoughtful 3y ago
  By choosing SQLite. No server process and a single file per DB which I can put wherever I like.
  • mort96 3y ago
    I like SQLite. But I really wish its default behavior wasn't to simply allow garbage data into the database. If I have an int column, don't let me accidentally store a string.
    • eesmith 3y ago
      https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html "In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table. ... The STRICT keyword at the end of a CREATE TABLE statement is only recognized by SQLite version 3.37.0 (2021-11-27) and later. sqlite> create table x (a int); sqlite> insert into x values ('hello'); sqlite> select * from x; hello sqlite> drop table x; sqlite> create table x (a int) strict; sqlite> insert into x values ('hello'); Runtime error: cannot store TEXT value in INT column x.a (19)
      • mort96 3y ago
        Yeah, I know about that, and I'm doing that on all my tables these days. It's just sad that the default behaviour is to allow garbage data, and that if you ever forget to put 'strict' on your tables you'll have a perfectly functional application with no sign that anything is wrong until you suddenly find corrupt data in your database.
    • euroderf 3y ago
      create table strict
  • lisasays 3y ago
    SQLite is great for its scope - but not in the same class as a full-fledged RDBMS.
    • MrThoughtful 3y ago
      Can you give a specific example of what you are missing when using SQLite?
      • endisneigh 3y ago
        Multi writer, access through the internet without 3rd party software, etc.
      • mort96 3y ago
        At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's? Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.
        • justinclift 3y ago
          Just to point out, there are now SQLite replication and various "distributed database" projects which seem to work fairly well. They're probably not as battle tested as the PostgreSQL ones, but they are around, have users, and are actively developed. The ones I remember off the top of my head: * https://litestream.io https://litestream.io * https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite <-- more of a "distributed database using RAFT" type of thing * https://github.com/canonical/dqlite https://github.com/canonical/dqlite
          • otoolep 3y ago
            rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
  • jjav 3y ago
    > By choosing SQLite. It's a good reminder to give some thought to whether one actually needs MySQL|Postgres. If not, SQLite is the way to go. Most of my code that uses a DB is using SQLite. But obviously, if you actually need MySQL|Postgres then SQLite is not an option.
• revskill 3y ago
  It's OracleSQL instead of MySQL now ?
• perf_eng 3y ago
  Most of the answers here are choosing Postgres over MySQL, But there are cases where MySQL is far better than Postgres. 1. MySQL's Replication is simple, more reliable and takes up less disk space than Postgres. 2. Ability to control the optimization of queries with Optimizer hints. At scale, to lower tail latencies you will definitely need to help the query planner since you have some domain knowledge about your app and queries that the planner doesn't have. Yes, In PG you can use pg_hint_plan, but still it is not an official solution. 3. MySQL has better connection scaling without a separate component like pgBouncer. Also, cost of connection creation is lower due to MySQL's thread per connection model vs Postgres' process per connection model 4. Collations suck in Postgres since it decided to depend on the OS for collation support. Version updates of other packages (glibc) or the OS can corrupt your database due to this collation mess. The fix for this is to use ICU collations but even they have multiple limitations (You can't specify a non-deterministic collation at the database level etc) 5. Postgres's MVCC implementation is the most inefficient among modern databases [1] Not only is it inefficient, it causes maintenance headaches with managing and tuning auto_vaccuum. It also causes increased disk usage, write amplification (entire row is rewritten on each update and index is updated even if the column being updated is not indexed) and increased bloat due to multiple dead copies of the rows. If you use wide tables (100s of columns) with updates (not even high frequency but moderate updates), MySQL will be far better. Heap-only-tuples (HOT) will only be helpful if your rows are narrow and there is sufficient free space in the page of the row being updated. For wide-tables, most often this is not the case, so even HOT won't be much helpful. Almost everyone would have read Uber's story of moving to MySQL [2], but even if you are not at Uber scale, Postgres's MVCC implementation, and its associated pains are better avoided. Unfortunately attempts to fix this in Postgres (zheap) have been long abandoned. 6. In MySQL (InnoDB), the rows are physically ordered by Primary Key. This improves cache-hit ratio and performance if most of you queries involve selecting or ordering by the PK, or a prefix of the PK in case of composite PKs. So, if you need performance at scale, reliable replication for HA, less maintenance, good connection scaling, go for MySQL. If your app depends on a extension or FDW that only Postgres has, then choose Postgres. Often, people may complain about some obscure SQL syntax that does not work in MySQL or that Postgres correctly implements but mostly there will be an alternative you can use in MySQL. [1] - https://ottertune.com/blog/the-part-of-postgresql-we-hate-the-most/ https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... [2] - https://www.uber.com/en-US/blog/postgres-to-mysql-migration/ https://www.uber.com/en-US/blog/postgres-to-mysql-migration/
• white_dragon88 3y ago
  [dead]
• mgl 3y ago
  We choose Postgres for extensibility and stability :)
  • david927 3y ago
    I was going to make a similar joke: you look at them and choose Postgres
• chunk_waffle 3y ago
  I dunno if this is still true but a couple of years ago MySQL was cheaper on AWS (RDS/Aurora) than Postgres.
  • maxmorlocke 3y ago
    For a typical db.t3.xlarge instance, you're talking about 29c/hour vs 27.2c per hour. That's $157.68 as the total difference for one year's runtime, when the whole instance cost for postgres would be $2540.4 for the year, or about 6%. The larger the machine, the closer to parity. Given the absolutely small difference, I hope this isn't the dividing line in any commercial project.
    • chunk_waffle 3y ago
      Again, I don't still know if this is the case, but you could use smaller instances with MySQL aurora than with Postgres, given the way our application worked it would have made a big difference for us if we had used mysql. RDS/Aurora was our most expensive resource so we were looking at ways to cut that cost down and mysql was one option (though the way the app worked and the extensions that it relied on made it not a possibility.)
• jstx1 3y ago
  Also interested in the responses, not because it seems like a close decision but because I would pick postgres by default for anything (anything that isn't simple enough to be done in sqlite).
  • majestic5762 3y ago
    Same.
• tough 3y ago
  Pick postgres unless you have a good reason not too?
  • cryptonector 3y ago
    And even then you pick Postgres.
    • tough 3y ago
      Can't really loose with postgres, I concur
• ac2u 3y ago
  You choose postgres.
• mixmastamyk 3y ago
  1) The choice is Postgres if you care about your data at all. 2) Yes, if you are already HUGE and have requirements on Vitesse then by all means use it. If so, you are not asking this question—see #1. 3) It's a blog or something where it doesn't matter, use a static site generator.
• bellBivDinesh 3y ago
  MySQL for the quick and dirty and Postgres for anything else
  • itake 3y ago
    Why is mysql better for quick and dirty? I feel like pg extensions offer a lot more "dirtiness" running inside pg than mysql has.
    • weaksauce 3y ago
      yeah that's a weird take. if you want quick and dirty you use sqlite and if you need something more you go with postgres. some replication things are nicer in mysql apparently but postgres is the better option for most workflows
      • itake 3y ago
        Are there easy offsite backup solutions for SQLite? The easy deploy tools (digital ocean apps, doku, beanstalk, heroku, etc) destroy the instance (losing state). You need to configure an offsite backup tool for your db or you have to manually setup a server to live forever. Having a separate db machine is so much easier to setup and plays better with the diy tools.
        • weaksauce 3y ago
          if you're working with ephemeral services like those then no you'd never use sqlite because they are temporary. in my mind quick and dirty being a backing store for something like a local script where you wouldn't want to spin up a full server for... or a small webserver that you are running locally. if you are going to do a proper website you'd ideally want to have something with more flexibility and data guarantees. sqlite is grand for a specific sort of read heavy or single access writes workflow.
• iamwil 3y ago
  The difference is not significant enough to matter for most projects, esp just starting out. Hence, I mostly choose Postgres, since I don't like Oracle as a company very much.
  • deleted 3y ago
    [deleted]
  • dylan604 3y ago
    Whenever I see MySQL, my brain automatically sees MariaDB. What is this Oracle thing you speak of ;-)
    • evanelias 3y ago
      MySQL and MariaDB have diverged quite a bit. I recently wrote a roundup of just the differences in table functionality and DDL, and despite keeping the post focused to that relatively-narrow topic, the list of differences is getting VERY long: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schema/ https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schem...
      • dylan604 3y ago
        >MySQL and MariaDB Again, what is this thing you are trying to compare? I just see MariaDB and MariaDB =) However, because of my blindness to actual MySQL, I have totally not paid attention to any differences between the two. I guess "drop in replacement" isn't actually true any more. Thanks for the info
• gardenhedge 3y ago
  If Postgres was that much better than MySQL then you would expect to see exact reasons on why to pick it. Every comment so far has not listed any reason.
  • mort96 3y ago
    Ok, here's one: When you give MySQL invalid data, its standard behavior is to just silently store garbage in your database in many cases where PostgreSQL would've told you that your data is invalid. MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions. People aren't providing strong reasons because the question wasn't "what are some objective reasons for picking one over another", but "how do you pick between them". People are simply answering the question OP asked, and a lot of people's process is simply to pick PostgreSQL.
    • CaveTech 3y ago
      Hasnt been the case for a few major versions, unless you want to anthropomorphise your db and and hold it accountable for past behaviour.
      • ok_dad 3y ago
        > unless you want to anthropomorphise your db and and hold it accountable for past behaviour No one is holding the literal bits that make up the database executable accountable here, they are indicating they don't trust the devs of MySQL/MariaDB to do a good job. Whether or not that is an accurate view on their part is arguable, but it's pretty clear from context that they don't think that several if/else statements had it out for them.
    • bakugo 3y ago
      > MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions. Unicode generally "just works" if the charset in use is utf8mb4. As of MySQL 8.0, this is the default.
• Doctor_Fegg 3y ago
  For anything involving location, choose Postgres because PostGIS is just so good.
  • frizlab 3y ago
    Good to know! I’m starting a project that will use location, and I chose Postgres, I’m happy to hear this :-)
  • solarkraft 3y ago
    PostGIS is somewhat of a standard interface for geolocation data and one of the main interfaces of QGis, the other being based on SQLite. It's kind of cool how powerful it is, being a "real" database instead of some more contrived format. My adventures with QGis+PostGIS have also led me to discover the fun fact that even setting the TLS mode to "enforce" may enforce that you're using TLS ... but not that the certificates are correct. Silly. It also won't use the system's store when set to "full". Oh well. By the way: I find Railway to be a great home for low-use Postgres/PostGIS databases. You basically pay for operations and the free tier is totally fine for semi-casual use (about 1-2k entities) with a few people from across the pond.
• mthoms 3y ago
  Previously - https://news.ycombinator.com/item?id=35599118 https://news.ycombinator.com/item?id=35599118 https://news.ycombinator.com/item?id=12166585 https://news.ycombinator.com/item?id=12166585
• adoxyz 3y ago
  Choose whichever one you/your team is more familiar with. Both are battle-tested and proven and will likely scale to whatever needs you have.
  • TehShrike 3y ago
    This is the correct answer. Whichever one you start out with, you will be annoyed if you switch to the other one 5 years later. I started out with mysql, and when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?). But when postgres folks grouse about stuff in mysql, I'm usually nodding along and saying "yeah, that would be nice". They're both great options. If anybody on your team is already an expert at one of them, use that one.
    • robertlagrant 3y ago
      > when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?) How long ago was this? :)
      • TehShrike 3y ago
        3 years ago. From this comment thread https://news.ycombinator.com/item?id=35908169 https://news.ycombinator.com/item?id=35908169 I infer that Postgres still doesn't support collations.
        • robertlagrant 3y ago
          Oh - is this ci collations, not collations in general?
          • TehShrike 3y ago
            A lot of the collations are useful in different circumstances – but the CI ones probably come up the most often, and are necessary to store email addresses in a way where you store the case information that the user typed in, but can do case-insensitive lookups against in the future.
• m0llusk 3y ago
  PostgreSQL is a community thing and MySQL is Oracle. Maybe make some basic benchmarks for comparison?
• azurelake 3y ago
  MySQL is still ahead operationally (no vacuum, group replication, gh-ost, optimizer hints, etc.). I would choose it unless one of the Postgres extensions is a killer fit for your project.
• sproketboy 3y ago
  [dead]
• NuSkooler 3y ago
  PostgreSQL every time, unless you have a specific reason, or as already pointed out, you're sure you don't just need SQLite. PSQL in my experience has vastly better tooling, community, and is ahead of the curve tech wise. Same with extensions availability. Or perhaps you need to move away from it to say CockroachDB, or similar which is much easier.
  • grzm 3y ago
    nit: psql is the command line client. postgres or pg are the more common shortenings of PostgreSQL. https://www.postgresql.org/docs/15/app-psql.html https://www.postgresql.org/docs/15/app-psql.html
• deleted 3y ago
  [deleted]
• CuriouslyC 3y ago
  The only instance where I'd choose mysql over postgres is if your database needs are very simple, but you need to be able to scale hard, and your devops aren't skilled enough to manage an advanced postgres setup.
• duiker101 3y ago
  Most answers seem written by fanboys rather than legit answers. I would say go with what you know and are most comfortable with. You are more likely to get the better outcome.
  • chunk_waffle 3y ago
    This. I've heard countless times that Postgres is better and I've watched talks where they show how loosey-goosey MySQL is with some things but I know how to backup, restore, tune, secure and replicate MySQL. I grok it's permissions in depth more than I ever have with Postgres and I've even written a mysql plugin in C so I have that in my toolbox if I need it. So I'd by default, usually go with MySQL (or in some cases SQLite.) but if I didn't have to administer or maintain it, and someone else was handling that I think I'd be fine with Postgres too.
  • deleted 3y ago
    [deleted]
• harryvederci 3y ago
  I flipped a coin, it landed on its side so I went with SQLite.
• dijit 3y ago
  There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone and everyone to avoid Oracle as if it has herpes. There are a lot of historic problems with MySQL accepting invalid data, committing data even when there are constraint issues, and having very poor transactional isolation, I am not sure if these have improved. Truthfully, the only benefits you gain from using MariaDB or MySQL are: * Memory tables * Having inconsistent replicas (which can be useful when you want your downstream to have less data than your upstream and you know it won’t get updated.)
  • noodlesUK 3y ago
    I think that the only reasons to choose MySQL (or Maria) over Postgres for a new project are operational. Postgres is probably the better database in almost all respects, but major version upgrades are much much more of a pain on Postgres than on almost any other system I have ever used. That being said, I would choose Postgres pretty much every time for a new project. The only reason I would use Maria or MySQL would be if I thought I later would want to have something like Vitess, for which I think there isn't really an equivalent for Postgres.
    • dijit 3y ago
      > but major version upgrades are much much more of a pain on Postgres than on almost any other system I have ever used. This is a thread comparing MySQL and Postgres and your claim is that postgres is harder to do major version upgrades than anything you have used?? Context is important here, have you honestly actually upgraded a MySQL node? It’s a lesson in pain and “major” version changes happen on minor versions, like the entire query planner completely trashing performance in 5.6->5.7 Postgres has two forms of updates: 1) in place binary upgrade. Fast, clean, simple, requires that you have the binaries for the old and the new database. 2) dump/restore. Serialise the database into text files, load a new database and deserialise those files into it. Slow, but works flawlessly & consistently with relatively low danger. MySQL can only do option 2. You can sort of fake an “update” by abusing the fact that MYSQLs replication offers no guarantees, so you can make a new server a replica; then roll over. But it is impossible to know what data was lost in that transition and MySQL will happily continue without ever telling you. I have experienced this behaviour in large e-commerce retailers. MySQL was very popular for a very long time and I am intimately aware of operational best practices and how they are merely patching over an insane system.
• vermaden 3y ago
  Its simple AF - I just always pick the well proven PostgreSQL database. ... if that is too big I use SQLite.
• MrResearcher 3y ago
  Postgres doesn't have type hints and it might create a false impression of robustness until it messes up table statistics and does FULL SCAN against a table with millions of rows ignoring all indicies. It happens super rarely though, so if you run anything critical, you'll probably be down only for a few hours once a year or two. Be ready for this to happen though. Apart from that (and noticeably higher memory consumption), Postgres is most likely preferable.
• skunkworker 3y ago
  Postgres for anything with a single database size < 4TB. But if you need something that can handle 100TB+, go Vitess(mysql compatible).
  • winrid 3y ago
    Why not Citus?
  • jhas78asd 3y ago
    What’s the reason though for Vitess? Postgres supports tables up to 32TB but hopefully you’re splitting them up using declarative partitioning in one or more ways before that. If you have tables that are smaller than a TB and a large memory DB (>1 TB RAM) Postgres should run ok right? I’d also imagine you’re splitting up your database into multiple databases and multiple instances (the writers) well before that as well right?
• erulabs 3y ago
  Use MySQL if you're expecting to have to do large operational database tasks re: migrations, maintenances, with no ability to go offline. gh-ost, percona-osc, the new INSTANT DDL stuff, is all quite far ahead in MySQL-land. Additionally, Vitess and Planetscale are making huge strides in MySQL performance. There are more people and guides in the world to help recover even the most mutilated of MySQL databases. MySQL gets more love in extremely large enterprise-level organizations, and it shows. Use Postgres if you need some of the quite-good extensions, most notably PostGIS, or if you just want things to work; most documentation will be postgres flavored. Postgres gets more love from web-developers, and it shows.
  • fzeindl 3y ago
    This is wrong. MySQL does not support transactional DDL, so you cannot run migration and abort them in the middle. Always use postgresql. It's more logical, more extensible, saner, supports many extensions and is more predictable. MySQL is inconsistent crap, that trades away consistency, correctness and stability for a little bit of performance in standard use cases. Do yourself a favor and always use postgreSQL. I switched 15 years ago and never looked back. Have done 15-50 projects since in psql.
    • deleted 3y ago
      [deleted]
    • amflare 3y ago
      Spoken like someone who switched 15 years ago and never looked back.
    • WJW 3y ago
      With a sane migration tool like pt-osc or gh-ost you can absolutely abort in the middle. What's more, you can pause in the middle or even slow down in the middle based on arbitrary logic (ie, pause migration if replication delay rises above a certain value). Postgres is nice and transactional DDL has its place but postgres stopped halfway through IMO. Vanilla Postgres > vanilla MySQL, but the migration story of MySQL + tooling is so far beyond Postgres + tooling that it's not even funny. That said, if you don't expect to have tables with 100m+ rows, even vanilla postgres will be good enough.
• MangoCoffee 3y ago
  pick the database that you are most familiar with
• trympet 3y ago
  The Postgres query optimizer is more powerful than the MySQL query optimizers [1]. It generally scales better for OLTP. Also tons of extensions that can accelerate your workload. [1] - https://ieeexplore.ieee.org/document/9537400 https://ieeexplore.ieee.org/document/9537400
  • spudlyo 3y ago
    It's also more opinionated than the MySQL query optimizer, in that you can't give it hints to prevent it from making a horrible mistake.
    • jhas78asd 3y ago
      There is a way to provide some type of planner hints https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html
• usrbinbash 3y ago
  > What sort of functional requirements would cause you to choose one over the other? Simple: I don't like having headaches. Therefore, I chose postgres.
  • dylan604 3y ago
    Not even funny or clever, and definitely not helpful to the actual question being posed
• pierat 3y ago
  Friends dont let friends use #Horracle software. That includes VirtualBox, MySQL, Horracle Cloud. Just step back. Walk away. Do not pass go, do not collect $20000 lawyers fees for unintended actions.
  • za3faran 3y ago
    That's quite silly. VirtualBox is great, and so is MySQL. They're also both OSS, so no lawyers in the sense you're implying.
    • pierat 3y ago
      Then you don't know what you're talking about, and are ignorant of the risks of using VirtualBox. That VirtualBox extension pack? That aint free... well, it is for personal use only because they're not shaking individuals down. However, Oracle watches what domains download that extension pack, and sues companies when too many employees download it. You can see that in this reddit thread: https://www.reddit.com/r/sysadmin/comments/d1ttzp/oracle_is_going_after_companies_using_virtualbox/ https://www.reddit.com/r/sysadmin/comments/d1ttzp/oracle_is_... And this article also addresses the risks of dealing with Horracle. And a company I worked for had dealings with them as well. Again, Horracle played dirty and did bullshit, except over Horracle DB itself.
      • za3faran 3y ago
        You're basically saying something that isn't free, isn't free.
• test6554 3y ago
  I've been using MariaDB (MySQL) as a hobbyist for years. I just set up a couple myqsql servers with phpmyadmin on Raspberry PIs and use them for local development. Basic crud apps, etc. I've always assumed that PostgreSQL is a step up, but never really bothered to look into what I get for the effort. Do I really get anything if I'm not trying to make apps at scale?
  • eqvinox 3y ago
    > I've always assumed that PostgreSQL, but never really bothered to look into what I get for the effort. You're making a (mistaken) assumption that Postgres giving you a "step up" means that you also have to put in more effort. You don't, at least not in my experience. Both are database servers with a bunch of install & setup. There's phppgadmin if you want an 1:1 replacement for phpmyadmin (no opinion on these, haven't used either). Postgres just gets you farther if you need to at a later point. I would recommend you swap out mysql for postgres on your raspis. You're gaining experience on one of the two. But experience on Postgres seems to be more useful and valuable (cf. rest of the HN comments), for the same cost of your time.
  • 12907835202 3y ago
    Same position. There's so many things I want to learn I'm not sure postgres is such a step up from MySQL that it's worth being at the top of the list.
    • tmountain 3y ago
      If you’re not at the level of detail to care about transaction isolation levels, clustered indexes, and the implementation details of replication, you can invest your time elsewhere. For certain use cases and workloads, the differences can matter a lot, but many developers are just looking for a backing store for their CRUD app and in that case, either is likely fine.
• bratao 3y ago
  One big factor that keep us on MySQL is the MyRocks engine. We have huge databases with billions of rows. The MyRocks enable the use of it with heavy compression, that PostgreSQL can´t handle it, as it is much slower and uses 30x more disk usage, even with heavy TOAST tuning and/or ZFS compression.
  • eqvinox 3y ago
    To be fair, at the scale of your use case there I really hope you have a proper DBA who understands multiple database systems and their details, and is able to make the best choice for your setup. (At some point the commercial and/or oddball SQL servers become an option too…) For everyone else who's in most cases not even stuffing a million rows into their database… just stick with Postgres :)
• bags43 3y ago
  If you are using .NET then Postgres might be better choice. (much better support for drivers and default ORM). If you need replication go with MySQL.
  • vp8989 3y ago
    https://mysqlconnector.net/ https://mysqlconnector.net/ is very good. Dapper and ^ that works very well IME. Agreed about replication.
• OrvalWintermute 3y ago
  Friends don't let their friends choose Mysql :) A super long time ago (decades) when I was using Oracle regularly I had to make a decision on which way to go. Although Mysql then had the mindshare I thought that Postgres was more similar to Oracle, more standards compliant, and more of a real enterprise type of DB. The rumor was also that Postgres was heavier than MySQL. Too many horror stories of lost data (MyIsam), bad transactions (MyIsam lacks transaction integrity), and the number of Mysql gotchas being a really long list influenced me. In time I actually found out that I had underestimated one of the most important attributes of Postgres that was a huge strength over Mysql: the power of community. Because Postgres has a really superb community that can be found on Libera Chat and elsewhere, and they are very willing to help out, I think Postgres has a huge advantage over Mysql. RhodiumToad [Andrew Gierth] https://github.com/RhodiumToad https://github.com/RhodiumToad & davidfetter [David Fetter] https://www.linkedin.com/in/davidfetter https://www.linkedin.com/in/davidfetter are incredibly helpful folks. I don't know that Postgres' licensing made a huge difference or not but my perception is that there are a ton of 3rd party products based on Postgres but customized to specific DB needs because of the more liberalness of the PG license which is MIT/BSD derived https://www.postgresql.org/about/licence/ https://www.postgresql.org/about/licence/ Some of the PG based 3rd party DBs: Enterprise DB https://www.enterprisedb.com/ https://www.enterprisedb.com/ - general purpose PG with some variants Greenplum https://greenplum.org/ https://greenplum.org/ - Data warehousing Crunchydata https://www.crunchydata.com/products/hardened-postgres https://www.crunchydata.com/products/hardened-postgres - high security Postgres for regulated environments Citus https://www.citusdata.com https://www.citusdata.com - Distributed DB & Columnar Timescale https://www.timescale.com/ https://www.timescale.com/ Why Choose PG today? If you want better ACID: Postgres If you want more compliant SQL: Postgres If you want more customizability to a variety of use-cases: Postgres using a variant If you want the flexibility of using NOSQL at times: Postgres If you want more product knowledge reusability for other backend products: Postgres
  • justin_oaks 3y ago
    The license issues are relevant. I've had to convert an application from using MySQL to using Postgres because we wanted to distribute copies of it. The Postgres license allowed us to distribute it without running afoul of GPL, which MySQL uses. Even distributing the MySQL JDBC driver with your software means that you need to provide source code of the Java app that uses it. I used MySQL by default for years. If you use it for internal-only apps then the GPL doesn't matter. But once you cross the line into sharing what you've built with others, MySQL is a non-starter unless you want to GPL everything you are sharing.
• spudlyo 3y ago
  This is like asking how you'd choose between Emacs and Vim, Mac and PC, Monoliths and Microservices, Functional and Object Oriented .. you're likely going to elicit a lot of passion and not a ton of objective information. For most applications, either choice is going to be just fine. Use what your team has the most experience with. If you have no experience, try them both out and go with whatever you're most comfortable with.
  • 0xf8 3y ago
    Nah, trivially the correct answers are: Vim, *NIX (so Mac), monoliths, and lambda calculus all the way—FP! jk, but FWIW I think sometimes, in rare instances, there does exist a pretty unequivocal consensus “right answer” to this sort of question … maybe such as like: Git vs any other distributed VCS ?
• gmac 3y ago
  Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime. But I just got the sense that MySQL was developed by people who didn’t quite know what they were doing, and that people who really did know what they were doing weren’t ever likely to be attracted to that to fix it.
  • kiernanmcgowan 3y ago
    Having used postgres for the past decade, I tried MySQL for a side project to see whats changed with it. The sad answer is that it feels like nothing has changed - Oracle seems to have let what used to be a core technology of the industry languish. I'm sure there are use cases where MySQL will be the better choice over postgres, but the future for the stack looks bleak.
    • johnny22 3y ago
      i think one is also referring to mariadb here and not just mysql. Maybe that's better enough? I wouldn't know, I just go with postres.
    • dunno7456 3y ago
      Creating a series of connections very quickly is cheaper in MySQL and MariaDB than in PostgreSQL. Typically, a connection poller is used before PostgreSQL to support connection scalability. I'm not sure if there has been a recent breakthrough that has changed that. I think that still applies today. Correct me if I'm wrong.
      • gabereiser 3y ago
        You can create a series of connections in postgres just as fast. The connection pooler you are referring to is when you put pgBounce or pgPool in between your pgdb and your client software to expand beyond the physical limits of connections and optimize clustered architectures. MySQL at scale is replication only. A few commercial offerings for MySQL like planetscale have brought MySQL into the 21st century. Postgres has a couple ways of clustering, sharding, scaling, beyond your Wordpress database.
• muhammadusman 3y ago
  piggybacking on this: does anyone know a Postgres alternative to PlanetScale?
  • fastest963 3y ago
    We use YugabyteDB and have had a lot of success with it. We deploy it across 5 continents and it's remarkably performant.
  • gmac 3y ago
    https://neon.tech https://neon.tech
  • dravita 3y ago
    CockroachDB is PostgreSQL compatible and is distributed SQL supporting single-region, multi-region and multi-cloud deployments.
    • xdanger 3y ago
      It's PostgreSQL protocol compatible, it's not PostgreSQL compatible. There are many differences. For more compatibility, check out YugabyteDB.
• toomuchtodo 3y ago
  Always Postgres. It will be more painful to migrate in the future versus starting with it.
• deleted 3y ago
  [deleted]
• deleted 3y ago
  [deleted]
• entropicgravity 3y ago
  If you're a gigacorp you might have reasons to go with a customized version of MySQL otherwise pick Postgres.
• didip 3y ago
  Always PostgreSQL. Unless if MySQL protocol is absolutely needed.
• kgwxd 3y ago
  I don't use either but I don't think I've seen MySQL mentioned in any meaningful way in almost a decade at this point. If forced, knowing only that, I'd choose postgres.
• tobyhinloopen 3y ago
  Am I the only one who thinks postgresql’s timestamp and timestamptz types are incredibly stupid? I just want to either save a local date and time, or an utc timestamp. Postgresql’s timestamp(tz) types do neither and both at the same time.
  • claytonjy 3y ago
    I've avoided `timestamp` and haven't needed localized times stored in the DB, but in what way is `timestamptz` not exactly the utc timestamp you expect?
    • tobyhinloopen 3y ago
      It converts the timezone automatically, which I do not want
• donatj 3y ago
  Unpopular opinion on HN apparently, but MySQL - It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. - Maintenance is simpler and far less necessary in my general experience. - Replication is simpler and more reliable. - You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend. That said, I wouldn't run Oracle MySQL. I opt for MariaDB on smaller projects and AWS Aurora MySQL for larger projects. Aurora scales insanely well, and replication lag is almost non-existent. In my general experience MySQL was always significantly faster but it's been a number of years since I've worked with Postgres and the comments here seem to indicate that that may no longer be the case. YMMV
  • rocho 3y ago
    I had the misfortune of inheriting a MySQL 5.6 database and I had to manage it for a year and a half. It wasn't very big (less than 100GB), but: - for some reason it hang periodically and had to be restarted during the night for no apparent reason - compared to Postgres, the tooling is garbage (both for backups and even more for general database administration) - essential features are missing, the most important one of which, for me, was proper CSV import/export. CSV-related functionality is so broken and terribly inconvenient to use. In a specific case I had to write a program to export millions of records manually since MySQL could not generate correct CSV export due to some columns containing text with special characters, quotes, newlines. Any combination of the export parameters ("ENCLOSED BY", "ESCAPED BY" and all the other garbage options) failed in one way or another. I even tried to use non-standard characters like \x15 and \r for column and row separation but even that failed. With Postgres, "with csv header" is simple and works every time. I also managed bigger Postgres databases (up to tens of terabytes) and never had the issues I encountered with MySQL.
  • droobles 3y ago
    samesies
  • EamonnMR 3y ago
    I would disagree on maintenance being simpler. I have never had Postgres randomly munge a table and require me to run a command to fix it.
• web3-is-a-scam 3y ago
  Easy. Always postgres. I also use the same logic applied to document databases. Mongo or Postgres? Postgres Also pub sub. Postgres or redis? Postgres Use postgres until it’s not technically feasible to use it anymore.
• endgame 3y ago
  https://web.archive.org/web/20211206040804/https://blog.sesse.net/blog/tech/2021-12-05-16-41_leaving_mysql.html https://web.archive.org/web/20211206040804/https://blog.sess... From a former MySQL developer: > let me point out something that I've been saying both internally and externally for the last five years (although never on a stage—which explains why I've been staying away from stages talking about MySQL): MySQL is a pretty poor database, and you should strongly consider using Postgres instead.
  • deleted 3y ago
    [deleted]
• snowski3 3y ago
  just pick MySQL. If you want 100 half-baked non-production-ready features pick Postgres.
• DiabloD3 3y ago
  Why would I choose MySQL in any year? There is no context you can provide to this question where I wouldn't always choose Postgres.
• herpderperator 3y ago
  I find PostgreSQL permission management quite convoluted. In MySQL it is simple to query for what grants a user has, but in PostgresSQL you need to write 100 lines of SQL to do the same... and you can't run \du and other commands without psql. Why couldn't they just come up with `SHOW` shortcuts that work in any SQL client?
  • jhas78asd 3y ago
    You can likely get the SQL for a meta command, and you could run the SQL from your preferred client if you don’t use psql. Here is one example: https://dba.stackexchange.com/a/131031 https://dba.stackexchange.com/a/131031 I also highly recommend investing in psql skills though if you are a Postgres user.
    • arp242 3y ago
      Yes, that's the "100 lines of SQL to do the same" the previous poster mentioned (obviously they were exaggerating a bit and it's less than literally 100 lines, but it's pretty complex).
• tdy721 3y ago
  Easy: SQLite
• milesward 3y ago
  You choose Postgres.
• GiorgioG 3y ago
  It's pretty easy, it's always Postgres. MySQL has been awful at every place I've had the misfortune of using it.
• api_or_ipa 3y ago
  Sigh. Hoping dang will swoop in and close this flame war...
• racl101 3y ago
  Can I actually work with it locally and on a production server?
• api_or_ipa 3y ago
  Practically speaking, they're very similar. Mysql and Postgres differ in their approaches to replication & clustering, which can have a big impact to high availability, high volume database configurations.
  • doyourhomework 3y ago
    If I need disk encryption for very basic security in my production database, I know which one I choose, and it doesn't start with a P.
• jfb 3y ago
  What's to choose? Postgres all the way.
• gibsonf1 3y ago
  By going with Apache Solr instead
• geenat 3y ago
  I know it seems dumb, but postgres really needs to add the simple developer experience stuff like: SHOW CREATE TABLE; SHOW TABLES; SHOW DATABASES; SHOW PROCESSLIST; CockroachDB added these aliases ages ago.
  • spacedcowboy 3y ago
    This. For anything at home, I would use MySQL just for those things. The psql client feels very primitive by comparison to me - even though it isn't.
    • tbarbugli 3y ago
      I highly suggest investing time learning psql, autocomplete works great and it has a ton of useful slash commands. \d for instance shows you the list of tables. Awesome tool
      • spacedcowboy 3y ago
        I don't need it often enough to invest the time. I generally set up a database as backing store to some project, fiddle with it until I'm happy it's working at the scale/performance I want, and then move on to something else. During those few weeks I'm actively using the database on the project, I can either get frustrated beyond belief with the CLI for Postgres, or just use what's at hand with MySQL. In fact, these days SQLite is getting more of my attention anyway, and I wrote a small CLI for it a decade or so back (before the sqlite3 client gave us most of the below) to provide: - Timings for the queries (in fact I called it 'tsql') - Aligned-column displays, with | separators between columns and index-indicators - Ability to parse some blobs (Plists on the Mac, for example) and display - Things like "SHOW DATABASES", "SHOW TABLES", "SHOW TABLES LIKE" etc. Mainly I wrote it to do some benchmarking, but I eventually preferred it over sqlite3 as the CLI. Note that all this is personal stuff - When I do commercial stuff, the answer is always "what is best understood by the people maintaining it afterwards"...
        • jhas78asd 3y ago
• glogla 3y ago
  I choose Postgres every time, because that is what I am familiar with. But nerdy stuff: Postgres stores data linearly (in heap - which has nothing to do with the heap data structure used for sorting, it just means pile of data). If you need to have fast access to data, you need to add secondary indexes - and the secondary indexes point to location in the heap as "this is where you find the data". MySQL stores data in a tree - a table is a tree sorted by primary key. You can create secondary indexes and instead of a pointer they contain the primary key value. That means for example that data with similar primary key will be located physically nearby each other, in MySQL but not in Postgres. At the same time, inserting new data with random (like UUID) primary key in MySQL will write all over the table, but will mostly "append at the end" in Postgres. Postgres also implements MVCC with Serializable Snapshot Isolation - so data that someone changes exists in multiple copies and needs to be cleaned up later - but there's no locking. MySQL relies on locks instead so there's no duplication but you might see transactions waiting for each other. I don't remember if MySQL implements a proper serializable isolation - but that is not really the default on any database anyway. Interestingly, Oracle has very similar design to Postgres (though it uses rollback segment for old data, so there's no bloat and vacuum but you might get "snapshot too old" error) while MS SQL Server is also tree and lock-based database like MySQL. Does this impact you? It might, like in cases where MySQL performs terribly due to UUID keys or Postgres can't vacuum fast enough due to high volume of updates or something. Or you're implementing money settlement logic and need proper serilizable transactions, who know. But it is cool to know the implementation details.
• ungawatkt 3y ago
  postgres just because I'm more familiar with it, and the extension ecosystem (TimescaleDB, PostGIS, etc etc). That said, I'm not going to be sad with MySQL, though I'd probably go with MariaDB just because of full open source (note, I don't know any details there, being a postgres guy)
• carpo 3y ago
  Am I working on a Wordpress site ? Yes -> MySql No -> Postgres
• johnchristopher 3y ago
  I choose mysql because they don't want to install extensions (geo stuff) at work and we are using many PHP things that use mysql. And WordPress. Choose boring tech or something.
• javajosh 3y ago
  Go with postgres. Unless you need php/wordpress, then pick mysql.
• craigkerstiens 3y ago
  Having answered this a ton over the years, don't want to really take shots at MySQL. But Postgres stands in pretty unique ground. 1. It's solid as a really reach data platform (more than just a relational database). It's extension framework is quite unique compared to others. It's JSONB support was the first among other relational databases and is feature rich and performant. Multiple index types. Transactional DDL. The list goes on. 2. No central owner. A lot of open source is source code is open, but it's maintained by a central company. 3. I mentioned extensions, but really that is understated. It can do really advanced geospatial, full text search, time series, the list goes on. Having explained this a ton of times first 10 years ago - https://www.craigkerstiens.com/2012/04/30/why-postgres/ https://www.craigkerstiens.com/2012/04/30/why-postgres/ and then again 5 years later with and updated version, most recently tried to capture more of this in an updated form on the Crunchy Data blog - https://www.crunchydata.com/why-postgres https://www.crunchydata.com/why-postgres
  • jhas78asd 3y ago
    Hi Craig! :) https://twitter.com/andatki https://twitter.com/andatki
• LinuxBender 3y ago
  For my little hobby sites I use whichever best fits the application. If an application has a specific need for a function that is in Postgres and not MySQL then I use Postgres. In my former work life we used Percona MySQL for the commercial support and very fast response to fix bugs and add features, but we also used Postgres and Oracle. In those cases it was more important to have awesome DBA's that could do anything with any database. I learned a lot from them and they earned a lot of respect from me. One of them could find significant design flaws in the application just be reviewing the schema. They cut our memory usage in half and bought us time to wait for the server vendors to support more memory in their next motherboard release.
• rc_mob 3y ago
  Its not even a choice anymore. One is far better than all of the others.
• xyproto 3y ago
  Just choose Postgres. If you want something more nimble, go with Redis (which does support persistant storage, contrary to popular belief).
• guggle 3y ago
  > What sort of functional requirements would cause you to choose one over the other? MySQL failed me big time in the past, so my functional requirement would be "don't f** with my data". And so far, PostgreSQL and SQLite never did. Don't have time to give second chance, don't need to.
• quags 3y ago
  For myself it’s about what I know and that’s MySQL / Mariadb If there is a problem I have probably seen it. MySQL won’t start for some reason I know what to do. Adding and removing users , permissions, running with selinux, optimizing queries, indexes - all with in my scope. Backups and restores, working with snapshots, MySQL relocation or master slave. I have been able for years to upgrade mysql versions with out much hassle. I know the table structure well enough I could downgrade in almost all cases - with adjustments I once downgraded mysql 8 to 5.7 because years ago it was too slow. Now if I had the desire (I’m not past 40 so meh) or i didn’t have such a difference in knowledge I’d probably seriously look at postgresql. So my suggestion is go with in your circle of competence. Regardless if you go with mysql it is a fine database that many use at a high scale.
• jpgvm 3y ago
  Generally speaking it should always be PostgreSQL. It's the default choice for a number of reasons but chief among them is just that it's higher quality. That is it's developed to higher standards due to community bar being really high (thanks Tom Lane, et al for your stewardship) and testing and analysis of changes to the database being ingrained into the culture. By pursuing correctness first before performance for many years PostgreSQL has built a stronger foundation that is now paying dividends in terms of both performance but also ability to ship powerful features quickly. This has generally resulted in the gap between MySQL and PostgreSQL only continuing to widen over the last 10 years. So when would you consider picking MySQL? To me that comes down to exactly one set of use-cases and that is workloads that are fundamentally incompatible with VACUUM. The PostgreSQL MVCC system requires that table heap be maintained by the VACUUM process to both ensure safety (txid wraparound) and reclaim/reuse heap storage. This process is very expensive for workloads that do a lot of updates, especially on indexed columns (as indices need VACUUMing also), less of an issue for non-indexed columns if you can use HOT (heap only tuple) updates and tune the target fill ratio of heap pages appropriately. In most cases it's highly unlikely your business is going to reach the level of write load where these deficiencies in write behaviour actually matter but it is possible. Uber famously migrated from PostgreSQL primarily because their experiences with write amplification and VACUUMing. If for instance though your data consists of a smaller live hot set and a warm set that is less frequently updated and easily separable by a deterministic factor like time you can very easily use PostgreSQL table partitioning to isolate the two and continue to scale for a very very long time on pure PostgreSQL. In practice this may be fixed in PostgreSQL one day, there was a project called zheap to implement an UNDO log style storage system for PostgreSQL (which would avoid all the VACUUM maintenance etc) but it stalled out, largely I believe because it wasn't able to provide obvious wins quick enough to stimulate further interest. However OrioleDB has picked up the torch now and does in fact seem to be showing very impressive results. If such a storage engine is merged in my mind there will no longer any reason to consider MySQL for production workloads.
  • jhas78asd 3y ago
    Thanks for calling out table partitioning. Besides implementing it at one level, multiple levels can be used simultaneously (eg list and range). Tables can be grouped and split out to their own database (aka functional sharding/vertical sharding) and again partitioned. This all takes more effort and investment but keeps you on PostgreSQL. As you said fillfactor can be tuned, more HOT updates. Even analyzing whether the Updates could be turned into inserts that are written at a high rate, not incurring bloat, and then fewer updates are made at a rate that does not outrun Vacuum.
• irrational 3y ago
  Easy. Choose Postgres. I worked with Oracle for 15 years before moving to Postgres. In my experience, Postgres has been better documented, more performant, better standards compliant, etc. Now, you are asking about MySQL and not Oracle, but I’ve never heard anyone say MySQL is better than Oracle, while I have heard that about Postgres many times.
• adw 3y ago
  If I were starting from zero knowledge, I'd choose Postgres, but you're not. Unless you require on particular database extensions (full-text search, PostGIS, etc), or you're operating at the kind of scale where all bets are off, it won't matter very much; use the one your team has more expertise with. MySQL sucked twenty years ago, but it's had twenty years of people beating on it, so it largely sucks in precise, well-understood ways now. This is nearly as good as being good.
• scosman 3y ago
  Postgres for almost anything. Robustness, ecosystem, accuracy, all top notch. One exception: I did migrate two very large tables (15B+ rows) from Postgres to MySQL for performance reasons. InnodB (MySQL storage engine) can arrange the records on page by primary key, and if you have a multi-value PK (user_id, uuid) it means all records from a user are in the same set of pages. Huuuuge improvement over having your data for a user spread out over N different pages. Memory cache way more efficient. Orders of magnitude speed up on cold queries, better cache hit rate, and cost reduction from smaller servers.
  • jhas78asd 3y ago
    Did you implement table partitioning with Postgres or consider that before moving?
    • scosman 3y ago
      Been a while. IIRC it didn’t work with millions of users, and partitioning with many users per partition would have same issues. Also looked at CLUSTER but there was no workable online version.
• muttantt 3y ago
  In 2023 you don't even debate between the two. The decision to go with Postgres is instant and by default.
• tommy_axle 3y ago
  It depends on the project but a couple things that would help me pick MySQL (MariaDB) more often (wish list) are: - Row-level security (RLS) - depending on the tenancy and DB level safe-guards you need Postgres supports it and MariaDB doesn't - transactions for schema changes - Postgres (and even sqlite) support this but MariaDB doesn't. Due to not having it, if there's a faulty migration manual clean-ups might be required to get it back to the state you need
• bkuehl 3y ago
  A lot of comments for Postgres, but it's the only major DB in 2023 that does not let you choose your character collation when creating a database. That is pretty much a deal breaker day 1. Guess you'll be doing a tolower() on every db search and not use indices which will kill performance or using column collation casts on every search query. I just don't get it. I once tried to migrate a SQL Server DB to Postgres and eventually gave up, with MySQL being a pretty easy switch with some minor stored procedure rewrites. Also it tends to do things way differently than every other DB. VACUUM is just a completely different concept that can footgun you pretty fast. Postgres is pretty powerful but it has certainly made some interesting design choices.
  • spprashant 3y ago
    Am I missing something here? Postgres does allow you to choose character collation when creating a database, as well as when creating new columns.
    • bkuehl 3y ago
      I'm sorry, I was wrong in that Postgres does let you specify a few character collations at the DB level, but they are pretty much ASCII vs UNICODE with no case-insensitive configurations. You can create collations in v12 and assign them to particular columns.
  • arp242 3y ago
    > doing a tolower() on every db search and not use indices If you create the index with lower() it will uses that; e.g. "create index on tbl (lower(email))" and then "select * from tbl where lower(email)=lower($1)". That's more or less the standard way to do this but there are some other ways as well. It's more explicit than MySQL, so in that way it's better. It's more effort and easy to forget, and in that way it's worse – either way: it's definitely possible to do case-insensitive searches with indexes. When I first used PostgreSQL I ran in to "how the hell do I do this?! MySQL just lets me [...]" kind of issues, but after many years of PostgreSQL usage I now have the opposite when I use MariaDB, which also has its share of awkwardness and issues (just different ones).
• cvalka 3y ago
  Neither. Use either TiDB or YugabyteDB.
• c4pt0r 3y ago
  For MySQL users today, there are modern cloud-native replacement solutions, such as tidb(tidbcloud.com), where tidb (github.com/pingcap/tidb) provides a MySQL-compatible syntax layer but it's not MySQL. Interests: I work at TiDB
• xtracto 3y ago
  None of them if you need to do something like pivot tables with dynamic number of columns. Postgres has some half baked pivoting functionality library, but you MUST define the column names in advance, which kind of defeats the purpose of a pivot table functionality.
• buglungtung 3y ago
  Absolutely Posgtres!!! Yesterday I had to migrate from postgres to mysql and I found some disadvantage things about mysql in my use case There is no returning for insert/update/delete what forces me do a second query to get new data. Why dont just return what I have updated? Because it is some thing like: UPDATE wallets SET balance = balance + 1 I have to give up json function because its hard to work with it on mysql. Have to do the aggregation in my code instead No uuid v4 support by default
• minaguib 3y ago
  That's EASY PostgreSQL. The answer has always been PostgreSQL, even at the height of MySQL's popularity and LAMP craze. It is a VASTLY better piece of software and ecosystem - yet it's boring at the same time - all things I demand from the DB system whether it's a toy project or an enterprise app.
• 88stacks 3y ago
  postgresql always, that’s how :)
• ilaksh 3y ago
  I would quit that job and get a gig or project where I can make my own decisions. Then stick everything in JSON files or whatever seems convenient for my particular problem.
• doyourhomework 3y ago
  Funny to be so blind, no one mentionned how the Heatwave analytics engine of MySQL is 10 years ahead of what PostgreSQL can do, oh but that's because of Oracle so it doesn't count right? Mixing OLTP and blazing fast analytic queries on the same database, at the very same time, removing ETL needs...
• manv1 3y ago
  The main reason I prefer mysql over PostgreSQL is that mysql is just more consistent - in its commands, quirks, etc. Postgres - is it pg, pgsql, psql, postgres, postgresQL? The answer is "yes." Plus the case behavior for tables and column names drives me crazy. It's like some leftover VMS shit. I mean seriously fix it. Can you or can you not use a capital letter for a table/column name? I can never remember. Or you can, but you have to quote it? Fuck. Until recently (which to be fair might be 8-10 years ago) postgres' performance monitoring tools sucked compared to mysql. I know at one point in the last 10 years they still used sunos4 as their base configuration because you know, the OS had been EOL for like a decade at that point. MySQL is fire and forget. psql (or postgres or pg or postgresql?) is not fire and forget. It's twitchy and requires constant vigilance. I don't want a piece of infrastructure that requires constant vigilance. That's not to say I won't use it. It's geo stuff is really great. It's JSON support is better than MongoDB's, from what I've heard. Row level security is awesome. But are those features good enough to overcome psql's quirks? Sometimes.
  • jhas78asd 3y ago
    If you’re talking about the command line client that’s built in, it’s psql. If you can’t remember the command name to launch it or regularly type those other commands when you meant to type psql, you could add aliases to your shell that point to psql. :) Learning any new CLI client is a bit daunting at first. With repetition and intention, I think the commands become very memorable. Eg “describe table” is “dt”.
  • arp242 3y ago
    > at one point in the last 10 years they still used sunos4 as their base configuration because you know, What exactly do you mean with this? I tried to find some more information, and all I could find were some fixes from 2001[1] (SunOS 4 was supported until 2003), a minor refactor in 2008 with "SunOS 4 is probably broken anyway"[2], and that's pretty much it. SunOS 4 was moved to "Unsupported Platforms" with the release of 7.3, in 2002.[3] [1]: https://postgrespro.com/list/thread-id/1598869 https://postgrespro.com/list/thread-id/1598869 [2]: https://www.postgresql.org/message-id/20081211091708.072607545A4%40cvs.postgresql.org https://www.postgresql.org/message-id/20081211091708.0726075... [3]: https://www.postgresql.org/docs/7.3/supported-platforms.html https://www.postgresql.org/docs/7.3/supported-platforms.html
• deleted 3y ago
  [deleted]
• NightMKoder 3y ago
  I’m going to go for the esoteric opinion: MariaDB. Specifically to get system versioned tables. Imagine having the full change history of every single row for any odd task that you need without the performance penalty of keeping historical data in the same table. It can be a huge amount of leverage. If that’s not your interest, I will admit that Postgres array support is far ahead of any of the MySQLs. Most ORMs don’t use it but you can get optimal query prefetching via array subqueries.
  • heyoni 3y ago
    I’m 100% with you regarding system versioned tables. However, I think they’re coming to Postgres soon-ish. I was following the tracker for a while and it looked like it was done.
• chengdujin 3y ago
  If row level security is a need, I’ll go with Postgres
• flippinburgers 3y ago
  By choosing Postgres. Next!
• erhaetherth 3y ago
  Easy. I keep using MySQL because that's all I know. Kinda j/k. I'm intrigued my Postgres but I don't start that many new projects. It has at least 1 feature that MySQL doesn't -- deferred referential integrity which there's no good workaround for that I'm aware of. Arises extremely rarely when you need to have 2 tables have a FK to eachother.
• davidgerard 3y ago
  the answer is: * always use Postgres. * if you need something else, it better have an actual reason. MySQL/MariaDB is only for when you have a framework that requires it or is highly optimised for it (e.g., WordPress or MediaWiki).
• machiaweliczny 3y ago
  What is go-to solution for Postgres to have data encrypted on disk? I think that MySQL is more popular in enterprise as there's transparent encryption in enterprise version that's single click.
  • funcDropShadow 3y ago
    Encrypt at the block level, e.g. LUKS or at the filesystem level: ZFS.
• ahachete 3y ago
  At our company, we provide Postgres 24x7 support. We have partners that provide support for other databases, and for some projects we work together with companies that have multiple databases. We have over the years compared the rate of production incidents Postgres vs MySQL. It's roughly 1:10 (MySQL has around 10 times more production incidents than Postgres). You may consider this anecdotal evidence, but numbers managed here are quite significant. The gist is that Postgres is not perfect nor free from required maintenance and occasional production incidents. But for the most part, it does the job. MySQL too, but with (at least from an operational perspective) many more nuances.
• profwalkstr 3y ago
  There was a thread asking this a month ago, here's what I wrote then: https://news.ycombinator.com/item?id=35618726 https://news.ycombinator.com/item?id=35618726
• michaelcampbell 3y ago
  It depends on my requirements and context, really. THESE DAYS with docker, postgres is almost a no-brainer for basically anything that _I_ do, anyway. If I even need a heavy write load or external-access-via-network then PG, otherwise for my mostly simple CRUD needs SQLITE, quite honestly. I have a production app on MySql, but that was before docker and MySQL was a fair bit easier to setup then. That was my need at the time.
• atomicnature 3y ago
  Simple: just pick Postgres :)
• aigoochamna 3y ago
  > It's 2023, how do you choose between MySQL and Postgres? Easy, you pick Postgres. There's no reason to ever use MySQL.
• nailer 3y ago
  Same way you did in 2003. You pick Postgres. There has been zero times mySQL has been a better choice.
• SergeAx 3y ago
  I think the answer is really easy: you should choose the one you or/and your team has more experience with.
• bdcravens 3y ago
  Completely tangential, but I need to do some transformations on some CSVs and decided I'd just import them into a local database, run my SQL, and export. The CSVs had the literal strings TRUE/FALSE in a column. MySQL would require me to import into a varchar column and then transform; Postgresql automatically converted it as a boolean. Plenty of big reasons, but it's the small quality of life stuff that makes a lot of difference.
• appleaday1 3y ago
  I kind of learned Git rebasing thanks to the postgres github repo, so I tend to use it nowadays. Does mysql even have a public github repo?
• Thetawaves 3y ago
  MySQL - Better performance and tune-ability. Higher connection limits/lower connection costs. Better multi-master and clustered replication story. 2+ decades in extremely high data and availability environments (telecom). Postgres - Better SQL semantics. Having supported both in 'bet the business' scenarios, I would choose MySQL hands down. Operating state of the art HA postgres clusters today feels like running MySQL clusters in 2005.
• caseyscottmckay 3y ago
  "PostgreSQL supports most of the major features of SQL:2016. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features."[1] [1] https://www.postgresql.org/docs/current/features.html https://www.postgresql.org/docs/current/features.html
• wageslave99 3y ago
  I worked for many years with MySQL and then started working in a company that used PostgreSQL. Things I liked from PostgreSQL: - Better SQL standard support (modernish?). - JSONB type, also with indexes! Very useful to have JSON fields to allow dynamic fields or avoid sub-querying some tables (i.e. I mean caching some computed results in a JSON field). I have to say that it was in the 2018-2019, maybe MySQL has improved since then.
• _448 3y ago
  Can it(the database) do simple pub/sub without third-party software? :)
• aristofun 3y ago
  There is nothing to argue about. Postgres is better in almost every possible aspect you can come up with. So you choose mysql in 2023 only if you have very very specific requirements and constraints to justify the sacrifice.
• fullstackchris 3y ago
  Actually I would like to know just ONE advantage MySQL has over PostgreSQL... are there actually any?
• digitalpacman 3y ago
  Easy. You don't ever pick MySQL because it's essentially garbage.