To migrate from MS SQL? MySQL or PostgreSql?

Our database of about 30 tables. In the largest of about 20 000 000 records.
Now use HP, but I want to give them up and store in the database only the data tables.

Refuse in connection with the transition from a Windows platform to Linux (Ubuntu).

What to choose for the migration, MySQL or PostgreSql? What tools to migrate?
October 8th 19 at 02:17
7 answers
October 8th 19 at 02:19
Prefer PostgreSql, buns and more opportunities -> less hemorrhoids with the sawing of the DBMS-dependent code.
October 8th 19 at 02:21
PostgreSQL. In MySQL constantly budete to face that there are no chips, to which you are accustomed.
October 8th 19 at 02:23
Why not try MongoDB?
in Mongo has no joins s... and I have many many-to-many - Jayde.Tow commented on October 8th 19 at 02:26
and if I remember correctly — he table memory stores... and the memory is not infinite :( - Jayde.Tow commented on October 8th 19 at 02:29
And what is there to try MongoDB? To completely rewrite all apps that use a database, instead of minor changes, if at all, used something MSSQL specific?

MongoDB and all the other NoSQL is fashionable of course, but it is not a "silver bullet" but a tool that could be used in certain situations when it makes sense.

> and if I remember correctly — it's all in-memory tables stores
Wrong remember. - Guiseppe commented on October 8th 19 at 02:32
MongoDB uses mmap, so it keeps in memory only the fact that he decided to keep Pamali OS. But the migration from MSSQL hardly justified — Mongo is too "poor" in the sense of possibilities. - Raquel_Towne47 commented on October 8th 19 at 02:35
Well, stuffing :) - charlotte91 commented on October 8th 19 at 02:38
rushter +1 ))) - Jayde.Tow commented on October 8th 19 at 02:41
Mongo DB Is Web Scale - terrill_Sanford commented on October 8th 19 at 02:44
October 8th 19 at 02:25
First you need to decide for yourself — how do you see your app in the future. If there will be 20 billion records in a few months, and closer work with the database, it is PostgreSQL.
If a framework is needed rarely updated the warehouse, and MySQL will do. Why shoot from a gun on sparrows?
If the gun and the gun cost the same, weigh the same, places are the same, and sparrows is required not to produce, and to destroy — then why not to shoot a gun?

If you have plans to go on the "Lada" and "Volkswagen" when operating costs do not concern you, then does it make sense to go on "the Zhiguli"? After all, the Volkswagen nicer. - Jayde.Tow commented on October 8th 19 at 02:28
October 8th 19 at 02:27
Recently moved one project (500 million records) from mssql to postgresql. Of course I had a little dopilivat, but overall very smooth.

Migrated with I had the trial version (string fields was not)
Tulsa worked quite slowly (6 hours), but for me it was the easiest way.
What do you mean string, Blob?
Not found on the website of the limitations on the trial version - Jayde.Tow commented on October 8th 19 at 02:30
text, nvarchar, etc.
The limitation of the trial he when you transfer replaces the first symbol rows in 'T' - Jayde.Tow commented on October 8th 19 at 02:33
Idea! The restriction can be circumvented. Before the migration to add the extra character at the beginning of each line. After migration to remove. - Guiseppe commented on October 8th 19 at 02:36
bulletproofcupid, then it is easier hands to write methods (in the language in which program) exporting data from one database to another - Raquel_Towne47 commented on October 8th 19 at 02:39
October 8th 19 at 02:29
Comparing MySQL and Postgree on speed. Ogromnaya difference between one-stream and multi-stream mode (many links from about 100 threads).

I write from memory test results (run on laptop). Time — 1 second. Value — the number of records cover less than 1 KB (one field and key).

MS SQL: ~700 records per second. both of these modes.
My SQL: 20 in one, 120 in lot.
Postgree: 100 in one, 100 in the lot. But in multi-stream crashes errors one after the other, from which I concluded that it does not work correctly in multi-stream mode.

In General I recommend to compare speed, it is not so big as it seems at first glance... IMHO, this is one of the most important parameters.

I finally chose MySQL.
Very large spread between mssql and postgresql. Something I can't believe... What version of compared? - Jayde.Tow commented on October 8th 19 at 02:32
The spread between MySQL and Postgree in multi-threaded mode is not big — about 100 and 120 (but MS SQL — Yes, there's more).

Compared to September 2011, have used the latest version. No adjustment was made. The table was stored on disk.

If someone else made measurements — offer to share the results. And why do people base their choice on the advice like "it's better" without numbers, instead of independently to take measurements.

The author of the question can make measurements all the parameters that interest him, do not be lazy. After the base you will be working for a few years and data is the heart of any business. So you need not be lazy to spend a day and to measure. That's the recommendation. - Jayde.Tow commented on October 8th 19 at 02:35
You have each renewal is a separate transaction?
What errors appeared in PostgreSQL?
No settings to make — it's hard. If we are testing, it is necessary to see to it that the base consumed approximately the same amount of resources and provided similar reliability levels. Otherwise it is not clear at all that compare. - Guiseppe commented on October 8th 19 at 02:38
October 8th 19 at 02:31
PG — versioni, his record does not lock reading, MS blokirovanie. Isolation of transactions is implemented completely differently.

A simple example:
ms, when executing a query insert into table (value) select max(value) +1 from table, in read commited will not allow duplication of values when you insert multiple sessions, and PG — easily.

In this context, MySQL may not be able to move much easier. He is also blokirovanie.
>MS — blokirovanie
since 2005 already has and VersionNT. - Jayde.Tow commented on October 8th 19 at 02:34

Find more questions by tags SQL ServerPostgreSQLMySQLMigration of data