4 ms·

How often does one even migrate DB’s? I’ve always seen the advice, but other than an explicit goal of supporting SQLite + Postgres simultaneously, I’ve never ac

by setr 5y ago

How often does one even migrate DB’s? I’ve always seen the advice, but other than an explicit goal of supporting SQLite + Postgres simultaneously, I’ve never actually ending up needing to move. And given that RDBMS’s tend to do have roughly similar performance profiles, except when they have radically different profiles (e.g. switching from row to columnar, or unique optimizations eg GIS in postgres), there’s not much incentive to be moving around — unless you hit the end of the envelope, at which point you’re probably looking at a re-design anyways if you’re already looking at re-architecting it. And if you’re switching to something with a different performance profile, you’re going to be rewriting those queries anyways. Personally I stopped considering databases as “standardized” or swappable — and using an ORM for hot swapping purposes I think might be as ridiculous as using a library for unifying web server frameworks (you can definitely do it… but your common denominator is fairly pathetic). The only scenario I’ve seen where one changes DB with an explicit goal of minor code changes is migrating from some legacy DB on some ancient version to something more modern (especially today, targeting a DB the cloud can auto-manage), where your ORM DB flexibility won’t help you whatsoever

zimpenfish 5y ago
> How often does one even migrate DB’s? I've never seen it happen on a production system in ~20 years. Might happen at the current gig but that's more down to "Firebase is junk for this job and we're rewriting everything anyway, might as well consider switching DB too".
- tomrod 5y ago
  Lucky! I've done a few.
- mind-blight 5y ago
  I've seen it happen once. Back in 2011, the company I was working for decided to switch all of their apps from MySQL to postgres. We had built everything in Django, so the transition was a lot less painful than it could have been, but it definitely wasn't free.
- BiteCode_dev 5y ago
  Not the point. The point is that people can then build db agnostic libs on top, so that several projects from different teams can use it despite using a different db. E.G: you can use django, no matter if you are a mysql or a posgreq shop and still use the entire ecosystem of apps.
qrios 5y ago
> How often does one even migrate DB’s? We (an insurance company in Europe) have around 200 DBs and half of them have been running for over 15 years. We need to migrate at least these to modern systems in the next 5 years. These new systems all come with their own ORM and RDBMS. Most of the longer existing institutions have the same challenges. But even new companies are in a similar situation. The main problem with ORMs always arises when the business itself changes and/or new requirements (e.g. GDPR) come in.
FalconSensei 5y ago
> How often does one even migrate DB’s? Never happened with me. Only used something different (or even a newer major version) when writing a new tool or doing a whole rewrite of a current tool. Which kind of counts as a new one, right?
spockz 5y ago
In my experience you seldom switch databases. Sometimes you need (to start) supporting multiple. I’m really in favour of having tighter and clearer/cleaner integrations using technology specific features (eg PostgreSQL specific or gRPC specific) getting the most out of the tool than wasting potential for the eventuality that you might need to change it someday. As long as you stay open to the idea of having to change it all someday.
spullara 5y ago
More often than not, you aren't porting. You are making the system work with more than one kind of database. When my first company was acquired and was built on PostgreSQL (which was designed to be hosted) the acquirer wanted an on-premise version that also supported Oracle. Using an ORM made that a relatively simple process with only a few annoying differences between the two databases. Specifically, treatment of strings (oracle empty string is a null string), booleans (no boolean type in oracle) and DDL (oracle doesn't have transactional DDL).
Zababa 5y ago
That makes me think, is there a tool or document about the intersections of features provided by DBs? For example, if you're building for Postgres and MySQL, is there a way to only use features supported by the two? Same for MySQL and SQLite, or Postgres and SQL Server?
chousuke 5y ago
My anecdata says once in two decades. I migrated a few simple Perl webapps some time ago, the oldest being 20+ years old or something? Not completely sure when it was created. I moved them from Oracle to PostgreSQL because we would have had to start paying for Oracle licenses ourselves. It's a very good reason to change databases. There was no ORM, so I just rewrote the queries to be portable and added some runtime branching in cases where that wasn't possible. PostgreSQL has an extension that adds some Oracle compatibility functions and views; that helped. The initial data migration went pretty nicely with ora2pg. For the largest (and most important) application I had the writing side duplicate writes to both databases so I could run two instances in "production" and compare their behaviour. It took a couple weeks of work and testing and then a couple months of observing that the new system works fine; the biggest pain was Oracle's '' = NULL thing and the lack of common SQL syntax for sequences; and the complete lack of tests, of course...
- spicybright 5y ago
  I mean hell, I've seen so many libraries need upgrading every few years. Two decades is a fucking steal in terms of man hours.
tonyarkles 5y ago
> How often does one even migrate DB’s? I have had multiple clients who have wanted to migrate from MySQL/MariaDB to Postgres, but afaik none have ever undertaken the Herculean effort to do so with the multiple years of slightly incorrect cruft that has accumulated in their old databases that have evolved over time.
philwelch 5y ago
If anything, you're more likely to keep the database and migrate whatever application is using it.

by setr 5y ago

zimpenfish 5y ago

> How often does one even migrate DB’s? I've never seen it happen on a production system in ~20 years. Might happen at the current gig but that's more down to "Firebase is junk for this job and we're rewriting everything anyway, might as well consider switching DB too".

tomrod 5y ago
Lucky! I've done a few.
mind-blight 5y ago
I've seen it happen once. Back in 2011, the company I was working for decided to switch all of their apps from MySQL to postgres. We had built everything in Django, so the transition was a lot less painful than it could have been, but it definitely wasn't free.
BiteCode_dev 5y ago
Not the point. The point is that people can then build db agnostic libs on top, so that several projects from different teams can use it despite using a different db. E.G: you can use django, no matter if you are a mysql or a posgreq shop and still use the entire ecosystem of apps.

qrios 5y ago

> How often does one even migrate DB’s? We (an insurance company in Europe) have around 200 DBs and half of them have been running for over 15 years. We need to migrate at least these to modern systems in the next 5 years. These new systems all come with their own ORM and RDBMS. Most of the longer existing institutions have the same challenges. But even new companies are in a similar situation. The main problem with ORMs always arises when the business itself changes and/or new requirements (e.g. GDPR) come in.

FalconSensei 5y ago

> How often does one even migrate DB’s? Never happened with me. Only used something different (or even a newer major version) when writing a new tool or doing a whole rewrite of a current tool. Which kind of counts as a new one, right?

spockz 5y ago

In my experience you seldom switch databases. Sometimes you need (to start) supporting multiple. I’m really in favour of having tighter and clearer/cleaner integrations using technology specific features (eg PostgreSQL specific or gRPC specific) getting the most out of the tool than wasting potential for the eventuality that you might need to change it someday. As long as you stay open to the idea of having to change it all someday.

spullara 5y ago

More often than not, you aren't porting. You are making the system work with more than one kind of database. When my first company was acquired and was built on PostgreSQL (which was designed to be hosted) the acquirer wanted an on-premise version that also supported Oracle. Using an ORM made that a relatively simple process with only a few annoying differences between the two databases. Specifically, treatment of strings (oracle empty string is a null string), booleans (no boolean type in oracle) and DDL (oracle doesn't have transactional DDL).

Zababa 5y ago

That makes me think, is there a tool or document about the intersections of features provided by DBs? For example, if you're building for Postgres and MySQL, is there a way to only use features supported by the two? Same for MySQL and SQLite, or Postgres and SQL Server?

chousuke 5y ago

My anecdata says once in two decades. I migrated a few simple Perl webapps some time ago, the oldest being 20+ years old or something? Not completely sure when it was created. I moved them from Oracle to PostgreSQL because we would have had to start paying for Oracle licenses ourselves. It's a very good reason to change databases. There was no ORM, so I just rewrote the queries to be portable and added some runtime branching in cases where that wasn't possible. PostgreSQL has an extension that adds some Oracle compatibility functions and views; that helped. The initial data migration went pretty nicely with ora2pg. For the largest (and most important) application I had the writing side duplicate writes to both databases so I could run two instances in "production" and compare their behaviour. It took a couple weeks of work and testing and then a couple months of observing that the new system works fine; the biggest pain was Oracle's '' = NULL thing and the lack of common SQL syntax for sequences; and the complete lack of tests, of course...

spicybright 5y ago
I mean hell, I've seen so many libraries need upgrading every few years. Two decades is a fucking steal in terms of man hours.

tonyarkles 5y ago

> How often does one even migrate DB’s? I have had multiple clients who have wanted to migrate from MySQL/MariaDB to Postgres, but afaik none have ever undertaken the Herculean effort to do so with the multiple years of slightly incorrect cruft that has accumulated in their old databases that have evolved over time.

philwelch 5y ago

If anything, you're more likely to keep the database and migrate whatever application is using it.