Agrégats dans la base de données - pourquoi, comment et en vaut-il la peine?

Au cours de la vie de l'application, de plus en plus de données s'accumulent dans sa base de données. Qu'il s'agisse de bureau, de SaaS ou même de mobile - peu importe, dans le monde moderne, presque tout le monde garde quelque chose «à la maison».

S'il s'agit d'une sorte d'utilitaire local, ce n'est pas effrayant, son existence même pour l'utilisateur est plutôt limitée. Mais s'il s'agit de quelque chose comme notre VLSI , qui s'accumule et aide à analyser les opérations sur toute la période d'existence d'une entreprise, alors, à mesure qu'elle se développe, non seulement le nombre d'opérations devient plus, mais aussi la compréhension de quels rapports de synthèse aide à la gestion opérationnelle .

Aujourd'hui, nous allons parler de la façon de produire rapidement de tels rapports, des moyens de leur mise en œuvre et il y a des «râteaux» en cours de route.

Comptage dynamique

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

---

? !