Re: Delay locking partitions during INSERT and UPDATE

On 1/19/19 12:05 AM, John Naylor wrote:
> 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?
>

Not sure. Initially I was going to say "no" because it's an internal
implementation detail and the risk of the deadlock is already there
anyway. But maybe this patch is making it more likely and we should at
least mention how partitions are locked.

>> 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.
>

What hardware are you running the tests on? I wouldn't be surprised if
you were hitting some CPU or I/O bottleneck, which we're not hitting.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services