Date: Wed, 27 Jul 2022 08:38:44 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgresql 14 partitioning advice
Message-ID: <20220727133844.GH15006@telsasoft.com>
References: 
 <CAMAYy4LE2ORnk7NEFSwA+A800rzWCS5qdKe7ZxPoFmwgT=843Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMAYy4LE2ORnk7NEFSwA+A800rzWCS5qdKe7ZxPoFmwgT=843Q@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220727133844.GH15006%40telsasoft.com>
Precedence: bulk

On Wed, Jul 27, 2022 at 08:55:14AM -0400, Rick Otten wrote:
> I'm spinning up a new Postgresql 14 database where I'll have to store a
> couple years worth of time series data at the rate of single-digit millions
> of rows per day.  Since this has to run in AWS Aurora, I can't use
> TimescaleDB.

> One person I talked to said "try not to have more than 100 partitions",
> even with the latest postgresql you'll end up with a lot of lock contention
> if you go over 100 partitions.

I'm not familiar with this (but now I'm curious).  We have over 2000 partitions
in some tables.  No locking issue that I'm aware of.  One issue that I *have*
seen is if you have many partitions, you can end up with query plans with a
very large number of planner nodes, and it's hard to set
work_mem*hash_mem_multiplier to account for that.

> This person also recommended manually
> kicking off vacuums on a regular schedule rather than trusting autovacuum
> to work reliably on the partitioned tables.

They must mean *analyze*, which does not run automatically on the partitioned
tables (only the partitions).  The partitioned table is empty, so doesn't need
to be vacuumed.

> I've got several keys, besides the obvious time-key that I could partition
> on.   I could do a multi-key partitioning scheme.  Since the data is
> inbound at a relatively steady rate, if I partition on time, I can adjust
> the partitions to be reasonably similarly sized.  What is a good partition
> size?

Depends on 1) the target number of partitions; and 2) the target size for
indexes on those partitions.  More partition keys will lead to smaller indexes.
Depending on the type of index, and the index keys, to get good INSERT
performance, you may need to set shared_buffers to accommodate the sum of size
of all the indexes (but maybe not, if the leading column is timestamp).

> Since the data most frequently queried would be recent data (say the past
> month or so) would it make sense to build an archiving strategy that rolled
> up older partitions into larger ones?  ie, do daily partitions for the
> first four weeks, then come up with a process that rolled them up into
> monthly partitions for the next few months, then maybe quarterly partitions
> for the data older than a year?  (I'm thinking about ways to keep the
> partition count low - if that advice is justified.)

I think it can make sense.  I do that myself in order to: 1) avoid having a
huge *total* number of tables (which causes pg_attribute to be large, since our
tables are also "wide"); and 2) make our backups of "recent data" smaller; and
3) make autoanalyze a bit more efficient (a monthly partition will be analyzed
numerous times the 2nd half of the month, even though all the historic data
hasn't changed at all).

> Or, should I just have a single 7 Trillion row table with a BRIN index on
> the timestamp and not mess with partitions at all?

Are you going to need to DELETE data ?  Then this isn't great, and DELETEing
data will innevitably cause a lower correlation, making BRIN less effective.

-- 
Justin