4 ms·

"it's a matter of reducing the complexity of migration" You put the code in your application so you can switch out the database, but why do you want to switch

"it's a matter of reducing the complexity of migration" You put the code in your application so you can switch out the database, but why do you want to switch databases? You can't switch out the database for one with more features, because then you're not using the lowest common denominator any more, and you can't switch back. It can't be licensing costs, because postgresql licenses are free. The only other reason I can think of is performance. But trying to avoid database features just because you might want to migrate to a less-featureful database later seems more likely to result in performance problems than prevent them. I agree you don't want to go out on a limb with crazy features just because you can. But those features are there for a reason, and might drastically simplify portions of your application if you use them. And a lot of them are SQL standard, just not supported properly in all database systems. Also, I'd like to point out that database migrations themselves are quite rare once they become established in an organization. You might be able to migrate one fairly simple application if you bend over backwards trying to use only the simplest features; but once a few applications are depending on it, it's just too expensive.

sandGorgon 14y ago
One reason if you are building a shippable product that needs to work with a variety of client databases rather than a choice of your own. 99% of the time, this is why you build DB agnostic apps.
- jeffdavis 14y ago
  The main reason to do that is licensing. You either need to get a redistribution license, or the client needs to work out their own licensing deal -- and either way, you don't want to be beholden when negotiation time comes. That also applies to MySQL, because it's GPL. That does not apply to Postgres, because you can just ship it with the app. I exaggerated a little, there certainly are times when you just don't care what database system you use and would rather have simple migrations at the expense of owning more code. But I think these reasons are weighted much too highly, particularly in the context of postgres.
- yread 14y ago
  I am building a product that can work with SQL Server, MySQL, PgSQL and Oracle. The reason was if the company who buys the software already has the infrastructure and DBAs in that database, they can leverage it. If I were making the decision now I would definitely choose just one and stick with it, because - astonishing amount of things are done differently on these four dbms (idenities/sequences and getting their last value, paging, DDL) - each of these dbms has specific features which would improve the performance or help us develop stuff but we can't use them because they require different architecture (and we want to keep it the same) Don't do software for multiple databases. It's just not worth the trouble.
  - jeltz 14y ago
    Not to mention even the basic data types are differently named and work differently (text vs varchar vs nvarchar vs varchar2). It is especially hard to when you have to work with date and time types/functions.
gfodor 14y ago
I've seen it happen multiple times. The reasons vary, but it generally has to do with scaling up the system into more modularized components and using data stores better suited for particular sub-problems that the "big honking database" was used previously. To be clear, I'm not really suggesting you avoid using fancy features in data stores like Redis or Solr. The relational database, with history as a guide, tends to be the first data store a system starts with, and ends up having everything thrown into it even if it is not the right tool for the job. Over time, chunks of data end up being moved out of the relational monster into other data stores. If your relational database choice is tightly coupled with your implementation, then you'll have a harder time moving things like your search engine into Solr, your queue into RabbitMQ, your counters into Redis, and so on. And yes, you'll also have a hard time moving to a different relational database, which I've also seen happen (on a massive, massive scale actually.) If you stick to using an ORM that provides few leaks in its abstraction, write non-clever SQL, model things relationally using the standard types, generally speaking moving parts of a system out of a relational database into different data stores goes from being nearly impossible to being merely difficult.
- saurik 14y ago
  > using data stores better suited for particular sub-problems Sometimes, rather than swapping out the entire data store to get access to a feature, if you cut the rope that is tying your hands behind your back you may find that your existing data store can already handle your particular sub-problem quite well, and in fact may do so better than the fancy new one that was tailored specifically for that purpose. This happens particularly often with PostgreSQL. If you haven't used its GiST or GIN index types, you really should play around with them, and you should keep them in the same arsenal and pull them out for the same reasons you might decide to just switch everything out to Solr. A lot of the reasons people might want to use Redis are handled quite well by PostgreSQL's type extensibility. In essence, if you are willing to throw everything out and switch to a different tool, it seems entirely inane to ignore that the tool you already have might actually already cover the features you need, especially so if the reason you are refusing to use that functionality is to make it easier for you to eventually jump ship to a tool where you no longer have your self-imposed handicap and are now willing to use the features offered.

sandGorgon 14y ago

One reason if you are building a shippable product that needs to work with a variety of client databases rather than a choice of your own. 99% of the time, this is why you build DB agnostic apps.

jeffdavis 14y ago
The main reason to do that is licensing. You either need to get a redistribution license, or the client needs to work out their own licensing deal -- and either way, you don't want to be beholden when negotiation time comes. That also applies to MySQL, because it's GPL. That does not apply to Postgres, because you can just ship it with the app. I exaggerated a little, there certainly are times when you just don't care what database system you use and would rather have simple migrations at the expense of owning more code. But I think these reasons are weighted much too highly, particularly in the context of postgres.
yread 14y ago
I am building a product that can work with SQL Server, MySQL, PgSQL and Oracle. The reason was if the company who buys the software already has the infrastructure and DBAs in that database, they can leverage it. If I were making the decision now I would definitely choose just one and stick with it, because - astonishing amount of things are done differently on these four dbms (idenities/sequences and getting their last value, paging, DDL) - each of these dbms has specific features which would improve the performance or help us develop stuff but we can't use them because they require different architecture (and we want to keep it the same) Don't do software for multiple databases. It's just not worth the trouble.
- jeltz 14y ago
  Not to mention even the basic data types are differently named and work differently (text vs varchar vs nvarchar vs varchar2). It is especially hard to when you have to work with date and time types/functions.

gfodor 14y ago

I've seen it happen multiple times. The reasons vary, but it generally has to do with scaling up the system into more modularized components and using data stores better suited for particular sub-problems that the "big honking database" was used previously. To be clear, I'm not really suggesting you avoid using fancy features in data stores like Redis or Solr. The relational database, with history as a guide, tends to be the first data store a system starts with, and ends up having everything thrown into it even if it is not the right tool for the job. Over time, chunks of data end up being moved out of the relational monster into other data stores. If your relational database choice is tightly coupled with your implementation, then you'll have a harder time moving things like your search engine into Solr, your queue into RabbitMQ, your counters into Redis, and so on. And yes, you'll also have a hard time moving to a different relational database, which I've also seen happen (on a massive, massive scale actually.) If you stick to using an ORM that provides few leaks in its abstraction, write non-clever SQL, model things relationally using the standard types, generally speaking moving parts of a system out of a relational database into different data stores goes from being nearly impossible to being merely difficult.

saurik 14y ago
> using data stores better suited for particular sub-problems Sometimes, rather than swapping out the entire data store to get access to a feature, if you cut the rope that is tying your hands behind your back you may find that your existing data store can already handle your particular sub-problem quite well, and in fact may do so better than the fancy new one that was tailored specifically for that purpose. This happens particularly often with PostgreSQL. If you haven't used its GiST or GIN index types, you really should play around with them, and you should keep them in the same arsenal and pull them out for the same reasons you might decide to just switch everything out to Solr. A lot of the reasons people might want to use Redis are handled quite well by PostgreSQL's type extensibility. In essence, if you are willing to throw everything out and switch to a different tool, it seems entirely inane to ignore that the tool you already have might actually already cover the features you need, especially so if the reason you are refusing to use that functionality is to make it easier for you to eventually jump ship to a tool where you no longer have your self-imposed handicap and are now willing to use the features offered.