Hacker News Reader

Comment by pvh | Hacker News Reader

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.

View on HN

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.

"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.

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.

"Now, it's rare that you are likely to get that into phone numbers..."

That was part of my point -- if your entire business revolves around phone numbers, because you are a phone company or something, then maybe a custom data type makes sense. Then do it, and do it right.

But if you think of a phone number as "something to call" then all you need to do is display it to the right person at the right time, and ensure that it reasonably matches something you can type on a phone. And that sounds more like a domain over a text field with a constraint, to me.

"I use custom types a lot for defining output tuples for stored procedures"

I don't really consider composite types "custom" types any more than an enum is a custom type. Maybe I misunderstood gfodor's statement.

With the ability to define columns which store composite types, the line between a custom data type in C and a composite type plus casts is blurring.

Back in the 7.x line at one point I discovered that you could do something like:

create table foo (...);

create table bar (myfoo foo);

And this table would be write-only, and indeed the backend would crash when reading from it. That's the only really interesting bug I ever found in PostgreSQL. However, I made the case that it would be nice to be able to treat tuples as full-fledged data types and now by at least 8.1, that was supported.

So now I can:

create type foo as (...); -- works with create table too

create function foo_to_text .....;

create function text_to_foo ....;

create cast......

create table bar (my_foo foo);

insert into bar (my_foo) values ('my.foo.as.text'::text);

So the point is that composite types can now be used to blur the lines quite a bit between traditional custom types and the roles that composite types are traditionally used for. You can go a long way prototyping what is useful with composite types, and then later do that right in C.

You don't need to create a custom type to return tuples anymore. (that kind of went out with 8.1). You can use OUT parameters or RETURNS TABLE syntax. RETURNS TABLE is ANSI compliant and supported since 8.3. SQL Server supports RETURNS TABLE as well.

"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.

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.

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.

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.

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.

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.

> 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.

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.

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.

>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."

Reply on news.ycombinator.com