partitioning and locking problems

From: "Marc Morin" <marc(at)sandvine(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: partitioning and locking problems
Date: 2006-01-31 23:25:01
Message-ID: 2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

We have a large database system designed around partitioning. Our
application is characterized with

- terabytes of data
- billions of rows in dozens of base tables (and 100s of paritions)
- 24x7 insert load of new data that cannot be stopped, data is time
sensitive.
- periodic reports that can have long running queries with query times
measured in hours

We have 2 classes of "maintenance" activities that are causing us
problems:
- periodically we need to change an insert rule on a view to point to a
different partition.
- periodically we need to delete data that is no longer needed.
Performed via truncate.

Under both these circumstances (truncate and create / replace rule) the
locking behaviour of these commands can cause locking problems for us.
The scenario is best illustrated as a series of steps:

1- long running report is running on view
2- continuous inserters into view into a table via a rule
3- truncate or rule change occurs, taking an exclusive lock.
Must wait for #1 to finish.
4- new reports and inserters must now wait for #3.
5- now everyone is waiting for a single query in #1. Results
in loss of insert data granularity (important for our application).


Would like to understand the implications of changing postgres'
code/locking for rule changes and truncate to not require locking out
select statements?

The following is a simplified schema to help illustrate the problem.

create table a_1
(
pkey int primary key
);
create table a_2
(
pkey int primary key
);

create view a as select * from a_1 union all select * from a_2;

create function change_rule(int) returns void as
'
begin
execute ''create or replace rule insert as on insert to a do
instead insert into a_''||$1||''(pkey) values(NEW.pkey)'';
end;
' language plpgsql;

-- change rule, execute something like the following
periodically
select change_rule(1);


We've looked at the code and the rule changes appear "easy" but we are
concerned about the required changes for truncate.

Thanks
Marc

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rodrigo Madera 2006-02-01 00:32:56 Storing Digital Video
Previous Message Luke Lonergan 2006-01-31 23:19:38 Re: Huge Data sets, simple queries