Skip to content

Instantly share code, notes, and snippets.

@valyala valyala/README.md

Last active Aug 13, 2020
Embed
What would you like to do?
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:
CREATE TABLE parent ... ;
CREATE TABLE child_1() INHERITS (parent);
CREATE TABLE child_2() INHERITS (parent);

-- always INSERT rows into child_1.
-- SELECT from parent.

-- periodically run the follwing sql for rotating child_1 with child_2:
TRUNCATE TABLE child_2;
BEGIN;
ALTER TABLE child_1 RENAME TO child_tmp;
ALTER TABLE child_2 RENAME TO child_1;
ALTER TABLE child_tmp RENAME TO child_2;
COMMIT;

This is much faster comparing to

DELETE FROM parent WHERE time < now() - interval 'given period'

This also avoids table fragmentation, so SELECT queries work faster on the table.

@ipchama

This comment has been minimized.

Copy link

ipchama commented Jul 13, 2019

For fast removal of data, would it be better to use table partitioning on a single table with the partitioning done by some time period so you can then just drop the partition later?

@valyala

This comment has been minimized.

Copy link
Owner Author

valyala commented Jul 15, 2019

Probably table partitioning could be better solution with new PostgreSQL versions. The guide was written when table partitioning in pg wasn't easy from operations point of view.

@andreasneuman

This comment has been minimized.

Copy link

andreasneuman commented Sep 24, 2019

I think using some query profiler like this could be also helpful in this case.

@alex

This comment has been minimized.

Copy link

alex commented Oct 8, 2019

Another optimization I learned about recently: avoid having any DEFAULTs on columns which are non-constant (e.g. SERIAL or DEFAULT uuid_generate_v4()) -- pre-generating IDs is much faster.

@voltechs

This comment has been minimized.

Copy link

voltechs commented Aug 5, 2020

It doesn't seem possible to set autovacuum_enabled=false on tables. I know this is a feature coming to PG 13, but this was created back in 2016. Am I missing something?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.