Re: Curious question about physical files to store database

Patrick Dung wrote:
> As I have seen, some database created or pre-allocate large physical files on the file system to as
> the backend of the database tablespace.
>
> For Postgresql, I have observed that it created several files in the base and global directory.
>
> It may be by design, what is the pros and cons of this behavior?

You are a bit unspecific; are you talking about Oracle?

The disk layout is of course by design.

Oracle uses large container files and keeps its data in those.
As far as I know, this is to bypass file system functionality.
Oracle usually recommends direct I/O and bypasses file system
functionality (caching etc.) as much as possible.

I guess one reason for this is that, historically, file system
implementations incurred more overhead than they do now and had
all sorts of other problems with larger amounts of data.
These days, filesystems perform much better, so this is no longer
necessary, but Oracle is quite old software.

Another reason may be Oracle's quest to rule the world, and the
storage layer is part of that. Lately, Oracle tries to get everybody
to use ASM, their storage layer, which completely bypasses
file system functionality.

PostgreSQL, on the other hand, does not have the resources or
intentions to write a better file system and actually uses
file system capabilities like caching to improve performance.

PostgreSQL keeps what Oracle calls segments as individual files
in a directory structure.

Yours,
Laurenz Albe