1

Database

  • PostgreSQL 13.10

Structure

enter image description here

Constraints

  • name is "filter_test_group_id_test_group_id_test_name_id_filter_key_uniq"
    • UNIQUE(id_test_group, id_test_name, id_filter, key)

Indexes

  • name is "filter_test_group_index_id_test"
    • CREATE INDEX filter_test_group_index_id_test ON filter_test_group(id_test_group)

Details

  • for most cases(~90%) the key column contains SHA1 hash
  • for some cases(~10%) the key column contains simple strings like 'filter1', 'filter2', ...

Query

INSERT INTO management.filter_test_group
   (id_test_group, id_test_name, id_filter, key, value)
VALUES
   ('{0}', '{1}', '{2}', '{3}', '{4}')
ON CONFLICT ON CONSTRAINT filter_test_group_id_test_group_id_test_name_id_filter_key_uniq
DO UPDATE SET value = '{4}'

Usage

  • I have many upserts for the value column based on the unique constraint
  • I do several upserts for unique constraints in a single transaction(ReadCommitted)
  • All upserts in a transaction belog to different unique constraints

Problem

  • The upsert query run slowly as the size of the table grows

Question

  • Who may I optimaze the structure of the tables or optimaze the way how to update the value column?

It is possible to leave only SHA1 hash for the key column if it will help

Oleg Karoza
  • 11
  • 1
  • "The upsert query run slowly as the size of the table grows" - What does the EXPLAIN ANALYZE show? – J.D. Aug 10 '23 at 12:45
  • It shows "Insert on filter_test_group as filter_test_group (rows=0 loops=1)" even for existence rows, looks like it works enough fast and an issue in other part of implementation. @J.D. thanks for the advice – Oleg Karoza Aug 14 '23 at 09:36

0 Answers0