Aggregates in the database - why, how, and is it worth it?

Over the course of an application's life, more and more data accumulates in its database. Whether it is desktop, SaaS or even mobile - it doesn't matter, in the modern world almost everyone keeps something "at home".

If this is some kind of local utility, it's okay, its very existence for the user is rather limited. But if this is something like our VLSI , which accumulates and helps to analyze operations over the entire period of the existence of a business, then, as it grows, not only the number of operations becomes more, but also the understanding of which summary reports help in operational management .

Here's how to make such reports fast, what are the ways of their implementation and there are "rakes" along the way, today we'll talk.

Dynamic counting

- count(*)/sum/min/max/...



. , , .

, - .

"SQL HowTo: 1000 ".

EXPLAIN- count(*)

"" , "" - " ". - , - MVCC, PostgreSQL , .

, "" count() EXPLAIN



.

-

- , , () "" .

:

--  
CREATE TABLE tbl(
  id
    integer
);

--  
CREATE TABLE agg(
  id
    integer
      PRIMARY KEY
, qty
    integer
);

--   
CREATE OR REPLACE FUNCTION agg() RETURNS trigger AS $$
BEGIN
  UPDATE agg SET qty = qty + 1 WHERE id = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER agg AFTER INSERT ON tbl
  FOR EACH ROW
    EXECUTE PROCEDURE agg();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, .

vs MVCC

, - MVCC  "" (dead tuples), , PostgreSQL . .

MVCC UPDATE "PostgreSQL Antipatterns: ".

, , PostgreSQL   autovacuum'. , "":

ALTER TABLE agg SET (
  autovacuum_vacuum_threshold = 100     --      100 
, autovacuum_vacuum_scale_factor = 0.01 --      1% 
);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . , autovacuum_naptime



, :

ALTER SYSTEM SET autovacuum_naptime = '1min'; --    
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

! /, autovacuum/autoanalyze .

- ? - "" "":

- , , - , .

, , "".

+ worker

- "" " ", Index Scan



, "" , "SQL HowTo: --".

- , ""   , ( ) "" , ,  .

- "". , - worker'. pg_try_advisory_lock



.

" advisory locks, ".

, , - (/) .

(+2 " "),     - .

  , .

- :

WITH del AS (
  DELETE FROM
    diff
  RETURNING * --   CTE  
)
INSERT INTO
  agg
SELECT        --    ID 
  id
, sum(qty)
, count(*)
FROM
  del
GROUP BY
  1
ON CONFLICT(id) --     
  DO UPDATE SET
    (sum, count) = (agg.sum + EXCLUDED.sum, agg.count + EXCLUDED.count);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

worker' diff-, ( , ) .

, -, - MVCC - , , "DBA: VACUUM — ".

-

( ) PostgreSQL - . , , .

  , , - .   , .

""   PostgreSQL.

.    NOTIFY



/PgQ/RabbitMQ/Kafka/..., worker "", .

, PostgreSQL

PostgreSQL "" ACID. , : Redis, Tarantool, ClickHouse, ... - .

  (Redis) (ClickHouse).

---

? !