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 1qs8HJ-006tgL-4C for pgsql-admin@arkaria.postgresql.org; Sun, 15 Oct 2023 21:04:01 +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 1qs8HG-00CjB5-LE for pgsql-admin@arkaria.postgresql.org; Sun, 15 Oct 2023 21:03:59 +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 1qs8HG-00CjAq-63 for pgsql-admin@lists.postgresql.org; Sun, 15 Oct 2023 21:03:58 +0000 Received: from mail-yw1-x112a.google.com ([2607:f8b0:4864:20::112a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qs8H9-000eC7-V9 for pgsql-admin@lists.postgresql.org; Sun, 15 Oct 2023 21:03:57 +0000 Received: by mail-yw1-x112a.google.com with SMTP id 00721157ae682-5a7be88e9ccso48666977b3.2 for ; Sun, 15 Oct 2023 14:03:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697403831; x=1698008631; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ZtiVLRSll+fpk/eQDYi3AmkGdBOZvz60H5sE0IQfTkg=; b=LTzIyB99oHdhq3RywewgGYSzgeHYxNKIXUQr9oAM4zMEVdpCYecZY/FYqSO/3bpmzy nqxKV5VWkzWGoikAvm79ZUEl85m8VbwibJbaMBblwpkRNkPShUZMBrcoL2V8fnsSOnJW g/plV9JJf5UdUVE7jjHkTM05iyD9mMIylxjpKOL0MruPv1Lu/cEq12xGrDJ0ATNVyN5s 8TfnMhPHMlpkcfPE7yFORxzMkYrWrQfYjZ8LkLhRm7hw/ykydOB4Bm80IiRP17wsNmd7 v4o3AX9xOJaZn041ICbisi+WCv9bATaTfh2bX9l/5JV7BgvubD3ROmdKRNc29ruwcEB+ KDZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697403831; x=1698008631; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ZtiVLRSll+fpk/eQDYi3AmkGdBOZvz60H5sE0IQfTkg=; b=Kn0mG3KMa1Ro8i9JJdKtx9bor1i6oOjYTnvN+8RTOR/kFKSxI6tUPIIJ8hTuEYlJzY X1DLshGqyO4mD+ZwkqBLKiWFM3KloinYnVINvxV5u2GkEIyo/tcE+kaV8AfknRBTU8/C OO0rBRJzo35qlo78vcMUz4wA+0ZMhLvWNHa8Ewbf14tye7WlEP8oOOLzlS4SnTqEaXgL anV5vc9RQ9s0wFHdHWBISST2Ju68pv859idilAwDPq8BthdKM46LZSF6E5ncZWWZb2Ux l9i3wW0fMj5DjK4rP4F00/X9Oqw488RPD9Q6zk0YeZfDX9m20mKA91NuCZiF2exf58vi qGLg== X-Gm-Message-State: AOJu0YxlZmgiGlbAD6QXB63VMMDsXnVRgOr1BTE7NM22BJFnoezUGbgP 0OzR+eiMHOenPzdh/IUBrqGgafuJzoY= X-Google-Smtp-Source: AGHT+IHIJaxTaOvUF3ItW7Gl7KHHa4ZnW22yuyKvdDOZGUv0AJYtPkCOEOy5RqeoHPWRG+qLUQwscg== X-Received: by 2002:a0d:eb13:0:b0:5a7:aa7b:cb9f with SMTP id u19-20020a0deb13000000b005a7aa7bcb9fmr18306738ywe.14.1697403830584; Sun, 15 Oct 2023 14:03:50 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:3504:20dc:f3b1:66d3? ([2600:1700:bd2c:b810:3504:20dc:f3b1:66d3]) by smtp.googlemail.com with ESMTPSA id i205-20020a0ddfd6000000b005a247c18403sm1653172ywe.37.2023.10.15.14.03.50 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 15 Oct 2023 14:03:50 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------Z3HUstKssFjsFMvNLOTm0DwI" Message-ID: Date: Sun, 15 Oct 2023 16:03:49 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Big table and partition Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Z3HUstKssFjsFMvNLOTm0DwI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 10/15/23 15:46, Rajesh Kumar wrote: > The columns are id, blocknumber and jsondata. No date column? How much json data? > > I have not been told anything ABT data retention and I will ask them. > > Problem I am trying to resolve is, 30gb is big Big is relative.  30GB in 2023 is pretty darned small. > and it will be every growing always. By doing this, i am making the scan > easier (despite having indexes). > >  Growth will be 10gb per year data. That's less than a gigabyte per month. > > Incase I am doing partition, Plz provide any script that does partitioning > automatically. We are using postgres version 15. *Keep It Simple, Stanley* and just DELETE WHERE id < /nnnnnnn/;.  Do that every month. > > On Sun, 15 Oct, 2023, 8:07 PM Christophe Courtois, > wrote: > > Hi, > > Le 14/10/2023 à 22:46, Rajesh Kumar a écrit : > > In my env, I have a table 30GB where data is getting inserted directly > > whenever the new block is created in block chain... > > I have been asked to do partition, i said manager since we are not at > > all using the past data, partition is not required. Instead we can stop > > the sync (downtime not required), change the name of table , create a > > new table with same structure with original name and keep only tis > month > > data. Then sync the blockchain so that new data will come to newly > > created table and old data will be there simply as a storage as we r > not > > selecting any query from old data... > > In fact, you want to do manual partitioning. > Why reinvent the wheel? > > > Note: on a longer run, data will keep on coming in whenever new > block is > > created. > > Is that right ? Any suggestions ? Better options ? > > What problem do you have to solve? 30 GB is not so big, but since when > do you store data? Will you purge the table? What is the main access key > of your queries (critical for the partitioning key)? > > Without other information I'd say that if you never read the former > months again, partition by month, perhaps by year. > > -- > Christophe Courtois > Consultant DALIBO > -- Born in Arizona, moved to Babylonia. --------------Z3HUstKssFjsFMvNLOTm0DwI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 10/15/23 15:46, Rajesh Kumar wrote:
The columns are id, blocknumber and jsondata.

No date column?

How much json data?


I have not been told anything ABT data retention and I will ask them.

Problem I am trying to resolve is, 30gb is big

Big is relative.  30GB in 2023 is pretty darned small.

and it will be every growing always. By doing this, i am making the scan easier (despite having indexes).

 Growth will be 10gb per year data.

That's less than a gigabyte per month.


Incase I am doing partition, Plz provide any script that does partitioning automatically. We are using postgres version 15.

Keep It Simple, Stanley and just DELETE WHERE id < nnnnnnn;.  Do that every month.


On Sun, 15 Oct, 2023, 8:07 PM Christophe Courtois, <christophe.courtois@dalibo.com> wrote:
Hi,

Le 14/10/2023 à 22:46, Rajesh Kumar a écrit :
> In my env, I have a table 30GB where data is getting inserted directly
> whenever the new block is created in block chain...
> I have been asked to do partition, i said manager since we are not at
> all using the past data, partition is not required. Instead we can stop
> the sync (downtime not required), change the name of table , create a
> new table with same structure with original name and keep only tis month
> data. Then sync the blockchain so that new data will come to newly
> created table and old data will be there simply as a storage as we r not
> selecting any query from old data...

In fact, you want to do manual partitioning.
Why reinvent the wheel?

> Note: on a longer run, data will keep on coming in whenever new block is
> created.
> Is that right ? Any suggestions ? Better options ?

What problem do you have to solve? 30 GB is not so big, but since when
do you store data? Will you purge the table? What is the main access key
of your queries (critical for the partitioning key)?

Without other information I'd say that if you never read the former
months again, partition by month, perhaps by year.

--
Christophe Courtois
Consultant DALIBO

--
Born in Arizona, moved to Babylonia.
--------------Z3HUstKssFjsFMvNLOTm0DwI--