Date: Tue, 10 Jan 2023 04:25:38 -0500
From: spiral <spiral@spiral.sh>
To: Justin Pryzby <pryzby@telsasoft.com>, pgsql-performance@postgresql.org
Subject: Re: Advice on best way to store a large amount of data in
 postgresql
Message-ID: <20230110042538.44cad73b@xps>
In-Reply-To: <20230109175647.GB9837@telsasoft.com>
References: <20230108070201.39b19d30@xps>
 <20230109175647.GB9837@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20230110042538.44cad73b%40xps>
Precedence: bulk

(re-sending this because I forgot to use "reply all". Sorry!)

On Mon, 9 Jan 2023 11:56:47 -0600
Justin Pryzby <pryzby@telsasoft.com> wrote:

> Are you using the extended query protocol with "bind" parameters, or
> are they escaped and substituted by the client library ?

Our client library uses parameters, yes. "$1" is passed literally to
postgres.

> can you arrange your partitioning such that the things to be dropped
> are all in one partition, to handle in bulk ?

Unfortunately, no. Deletes are all generated from user actions - if a
user deletes one message, we need to delete one single row from our
database.

> [...] one of the main reasons for using partitioning
> How many "partitions" do you have (actually, timescale uses
> inheritance) ?

We have ~1600 timescaledb chunks. We could increase the chunk count. We
could also stop using partitions/chunks if it would improve things. I'm
currently setting up a test database without partitioning to see what
the performance would look like.

spiral