Which architecture option is better to choose for data storage?

Question

Database

The logic of the service is as follows.
It is checked whether the given ID exists in the database table. If there is already one, the delete operation is performed and then the insertion takes place (I can’t use upsert because I need to delete data immediately from a bunch of linked tables)

The problem is that the database (PG) already contains hundreds of millions of records and each such check is very strong slows down the insert.

I was thinking of using some kind of in-memory storage for the specified IDs so that all checks are performed in it.

The question is - is it worth it to fence such a garden? If so, which solution should you choose?

Answer 1

Theoretically, if there is an index by ID, then the search should be fast enough. You can make horizontal partitioning by hash from ID to split the table into several pieces.
Try to take out the ID in a separate, small table.
You can also only insert data and select only with the last insertion time. Although the search for reading will be slow. Again, partition by insertion time.