Comment by mdasen | Hacker News Reader

Comment by mdasen | Hacker News Reader

Use a good abstraction library and start coding. Seriously.

PostgreSQL tends to be what is favored by people here. I used to use it for everything until I tried to set up a replicated cluster with it. PostgreSQL's replication is severely lacking. Slony-I, the most mature of the bunch, is very difficult to set up (with many steps needed for every single table) and a lot slower than MySQL's replication (due to its use of SQL and triggers rather than binary log shipping as well as the fact that its design causes communication costs to grow quadratically).

However, PostgreSQL's query planner is a lot better than MySQL's (especially if you're doing something like subqueries) and I find that complex queries run decently faster. The community process is also a lot more attractive.

To address the Oracle question:

Oracle cannot withdraw InnoDB. It's GPL licensed. Anyone can fork it should Oracle decide they don't want to play ball - just as anyone can fork MySQL. In fact, there are already forks underway including Drizzle. So, that isn't a big deal. The bigger deal would be if Oracle decided to halt future development. I'm guessing they won't since it would simply mean they would loose control to the community that still has rights to the GPL'd code.

In the end, don't worry about this issue. It distracts from what is really important: actually creating something. They both work fine. There are plenty of abstractions that will allow you to create code that will work with either with no modifications. Do that, build your application, and let the pundits from either side debate this issue until they're blue in the face while you're actually creating useful things.

Your job as a developer is to pick a technology and build your application. Building/implementing an entire extra layer onto your project just so you don't have to make a choice is a bad move. The technical term, I believe, is Yak Shaving.

Postponing the choice until you have enough data to make an informed decision is a good idea, though, and trying to isolate direct interaction with the database to a relatively small number of code paths would be worth doing regardless. If your code is so entangled with the database code that you can't separate them easily, you have much more immediate problems than theoretically having to coping with scalability someday (if you're lucky).

Out of curiosity, how many times in your career have you decided to switch databases halfway into a project?

In 15 years of doing this for a living, I've never actually needed to do so. With that in mind, I don't feel particularly worried that my SQL is not instantly portable.

I've moved from sqlite to serialization via Lua on one, when clarifying the problem made it clear that using a relational database was actually a poor fit. Most of my examples aren't database-related, though. I agree that switching databases doesn't happen much, I'm just noting that having a major, conceptually distinct part of a project entangled with the others is usually a sign of other problems. (Maybe that's just my experience maintaining legacy codebases, though.)

I think that getting preoccupied with moving from one database to another for scalability reasons is usually wishful thinking -- it's like worrying about all the positive attention you're getting.

in theory lots of things can 'cause scalability problems. I like to write a lot of my code in the database, and I would never be able to move to mysql from postgres, since mysql just doesn't support half the stuff postgres does. I might be able to migrate to sql server, db2, or oracle, but not the other way.

You shouldn't have to build that layer yourself. I'm only familiar with the Python tools like Django and SQLAlchemy that abstract out which database you use, but I know Rails works similarly, and I'm pretty sure there are existing solutions for other languages.

> Use a good abstraction library and start coding. Seriously.

I'm pretty skeptical of this approach. Cross platform development is a pain in the ass, whether we're talking databases or operating systems. I say pick the most powerful database you can and marry it. Use its features to the fullest and ignore portability. I suggest db2 or oracle.

if you are a startup you are not even considering db2 or oracle. you consider exactly what the op suggested. further, as your business grows so to will your needs. you may start with one backend in a particular configuration and evolve and/or migrate to a different configuration/platform.

abstraction is "the right thing to do" (tm).

> if you are a startup you are not even considering db2 or oracle.

Why not? The free versions exceed postgresql. If you succeed and face heavy load, then you just throw some money at the problem rather than blow hundreds of man-hours fidling with the hacks people use to scale the free DBMSes.

You can just throw money at Postgres problems and make them go away. There are a number of very capable firms that do nothing but Postgresql support. And, they don't hold you captive w/ licensing. And if your needs are really that unique they can help you with building extensions to postgresql that support the capabilities you need.

See http://www.commandprompt.com/support/ for an example, or http://pgexperts.com/services.html

Agreed. I love developing locally with a version controlled SQLite database and then deploying to *sql and having everything just work. Sometimes there are differences that need to be worked around (same as cross platform development), but starting with the assumption that your db might change is a good thing, in my opinion.

For long term maintainability, absolutely abstraction is the way to go. Any pros and cons listed now can help you decide, but what about a year from now? Two? Five?

This goes double if you are considering commercial products.

For various reasons we are forced to use $Vendor, but we are not a commercial shop and we always have the fear factor that $Vendor will price us out (they occasionally try it on by "forgetting" our academic discount). We hid all the DB interactions in two layers (one to hide the relational nature of the database, and the other to hide the specific implementation). As a result, I am in the position to tell $Vendor "How much? Well, you can give us this price or we can move to postgress - either is fine by me". And mean it. And they can tell.

Reply on news.ycombinator.com