How Fast is Windows File System for Database Operations?

Question

I came across a crazy thought and I wanted to share it with you and ask about its feasibility, especially performance wise:

The idea is to manage object database operations by:

• creating a folder for each class named after class name
• creating a sub-folder for each sub-class named after sub-class name
• creating a file for each object named after its unique ID
• creating a sub-folder for each index named after names of indexed fields
• creating a shortcut file for each index entry referring to the original object file
• reading/writing binary objects by very fast serializer/deserializer
• inserting/updating/deleting objects and index entries by renaming object and shortcut files
• caching/paging by using memory-mapped files
• querying would utilize binary search on sorted file names

UPDATE: Thank you all for your replies. I was thinking this can be even improved by using some compression/encryption library such as 7z, instead of dealing with the OS file system. Otherwise, all of your stated concerns so far are valid. I'm wondering what kind of underlying file system does, for example, Oracle uses

Answer 1

As a practice to learn more about databases sure, but for a real world projekt that should do anyting, no.

There is so much under the hood of databases that unless you actually know exactly what you are doing, and in that case you would not be asking here ;), you will most probable never match existing solutions.

Go fo an existing object database instead and concentrate on the specifics of you application/site/...

Answer 2

Cons:

• On most filesystems, even a 1 byte file takes a full block of 4kb. Can be a huge problem depending of the kind of object you wanna store in your database.
• Most filesystems are not designed to scale with directories containing millions of files.
• Complex queries will require the opening/reading/deserializing/closing of million of files, and thus will be very slow.

Answer 3

Its an interesting concept, a few thoughts on immediate issues you will have to resolve.

• Windows file performance takes a hit after a few hundred thousands files, you need to alter certain aspects (turn off 8.3 and last update timestamps) to get it to not cause delays when reading the file system.
• Locking - the locking mechanism will be an interesting challenge, you need to be able to lock things for update, but permit reads simultaneously.
• ACID - whilst performing operations against this 'database' how would you enforce the ACID principles - each of them is a non-trivial problem.