Re: Single-file DBs WAS: Need concrete "Why Postgres - Mailing list pgsql-hackers

From Josh Berkus
Subject Re: Single-file DBs WAS: Need concrete "Why Postgres
Date
Msg-id 200308220907.52994.josh@agliodbs.com
Whole thread Raw
In response to Re: [GENERAL] [pgsql-advocacy] Need concrete "Why Postgres  (Andrew Dunstan <andrew@dunslane.net>)
Responses Re: Single-file DBs WAS: Need concrete "Why Postgres
Re: Single-file DBs WAS: Need concrete "Why Postgres
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: "Matthew T. O'Connor"
Date:
Subject: Re: [GENERAL] Buglist
Next
From: Manfred Koizar
Date:
Subject: Re: [GENERAL] Buglist