0

What kind of setup do I need to have to be able to insert 10M records/minute, and maintain/query tables of 300M rows?

My data is two varchar(50) fields that are indexed, and one is unique. No other columns.

My script is batching INSERTS, and I am able to get to ~1M records/min, which deteriorates after a while (at 100M added its down to half or a bit less - 400k/min or so).

Using COPY is not an option because it terminates every time there is a duplicate entry.

EDIT: hardware is: 96GB RAM, 32 cores, SSD hard drives.

Improve this question
4
  • If the size is about fixed at 300M, then they must go out at the same rate they come in. How is that done? If the max field length is 50, what is the average?
    – jjanes
    May 25, 2022 at 19:30
  • @jjanes all of them are 50 char long, and no, it can be more than 300M. I need to be able to do it from a flat file all at once.
    – Darko
    May 25, 2022 at 22:40
  • Your question just raises more questions. Do you run this once ever for 30 minutes (300M rows at 10M/minute)? Every day for 30 minutes? 24/7? What does the checkpoint activity look like? What does pg_stat_wal contain at 5 minutes into the run? After inserting 50M rows? After 100M? Is autovacuum being triggered, and if so, how often? Have you tried multiple clients simultaneously, and if so, what was different? Are the incoming data ordered by any of the index keys, and if not, can it be?
    – mustaccio
    May 25, 2022 at 23:11
  • You say SSD hard drives - how many and in what configuration (RAID 0, 1, 5, 10...)? Also, the manufacturer and and model would be good!
    – Vérace
    May 26, 2022 at 5:27

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.