8 ms·

I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak,

by pvh 14y ago

I absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors indices? Why would you want to roll your own full text search functionality? The alternative is to introduce whole other services to your infrastructure! In closing: if you've got 'em, smoke 'em.

monstrado 14y ago
Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. For example, people using some sort of abstraction on top of their database (an ORM) often switch between databases. With that being said, if you're actually designing a complex database back-end for an application you will likely want to spend time becoming very familiar with the database of choice, learn the advantages and then exploit them. This could prove to be pretty difficult to just replace on a whim later on. edit: Switching between an RDBMS and a "NoSQL" database would be much different though. Switching between one SQL server to another is one thing, but switching from a SQL server to a "NoSQL" server is going to be depend on what kind of model you're changing to. For example, MongoDB & Riak are both "NoSQL" databases but they are both quite different fundamentally.
- deafbybeheading 14y ago
  >Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. If you're doing something simple, sure, maybe. But beyond trivial things like DDL syntax and query syntax, there's query optimization (even when only going in via ORM, because schema design can affect this), tuning, backups, HA, monitoring, and a dozen other things. You're already making a huge non-portable investment in using a complex tool like a database. In comparison to this, introducing dependence on its non-standard features is pretty small change, so you might as well stop worrying (considering how seldom people actually migrate), raise a glass to YAGNI, and learn to love your database. Maybe in another five years your or some successor will end up cursing the day you made that decision, but if you could really use non-standard feature X and it's sitting right there in front of you, it's silly to shun it "just in case."
gfodor 14y ago
It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom data types, for example, is quite weak, when considering the tradeoffs. They move complicated logic into the database, are unfamiliar to most developers, and end up needing to be reverse engineered if you want to move your data into different storage. I think they should be an option of last resort, or used in situations where the cost/benefit ratio is so skewed in their favor you'd be crazy to not take advantage. Generally speaking these are the rarest of cases.
- jeffdavis 14y ago
  "Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators. Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension". If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.
  - einhverfr 14y ago
    The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.
mb_72 14y ago
And I, also, disagree. I'm using the Developer Express ORM, XPO, and it provides 'drop-in portability' between a range of databases. I've successfully run my small-mid scale app on three different backends - MS SQL, MySQl and Postgres - simply by changing the connection string. Yes, it's .NET / Windows only, but drop-in portability is definitely not a myth.

by pvh 14y ago

monstrado 14y ago

Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. For example, people using some sort of abstraction on top of their database (an ORM) often switch between databases. With that being said, if you're actually designing a complex database back-end for an application you will likely want to spend time becoming very familiar with the database of choice, learn the advantages and then exploit them. This could prove to be pretty difficult to just replace on a whim later on. edit: Switching between an RDBMS and a "NoSQL" database would be much different though. Switching between one SQL server to another is one thing, but switching from a SQL server to a "NoSQL" server is going to be depend on what kind of model you're changing to. For example, MongoDB & Riak are both "NoSQL" databases but they are both quite different fundamentally.

deafbybeheading 14y ago
>Depending on your application and how you're taking advantage of your database, you could drop-in replace a database. If you're doing something simple, sure, maybe. But beyond trivial things like DDL syntax and query syntax, there's query optimization (even when only going in via ORM, because schema design can affect this), tuning, backups, HA, monitoring, and a dozen other things. You're already making a huge non-portable investment in using a complex tool like a database. In comparison to this, introducing dependence on its non-standard features is pretty small change, so you might as well stop worrying (considering how seldom people actually migrate), raise a glass to YAGNI, and learn to love your database. Maybe in another five years your or some successor will end up cursing the day you made that decision, but if you could really use non-standard feature X and it's sitting right there in front of you, it's silly to shun it "just in case."

gfodor 14y ago

It's not a matter of drop-in portability, it's a matter of reducing the complexity of migration as well as developer confusion. Often the case for using custom data types, for example, is quite weak, when considering the tradeoffs. They move complicated logic into the database, are unfamiliar to most developers, and end up needing to be reverse engineered if you want to move your data into different storage. I think they should be an option of last resort, or used in situations where the cost/benefit ratio is so skewed in their favor you'd be crazy to not take advantage. Generally speaking these are the rarest of cases.

jeffdavis 14y ago
"Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators. Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension". If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.
- einhverfr 14y ago
  The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.

mb_72 14y ago

And I, also, disagree. I'm using the Developer Express ORM, XPO, and it provides 'drop-in portability' between a range of databases. I've successfully run my small-mid scale app on three different backends - MS SQL, MySQl and Postgres - simply by changing the connection string. Yes, it's .NET / Windows only, but drop-in portability is definitely not a myth.