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!

    • pageflight@lemmy.worldEnglish
      25·
      2 days 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.

  • sugar_in_your_tea@sh.itjust.worksEnglish
    12·
    2 days ago

    Postgres. It’s more strict by default, which leads to a lot fewer surprises.

    Here’s my rule of thumb:

    1. SQLite - if it’s enough
    2. Postgres
    3. MariaDB - if you don’t care about your data and just want the thing to work
    4. MySQL - if you sold your soul to Oracle, but still can’t afford their license fee
    5. Something else - you’re a hipster or have very unique requirements
  • zoostation@lemmy.worldEnglish
    54·
    3 days ago

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

  • friend_of_satan@lemmy.worldEnglish
    361·
    2 days ago

    As somebody who just watched a team implement MySQL for an app that only supported Postgres, I’d go with Postgres.

    I never want to use MySQL again. Postgres or SQLite for relational databases.

    • corsicanguppy@lemmy.caEnglish
      126·
      2 days ago

      Ha! My deepest experience with postgres was watching it fall over and wedge daily when run behind red hat’s satellite (the flailing lame foreman one, not spacewalk).

      Wow, was it ever a dog. Yeah, I get it: the company who shat Systemd on the planet can’t be asked to do much better, but still.

  • Dark Arc@social.packetloss.ggEnglish
    23·
    2 days 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.

  • AllNewTypeFace@leminal.spaceEnglish
    7·
    2 days 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.

  • z3rOR0ne@lemmy.mlEnglish
    151·
    2 days ago

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

  • ThirdConsul@lemmy.mlEnglish
    81·
    2 days ago

    The answer is impossible to answer until you tell us more about your needs. Better choice considering what?

    In general, untill you have terabytes of data or a significant amount of traffic (operations per second) database choice does not matter and you should be using cheaper option, where the cost should be assessed as a derivative of price of hosting, cost per operation, cost to deliver (how familiar you are with it).

    When you have significant amount of data or traffic - only then you should worry about database kind or language. Until then this could be a premature optimization.

  • femtech@midwest.socialEnglish
    10·
    2 days ago

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

    Postgis for images

    CloudNative-pg for running DB clusters in kuberneties.

  • threesigma@lemm.eeEnglish
    16·
    3 days 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.

    • DeadMartyr@lemmy.zipEnglish
      7·
      2 days ago

      I used MariaDB for school projects, what exactly is wrong with it? Asking because I’m just unaware

      • earmuff@lemmy.dbzer0.comEnglish
        113·
        2 days ago

        While there was a time, where those databases were considered “good”, they are only this famous because they have been free or open source for ages. Professors love open source stuff. This does not necessarily mean it is a good product in terms of database functionality. They have been stuck in the old age and simply get outperformed by almost anything. Professors also hate to change their slides and to learn something new. Because their priority is on functionality, not on real world use. And when you want to use a product in the real world, non-functional properties gain a lot of value. One of them is performance.

        If you want to have a fast, reliable, open source database, use ClickHouse.

        • sugar_in_your_tea@sh.itjust.worksEnglish
          32·
          2 days ago

          Generally speaking, if a professor recommends something, it probably sucks. Their information is incredibly outdated and is usually whatever they used in their own undergrad program.

          At school I learned:

          • Java
          • PHP
          • MySQL
          • C#
          • C++
          • Racket (Lisp)

          Each of those has a better alternative, with C# being the least bad. For example:

          • Java -> Kotlin
          • PHP -> Python
          • MySQL -> SQLite or Postgres
          • C# -> Python (desktop QT GUIs) or web stack (e.g. Tauri for desktop web stack)
          • C++ -> Rust (non-games) or a game engine
          • Lisp -> Haskell

          Formal education is for learning concepts, learn programming languages and tools on your own.

          • earmuff@lemmy.dbzer0.comEnglish
            12·
            2 days ago

            Smear campaign with an open source product? Are you sure you still have a working organ between your ears?

            That being said, my recommendation is based on using databases in big data environments for 15 years. But I am glad that your home lab is working fine with MariaDB. Does not mean it is a good product. And your comment just proves my point.

  • barkingspiders@infosec.pubEnglish
    13·
    3 days ago

    Choosing is not so much about whether it’s internet facing or not. From the programmer’s perspective and an administrator’s perspective there are pros and cons to both. As someone looking to self-host, if you want to run a service that works with either, I would make the choice based on what seems the most supported, or which one you feel the most comfortable looking up and performing administrative tasks on. I tend to use postgresql more just because I have more experience with it and can recommend it if that’s what you need, but mysql can be just as good or better in many circumstances. Pick whichever one looks easier to you.