Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to decide between storing some XML data either under uniquely identified file paths on a Linux filesystem, or in text columns in a database table. All other things being equal, is access time given a item identifier (either incorporated into the file path or a database unique column) roughly equal?

I expect maybe 20,000 items.

share|improve this question

closed as not a real question by mdpc, Ward, Khaled, Dave M, Alex Feb 18 '13 at 20:47

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
I think that this question does not really have a real specific answer. It depends on a lot of conditions and variables that are different from situation to situation. Everybody can offer conjectures...but they are just that...not specific answers. Well, I guess the answer is it depends! –  mdpc Feb 18 '13 at 3:38
1  
Depends on the filesystem, OS, other parameters... With default Linux ext3, don't do it. –  ewwhite Feb 18 '13 at 17:15

2 Answers 2

up vote 6 down vote accepted

20,000 is nothing for a database, but hefty for a file system if they are all in a single folder.

If you break them apart into separate folders (perhaps use the first 2 or 4 letters of the GUID) this will help.

It also depends on your workload. If you are doing a lot of blocking/locked queries on the table, use the file system.

If you want to store metadata with the files and access them from a 3rd party, use the db.

If you want to roll your own authentication to interface with the files, use the db.

If you want to be able to easily modify the files from say notepad, use the file system.

If they are big, use the file system. You don't want 20gb of files in your table if you can avoid it.

share|improve this answer
    
You should mention htree and dir_index for ext3 or newer. –  Mircea Vutcovici Feb 18 '13 at 1:00
    
Don't forget that you can use both: put the file in the filesystem and the path to the file and other metadata in the database. This has the added benefit of making it a lot easier to rearrange the files in the future –  DerfK Feb 18 '13 at 1:04
1  
Oh, and if it occurs to you to just shove the XML file into the database whole, then you're probably doing it wrong. –  Michael Hampton Feb 18 '13 at 1:07
    
@MirceaVutcovici - I'm a Windows/SQL Server admin, I've almost no idea about ext3 or what those flags mean. Feel free to elaborate if you like! –  Mark Henderson Feb 18 '13 at 1:27
2  
@DerfK, that way you pay both prices... unless you organize the files into lots of directries, the filesystem cost will be huge. –  vonbrand Feb 18 '13 at 2:10

File systems are databases. They are just optimized to work for a specific task - to hold rather large objects in a hierarchy.

When you decide if you want a database or a filesystem there are a lot of factors to take in consideration:

  • Performance - for those are benchmarks.
  • Manageability - This depends on who will do the maintenance.
  • Scalability - This depends of the current size of the database, on the expected size in a few years, on the rate of growth and how easy is to add new servers in your environment.
  • Normalization - how you will store the objects - like BLOBs, or like a parsed structure?
  • How often the objects will change? are searched and in which way.

If you intend to use them as BLOBs, a filesystem is faster. The larger they are the better are served as files.

If you need structured data, a parsed XML in a database is much faster.

See:

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.