Skip to content

Instantly share code, notes, and snippets.

View MelchiSalins's full-sized avatar
🏠
Working from home

Melchi MelchiSalins

🏠
Working from home
9 followers · 8 following
View GitHub Profile
@MelchiSalins
MelchiSalins / README.md
Created September 20, 2021 11:39 — forked from valyala/README.md
Optimizing postgresql table for more than 100K inserts per second

Optimizing postgresql table for more than 100K inserts per second

  • Create UNLOGGED table. This reduces the amount of data written to persistent storage by up to 2x.
  • Set WITH (autovacuum_enabled=false) on the table. This saves CPU time and IO bandwidth on useless vacuuming of the table (since we never DELETE or UPDATE the table).
  • Insert rows with COPY FROM STDIN. This is the fastest possible approach to insert rows into table.
  • Minimize the number of indexes in the table, since they slow down inserts. Usually an index on time timestamp with time zone is enough.
  • Add synchronous_commit = off to postgresql.conf.
  • Use table inheritance for fast removal of old data: