Re: Single-file DBs WAS: Need concrete "Why Postgres

Guys,

> >BTW any comments on storing an entire database in single file? I don't
> > trust any file system for performance and data integrity if I have single
> > 100GB file. I would rather have multiple of them..
>
> I don't see why not. Entire file systems are stored within a single file
> sometimes. Examples: vmware, and IIRC UserMode Linux.

Several database systems use a "single file" for data storage. The problem
with this is that it's not really a single file .... it's a proprietary file
system on top of the host file system. This sort of design makes a couple
assumptions:

1) That the database is better than the host filesystem/OS and storage system
at regulating its use of, and storage of, data files;
2) that your data file will not exceed the maximum file size for the host OS.

Both of these assumptions are, IMHO, based on antiquated data (or on Windows).
Modern *nix filesystems and RAID are very, very efficient at file access and
only a database with the development budget of Oracle could hope to keep up.
Additionally, databases larger than 2GB are becoming increasingly common.

Single-file databases also introduce a number of problems:

1) The database file is extremely vulnerable to corruption, and if corruption
occurs it is usually not localized but destroys the entire database due to
corruption of the internal file structure. Recovery of raw data out of a
damaged single-file database inevitably requires specialized tools if it is
possible at all.
2) Often DBAs are prevented from using normal file operations on the database
files to maintain their systems. For example, try moving a partition on an
MS SQL Server installation. Go on, I double-dog dare you.
3) Due to the necessity of maintaining not only data and metadata, but a file
partitioning structure as well, maintenance on single-file databases is often
more time-consuming but at the same time more crucial (to prevent #1).

--
Josh Berkus
Aglio Database Solutions
San Francisco