Agregados en la base de datos: ¿por qué, cómo y vale la pena?

A lo largo de la vida de la aplicación, se acumulan más y más datos en su base de datos. Ya sea de escritorio, SaaS o incluso móvil, no importa, en el mundo moderno casi todo el mundo tiene algo "en casa".

Si se trata de algún tipo de utilidad local, no da miedo, su existencia misma para el usuario es bastante limitada. Pero si esto es algo como nuestro VLSI , que acumula y ayuda a analizar las operaciones durante todo el período de existencia de una empresa, entonces, a medida que crece, no solo aumenta el número de operaciones, sino también la comprensión de qué informes resumidos. Ayuda en la gestión operativa .

A continuación, le mostramos cómo hacer esos informes rápidamente, cuáles son las formas de su implementación y hay "rastrillos" en el camino, hoy hablaremos.

Conteo dinámico

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

---

? !