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