Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have implemented data denormalization strategy using postgresql RULEs. I picked rules instead of triggers for performance reasons.


Schema is structured like this:

  • Application has many clients
  • Client has many projects
  • Project has many users

One part of the system is storing hits for every user in stats table. Hit is an imaginary metric, it is not really relevant. System can collect many of these metrics. There are a lot of records in stats table (> 1,000,000 per day).

I want to know how many hits are per user, per project, per client and per application for given day.

To make it work fast, I've groupped stats by day and stored the output into user_hits table. During this process, also the application_id, client_id and project_id has been added (as columns), and appropriate indexes created.

I want to further optimise the process by grouping things by project_id, client_id and finally application_id. The data pipeline is like this:

stats -> user_hits -> project_hits -> client_hits -> application_hits

I want to make sure when I delete the data from user_hits for given day, that the data in project_hits for that same date is also deleted. This process should propagate to last table in chain.

I defined these simple rules:

CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  DELETE FROM client_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  DELETE FROM application_hits WHERE day = OLD.day;

However, when I issue statement like this:

DELETE FROM user_hits WHERE day = current_date;

I expect it to run these 3 queries in return:

DELETE FROM project_hits WHERE day = current_date;
DELETE FROM client_hits WHERE day = current_date;
DELETE FROM application_hits WHERE day = current_date;

However, it doesn't.

It completes the operation, but it takes couple of minutes to do that (with test data). With real data it takes hours, while running those 3 queries by hand takes couple of milliseconds. The time it takes seems proportional to number of combinations (users x projects x clients x applications).

What is the problem here? Am I missing something? Can this be implemented with triggers in an optimised way?


Included sample script which reproduces the problem:

https://gist.github.com/assembler/5151102


UPDATE: Transition from user_hits to project_hits (and so forth) is done by worker process in background (since it involves contacting 3rd party services for additional info). It is smart enough to recalculate everything for missing dates. So the only thing i need is a way to DELETE records cascadingly in optimised way.


UPDATE: stats table is filled on daily basis. The only possible scenario is to unconditionally delete the data for whole day and then replace it with new values.


UPDATE: I noticed that the number of affected rows (extracted from explain statement) is exactly equal to the product of affected rows in user_hits, project_hits, client_hits, and application_hits tables (hundreds of millions of rows).

It turns out that it works like this:

  1. I run DELETE FROM user_hits WHERE day = current_date;
  2. For each row in user_hits table, RULE is triggered which deletes EVERY row from project_hits
  3. For each row of project_hits, RULE is triggered which deletes EVERY row from client_hits
  4. For each row of client_hits, RULE is triggered which deletes EVERY row from application_hits

So, the number of operations is equal to the product of count of affected rows in these tables.

share|improve this question

migrated from stackoverflow.com Mar 16 '13 at 19:22

This question came from our site for professional and enthusiast programmers.

4  
Rules are "somewhat" deprecated. There is a clear recommendation from the Postgres team to use triggers instead of rules. And what exactly does "blocks forever" mean? Do you see locks in pg_locks? What's the state of your statement in pg_stat_activity? –  a_horse_with_no_name Mar 12 '13 at 17:29
    
According to postgresql docs (postgresql.org/docs/9.2/static/rules-triggers.html), rules are not deprecated in favor of triggers. Can you point me to reference that state that triggers should be used instead of rules? I need rules for performance reasons millions of rows should be deleted as result of single row deletion. Rules perform a LOT faster in this situation. –  Milovan Zogovic Mar 13 '13 at 7:43
    
I'll check the pg_locks and pg_stat activitiy and will let you know what i find.. thanks –  Milovan Zogovic Mar 13 '13 at 7:45
    
by joining pg_locks with pg_class I found that all of the relations that should be affected (per_day, per_week, per_month...) are indeed in pg_locks. –  Milovan Zogovic Mar 13 '13 at 8:21
3  
Rules are not deprecated in favor of triggers. They are very different and a bit of a hassle to work with. The big reason is that for simple udateable views, they are far more flexible than triggers when it comes to performance tuning. There are many Pg folks who would like them to be deprecated and many more who do not feel they should ever be deprecated. –  Chris Travers Mar 13 '13 at 9:09

3 Answers 3

up vote 7 down vote accepted

Next time, please include the EXPLAIN output rather than making us dig for it in your scripts. There's no guarantee my system is using the same plan as yours (although with your test data it is likely).

The rule system here is working properly. First, the I want to include my own diagnostic queries (note I did not run EXPLAIN ANALYSE since I was just interested in what query plan was generated):

rulestest=# explain DELETE FROM user_hits WHERE day = '2013-03-16';
                                              QUERY PLAN                        

--------------------------------------------------------------------------------
----------------------
 Delete on application_hits  (cost=0.00..3953181.85 rows=316094576 width=24)
   ->  Nested Loop  (cost=0.00..3953181.85 rows=316094576 width=24)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..128.53 rows=6352 width=22)
               ->  Nested Loop  (cost=0.00..96.78 rows=6352 width=22)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..2.49 rows=16 width=16)
                           ->  Nested Loop  (cost=0.00..2.41 rows=16 width=16)
                                 ->  Seq Scan on application_hits  (cost=0.00..1
.10 rows=4 width=10)
                                       Filter: (day = '2013-03-16'::date)
                                 ->  Materialize  (cost=0.00..1.12 rows=4 width=
10)
                                       ->  Seq Scan on client_hits  (cost=0.00..
1.10 rows=4 width=10)
                                             Filter: (day = '2013-03-16'::date)

 Delete on client_hits  (cost=0.00..989722.41 rows=79023644 width=18)
   ->  Nested Loop  (cost=0.00..989722.41 rows=79023644 width=18)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..43.83 rows=1588 width=16)
               ->  Nested Loop  (cost=0.00..35.89 rows=1588 width=16)
                     ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 wi
dth=10)
                           Filter: (day = '2013-03-16'::date)
                     ->  Materialize  (cost=0.00..1.12 rows=4 width=10)
                           ->  Seq Scan on client_hits  (cost=0.00..1.10 rows=4 
width=10)
                                 Filter: (day = '2013-03-16'::date)

 Delete on project_hits  (cost=0.00..248851.80 rows=19755911 width=12)
   ->  Nested Loop  (cost=0.00..248851.80 rows=19755911 width=12)
         ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=10)
               Filter: (day = '2013-03-16'::date)
         ->  Materialize  (cost=0.00..16.91 rows=397 width=10)
               ->  Seq Scan on project_hits  (cost=0.00..14.93 rows=397 width=10
)
                     Filter: (day = '2013-03-16'::date)

 Delete on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
   ->  Seq Scan on user_hits  (cost=0.00..1887.00 rows=49763 width=6)
         Filter: (day = '2013-03-16'::date)
(39 rows)

rulestest=# select distinct day from application_hits;
    day     
------------
 2013-03-15
 2013-03-16
(2 rows)

rulestest=# select count(*), day from application_hits group by day;
 count |    day     
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from client_hits group by day;
 count |    day     
-------+------------
     4 | 2013-03-15
     4 | 2013-03-16
(2 rows)

rulestest=# select count(*), day from project_hits group by day;
 count |    day     
-------+------------
   397 | 2013-03-15
   397 | 2013-03-16
(2 rows)

If your data is anything like your existing data, neither rules nor triggers will work very well. Better will be a stored procedure which you pass a value and it deletes everything you want.

First let's note that indexes here will get you nowhere because in all cases you are pulling half of the tables (I did add indexes on day on all tables to help the planner but this made no real difference).

You need to start with what you are doing with RULEs. RULEs basically rewrite queries and they do so using ways that are as robust as possible. Your code also doesn't match your example though it matches your question better. You have rules on tables which cascade to rules on other tables which cascade to rules on other tables

Therefore when you delete from user_hits where [criteria], the rules transform this into a set of queries:

DELETE FROM application_hits 
 WHERE day IN (SELECT day FROM client_hits 
               WHERE day IN (SELECT day FROM user_hits WHERE [condition]));
DELETE FROM client_hits
  WHERE day IN (SELECT day FROM user_hits WHERE [condition]);
DELETE FROM user_hits WHERE [condition];

Now, you might think we could skip the scan on client_hits in the first, but that isn't what happens here. The problem is that you could have days in user_hits and application_hits that are not in client_hits so you really have to scan all tables.

Now here there is no magic bullet. A trigger isn't going to work much better because, while it gets to avoid scanning every table, it gets fired every row that gets deleted so you basically end up with the same nested loop sequential scans that are currently killing performance. It will work a bit better because it will delete rows along the way rather than rewriting the query along the way, but it isn't going to perform very well.

A much better solution is to just define a stored procedure and have the application call that. Something like:

CREATE OR REPLACE FUNCTION delete_stats_at_date(in_day date) RETURNS BOOL 
LANGUAGE SQL AS
$$
DELETE FROM application_hits WHERE day = $1;
DELETE FROM project_hits WHERE day = $1;
DELETE FROM client_hits WHERE day  = $1;
DELETE FROM user_hits WHERE day = $1;
SELECT TRUE;
$$;

On the test data this runs in 280 ms on my laptop.

One of the hard things regarding RULEs is remembering what they are and noting that the computer cannot, in fact, read your mind. This is why I would not consider them a beginner's tool.

share|improve this answer
    
Thanks for awesome explanation of how rules work and for suggesting the solution. You rock! –  Milovan Zogovic Mar 17 '13 at 8:18
-- this is the datamodel with the (correct?) PRIMARY and FOREIGN KEYs 
-- the `deferrable initially deferred` thing is there to accomodate the
-- table filling (which had to be altered to avoid duplicate keys)
-- ------------------------------------------------------------------------

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path = tmp ;

-- table definitions

CREATE TABLE application_hits
  ( zday DATE NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,application_id)
  );

CREATE TABLE client_hits
  ( zday DATE NOT NULL
  , client_id INTEGER NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,client_id,application_id)
        , FOREIGN KEY (zday,application_id)
           REFERENCES application_hits (zday,application_id) DEFERRABLE INITIALLY DEFERRED
  );

CREATE TABLE project_hits
  ( zday DATE NOT NULL
  , project_id INTEGER NOT NULL
  , client_id INTEGER NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,project_id,client_id,application_id)
        , FOREIGN KEY (zday,client_id,application_id)
           REFERENCES client_hits (zday,client_id,application_id) DEFERRABLE INITIALLY DEFERRED
  );

CREATE TABLE user_hits
  ( zday DATE NOT NULL
  , user_id INTEGER NOT NULL
  , project_id INTEGER NOT NULL
  , client_id INTEGER NOT NULL
  , application_id INTEGER NOT NULL
  , hits INTEGER NOT NULL DEFAULT 0
        , PRIMARY KEY (zday,user_id,project_id,client_id,application_id)
        , FOREIGN KEY (zday,project_id,client_id,application_id)
           REFERENCES project_hits (zday,project_id,client_id,application_id) DEFERRABLE INITIALLY DEFERRED
  );

--- rules
CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  UPDATE project_hits dst SET hits = dst.hits - OLD.hits
        WHERE dst.zday = OLD.zday
        AND dst.project_id = OLD.project_id
        AND dst.client_id = OLD.client_id
        AND dst.application_id = OLD.application_id
        ;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  UPDATE client_hits dst SET hits = dst.hits - OLD.hits
        WHERE dst.zday = OLD.zday
        AND dst.client_id = OLD.client_id
        AND dst.application_id = OLD.application_id
        ;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  UPDATE application_hits dst SET hits = dst.hits - OLD.hits
        WHERE dst.zday = OLD.zday
        AND dst.application_id = OLD.application_id
        ;

        -- Rules for UPDATE
CREATE RULE update_children AS ON UPDATE TO project_hits
  DO ALSO
  UPDATE client_hits dst SET hits = dst.hits - OLD.hits +NEW.hits
        WHERE dst.zday = OLD.zday
        AND dst.client_id = OLD.client_id
        AND dst.application_id = OLD.application_id
        ;

CREATE RULE update_children AS ON UPDATE TO client_hits
  DO ALSO
  UPDATE application_hits dst SET hits = dst.hits - OLD.hits +NEW.hits
        WHERE dst.zday = OLD.zday
        AND dst.application_id = OLD.application_id
        ;

-- filling user_hits
BEGIN WORK;
INSERT INTO user_hits (zday, user_id, project_id, client_id, application_id, hits)
SELECT
  current_date - (s%1313)::INT
        , (s % 1001)::INT , (s % 101)::INT , (s%13)::INT , (s%11)::INT
        , (50*random())::INT
FROM
  generate_series(1, 100000) s;

-- filling project_hits
INSERT INTO project_hits (zday, project_id, client_id, application_id, hits)
SELECT zday, project_id, client_id, application_id, SUM(hits)
FROM user_hits
GROUP BY zday, project_id, client_id, application_id
        ;

-- filling client_hits
INSERT INTO client_hits (zday, client_id, application_id, hits)
SELECT zday , client_id , application_id , SUM(hits)
FROM project_hits
GROUP BY zday, client_id, application_id;

-- filling application_hits
INSERT INTO application_hits (zday, application_id, hits)
SELECT zday, application_id, SUM(hits)
FROM client_hits
GROUP BY zday, application_id
        ;
COMMIT WORK;


-- create view for today
CREATE VIEW v_today
AS SELECT
  (SELECT SUM(hits) FROM user_hits WHERE zday = current_date) AS user_hits
  , (SELECT SUM(hits) FROM project_hits WHERE zday = current_date) AS project_hits
  , (SELECT SUM(hits) FROM client_hits WHERE zday = current_date) AS client_hits
  , (SELECT SUM(hits) FROM application_hits WHERE zday = current_date) AS application_hits
   ;


SELECT * FROM v_today;
-- explain analyse
DELETE FROM user_hits WHERE zday = current_date;
SELECT * FROM v_today;

And next: create some rules for INSERT ... And don't forget the nasty cases of UPDATE to the key fileds.

share|improve this answer
    
Thanks! This is really helpful. However, the only thing i want to do is to DELETE records. There is external system which detects missing dates and recalculates the values (see latest update). I'll check your script a bit more and see how i can make use of it.. –  Milovan Zogovic Mar 13 '13 at 13:28
    
You don't want to delete (uncoditionally). For instance: You only want to delete from application_hits if there is stil one ore more records with the same {zday, application_id} in client_hits. there can be more than one records in client_hits with the same {zday, application_id}, but with a different client_id. (You do want to delete if the hitcount goes to zero, but that's a different issue) –  wildplasser Mar 13 '13 at 13:59
    
But I DO want to delete unconditionally. Data in stats table is imported on daily basis. I want to be able to delete data for WHOLE day, and import the new data. Thanks for your help man. –  Milovan Zogovic Mar 15 '13 at 12:00
1  
If you want to delete unconditionally, don't use rules to do it. Rules are fundamentally conditional and this is your problem (having to scan multiple tables to see what should be deleted). Use a user-defined function instead. –  Chris Travers Mar 16 '13 at 7:41

OK, it's been a long time, but if you run an EXPLAIN we can see if my recollection is correct. I think the plan for the subsidiary queries are being created at the wrong time, before the planner can take indexes into account. I think you are getting Table Scans.

Having said that, did you benchmark that a standard cascading delete foreign key is too slow? And that a rule would be faster?

[Edit after comments]

CREATE RULE delete_children AS ON DELETE TO user_hits
  WHERE day = OLD.day  -- added
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

Reading the docs closely, it seems that (unlike a trigger) when a rule is used, it will (in the absence of the added where clause) be applied to the entire original table?!

share|improve this answer
    
Thanks for the answer. I don't believe it is the problem with indexing. Even if I remove all indexes, the 4 delete statements will run relatively fast. However, with rules, it takes minutes to complete (and on real data, hours). From the explain I saw that the query needs to process millions of rows. The number of rows it needs to process is EXACTLY EQUAL to the product of number of rows accross those 4 affected tables (I'll include this in latest update). –  Milovan Zogovic Mar 15 '13 at 12:07
    
Daily stats are often aggregated (summed) for given period, so partitioning by day is not an optimal solution.. –  Milovan Zogovic Mar 15 '13 at 14:53
    
See edit of answer. –  Andrew Lazarus Mar 15 '13 at 16:34
    
I've tried this with the test script, and it still took a lot of time to complete (318 million of rows affected).. –  Milovan Zogovic Mar 15 '13 at 17:59
    
In his test script, indexes are not usable. If this is the case for his production db too, that's all a non-issue. –  Chris Travers Mar 16 '13 at 7:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.