Expanding on my suggestion to use Views; the core improvement is the splitting of the table:
A View is a stored query, and any index of the base tables can be accessed (there are exceptions). There are two types of Views when it comes to updates:
- atomatically updateable Views
- complex/composite Views
The first type needs to be a simple, non-aggregating
SELECT directly on tables, or a similarly simple sub-query, where each column expression can be directly mapped to a base table column, e.g. (note that I assume you have split your table into
CREATE VIEW <view> AS
FROM <static_data> AS a
JOIN <dynamic_data> AS b
This is likely all you need. All indexes on both tables are used, and
UPDATEs work just as on a normal table.
This is simple and practical, and would benefit from splitting the tables, as then only a single table with a few columns has to be updated (and its data hold in a transitional state). In any case, it should (untested) boost reaction time in QGIS.
Only if you plan to have more complex View definition it is necessary to have an
INSTEAD OF trigger in place to reroute data to the respective base tables (probably with intermediate pre-processing).
It could be beneficial to have a trigger on your base table with a
ON BEFORE UPDATE OF <dynamic_column_a>, <dynamic_column_b> statement and a
WHEN (OLD.* IS DISTINCT FROM NEW.*) filter that handles the actual
UPDATE for when its conditions are met (and only then).