Best practice for entry position indicators
No matter what solution you choose, you're going to have to "refresh your product cache" anyway, since cached information won't reflect your new product either.  You should let the DBMS do its job and just query against the base table.

In terms of managing arbitrary (manually imposed) sequences, one solution to consider which allows for easy inserts with only periodic recalculation of the whole sequence is to use double instead of int for your sequence number.

When you insert a record between two existing records, set the sequence number of the new record to half way between the existing records.  This can go on for a very long time before you have to break down and recalculate the whole sequence.

You could have a nightly/weekly/monthly process that recalculates all of your sequence numbers so that they use natural numbers again.