5 ms·

Curious if Postgres would be up to the task here?

by pbreit 10y ago

Thaxll 10y ago
Who do people feel the need to mention Postgres on every post talking about MySQL... It reminds me the same problem with Linux and *BSD.
- infamouscow 10y ago
  What drives me mad is when people claim they choose PostgreSQL over MySQL because data integrity concerns, yet they run PostgreSQL on Linux as opposed to FreeBSD or Illumos using ZFS.
  - dijit 10y ago
    Postgres' MVCC is almost like copy-on-write by itself, so anything more than a journal is overkill in any realistic scenarios. Personally we tried ZFS with PostgreSQL and we managed to fill the WAL drive before the vacuum could clear it out with our update rate. So we went back to XFS on the more commonly adopted (in our company); CentOS
- deleted 10y ago
  [deleted]
- EGreg 10y ago
  BSD > Linux Postgres > Mysql (in most cases) But larger learning curve
- dump121 10y ago
  They are two competing open source databases, knowing strength/weakness of each is always good. So unless the answer is `we rock, you suck`, it would be a useful info. for lots of people
- threeseed 10y ago
  PostgreSQL has been mentioned on EVERY database post for years now. And it always involve an endless amount of condescension in particular directed at those that god forbid choose to use a NoSQL database. Everything seems to get turned into a competition despite the fact that the world have moved away from the centralised, vertically scaled database towards a more heterogenous landscape.
spudlyo 10y ago
Sadly at present Postgres has absolutely awful mysqldump support (It's not even on their roadmap) so I doubt it would be a good fit for Pinterest's environment.
- dijit 10y ago
  is that a joke? of course they wont support mysqldump, but there is pg_dump, which does the same sort of thing except you can dump to binary and compress it on the way without pipes/forks.
  - spudlyo 10y ago
    Wait, hold the phone, are you telling me by migrating to Postgres I can shave a handful of system calls off my long-running i/o bound logical backups!? Oh Postgres, is there anything you can't do better than MySQL?
    - dijit 10y ago
      No I'm telling you that `mysqldump` will not support postgres the same way that a diesel truck will not take petrol.
      - spudlyo 10y ago
        I'm glad you cleared that up, because for a moment there I thought your comment was advocating for Postgres in a thread ostensibly about MySQL by pointing out that pg_dump has baked-in compression; an optimization which could potentially shave milliseconds off a process that the article mentioned can take over 12 hours on large tables. So, putting aside the issue of Postgres not supporting mysqldump for the moment, there is also the issue of Postgres having poor support for the InnoDB on-disk format, and practically non-existent support for the MySQL client protocol. The more I think about this, the more I realize just how unsuitable Postgres is for Pinterest's current environment.
        dreaminvm 10y ago
jasode 10y ago
I notice you often ask that question on MySQL threads.[1][2] Here's the recurring pattern: if it's a single-instance database, either PostgreSQL or MySQL will work. However, if it is sharded database with multi-master replication, the overwhelming industry preference is MySQL instead of PostgreSQL. When it's just a single-instance db, a compelling case can be made for PostgreSQL because of features such as stricter type checking and stronger stored procedure language. However for sharded databases, there have been several high-profile case studies of migrations from PostgreSQL to MySQL including Etsy and Uber. I can't think of any major company doing the reverse of migrating a multi-master db from MySQL to PostgreSQL.[3] From an operational standpoint, the replication of Postgre dbs is fragile compared to MySQL. Instagram is a famous example of a large website using PostgreSQL but keep in mind that they did(do) master-slave instead of master-master. Reddit on Postgre is another example configured as master-slave. As for this particular thread about Pinterest, they use master-master and I've seen no evidence that Postgres+3rd party tools is superior for that scenario. If you disagree with the above assessment, it would be helpful if you explain how PostreSQL is equivalent or better than MySQL for multi-master db replication architectures. If you just repeatedly ask, "is Postgres good for this?", the replies don't seem to give you the answers you're looking for. If you have a strong position, you should state it so it guides further discussion. [1]https://news.ycombinator.com/item?id=10087412 https://news.ycombinator.com/item?id=10087412 [2]https://news.ycombinator.com/item?id=10926854 https://news.ycombinator.com/item?id=10926854 [3] when Uber migrated from MySQL to PostgreSQL in 2013, it was still a single-instance to single-instance migration. They did it to take advantage of PostGIS features. The subsequent 2016 operational difficulties of multi db replication pushed them back to MySQL.
- olavgg 10y ago
  I cannot understand the fuzz about multi-master replication, it either involves dirty/phantom reads, or it is awfully slow. Wikipedia has a lot of examples of these issues https://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Non-repeatable_reads https://en.wikipedia.org/wiki/Isolation_%28database_systems%... There should be a book about the dark side of multi-master replication at scale, about what can you trade away and still be happy with. Preferring either PostgreSQL or MySQL for this is just avoiding the technical complexity that multi-master replication is.

Curious if Postgres would be up to the task here?

by pbreit 10y ago

Curious if Postgres would be up to the task here?

Who do people feel the need to mention Postgres on every post talking about MySQL... It reminds me the same problem with Linux and *BSD.

infamouscow 10y ago
What drives me mad is when people claim they choose PostgreSQL over MySQL because data integrity concerns, yet they run PostgreSQL on Linux as opposed to FreeBSD or Illumos using ZFS.
- dijit 10y ago
  Postgres' MVCC is almost like copy-on-write by itself, so anything more than a journal is overkill in any realistic scenarios. Personally we tried ZFS with PostgreSQL and we managed to fill the WAL drive before the vacuum could clear it out with our update rate. So we went back to XFS on the more commonly adopted (in our company); CentOS
deleted 10y ago
[deleted]
EGreg 10y ago
BSD > Linux Postgres > Mysql (in most cases) But larger learning curve
dump121 10y ago
They are two competing open source databases, knowing strength/weakness of each is always good. So unless the answer is `we rock, you suck`, it would be a useful info. for lots of people
threeseed 10y ago
PostgreSQL has been mentioned on EVERY database post for years now. And it always involve an endless amount of condescension in particular directed at those that god forbid choose to use a NoSQL database. Everything seems to get turned into a competition despite the fact that the world have moved away from the centralised, vertically scaled database towards a more heterogenous landscape.

spudlyo 10y ago

Sadly at present Postgres has absolutely awful mysqldump support (It's not even on their roadmap) so I doubt it would be a good fit for Pinterest's environment.

dijit 10y ago
is that a joke? of course they wont support mysqldump, but there is pg_dump, which does the same sort of thing except you can dump to binary and compress it on the way without pipes/forks.
- spudlyo 10y ago
  Wait, hold the phone, are you telling me by migrating to Postgres I can shave a handful of system calls off my long-running i/o bound logical backups!? Oh Postgres, is there anything you can't do better than MySQL?
  - dijit 10y ago
    No I'm telling you that `mysqldump` will not support postgres the same way that a diesel truck will not take petrol.
    - spudlyo 10y ago
      I'm glad you cleared that up, because for a moment there I thought your comment was advocating for Postgres in a thread ostensibly about MySQL by pointing out that pg_dump has baked-in compression; an optimization which could potentially shave milliseconds off a process that the article mentioned can take over 12 hours on large tables. So, putting aside the issue of Postgres not supporting mysqldump for the moment, there is also the issue of Postgres having poor support for the InnoDB on-disk format, and practically non-existent support for the MySQL client protocol. The more I think about this, the more I realize just how unsuitable Postgres is for Pinterest's current environment.
      - dreaminvm 10y ago

jasode 10y ago

I notice you often ask that question on MySQL threads.[1][2] Here's the recurring pattern: if it's a single-instance database, either PostgreSQL or MySQL will work. However, if it is sharded database with multi-master replication, the overwhelming industry preference is MySQL instead of PostgreSQL. When it's just a single-instance db, a compelling case can be made for PostgreSQL because of features such as stricter type checking and stronger stored procedure language. However for sharded databases, there have been several high-profile case studies of migrations from PostgreSQL to MySQL including Etsy and Uber. I can't think of any major company doing the reverse of migrating a multi-master db from MySQL to PostgreSQL.[3] From an operational standpoint, the replication of Postgre dbs is fragile compared to MySQL. Instagram is a famous example of a large website using PostgreSQL but keep in mind that they did(do) master-slave instead of master-master. Reddit on Postgre is another example configured as master-slave. As for this particular thread about Pinterest, they use master-master and I've seen no evidence that Postgres+3rd party tools is superior for that scenario. If you disagree with the above assessment, it would be helpful if you explain how PostreSQL is equivalent or better than MySQL for multi-master db replication architectures. If you just repeatedly ask, "is Postgres good for this?", the replies don't seem to give you the answers you're looking for. If you have a strong position, you should state it so it guides further discussion. [1]https://news.ycombinator.com/item?id=10087412 https://news.ycombinator.com/item?id=10087412 [2]https://news.ycombinator.com/item?id=10926854 https://news.ycombinator.com/item?id=10926854 [3] when Uber migrated from MySQL to PostgreSQL in 2013, it was still a single-instance to single-instance migration. They did it to take advantage of PostGIS features. The subsequent 2016 operational difficulties of multi db replication pushed them back to MySQL.

olavgg 10y ago
I cannot understand the fuzz about multi-master replication, it either involves dirty/phantom reads, or it is awfully slow. Wikipedia has a lot of examples of these issues https://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Non-repeatable_reads https://en.wikipedia.org/wiki/Isolation_%28database_systems%... There should be a book about the dark side of multi-master replication at scale, about what can you trade away and still be happy with. Preferring either PostgreSQL or MySQL for this is just avoiding the technical complexity that multi-master replication is.