From: | John Naylor <jcnaylor(at)gmail(dot)com> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Delay locking partitions during INSERT and UPDATE |
Date: | 2019-01-18 23:05:51 |
Message-ID: | CAJVSVGVz2Ln3o8hq7rWTbjn1JCM3FGiLFKE4ofAhqGuh89m8kA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 11/22/18, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> If required, such operations could LOCK TABLE the top partitioned
> table to block the DML operation. There's already a risk of similar
> deadlocks from such operations done on multiple separate tables when
> the order they're done is not the same as the order the tables are
> written in a query, although, in that case, the window for the
> deadlock is likely to be much smaller.
Is this something that would need documentation anywhere?
> With this done, the performance of an INSERT into a 10k partition
> partitioned table looks like:
>
> Setup:
> create table hashp (a int) partition by hash(a);
> select 'create table hashp'||x::Text || ' partition of hashp for
> values with (modulus 10000, remainder '||x::text||');' from
> generate_Series(0,9999) x;
> \gexec
>
> hashp_insert.sql:
> \set p_a random(1,1000)
> insert into hashp values(:p_a);
>
> Results:
> $ psql -c "truncate hashp;" postgres && pgbench -n -f hashp_insert.sql
> -M prepared -c 4 -j 4 -T 60 postgres
I used a similar test, but with unlogged tables, and "-c 2", and got:
normal table: 32000tps
10k partitions / master: 82tps
10k partitions / patch: 7000tps
So far I haven't gotten quite as good performance as you and Tomas,
although it's still a ~85x improvement.
-John Naylor
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-01-18 23:34:00 | Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages) |
Previous Message | Tomas Vondra | 2019-01-18 22:32:48 | Re: jsonpath |