There are a number of reasons why a relational database would be great for key/value pairing tables.
1. Faster map scanning
Use triggers to set a timestamp of all updated blocks in the database. This would allow programs that would normally have to scan the whole database to only scan actual changed data. Imagine you just wanted to update a map. You would only need to scan the changed blocks since the last time the map was generated. There are programs that scan the map for protection blocks, shops, or locked chests. Again, scanning only the changes is far faster.
2. High Availability
PostgreSQL can be clustered, allowing any since server in the cluster to fail or be taken down while the database is in use.
3. Deployment
Deploying MT servers is much easier if you can just create a container on a 5400rpm drive. Storing the database on the local machine might not be desirable. Imagine having 4 servers with many containers on them. The software doesn't need 15k rpm drives. The game and it's accompanying files are normally referenced only at the time the server is started, but the database is reading and writing all the time.
4. Backups
With PostgreSQL, you can have continuous backups where transaction logs are written to a different server or device. This allows for recovery up to the second should anything happen.
5. Speed
PostgreSQL allows for table partitioning. This breaks up a large table into a smaller series of tables that act like a big table. It's possible to pre-generate tables that represent different segments of the MT world and write them to entirely different tables. This would be done with leveldb, but not without significant work and coding.
6. Customization
Imagine you had a specific set of contributing factors that went into creating blocks and you wanted to save that information for debugging purposes. You could create a table containing your server configuration, such as the MT version. mods, and mod version. Something like 'Minetest 0.4.10, ethereal 1.10m', in a table with numeric Ids as the key. You could add a column to blocks such as config_id that would reference the max config ID. This would allow you to know how each block was generated.
7. Map refresh
With PostgreSQL, I used to save blocks when inserted into the database in two different tables, blocks and blocks_orig. When the map got too messy I could easily refresh the block using the originally generated block. I could just delete the block and allow it to regenerate, but changes in the mapgen system and mods often make this a messy process. I saw this first hand when a server operator wanted to replace a corrupt block and got something completely unexpected which has to be world edited away quickly.
burli wrote:That's great to hear. But I have a question
Minetest only uses key/value storage. Wouldn't it be more effecient to use key/value databases like leveldb, redis or maybe couchdb instead of SQL databases? Is there any reason except that DBs like Postgresql are more common? Is there a difference in the performance?
It's no rant. Just curious