Re: Postgresql 14 partitioning advice

On Mon, Aug 08, 2022 at 03:45:11PM -0700, Slava Mudry wrote: > Postgres 14 improved partitioning quite a bit. I used it in Postgres 9 and > there was a lot of locking on partition hierarchy when you add/drop > partition tables. Note that postgres 9 didn't have native/declarative partitioning, and

Re: Postgresql 14 partitioning advice

> 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. I think you absolutely need to use partitioning for the following reasons: 1. maintenance and roll-off of older data 2. indexes are

Re: Postgresql 14 partitioning advice

On Mon, Aug 1, 2022 at 10:16 AM Rick Otten wrote: > >> The other problem I ran into, which I'm still building a test case for >> and I fear might be a bug if I can easily reproduce it, >> is if I did the original select in a CTE, and then did a sort outside of >> the CTE, even though the CTE foun

Re: Postgresql 14 partitioning advice

> > > The other problem I ran into, which I'm still building a test case for and > I fear might be a bug if I can easily reproduce it, > is if I did the original select in a CTE, and then did a sort outside of > the CTE, even though the CTE found 0 rows, the database > still spent a _ton_ of time s

Re: Postgresql 14 partitioning advice

> On 30/07/2022, at 9:44 AM, Rick Otten wrote: > > On Wed, Jul 27, 2022 at 8:55 AM 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

Re: Postgresql 14 partitioning advice

On Wed, Jul 27, 2022 at 8:55 AM 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. > I though

Re: Postgresql 14 partitioning advice

On Wed, Jul 27, 2022 at 8:55 AM Rick Otten wrote: > > 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. > > It is hard to know how seriously to take the advice of a

Re: Postgresql 14 partitioning advice

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. >

Postgresql 14 partitioning advice

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. I've been soliciting advice for best practices for building this. One