How to take penultimate value before inserting a new record in a large postgres table?

Question

PostgreSQL

We have a table with customer debts, it is large, about 5 million records, the essence of the problem is that before inserting a new debt value for a specific client, I need to get a record with the client’s previous debt (usually debts are accrued in bundles by crown), solving the problem I chose two solutions:
1) a trigger is written to this large table and before insertion, the id of the last record for a certain client id is calculated and written to the intermediate table. But it seems to me that this option is not very humane to use, because the speed of executing the query for the selection of the id of the last record I need is ~ 1.2 seconds, about 50,000 records are inserted into the table per night, it is quite possible that I am not correctly compiling the query for the selection, if something goes wrong please point me in the right direction, here is my request:

select id from debt where id<6534794 AND cus_id = 143867 ORDER BY id desc LIMIT 1

And I insert its results into the intermediate table for further operations
2) In the intermediate table, I write only the id of the new record that was inserted into the large table, then I write a function in the plpgsql language that analyzes the intermediate table, takes the id of the inserted record and based on this id, it makes a selection from a large table of the penultimate value, but this is very long, it is critical for me that the query completes less than a second for one record
You might be wondering, why bother doing that? And then, that this data will be processed by a php script and, among other things, it will calculate the delta between the debts of clients, so I take the data from the intermediate table with debts, then add a huge amount of other data to them and write to a separate table, the question , why don’t I do it with one sql query, because it will slow down a lot, it’s easier to display it in a separate table and insert data into it, and then add the necessary indexes and split it into partitions, speed of work is very important in this project, so if you think that I am a crutch, I ask you to suggest me options for the correct solution to this problem. Thank you.

Answer 1

and than RETURNING it is not pleasant?
You can also attach anything there.
For example, when deleting some data, I pull out what was generally deleted and fasten everything that I need to pull out with joins. For example

DELETE FROM 
        waybills 
      USING 
        invoices 
      JOIN invoice_data ON
        invoice_data.invoice_uuid = invoices.invoice_uuid
      WHERE 
        waybills.invoice_uuid = waybills.invoice_uuid AND 
        waybills.waybill_uuid = ?
      RETURNING 
        invoice_data.order_data_id,
        invoices.order_id,
        invoices.invoice_uuid

You can also add RETURNING in your insert and specify what you want to return)))