Hacker News Reader

Comment by hardwaresofton | Hacker News Reader

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 is there?

Why do people still choose MySQL/MariaDB/Oracle over PostgreSQL at all?

View on HN

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.

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

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

>>> Companies frequently stick to what they know and have invested into, so long as it still works.

This. For example I'm still on Java 1.6 because the cost of migrating to 1.7 (java is not the problem, the libraries we use are) is big. I'm still on Solaris because moving to Linux (for example) would imply redeployment of thousands of programs which translates to ten of thousands of man/hours.

Big corps go at geological speed :-)

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)

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.

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.

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.

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.

It's disappointing that pgsql has no index-organized tables aka clustered indexes. They are quite advantageous for a variety of workloads.

You can cluster a table by an index: https://www.postgresql.org/docs/9.6/static/sql-cluster.html

Or am I not understanding what you're asking for?

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

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

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.

That kind of clustering is a point-in-time operation. Subsequent inserts and updates aren't stored in clustered order. When the ordering is guaranteed, the query planner can be more aggressive in optimizing certain kinds of queries. A real clustered index could also be used as an implicit covering index as grzm mentions.

PostgreSQL can do all of the same query optimizations. The main difference is that for primary key scans PostgreSQL will have an additional layer of indirection meaning more work needs to be done when scanning on primary key and potentially more disk seeks.

On the flip side PostgreSQL's approach is good when you query secondary indexes since these can point directly to the heap rather than to a primary key, removing the cost of long primary keys and allowing for scanning the heap in physical order after some kinds of secondary key lookups. It is also cheaper to sequentially scan heap compared to sequentially scanning a clustered index.

There are also some difference s on the write side too, but the gist of it is that both models have their own strengths and weaknesses.

I don't see how pgsql could perform such optimizations as eliminating a sorting step when ordering results by column(s) in a clustered index if there is no guarantee that new or updated data will also be ordered by the clustered index. I agree that both models have their pros and cons. While I am a big fan of pgsql, I do also appreciate those databases that offer both heaps and clustered indexes that I can mix and match in my schema as best suits my workload.

PostgreSQL can use that optimization since an index scan in PostgreSQL still returns all tuples from the heap in index order, not in heap order. During an index scan PostgreSQL traverses the B-tree in index order and for each matching tuple found in the index it fetches the data from the heap (which is a quite cheap O(1) operation).

Also this operation imposes full read/write lock on table.

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

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.

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.

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.

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.

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

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.

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.

Can you provide specifics? AFAICT that applies only to indexes (where there is another layer of indirection in MySQL).

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.

Very late answer, but no, that is incompatible with mvcc. How can transactions (and rollbacks) work?

Yes, column updates. Although, using MySQL now, I really miss DDL migrations of PostgreSQL. It's a trade off.

Reply on news.ycombinator.com