Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sITOJ-00Fqa1-Ie for pgsql-general@arkaria.postgresql.org; Sat, 15 Jun 2024 13:24:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sITOG-00DZfs-02 for pgsql-general@arkaria.postgresql.org; Sat, 15 Jun 2024 13:24:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sITOF-00DZfj-B3 for pgsql-general@lists.postgresql.org; Sat, 15 Jun 2024 13:24:20 +0000 Received: from mail-ua1-x934.google.com ([2607:f8b0:4864:20::934]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sITOD-001RbR-Kz for pgsql-general@lists.postgresql.org; Sat, 15 Jun 2024 13:24:18 +0000 Received: by mail-ua1-x934.google.com with SMTP id a1e0cc1a2514c-80b76c5de79so887542241.1 for ; Sat, 15 Jun 2024 06:24:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718457856; x=1719062656; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Rfslw58MenJsY/u9M1wf18Y3CJpjVkjiPdZVvkBYOzU=; b=j787/cChkJeRKaHynBvAXz/1CoQU8bi4IK74Fn2H7gx34EIvXusHfe6+DHswp5e7nA OrPdbt2zF7WVmuULLmSWlAo67oxLMBBPKElgs2JBW4ztIVxoLaehOe/mFCWUwJKbVH07 jrIMrlBFGhCI523ImIomrQAz8O+qfieYCSRhmKLySqCFUcTMvg6AijfQIFkg96avVlJ3 TjugOPtPBBsQQRqFAW8ozNuA7b3oaFcllrvUG/JvZjL12KsorfbTmvw4L92x7V/cnfZg COQeZgfrXfoW8ALSw5SnkoVECVGTAqs70qjmB0RDPVVveqPhFJnX22i86wtJrlfiYUCF bcaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718457856; x=1719062656; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Rfslw58MenJsY/u9M1wf18Y3CJpjVkjiPdZVvkBYOzU=; b=JLQ86ha2APvwSu5QsK92uOoni3dIOYlIqyc2hGuRgbIrPSp4b0k8RTSE8cCQhNuBJR YfvUc9rcMgxXUtZPn/Hb56HS04inmoMJNjCh9MlRsh2cJmj3mjfLf4R520L9vqaLutwj Y94qz9nvT/CWBALzO2yPpivB8pH5q2uyQgARAFlMRw7GLnQwsUmAy5y1419brC16NJIX X0XHLfQ54rugv4HwtpKAnOzoJuuMy8r3c56MLkwpsqRKkiu6GPdWQV7E3xWMVEvqzcRX H1ctyQ21Q8HPlthXe1opZMMsYmCB4mK7s/V34ZHl22J+DmrjeJNu9wvZuXivjw636oZx ghGw== X-Gm-Message-State: AOJu0YzbfEkMzhI/4Gi6K9kZ6QAvTII49+n+yGN2nGuoZOF8nPBlT+cq dqNx9ZUB1TAwytlMy/n0mpxKjbXb/CHyL2cemm8EQ8pYPcCySOfkmQMpICt1mdlKvsTm2BLNAzz zcKxuR3rqfcDEBe3mpZyk/h9eU5fydjBr X-Google-Smtp-Source: AGHT+IHJ8A9mluO10YxmPaFYYcl1yALCC+eG3SJzXYal7txZsbpo7ahX82NAkXbzPxc0iBQtMl0YG58HXq82/GifL6o= X-Received: by 2002:a67:f159:0:b0:48d:ca55:a145 with SMTP id ada2fe7eead31-48dca55a4cdmr1191395137.27.1718457856167; Sat, 15 Jun 2024 06:24:16 -0700 (PDT) MIME-Version: 1.0 From: sud Date: Sat, 15 Jun 2024 18:54:03 +0530 Message-ID: Subject: Design for dashboard query To: pgsql-general Content-Type: multipart/alternative; boundary="00000000000014159a061aeda69c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000014159a061aeda69c Content-Type: text/plain; charset="UTF-8" Hello All, Its postgres version 15.4. We are having a requirement in which aggregated information for all the users has to be displayed on the UI screen. It should show that information on the screen. So basically, it would be scanning the full table data which is billions of rows across many months and then join with other master tables and aggregate those and then display the results based on the input "user id" filter. In such a scenario we are thinking of using a materialized view on top of the base tables which will store the base information and refresh those periodically to show the data based on the input user id. However i am seeing , postgres not supporting incremental refresh of materialized view and full refresh can take longer. So , do we have any other option available? Additionally , It should not impact or block the online users querying the same materialized view when the refresh is happening. --00000000000014159a061aeda69c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

= Hello All,

Its postgres version 15.4. We are having a require= ment in which aggregated information for all the users has to be displayed = on the UI screen. It should show that information on the screen. So basical= ly, it would be scanning the full table data which is billions of rows acro= ss many months and then join with other master tables and aggregate those a= nd then display the results based on the input "user id" filter.<= /span>

In such a scenario we are thinking= of using a materialized view on top of the base tables which will store th= e base information and refresh those periodically to show the data based on= the input user id. However i am seeing , postgres not supporting incremen= tal refresh of materialized view and full refresh can take longer. So , do = we have any other option available? Additionally , It should not impact or block the online use= rs querying the same materialized view when the refresh is happening.


--00000000000014159a061aeda69c--