Migrate static objects into a separate database table

[sorcerykid]

Problem

During a recent discussion about persistent unique object IDs (see PR #14135), I suggested that we should rethink object data storage in Luanti. Several other issues highlight the underlying design limitations of the engine as it relates to object data storage:

• Add parameter to core.clear_objects to ignore objects with a specific attribute #6124
• Add APIs for offline players and entities #15659
• Node bound entities #6963

In order to simplify management of static objects and to fully benefit from persistent unique object IDs, I believe it would be beneficial to migrate static objects into a separate table. The existing database architecture is clearly an impediment to the long-term needs and goal of mod developers. And we should find a solution that is scalable and robust.

For example, the only means of removing objects from the entire database is by reading, decompressing and deserializing every mapblock. Since most mapblocks do not contain any objects, this can be extremely time-consuming. And even for mapblocks with objects, they must still be reserialized, recompressed, and rewritten. However, by storing all objects in a separate table, a single SQL statement would allow for efficient removal.

Similarly, it is only possible for mods to create and remove entities within loaded mapblocks. Once the parent mapblock is unloaded, then all of the associated entities become inaccessible to mods. Forceloading is one workaround to this problem, but that has the side effect of reactivating all entities within the same mapblock. However, if objects were stored in a separate table, then the engine could provide an API to manage unloaded entities.

Solutions

The most important design decision will be the database schema. Some viable options include:

1. Migrate all static objects into a separate "objects" table. Retain the existing BLOB format for each object. Include an automaticaly incrementing ID column as the primary key. Store the list of corresponding object IDs within the BLOB of each mapblock.

2. Migrate all static objects into a separate "objects" table. Retain the existing BLOB format for each object in a data column. Also include an automaticaly incrementing ID column as the primary key in addition to columns for the parent mapblock position.

3. Migrate all static objects into a separate "objects" table. Include columns for each object property (position, name, hp, etc.). Also include an automaticaly incrementing ID column as the primary key in addition to columns for the parent mapblock position.

There are pros and cons to each of these alternatives. Some considerations include:

1. The BLOB format is ideal for data compression. However, since objects tend to have limited data requirements in comparison to mapblocks anyway, the benefits would be marginal.
2. Storing a list of object IDs within each parent mapblock is potentially redundant, It could also lead to inconsistencies if the objects and blocks tables are not properly synchronized.
3. Having distinct columns for the name, staticdata, position, velocity, etc. could allow for easily filtering objects either as part of a new entity management API or by external map tools.
4. The BLOB format could allow for multiple types and versions of entities to co-exist within the same table, each with different sets of properties or with an entirely custom specification.

It should be noted that the automatically incrementing IDs would be managed by the engine, not internally by the database, since we need these unique IDs to be generated at the time of object creation (prior to being stored in the database).

I personally favour automatically incrementing IDs over UUIDs or other random strings of numbers because they can be stored in 64-bits and are fast to generate. However, I'm otherwise ambivalent about whether to use integers or strings for object IDs.

Alternatives

There are no straightforward alternatives, other than to continue adapting the existing database architecture with band-aid approaches, which I do not consider an ideal solution for the long-term.

Additional context

No response

[appgurueu]

👍 agreed with the general concept.

Storing a list of object IDs within each parent mapblock is potentially redundant

Indeed, but it might make sense for performance; we'll have to see. We want to be able to efficiently activate entities along with a mapblock. This requires some map from mapblock pos to entities in the mapblock. An index on entity positions can help with range queries along a single axis, but not across all 3 axes simultaneously.

I personally favour automatically incrementing IDs over UUIDs or other random strings of numbers because they can be stored in 64-bits and are fast to generate.

I don't think object ID generation speed will be a concern anytime soon (esp. in relation to current object performance). See e.g. sfence's benchmarks here (and there is still room for improvement in that code if it turned out to be). Generating random numbers is pretty cheap.

Personally I'm a fan of random IDs. I think a kind of (engine-side) autoincrement complicates the implementation: The ID generation needs to be coupled to the persistence layer. You need to make sure your counter is always saved when any generated IDs are, otherwise you risk collisions (esp. because we can't ensure that modders don't store IDs wherever, which needn't be in sync with the database where the counter is stored ¹).

Footnotes

1. Though arguably this raises the question of how much can we even do in this scenario? Still, a random ID solution has a risk of collision that is completely independent of any consistency woes. ↩

[sfan5]

This is a good idea and under the premise that objects will soon have a lifetime-unique ID, here's my proposal:

We add a new table as follows.

CREATE TABLE `objects` (
    `guid` TEXT,
    `x` INTEGER NOT NULL, -- position of containing block
    `y` INTEGER NOT NULL,
    `z` INTEGER NOT NULL,
    `data` BLOB NOT NULL, -- whatever StaticObject::serialize spits out
    PRIMARY KEY (`guid`)
);
CREATE INDEX `objects_pos` ON `objects` (`x`, `z`, `y`);
-- we could even add a FOREIGN KEY ON DELETE CASCADE that references blocks(x,y,z) if we wanted to. not sure if that's a good idea wrt synchronization however.

Serialized blocks will stop storing any object-related data entirely.
To activate a block we consult the table with x = ? AND y = ? AND z = ?. This will use the index and be fast.

To insert/update an object we update by guid = ? or insert a new row.
This equally allows working with offline objects only by their GUID.

What about external tools?
Basic position filtering is possible but they will need to pick apart the BLOB for more information. This keeps flexibility for the engine.
Won't indexing on a string be slow?
An integer primary key would surely be faster, but in practice I think the index will take care of it.
Just using the GUID immediately resolves a bunch of concerns that arise with a separate ID.
(Note that each row will have an implicit rowid anyway so perhaps we can do optimization tricks with it in the future).

[appgurueu]

Yeah, if we store the block pos then we effectively get the same index as blocks 👍
I was thinking that trying to index directly on the object pos wouldn't work

[sfan5]

One question we will have to answer is: what about the non-relational map backends?
Having objects being loaded from the MapBlock blob and then moved to the separate table (the normal case when opening an older world) should work in practice, but we can't work with a backend where that's flat out not available.
We either have to deprecate and remove those in record time (what about old worlds?) or invent a layer of relational abstraction on top of them.

[sfan5]

Of course the simplest way out is to declare static object retrieval unsupported on unsuitable map backends and provide Lua with a way to check if this is the case. But that's not a long-term solution.