Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jFWwN-0003mb-B4 for pgsql-admin@arkaria.postgresql.org; Sat, 21 Mar 2020 05:44:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jFWwL-0000mB-4g for pgsql-admin@arkaria.postgresql.org; Sat, 21 Mar 2020 05:44:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jFWwK-0000m3-NY for pgsql-admin@lists.postgresql.org; Sat, 21 Mar 2020 05:44:56 +0000 Received: from impout002aa.msg.chrl.nc.charter.net ([47.43.20.26] helo=impout002.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jFWwF-0000vJ-Cj for pgsql-admin@lists.postgresql.org; Sat, 21 Mar 2020 05:44:55 +0000 Received: from cerberus.crazybean.net ([24.151.69.73]) by cmsmtp with ESMTP id FWwDjNVZnh15uFWwDj5TZ7; Sat, 21 Mar 2020 05:44:49 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=etJrMbhX c=1 sm=1 tr=0 a=YXTuGtgWN9YX+aIhe8tYuA==:117 a=YXTuGtgWN9YX+aIhe8tYuA==:17 a=jpOVt7BSZ2e4Z31A5e1TngXxSK0=:19 a=xqWC_Br6kY4A:10 a=SS2py6AdgQ4A:10 a=C_1_nwflLde_0Uif5D8A:9 a=CjuIK1q_8ugA:10 a=VOdvTk-1ANMsgOKl6ywA:9 a=h5KVJgsB5xu7bLdg:21 a=_W_S_7VecoQA:10 Received: from [IPv6:2001:470:e117:4:35f6:90ee:4d56:650a] ( [2001:470:e117:4:35f6:90ee:4d56:650a]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 9886ba49 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Sat, 21 Mar 2020 01:44:48 -0400 (EDT) From: Rui DeSousa Message-Id: <7CBBAB2A-A8C4-47D6-9912-7B3AD6554EAF@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_7CADEDDA-EC6C-4DE2-8E4F-29E88E948578" Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\)) Subject: Re: Append only tables Date: Sat, 21 Mar 2020 01:44:47 -0400 In-Reply-To: <20200320215053.GH22330@roeckx.be> Cc: pgsql-admin@lists.postgresql.org To: Kurt Roeckx References: <20200320215053.GH22330@roeckx.be> X-Mailer: Apple Mail (2.3608.60.0.2.5) X-CMAE-Envelope: MS4wfJddHoeLPr/H2uaZhaiXXUvpP0uthmqd01K8jLh4tPwszYoW86tjpKPhhjxIytl5cxLK7wSaudjqXEYRHJdonParh8UPcU61YF+m1/e3XO2YFgpo4j2s Fyl+8FZfZ6q2TlW3JypmsqC3WfLPmIk51lSjtJRu0uTmctJpYbRX/V94JYGOKG011g9s+lnRCPnlYN8nuIrKMVmuXKlI9w0Za+yMh7WICWB9RtueABbNJM7U List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_7CADEDDA-EC6C-4DE2-8E4F-29E88E948578 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Mar 20, 2020, at 5:50 PM, Kurt Roeckx wrote: >=20 > Hi, >=20 > I have a few tables that are append only. Thre are only gets insert > and select queries, never update or delete. >=20 > What I see is that every file is still being updated. It's > currently about 500 GB big, and every of that almost 500 files has > been touched the past 24 hours. >=20 > (auto) vacuum is not happening on the table. >=20 Vacuum is still required as the tuples need to be frozen. Have you = vacuumed the table? It could be the result of records being frozen during selects = statements; select can freeze tuples that meet the criteria for being = frozen.=20 I would recommend vacuuming the table to get the existing records = frozen; then I believe you should see fewer updates to the all the base = files.= --Apple-Mail=_7CADEDDA-EC6C-4DE2-8E4F-29E88E948578 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii


On = Mar 20, 2020, at 5:50 PM, Kurt Roeckx <kurt@roeckx.be> = wrote:

Hi,

I have a few tables that are = append only. Thre are only gets insert
and select queries, = never update or delete.

What I see is that = every file is still being updated. It's
currently about = 500 GB big, and every of that almost 500 files has
been = touched the past 24 hours.

(auto) vacuum is = not happening on the table.


Vacuum is still required as the tuples need to be = frozen.  Have you vacuumed the table?

It could be the result of records being frozen during = selects statements; select can freeze tuples that meet the criteria = for being frozen. 

I = would recommend vacuuming the table to get the existing records frozen; = then I believe you should see fewer updates to the all the base = files.
= --Apple-Mail=_7CADEDDA-EC6C-4DE2-8E4F-29E88E948578--