Agregat dalam database - mengapa, bagaimana, dan apakah itu sepadan?

Selama masa pakai aplikasi, semakin banyak data terakumulasi dalam database-nya. Baik itu desktop, SaaS, atau bahkan seluler - tidak masalah, di dunia modern hampir semua orang menyimpan sesuatu "di rumah".

Jika ini adalah semacam utilitas lokal, tidak menakutkan, keberadaannya bagi pengguna agak terbatas. Tetapi jika ini adalah sesuatu seperti VLSI kami , yang mengakumulasi dan membantu menganalisis operasi selama seluruh periode keberadaan bisnis, maka, seiring pertumbuhannya, tidak hanya jumlah operasi menjadi lebih banyak, tetapi juga pemahaman tentang laporan ringkasan mana membantu dalam manajemen operasional .

Hari ini kita akan berbicara tentang cara membuat laporan seperti itu dengan cepat, apa saja cara penerapannya dan ada "garu" di sepanjang jalan.

Penghitungan dinamis

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

---

? !