I am not able to look as closely as it deserves ...
... but I see two seq scans in your explain in a loop -- this is
probably not good. If you can find a way to rewrite the IN clause
(either de-normalizing through triggers to save whatever you need on
an insert and not have to deal with a set, or by using except in the
query, or someing else more creative)...
Also -- there is a good book on temporal databases by Snodgrass that
might give some interesting ideas; maybe you have already seen it, but
still. I am thinking you could increment a sequence variable to give
you a "tick" integer with each action, rather than trying to use
timestamps with all their overhead and inaccuracy (1 second is a long
time, really). Lamport also did work on clocks that might apply.
Also have you tried dropping all your fk and checks just to see if you
get a difference in speed on an update? It would be interesting,
perhaps.
If you could get rid of the sorted limit 1 clause in your function,
there would be less variablity and make it easier to understand; you
probably need to denormalize somehow, perhaps using ticks; I am not
sure....
Could a trigger set your previous_value on insert or update, rather
than querying for it later?
> I'm now thinking of separating each activity into its own database, in
> the hopes that this will improve the system speed enough to do what I
> need. But I'm far from convinced that this will really help.
Better to figure out the real problem -- more interesting, more scalable.
Hope my incoherent brain dump lights a spark.