Re: large database

On Mon, 10 Dec 2012 15:26:02 -0500 (EST) "Mihai Popa" <mihai(at)lattica(dot)com> wrote:

> Hi,
>
> I've recently inherited a project that involves importing a large set of
> Access mdb files into a Postgres or MySQL database.
> The process is to export the mdb's to comma separated files than import
> those into the final database.
> We are now at the point where the csv files are all created and amount
> to some 300 GB of data.
>
> I would like to get some advice on the best deployment option.
>
> First, the project has been started using MySQL. Is it worth switching
> to Postgres and if so, which version should I use?

I've been managing a few large databases this year, on both PostgreSQL and
MySQL.

Don't put your data in MySQL. Ever. If you feel like you need to use
something like MySQL, just go straight to a system that was designed with
no constraints right off the bat, like Mongo or something.

Don't put large amounts of data in MySQL. There are lots of issuse with it.
Despite the fact that lots of people have been able to make it work (me,
for example) it's a LOT harder to keep running well than it is on
PostgreSQL. MySQL just isn't designed to deal with large data. As some
examples: lack of CREATE INDEX CONCURRENTLY, the fact that the default
configuration stores everything in a single file, the fact that any table
changes (including simple things like adding a comment, or seemingly
unrelated things like adding an index) require a complete table rebuild,
and the fact that if you use anything other than INT AUTO_INCREMENT for
your primary key you're liable to hit on awful inefficiencies.

PostgreSQL has none of these problems.

--
Bill Moran <wmoran(at)potentialtech(dot)com>