Why are store translation files stored in the database?

Question

I have set up a fresh minimal Drupal 7 site. When I add 2 more language files e.g. French and German the database increases up to 3MB.

Why does Drupal store translation files to the database? What about performance? Wouldn’t it be much faster to load language files from the file system?

Answer 1

It's not likely that loading the translation files from disk would result in a faster page load time than it does from the database -- in fact I would expect it to be much slower. This is essentially the job of a database: store and organize a large amount of data on disk and provide other programs the parts they need quickly. Drupal (and all similar CMS's) are built on the assumption that for processing the content you are better off pulling from the database, and translations are basically just content. Disk-based optimizations usually help when you are trying to deliver unprocessed data as quickly as possible i.e. it's often faster to have the web server pull an image file off a disk instead of loading a script that loads a binary field from a database, and then streams it down.

There are several reasons that the database is likely faster for most use cases:

• To keep translations flexible and manageable there are actually many files that go into translating the back-end (each module can have its own PO file for each language). For a production site you'd then also be looking for a place to store all the translated content so we aren't looking at loading one file, but tens or hundreds of files to process.
• Each file has to loaded, parsed into useful structures, and then matched to the default (English) text. Yes, the site could cache a unified file, but that file could become massive and cause trouble on its own for some OSes and file systems.
• The site content is already stored in the database so overriding displayed text would have to come from two systems (either you'd have some in file and some in database, or you'd have all the default language in database and the translations in files), which adds complexity an likely processing time as Drupal determines where to pull the data from next.

Now think about what happens with the translations in the database:

Since nodes (and other fielded objects) store all the languages in the same tables, most of the time Drupal doesn't have to do anything extra. All the translations for a given field get loaded with the object and then Drupal picks which to display at render time. When Drupal does need to load a translation specifically, Drupal is able to re-write the queries used so it again has the data on hand at the same time it would have had the default language (that rewrite time should be trivial since it had a write a query in the first place). So almost nothing extra needs to be added to the load time to load the needed translated text.

Finally you have to keep in mind that good page load speed is mostly driven by caching, which Drupal does in the database by default, it does not load even the full node objects whenever it can avoid it.

Disclaimer: I don't know that anyone checked a file-based system when the translation system was initially written and I've never seen actual numbers run, so this is mostly speculation.