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

At the moment there is a table on 500 million records in postgresql. It increases by about 100k records a day. The table contains 3 columns id, factor_id, parcel_id, primary key, and two links to other tables, respectively. This table is not necessary to analyze or aggregate. You only need to have the ability to quickly insert and read the post. Going to particularbut table key factor_id, but such tables can be more than 10K, I'm afraid is just a mess, especially when it will be deleted related records factor_id and the table will always stay in empty. Advise your option. In advance, thank you.
April 3rd 20 at 18:45
4 answers
April 3rd 20 at 18:47
Solution
Decide how you will remove this data.

3 columns id, factor_id, parcel_id

This table is not necessary to analyze or aggregate. You only need to have the ability to quickly insert and read the post.

Very much doubt you need an id instead of a composite pk.

increases by about 100k records a day

Ie now you have data for 15 years and you are concerned about what will happen in another 15 years? Well, just counting the amounts of insertion.

In full MVCC in PostgreSQL tranzakcionnye a very small string to keep a few expensive in the place, the row header is great.

3 columns id, factor_id, parcel_id

24 Yes 3*8 bytes if the fields are considered bigint'AMI, Yes on the lard records... of about 25GB, plus or minus. Garbage question actually. You have a problem is there? Or just itching?
Very much doubt you need an id instead of a composite pk.

And can you give me a little more about this? - Giles14 commented on April 3rd 20 at 18:50
@Giles14, mmm, and much more? This id is really needed here? The answer is "well, it's a primary key" is not accepted. primary key does not have to be a surrogate auto-increment - Lenore_Emmerich commented on April 3rd 20 at 18:53
@Lenore_Emmerich, and why is he a surrogate, if the ID can be not only abstract-increment, but also quite a working entity performing the function ID in the database? - Giles14 commented on April 3rd 20 at 18:56
@Giles14, because it seems a common mistake, when for some reason doing meaningless surrogate key instead of the native composite key subject area. I have a very strong suspicion that here we need the primary key (factor_id, parcel_id) and is not needed for anything the id field.

if my crystal ball is clouded over and it suddenly this error, and all three fields need to consciously do something good. - Lenore_Emmerich commented on April 3rd 20 at 18:59
April 3rd 20 at 18:49
not to try and use foreign keys, do not delete the data (only mark as deleted)
Foreign keys hamper insert? What is the profit? - Vicky commented on April 3rd 20 at 18:52
@Vicky, of course, you rebuilds the indexes for the current table, and even checked for the rest. And without the foreign keys for the current. Remove the same - in principle a very time-consuming operation. - esteban.Hickle commented on April 3rd 20 at 18:55
do not delete the data (only mark as deleted)

Let's not bad advice, okay?
Ask at your leisure how is update in MVCC.

About fk tradeoff as highly controversial. - Lenore_Emmerich commented on April 3rd 20 at 18:58
April 3rd 20 at 18:51
  • The number protezirovanija tables in the 10K - it's the little things, so feel free to separate.
  • Get rid of id - it's not necessary.


If it is possible to use other DBMS, then the same Redis, as mentioned, will solve your problem.
April 3rd 20 at 18:53
Without sarcasm Oleg Bunin
He's at the end, tells a similar case. Maybe you'll find something useful.

Find more questions by tags Databases