Should cached data be kept separate from primary tables?

Question

I have a table in an SQL database for storing information about some business object that looks something like this (details changed so as not to give away what company I work for):

CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price MONEY NOT NULL,
    ...  -- Some other stuff
);

This data is updated "manually" (with a tool) and rarely changes.

I'm planning to cache the result of an expensive operation that we normally do at runtime in the DB, which maps 1-to-1 with the aforementioned table. The cache should be able to be cleared and rebuilt every few minutes.

My instinct is to keep this autogenerated data in a separate table, with a foreign key linking it to the manually updated table. Something like:

CREATE TABLE product_is_popular_cache (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES product(id),
    is_popular BOOLEAN NOT NULL
);

My question is: Is this just unnecessary complication? I could make it a new column on the original table, but mixing human-generated and machine-generated data smells to me. Perhaps there is a performance benefit. I could imagine that deleting and inserting many rows faster than scanning the product table and changing only the relevant rows. Maybe they scale differently.

Answer 1

It is not a requirement that they be separate, so should may be too strong a word.

However, there are certain things you can do when you keep it separate. For example, you could index your static table with a 100% fill factor, which will improve performance, without needing to reindex each time the cache is refreshed. You could deploy the cache to a different filegroup on a faster drive. You might want to exclude the cache from backups, or put it on a different schedule. If the table gets very heavy traffic, you could even replicate it to scale out.

Also, if you are following the principle of least privilege, you may want to assign different permissions for updating the static table vs. updating the cache. That would be easier to administer if the data were kept separate.