Making use of the log table on the log_server

[swingman]

Hi all,

we have been logging all changes to critical tables in our system for a while now and would like to investigate changes made to certain records in the past.
The log table is very large and queries are very slow because there are no indexes to help the query. This in PostgreSQL 12.4.

Now, if I add indexes to the log table my searches will become fast, but the insert speed may drop (by how much?)
Has anyone done this? What happened?

We may be able to get away with partial indexes, i.e. only index the events we are interested in.

Another option is to restore a backup of the log server and index that copy.

I'd like to hear some views and experiences!