Fetch the most recently updated records in a database across multiple tables in a performant manner

Question

I have an Angular + Django web application in which Django functions as my backend.

In this web application, I have 12 different tables about articles, each table representing a different kind of article, which means they contain vastly different columns at times. They all have the fields creation_datetime and update_datetime but are otherwise very dissimilar.

Example:

//SQLite DDL for character table
CREATE TABLE "wikientries_character" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "player_character" bool NOT NULL,
    "alive" bool NOT NULL,
    "name" varchar(200) NOT NULL UNIQUE,
    "gender" varchar(10) NOT NULL,
    "race" varchar(50) NOT NULL,
    "title" varchar(200) NULL,
    "description" text NULL,
    "is_secret" bool NOT NULL,
    "current_location_id" integer NULL REFERENCES "wikientries_location" ("id") DEFERRABLE INITIALLY DEFERRED,
    "organization_id" integer NULL REFERENCES "wikientries_organization" ("id") DEFERRABLE INITIALLY DEFERRED,
    "creation_datetime" datetime NOT NULL,
    "update_datetime" datetime NOT NULL
    
) 

//SQLite DDL for location Table
CREATE TABLE "wikientries_location" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" varchar(200) NOT NULL,
    "description" text NULL,
    "is_secret" bool NOT NULL,
    "creation_datetime" datetime NOT NULL,
    "update_datetime" datetime NOT NULL,
    "parent_location_id" integer NULL REFERENCES "wikientries_location" ("id") DEFERRABLE INITIALLY DEFERRED
)

I want to fetch the X most recently updated articles across all 12 tables, so the top X entries if ordered by update_datetime.

The naive approach to doing this is fetching all entries across all 12 tables (which is at least 12 queries) into your application server, putting them all in a massive list/array, sorting by update_datetime and then taking the top X entries. This is what I have implemented currently (I have a small database at the moment), but I don't think that is viable for larger datasets because it requires fetching a lot of data from the database multiple times.

The only other approach I can think of, is the following:

• Create new table Recently Updated Articles that references an article across the 12 tables. To do so it would have the columns "table_name" (varchar of the table the record is in) and "pk" (the primary key of the specific record on the table). It shall also have the column "update_date" which is the value
• Recently Updated Articles shall never be manipulated directly by any view, only read from
• Implement triggers on my application server (Django Signals) that update Recently Updated Articles whenever a Create, Delete or Update operation happens on one of my 12 tables.

I could then get references to the X most recently updated articles by just querying Recently Updated Articles and then fetching content of those referenced articles from my 12 tables.

What's stopping me from doing this is that it seems... ugly. But I'm also not quite seeing a better way of doing this. Is there a better solution for this kind of problem?

Answer 1

Think about your objects first.

You have something like :

Article 
{
    id
    name
    description
    creation
    update
}

And then:

LocationArticle : Article
{
    //extra fields
}

CharacterArticle : Article
{
    //extra fields
}

You can make your tables match these classes:

CREATE TABLE "Article" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "type" varchar(200) NOT NULL,
    "name" varchar(200) NOT NULL,
    "description" text NULL,
    "creation_datetime" datetime NOT NULL,
    "update_datetime" datetime NOT NULL
)

CREATE TABLE "LocationArticle" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "is_secret" bool NOT NULL,
    "parent_location_id" integer NULL REFERENCES "LocationArticle" ("id") DEFERRABLE INITIALLY DEFERRED
)

Now you can select the most recently edited article ids and type from the Article table in a single select, and LEFT JOIN where id=id to the other tables for the extra fields if required.

Answer 2

The 12 different tables indeed sound a little fishy. But if you're somehow forced to use that design, you might implement a database view as a union of selects from the various tables, and run your queries against this view. Let the database engine figure out how to optimize this:

CREATE VIEW ALL_ARTICLES AS
SELECT CREATION_DATETIME,UPDATE_DATETIME,'TABLE1' FROM TABLE1
UNION
SELECT CREATION_DATETIME,UPDATE_DATETIME,'TABLE2' FROM TABLE2
UNION ...

In a realistic case, you would probably have SUBJECT, AUTHOR and ID columns so you could present search results nicely and access selected articles.

Answer 3

The answer provided by @Ewan is the one I have accepted, as at a larger scale it seems to me that it is the correct one. It models the data in the database in the way you'd also think about it, even though that makes handling it somewhat more complicated.

However, @Hans-Martin Mosner's approach also is very valid and in my case a lot less work to implement. As such, I went with that, created a view, put a Django model on top of it and created some helper functions that allowed me to fetch the original records via the ORM fairly easily.

Here my excerpt from Django on how I implemented this for future readers:

#mymodels.py
class ErroneousViewException(Exception):
    """Custom Error that is raised when the view V_ALL_ARTICLES turns out to contain incorrect information"""
    def __init__(self, faulty_record_id: int, table: str, message: str) -> None:
        self.faulty_record_id = faulty_record_id,
        self.table = table
        self.message = message
        super().__init__(message)

class AllArticlesView(models.Model):
    table_name = models.CharField(max_length=100, help_text="The name of the table that the record belongs to")
    record_id = models.PositiveIntegerField(help_text="The id of a record in the table with the given name")
    update_datetime = models.DateTimeField(db_index=True, help_text="The time the referenced record was last updated")
    guid = models.CharField(max_length=100, primary_key=True)

    class Meta:
        managed = False
        unique_together = ('table_name', 'record_id')
        db_table = "v_all_articles"

    def get_record_model(self) -> models.Model:
        (app_name, model_name) = self.table_name.split("_")
        return apps.get_model(app_name, model_name)
    
    def get_article_object(self) -> models.Model:
        article_model: models.Model = self.get_record_model()
        article_objects = article_model.objects.filter(pk=self.record_id)[0]

        if len(article_objects) == 0:
            raise ErroneousViewException(
                self.record_id,
                self.table_name,
                message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name} but no such record exists!"
            )

        if len(article_objects) > 1:
            raise ErroneousViewException(
                self.record_id,
                self.table_name,
                message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name}, which should be unique. It fetches more than 1 record though!"
            )

        return article_objects[0]


# views.py <-- Here we determine which subset of articles to query from the database
class RecentUpdatesPagination(APIView):
    queryset = mymodels.AllArticlesView.objects.all().order_by("-update_datetime")
    pagination_class = PageNumberPagination
    
    def get(self, request, page_number=0):
        page_size = settings.REST_FRAMEWORK["PAGE_SIZE"]
        page_start_index: int = page_size * page_number
        page_end_index = page_start_index + page_size

        article_references_on_page: QuerySet = self.queryset[page_start_index : page_end_index]
        articles: List[models.Model] = [article_reference.get_article_object() for article_reference in article_references_on_page]
        serialized_articles = [self.serialize_article(article) for article in articles]

        return Response(serialized_articles)

    def serialize_article(article):
        #A lot of logic for serialization

# create_all_articles_view.sql
create view v_all_articles as
    select 'wikientries_character' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_character' || id AS guid
    FROM wikientries_character
    UNION ALL
    select 'wikientries_creature' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_creature' || id AS guid
    FROM wikientries_creature
    UNION ALL
    select 'wikientries_diaryentry' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_diaryentry' || id AS guid
    FROM wikientries_diaryentry
    UNION ALL
    select 'wikientries_encounter' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_encounter' || id AS guid
    FROM wikientries_encounter
    UNION ALL
    select 'wikientries_item' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_item' || id AS guid
    FROM wikientries_item
    UNION ALL
    select 'wikientries_location' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_location' || id AS guid
    FROM wikientries_location
    UNION ALL
    select 'map_map' AS table_name,
        id AS record_id,
        update_datetime,
        'map_map' || id AS guid
    FROM map_map
    UNION ALL
    select 'wikientries_organization' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_organization' || id AS guid
    FROM wikientries_organization
    UNION ALL
    select 'wikientries_quest' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_quest' || id AS guid
    FROM wikientries_quest
    UNION ALL
    select 'wikientries_rules' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_rules' || id AS guid
    FROM wikientries_rules
    UNION ALL
    select 'fileserver_sessionaudio' AS table_name,
        id AS record_id,
        update_datetime,
        'fileserver_sessionaudio' || id AS guid
    FROM fileserver_sessionaudio
    UNION ALL
    select 'wikientries_spell' AS table_name,
        id AS record_id,
        update_datetime,
        'wikientries_spell' || id AS guid
    FROM wikientries_spell;