Re: why insert into UNLOGGED table WITH (autovacuum_enabled=false) on conflict do no nothing is slow?

Hi!
Thanks for your response.
> Is there any specific reason(s) to disable autovacuum?
Google says it can go faster

> Do you have your own vacuum logic behind the scene (i.e., pg_repack or
own implementation something similar to pg_repack)?
No, the plan is to run vacuum manually from time to time

>Is there any replication servers which runs heavy queries?
Just a single Postgres instance to keep several "cache" tables 1-10M
records each. These tables are the "cache" of DWH having 1B rows inside.

> What is the update / delete ratio in that table?
Only insert on conflict do nothing. Then select using sophisticated joins.
The plan is to delete some unused records once a month in bulk "delete from
table where create_date < XXX"

пн, 5 апр. 2021 г. в 03:36, Bk B <rbbalakumaran(at)gmail(dot)com>:

> Hi,
>
> Is there any specific reason(s) to disable autovacuum?
>
> Do you have your own vacuum logic behind the scene (i.e., pg_repack or own
> implementation something similar to pg_repack)?
>
> Is there any replication servers which runs heavy queries?
>
> What is the update / delete ratio in that table?
>
> What is the PG version?
>
> On Mon, Apr 5, 2021, 3:04 AM Ivan Petrov <capacytron(at)gmail(dot)com> wrote:
>
>> Hi, I'have rather odd case.
>> I have a "cache" table in Postgres and I need to insert 100K - 1M records
>> in parallel from different sources. Sources can try to insert duplicated
>> data.
>> I'm too lazy to write complex sync code around INSERT process that is why
>> I do it this way:
>> 1. create UNLOGGED table WITH (autovacuum_enabled=false)
>> 2. do insert into TABLE (foo,bar) values (1,2) on conflict do nothing.
>>
>> I'm fine with lower perf compared to the COPY command since writing
>> synchronization is 100 time more expensive than slow insert.
>> BUT the performance is waaay to slow.
>> It takes around 10.000 ms to insert 50.000 rows.
>> Each row has 150 columns.
>> Table has single PK (I can't drop it)
>>
>> Why is it so slow?
>> Can I do something with it?
>>
>