I have a database table invoices, the table contains three fields id auto incremented, created_at and invoice_id, I want to make the invoice_id so that each day or each time the user needs to reset the invoice_id, it start from 1 again.

so the way I solved this problem is by using postgres transaction isolation, to prevent race condition when I try to read then write the new invoice_id, and made the invoice_id as unique, made so that the starting invoice_id is 1001 this way I only increment it by one when adding new invoice, and only show the last three digits in the invoice, and when I need to reset I can increase the invoice_id by 1000 and reset the three digits to start be like this 2001, in case when I need to get the last record, from what I know MAX(id) is slow when I have millions of records, so I could use a separate table that has only one record to save the last id, and increase it every time I add a new record, this way we know exactly where is the id, and do not need to use MAX(id), this would be faster.
is this the best solution, or is there something better than this, I feel like I’ve gone to a very complex solution, could be simpler and more efficient ?