Ask HN: Database migration project – which database?
Hi! I'm tasked with migrating a existing db project from a proprietary database to "a better solution" as the current one is becoming quite unstable due to various bugs and the fact that it reached EOL years ago.
The workflow is pretty simple: mass update existing records or appending new records once in a while (this will be done in a controlled fashion invoked by someone - no services) -> do some ETL work -> re-build existing tables from source after doing some join magic -> export tables for delivery to customers.
Tables have around 10 to 100 million rows and 10 to 300 fields (will probably normalize a bit..) The data is relational and as for now the size is ~400GB which I expect to grow around 3% each time. There wont be much reads as the tables will be exported for further process and the the writes will be in a controlled fashion as stated above.
I'm looking at mysql and postgresql - which one would you choose? Or something else? It also depends on how the DB is used: read or write heavy? Is the data relational? How much? How do you need to query the data? Etc. Updated with "The data is relational and as for now the size is ~400GB which I expect to grow around 3% each time. There wont be much reads as the tables will be exported for further process and the the writes will be in a controlled fashion as stated above." Queries will only be done as spot checks to ensure correctness. postgrsql's future is pretty safe, whereas MariaDB/MySQL was pretty worrying for a while. pgsql is closer to the (SQL) standard and has a rock-solid track record. However, given the size of your project, you might want to test both in "real" conditions, see which ones work correctly. You should perhaps even call for a contractor.