データベース内の集計-なぜ、どのように、そしてそれは価値がありますか？

アプリケーションの存続期間中、データベースにはますます多くのデータが蓄積されます。デスクトップ、SaaS、さらにはモバイルのいずれであっても、問題ではありません。現代の世界では、ほとんどの人が何かを「家に」置いています。

これが何らかのローカルユーティリティである場合、それは恐ろしいことではなく、ユーザーにとっての存在自体はかなり制限されています。しかし、これが私たちのVLSIのようなものであり、ビジネスの存在の全期間にわたって蓄積され、操作を分析するのに役立つ場合、それが成長するにつれて、操作の数が増えるだけでなく、どの要約レポートの理解も増えます運用管理のヘルプ。

今日は、そのようなレポートを高速化する方法、それらの実装方法は何か、そして途中で「レーキ」があることについてお話します。

動的カウント

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

---

? !