Re: Advice on best way to store a large amount of data in postgresql

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

On Mon, 9 Jan 2023 11:56:47 -0600
Justin Pryzby <pryzby(at)telsasoft(dot)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