7 ms·

Does anyone have any use cases where PostgreSQL falls down/loses to other DB systems? I know sharding/replication has long been a sticking point, but what else

• dk8996 9y ago
  Yes, if you have a need to update large amounts of records. For example, updating a single column of all your records will cause the whole table to be rewrite, thus causing a super high IO load.
  • deepsun 9y ago
    Mmm, but it's the same for MySQL, no? Whenever we change a column in one of our tables (pretty big), the whole server hiccups for several seconds. We're using Google's Cloud SQL. At least PostgreSQL allows you to wrap schema changes in BEGIN/COMMIT/ROLLBACK transactions, unlike MySQL.
    • dk8996 9y ago
      UPDATE client set enabled = true; So something like this will rewrite the whole table because of MVCC. MySQL will update the record in place without rewriting the whole table.
      • mst 9y ago
        MVCC is almost always a feature for my workloads but it's well remembering it's a trade-off. Thanks for reminding HN of this point.
      • eloff 9y ago
        MyISAM I assume? InnoDB, which I hope you're using, uses MVCC too, just like pretty much all mainstream SQL databases of the last 30 years. Updates occur in place, with the older version of the columns relocated to UNDO space. Not sure what you're gaining over PostgreSQL there. Also, if the column is indexed, the index will contain all versions for each row. This is what allows index scans.
        • deleted 9y ago
          [deleted]
      • riku_iki 9y ago
        Because FS operates pages, and not individual bytes, MySQL will also, for each column, read whole page with row included, and write it back, thus rewriting whole table..
    • mst 9y ago
      You're talking about DDL. They're talking about an in-place rewrite of the value of a single column, which, yes, InnoDB will do with way less write load than postgresql.
      • dk8996 9y ago
        Yes, column updates. Although, using MySQL now, I really miss DDL migrations of PostgreSQL. It's a trade off.
      • ibotty 9y ago
        Can you provide specifics? AFAICT that applies only to indexes (where there is another layer of indirection in MySQL).
        • mst 9y ago
          InnoDB clusters on disk by PK so if your record is the same width and you don't change the PK it can AFAIK update in-place.
          • ibotty 9y ago
            Very late answer, but no, that is incompatible with mvcc. How can transactions (and rollbacks) work?
• adventured 9y ago
  > Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all? Oracle: because the enterprise is its own unique beast. Companies frequently stick to what they know and have invested into, so long as it still works. Enterprise customers buy/adopt software in a very different way than everyone else. MySQL: if you've got a history with the product and it works well for what you're doing, there is absolutely no necessity-based reason to switch to PostgreSQL. You should switch from MySQL to PostgreSQL if there's a very good reason/s to, a meaningful gain to be had, it's that simple. Otherwise, it's unnecessary optimization. There are a vast number of considerations when operating a business, upgrading to the latest & greatest (speaking very broadly) just to do it, is not a good reason most of the time. There should be a specific prompt to action based on a gain/s or capability that PostgreSQL provides you, in/for your use case, over eg MySQL.
  • linux2647 9y ago
    Aside from those reasons, are there technical reasons for Oracle over Postgres? My coworkers and I want to move our data warehouse over to Postgres and my boss has been warming up to the idea. (I know switching databases with that much data can be complex and troublesome, but those concerns are outside the scope of this question)
    • adventured 9y ago
      Oracle is still a very good option for data warehousing. When well tuned it's extremely performant and reliable for that purpose. If I were operating an EDW today, I'd choose Oracle over Postgres 10 times out of 10 when it comes to very large scale operations. You'll still see plenty of credible arguments around that Oracle can be optimized to be faster than every major alternative. And you'll still see arguments around replication that are legitimate. Obviously I don't know what your cost or performance considerations are. If your organization can save a meaningful amount of money and operational headache by switching to Postgres, that can be a big deal for any business. If cost isn't much of a consideration in your operation (that is, what sticking with Oracle is costing you), stick to Oracle for data warehousing over Postgres - at least for now imo. With Oracle it's the same story it has been for a long time (and probably will remain), it's expensive and a pain in the ass to get up and running the way you want it. Postgres has everything to gain vs Oracle from here forward, it seems very likely to bite into some of Oracle's well guarded high value territory in the coming years.
• ianamartin 9y ago
  Dynamic SQL in Postgres is really frustrating. I understand why. It's because you shouldn't fucking write SQL that writes SQL. On the other hand, sometimes you just have to. SQL Server . . . I won't say it makes it easy, but it doesn't make it so goddam impossible. If I lived in a world without Postgres, I would use SQL Server without thinking twice. MySQL/Maria I'm not fond of, and I kind of don't want to touch them. Oracle has some cute features and language extensions, but you have to deal with Oracle. I can't think of a use case where Postgres falls down compared to other RDBMSs. What we are seeing is Postgres competing with NoSQL and Mongo-type stuff. With full-text search support for JSONB types, we're looking at Postgres moving into ElasticSearch territory with a vastly more simple deployment model and still carrying ACID guarantees. It's pretty remarkable.
  • Tostino 9y ago
    I don't understand where you're having trouble writing dynamic sql with postgres. I've worked extensively with sql server, and postgres and I found both to be pretty equal once I learned the ins and outs of each.
  • garyclarke27 9y ago
    Dynamic SQL is easy in Postgres Just create a simple pl/pgsql function that takes a text argument and executes it. Works for me perfectly for anything I need such as building tables views functions etc.
• dhd415 9y ago
  It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.
  • Boxxed 9y ago
    You can cluster a table by an index: https://www.postgresql.org/docs/9.6/static/sql-cluster.html https://www.postgresql.org/docs/9.6/static/sql-cluster.html Or am I not understanding what you're asking for?
    • grzm 9y ago
      Indexes in PostgreSQL require lookups into the table to access the data values in the rows. Index organized tables have indexes which include the data values in the index itself, removing the need for the lookup in the table itself. Here's some more detail: https://news.ycombinator.com/item?id=10451095 https://news.ycombinator.com/item?id=10451095
      • bjt 9y ago
        Perhaps I'm missing something, but your description doesn't seem consistent with my understanding of the index-only scan feature that's been in PG since 9.2 https://wiki.postgresql.org/wiki/Index-only_scans https://wiki.postgresql.org/wiki/Index-only_scans
        • grzm 9y ago
          Yes, you're right about index-only scans, in the sense that the data in the indexed columns can be used in some cases. As I understand it, indexed organized tables goes further than that in that row data for non-indexed columns is also included.
          • deleted 9y ago
            [deleted]
    • 9y ago
• blaisio 9y ago
  The biggest case almost completely unhandled by PostgreSQL is if you need a clustered index like in MySQL. There are basically two common ways of organizing rows in a database: store everything in a tree according to the primary key, or store everything in a heap, and have a separate tree relating values to pointers into the heap. MySQL stores rows in a tree, and PostgreSQL stores rows in a heap. The strategy used by PostgreSQL is simpler, more flexible, and cleaner, but there are some cases where MySQL's way of storing things can be significantly faster. There has been talk of PostgreSQL supporting clustered indexes, but that would probably require reimplementing a ton of stuff to support both storage formats efficiently. Also, it turns out that there are a bunch of ways in which PostgreSQL can cheat such that in most workloads there's no difference at all - though not all of these cheats have been implemented. Note that I didn't say MySQL is faster or slower than PostgreSQL. Such statements don't make sense, because it depends entirely on your workload, and as long as you aren't literally using PostgreSQL as a key/value store instead of a relational database, you can probably always find a way to make it as fast as necessary.
  • anarazel 9y ago
    > The biggest case almost completely unhandled by PostgreSQL is if you need a clustered index like in MySQL. Heh. It's funny how "the biggest thing" is different for a lot of users. I can't count the number of times I heard "if you only had XXX, I'd migrate / be happy / ..." for vastly different features. If you then tell them what other people think the most critical bit is they look at you with some surprise. That's not to say these people, including you, are wrong, just that it's not always that clear what the highest priority items are. It doesn't get easier, although it has a lot of advantages too, if there's no coherent direction for the entire project, due to all the different directions various companies and people want to go. WRT clustered tables: I personally think it's a good feature, but there's other more pressing concerns. Since my employer thinks that as well, I'm for now working on other things...
• collyw 9y ago
  MySQL has native bitcount operator (and more data types last time I checked). Using it ended up being the fastest way to implement one problem I had - I tried Python with Numpy, Java, but generating large combinations in MySQL (joining a table to itself multiple times) and using bitshift and bitcount operators to filter the rows ended up being far faster than generating the combinations either language that I knew or Postgres lacking the bitcount operator. Probably spending a fair bit more time in C or Rust would have been the real solution. But that would have involved way more time than my work were willing to let me spend on it. Also initial setup is easier with MySQL. It is installed everywhere and setting it up uses involves less messing about. (I would imagine there are far more insecure MySQL install around as a result).