4 ms·

But that's my point. MySQL also has features that Postgres doesn't support. These articles don't bring those up. It seems like you have illustrated my point per

by ebbv 11y ago

But that's my point. MySQL also has features that Postgres doesn't support. These articles don't bring those up. It seems like you have illustrated my point perfectly; because these articles don't bring up the features MySQL has that Postgres lacks, you think it's a one sided issue.

spacemanmatt 11y ago
What features MySQL has that PostgreSQL lacks?
- Jweb_Guru 11y ago
  Not a huge number, as of Postgres 9.5 (which isn't out yet, to be fair, but I think the only thing it adds that MySQL has already is upsert). Easy-to-configure failover is the obvious one. Index-organized tables are another (it doesn't have non-index-organized tables, though :P). It also uses direct I/O instead of going through the OS, so it can be considerably faster for some types of queries in InnoDB (I've seen over 2x better performance for something like raw insert). It also has a pluggable storage engine (though Postgres sorta does too, in the sense of multiple backends, most of its functionality is closely tied to the default one). InnoDB's MVCC implementation is different from Postgres's so it probably has advantages in some situations (though I don't know what those might be). It uses a thread per connection, rather than a process, and doesn't have to allocate the majority of the per-session memory up front, so it can support more connections than Postgres by default (aka without using some third-party tool like pgbouncer to create a connection pool). There's probably more stuff? However, Postgres has a ton of other features that weren't mentioned in the article... I think the general response from MySQL developers on this topic is that in practice companies don't want all the features Postgres has. I can't comment on the general developer, but I can say that for me virtually all of its features have been useful. And BTW, the list of "stuff MySQL has but not Postgres" used to be a lot longer. I'd be happy for people more experienced with MySQL to correct me or add anything I was missing. But I think in general the notion that this article is super biased and MySQL has a ton of stuff Postgres doesn't is incorrect.
  - spacemanmatt 11y ago
    That's a lot of things. PostgreSQL has had index organized tables (aka clustered indexes) for a long time. I can't consider a pluggable storage engine a feature. Databases are about little except reliable storage. From memory, the only time pluggable storage drivers affected me is when I realized MyISAM was inadequate and I should have used InnoDB in the first place. Show me meaningful benchmarks about this 2x performance advantage. Is this raw insert comparable to a local copy with triggers/constraints deferred? Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores. My guess is you are unfamiliar with the PostgreSQL-equivalent functionality in this case. Please don't assert "different so probably better" about MVCC implementations. That's far from rational. Thread vs process per connection is an artifact of design. More connections don't get you more throughput, rather just consume more RAM. I'm not seeing a feature in this. Real applications pool connections whether in-app or using pgBouncer. I don't know how you get a "general response" that people don't want "all the features" that PostgreSQL has. I've yet to find an organization using a database that doesn't experience a nearly constant feature demand as they integrate what they have and seek the next level for their operation. I'm thinking the article is accurate, even if it seems gloating to MySQL fans.
    - anarazel 11y ago
      > PostgreSQL has had index organized tables (aka clustered indexes) for a long time. Index ordered and clustered tables are different. Postgres' CLUSTER command simply rebuilds the the index after sorting the underlying data, in a on-off, blocking, operation. That means further operations will slowly reduce the "sortedness" and that it can't be relied upon when correctness is relevant (e.g. to skip a sort step for an ORDER BY). Real index ordered tables would be nice, but I don't see them in the near future. > Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores. I'm not aware of any really low-hanging fruits that we know of. While I'd like to improve INSERT performance a bit more, I think at the moment real bottlenecks are elsewhere. I mean on a halfway good single server I can do ~240k INSERTS/sec, without pipelining and about 1100k with. That's not exactly nothing. With COPY instead of INSERTs you can do a lot more. I think write performance can be a improved a fair bit, but it's not the low hanging fruit level anymore. The biggest things I know are 1) replacing the buffer mapping hash table with a lock free datastructure (radix tree is what I/we are experimenting with) 2) better cache replacement implementation, suitable for very large memory sizes with a high turnover 3) make buffer pins lock free (patch exists) 4) make relation extension scale better > I'm thinking the article is accurate, even if it seems gloating to MySQL fans. I wish we could all use a bit less adversarial tone in these kinds of discussions. Mysql does some things better. Postgres some others. For some others it's not yet clear which direction is better. Of course I personally prefer PostgreSQL, but that shouldn't make me blind that they got some things right that we didn't. EDIT: Updated throughput number from 800k to 1.1 mio.

by ebbv 11y ago

spacemanmatt 11y ago

What features MySQL has that PostgreSQL lacks?

Jweb_Guru 11y ago
Not a huge number, as of Postgres 9.5 (which isn't out yet, to be fair, but I think the only thing it adds that MySQL has already is upsert). Easy-to-configure failover is the obvious one. Index-organized tables are another (it doesn't have non-index-organized tables, though :P). It also uses direct I/O instead of going through the OS, so it can be considerably faster for some types of queries in InnoDB (I've seen over 2x better performance for something like raw insert). It also has a pluggable storage engine (though Postgres sorta does too, in the sense of multiple backends, most of its functionality is closely tied to the default one). InnoDB's MVCC implementation is different from Postgres's so it probably has advantages in some situations (though I don't know what those might be). It uses a thread per connection, rather than a process, and doesn't have to allocate the majority of the per-session memory up front, so it can support more connections than Postgres by default (aka without using some third-party tool like pgbouncer to create a connection pool). There's probably more stuff? However, Postgres has a ton of other features that weren't mentioned in the article... I think the general response from MySQL developers on this topic is that in practice companies don't want all the features Postgres has. I can't comment on the general developer, but I can say that for me virtually all of its features have been useful. And BTW, the list of "stuff MySQL has but not Postgres" used to be a lot longer. I'd be happy for people more experienced with MySQL to correct me or add anything I was missing. But I think in general the notion that this article is super biased and MySQL has a ton of stuff Postgres doesn't is incorrect.
- spacemanmatt 11y ago
  That's a lot of things. PostgreSQL has had index organized tables (aka clustered indexes) for a long time. I can't consider a pluggable storage engine a feature. Databases are about little except reliable storage. From memory, the only time pluggable storage drivers affected me is when I realized MyISAM was inadequate and I should have used InnoDB in the first place. Show me meaningful benchmarks about this 2x performance advantage. Is this raw insert comparable to a local copy with triggers/constraints deferred? Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores. My guess is you are unfamiliar with the PostgreSQL-equivalent functionality in this case. Please don't assert "different so probably better" about MVCC implementations. That's far from rational. Thread vs process per connection is an artifact of design. More connections don't get you more throughput, rather just consume more RAM. I'm not seeing a feature in this. Real applications pool connections whether in-app or using pgBouncer. I don't know how you get a "general response" that people don't want "all the features" that PostgreSQL has. I've yet to find an organization using a database that doesn't experience a nearly constant feature demand as they integrate what they have and seek the next level for their operation. I'm thinking the article is accurate, even if it seems gloating to MySQL fans.
  - anarazel 11y ago
    > PostgreSQL has had index organized tables (aka clustered indexes) for a long time. Index ordered and clustered tables are different. Postgres' CLUSTER command simply rebuilds the the index after sorting the underlying data, in a on-off, blocking, operation. That means further operations will slowly reduce the "sortedness" and that it can't be relied upon when correctness is relevant (e.g. to skip a sort step for an ORDER BY). Real index ordered tables would be nice, but I don't see them in the near future. > Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores. I'm not aware of any really low-hanging fruits that we know of. While I'd like to improve INSERT performance a bit more, I think at the moment real bottlenecks are elsewhere. I mean on a halfway good single server I can do ~240k INSERTS/sec, without pipelining and about 1100k with. That's not exactly nothing. With COPY instead of INSERTs you can do a lot more. I think write performance can be a improved a fair bit, but it's not the low hanging fruit level anymore. The biggest things I know are 1) replacing the buffer mapping hash table with a lock free datastructure (radix tree is what I/we are experimenting with) 2) better cache replacement implementation, suitable for very large memory sizes with a high turnover 3) make buffer pins lock free (patch exists) 4) make relation extension scale better > I'm thinking the article is accurate, even if it seems gloating to MySQL fans. I wish we could all use a bit less adversarial tone in these kinds of discussions. Mysql does some things better. Postgres some others. For some others it's not yet clear which direction is better. Of course I personally prefer PostgreSQL, but that shouldn't make me blind that they got some things right that we didn't. EDIT: Updated throughput number from 800k to 1.1 mio.