pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marc Morin <marc@sandvine.com>
To: pgsql-performance@postgresql.org
Subject: partitioning and locking problems
Date: Tue, 31 Jan 2006 18:25:01 -0500
Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com> (raw)

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




view thread (13+ messages)  latest in thread

Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com>
Permalink:  ../2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com/
Also on:    postgresql.org/message-id/2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: marc@sandvine.com
  Subject: Re: partitioning and locking problems
  In-Reply-To: <2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com>
  References: <2BCEB9A37A4D354AA276774EE13FB8C263B3DE@mailserver.sandvine.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox