Hey, Threadiverse! I’m looking for informed opinions on database choices.

I can stand up an Internet-facing application and have it use either MySQL or PostgreSQL. Which is the better choice, and why do you think so?

Thanks!

  • femtech
    link
    fedilink
    English
    13
    edit-2
    2 months ago

    Postgres, the extensions and open source community have been very helpful.

    Postgis for images

    CloudNative-pg for running DB clusters in kuberneties.

    • Jerry on PieFedOP
      link
      fedilink
      English
      02 months ago

      Nope. Locked inside a VPC with only one VPS allowed to communicate with it.

  • @zoostation@lemmy.world
    link
    fedilink
    English
    592 months ago

    Postgres is a more robust and better designed and developed product, also it’s not owned by fucking Oracle.

  • @AllNewTypeFace@leminal.space
    link
    fedilink
    English
    92 months ago

    I have historically gone with PostgreSQL and had no complaints. The licensing issues concerning MySQL also give one pause (Oracle are greedy bastards who will use any excuse to extract money from captive customers, so depending on their properties is to be avoided). Having said that, these days, SQLite is probably sufficient for many workloads and has the advantage of not requiring a database server.

  • @ShittyBeatlesFCPres@lemmy.world
    link
    fedilink
    English
    -22 months ago

    The only reason I wouldn’t go with Postgres is if I planned to do other things on the same machine. MariaDB/MySQL has been around forever. You may find something that requires it — Wordpress1, for example, requires MariaDB (or MySQL but use MariaDB) and doesn’t support Postgres.

    Also, there’s solutions like Docker containers if you are running multiple things on the same server. But if you’re just learning and putting one thing on a Raspberry Pi as a project or whatever, you don’t need to learn Docker yet.

    1 I’m not recommending Wordpress. It’s ancient and has security issues all the time. But over 40% of sites on the Net still use it in some form. (I mean Wordpress.org, the open source project. The Wordpress company seems to be having some “crazy CEO” drama at the moment.)

  • Dark Arc
    link
    fedilink
    English
    302 months ago

    PostgreSQL is just better. It’s supports transactions on DDL (things like altering table structure) and enforces unique constraints after transactions complete … so you can actually do a bunch of important stuff (like update your table structure or swap unique values between rows) safely.

  • Jeena
    link
    fedilink
    English
    112 months ago

    PostgreSQL is the more feature rich, but if you don’t care about all those features like saving and searching in json structures, Geo data structures and a to of other stuff because you have a simple APO then MySQL is good enough, maybe even SQLite.

    • @expr@programming.dev
      link
      fedilink
      English
      22 months ago

      Its query planner is also much, much more powerful. Like it’s not even close.

      There’s hardly any good reason to use MySQL today. Postgres is easier and nicer to work with, with a strong community backing it.

      SQLite is completely different from both and has entirely different usecases.

  • z3rOR0ne
    link
    fedilink
    English
    152 months ago

    Most applications can do just fine with SQLite, but if you need something with a lot more write speed, go with PostgreSQL.

  • Max-P
    link
    fedilink
    English
    802 months ago

    As someone that admins hundreds of MySQL at work, I’d go with PostgreSQL.

    • @pageflight@lemmy.world
      link
      fedilink
      English
      292 months ago

      Yeah, every time I find some weird annoying behavior or some missing feature in MySQL, PostgreSQL is doing it right.

      That said, also ask yourself if you really need a relational database, or whether an object store or append-only / timeseries db would fit better.

  • @halloween_spookster@lemmy.world
    link
    fedilink
    English
    52 months ago

    We have both MySQL and PostgreSQL in our production environment. Postgres is way nicer as a user of the DB. I created a document months ago outlining a dozen different things that Postgres does that MySQL either doesn’t do or does worse. I can’t speak to managing the DB as I don’t have experience with that.

  • @threesigma@lemm.ee
    link
    fedilink
    English
    182 months ago

    Postgres also had the advantage of great support for JSON elements, which gives you the power of a no-sql system like mongo in the package. A major selling point if your schema is evolving.

  • @rumba@lemmy.zip
    link
    fedilink
    English
    42 months ago

    Hardly anyone ever says mysql is better. Postgres has a lot of nice features, But they’re still a hell of a lot more people out there with mySQL experience.

    If for some reason you really want to go mysql I would urge you to look into percona and percona tools. It’s incredibly fast super optimized. The tools let you do backups that my sequel could only dream of.

    That said, if you don’t have any strong needs for mySQL, and you don’t have any experience with it I would probably start picking up postgres.

  • @droopy4096@lemmy.ca
    link
    fedilink
    English
    32 months ago

    My opinion is that of the two Postres is more “adult”. So if you want to"just wing it" MariaDB would work, but if you’re serious Postgres is a better choice. However Postgres also requires better understanding of you setup etc. So it’s a ROI game - what’s more important to your project, how complex your DB is, what are the requirements for availability, transaction security etc. There is no “better” or “worse” there’s “feasible” and “prohibitive” 😉