2 added 96 characters in body
source | link
  • Would partitioning with PostgreSQL 11 help? I could provide an additional integer "chromosome_no" column that could be used for hashed partitioning.
  • Is it possible to use bloom indices to speedup "upsert" performance with unique constraints? (EDIT I realize that bloom filters will not help because they err into the wrong direction)
  • Would partitioning with PostgreSQL 11 help? I could provide an additional integer "chromosome_no" column that could be used for hashed partitioning.
  • Is it possible to use bloom indices to speedup "upsert" performance with unique constraints?
  • Would partitioning with PostgreSQL 11 help? I could provide an additional integer "chromosome_no" column that could be used for hashed partitioning.
  • Is it possible to use bloom indices to speedup "upsert" performance with unique constraints? (EDIT I realize that bloom filters will not help because they err into the wrong direction)
1
source | link

PostgreSQL "upsert" tuning with unique constraint

I have the following table (PostgreSQL 9.6 but could upgrade):

# \d annotation_annotation 
                                       Table "public.annotation_annotation"
      Column       |          Type           |                             Modifiers                              
-------------------+-------------------------+--------------------------------------------------------------------
 id                | bigint                  | not null default nextval('annotation_annotation_id_seq'::regclass)
 release           | character varying(32)   | not null
 chromosome        | character varying(32)   | not null
 position          | integer                 | not null
 reference         | character varying(512)  | not null
 alternative       | character varying(512)  | not null
 database          | character varying(8)    | 
 effect            | character varying(64)[] | not null
 gene_id           | character varying(64)   | 
 transcript_id     | character varying(64)   | 
 transcript_coding | boolean                 | 
 hgvs_c            | character varying(512)  | 
 hgvs_p            | character varying(512)  | 
Indexes:
    "annotation_annotation_pkey" PRIMARY KEY, btree (id)
    "annotation_annotation_release_chromosome_posit_d102b713_uniq" UNIQUE CONSTRAINT, btree (release, chromosome, "position", reference, alternative, transcript_id)
    "annotation__release_3f254e_idx" btree (release, chromosome, "position", reference, alternative, gene_id)

I'm running bulk insertions with ON CONFLICT IGNORE of tens of millions of files into the table. I'm expecting a high number of duplicates (>90%). The table is append-only. Using pg_activity, I'm seeing that PostgreSQL is spending more time reading than writing (as I would expect if the number of duplicates is high).

What would be a good way of tuning "upsert" performance here:

  • Would partitioning with PostgreSQL 11 help? I could provide an additional integer "chromosome_no" column that could be used for hashed partitioning.
  • Is it possible to use bloom indices to speedup "upsert" performance with unique constraints?