Where and how to store a table with 1 billion records?

Question

Database

At the moment there is a table with 500 million records in postgresql. It increases by about 100k records per day. The table contains 3 columns id, factor_id, parcel_id, primary key, and two links to other tables, respectively. This table does not need to be parsed or aggregated. You just need to be able to quickly insert and read the record. I'm going to partition the table by the factor_id key, but there can be more than 10k of such tables, I'm afraid it will just be a mess, especially when the related factor_id records are deleted and the table will always be empty. Please advise your option. Thanks in advance.

Answer 1

Decide how you will delete this data.
There is a very big doubt that you need an id here instead of a composite pk.
Those. now you have data for 15 years of work and you are worried about what will happen in another 15 years? Well, just counting the indicated insertion volumes.
In a full-fledged PostgreSQL MVCC transactional, very small rows are somewhat expensive to keep exactly in place, the row header is large.
24 yes 3 * 8 bytes if these fields are considered bigints, yes per lard of records ... about 25GB plus or minus. Bullshit question in general. Do you have a problem? Or just itchy hands?

Answer 2

don't use foreign keys, don't delete data (only mark as deleted)

Answer 3

• The number of partitioned tables in 10k is trifles, so feel free to share.
  If it is possible to use other DBMS, then the same Redis, as was said, will perfectly solve your problem.

Answer 4

Without sarcasm Oleg Bunin
He is at the end, tells a similar case. Maybe you will find something useful for yourself.