
How Swat.io migrated from MySQL to PostgreSQL in 2 years - MarkusWinand
http://garage.socialisten.at/2017/04/how-swat-io-migrated-from-mysql-to-postgresql-in-2-years/
======
justinclift
> [...] the maintenance headaches with MySQL started to have negative effects
> on our teams morality [...]

As a general observation, "morality" (eg moral values) might be the wrong word
there. Guessing you're meaning "morale" (?), which is more like "how happy our
team members are".

That minor nitpick aside though, thanks for sharing. :)

~~~
apeace
I also enjoyed the article, and also have a nitpick which you may find
interesting.

> Still, over time every new internal project pretty soon begged the
> question...

You probably mean "raised the question". Begging the question is a type of
logical fallacy[0].

[0] [http://www.nizkor.org/features/fallacies/begging-the-
questio...](http://www.nizkor.org/features/fallacies/begging-the-
question.html)

------
mt42or
"Cannot online add a new column" This is false.
[https://dev.mysql.com/doc/refman/5.6/en/innodb-create-
index-...](https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-
overview.html#innodb-online-ddl-summary-grid)

Very weird.

~~~
MarkusWinand
From [https://dev.mysql.com/doc/refman/5.6/en/innodb-create-
index-...](https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-
overview.html):

    
    
      Add column
      In-Place?: *Yes*
      Rebuilds Table?: *Yes*
      Permits Concurrent DML?: *Yes* (Concurrent DML is not permitted when adding an auto-increment column.)
      Only Modifies Metadata?: *No*
      Data is reorganized substantially, making it an expensive operation.
    

In practice, the last _no_ is a serious problem.

~~~
mt42or
Could you elaborate why it prevents online DDL ?

~~~
MarkusWinand
I think the point of the article is that adding an index to a big table with
lots of writes is _practically_ not possible:

Quoting from the article:

    
    
      real problem for big tables
    
      as adding a few columns to our biggest tables started to take 2+ hours
      or sometimes was completely unpredictable and exceeded our announced downtime windows.
    

Apparently, adding a column was even a problem during a maintenance window
because the runtime was even longer than they expected.

Compared to PostgreSQL:

    
    
      As long as the new column is NULL and does not have a default value,
      it’s in practice a no-op to add it. No matter if your table size is 100MB or 100GB

~~~
tveita
The linked [https://www.percona.com/doc/percona-toolkit/2.1/pt-online-
sc...](https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-
change.html) generally works really well though, and in the simplest case it's
as easy as following the usage example. I'm surprised that they knew about it
yet didn't invest the time to learn it.

------
thinkMOAR
Interesting read, and jealous somebody gave you 2 years for such migration :)

Small point of attention, on Safari/OSX swat.io loads as a white page with a
scrollbar (it notices the page is larger in height), but only to be populated
with content only 5 to 10 seconds after.

------
sfilargi
"We had complete lock ups where max_connections was exceeded and we could
never find a source, internal or external, to our system. Literally hundreds
of connections doing SELECT statements but nothing else. Eventually manually
killing them “solved” it"

Wouldn't having "max_connections" postgresql processes create more problems?

~~~
davidgould
Usually not. When you reach max connections it does not cause problems for
existing connections ie, no "complete lock ups)", it just stops allowing new
connections. Also, there are normally reserved connections for postgresql
superusers so you can query to find out where all the excess connections are
coming from and kill them as needed.

