Re: Question on pg_cron

On Sat, Jun 8, 2024 at 10:05 PM yudhi s wrote: > > > On Sat, 8 Jun, 2024, 9:53 pm Ron Johnson, wrote: > >> On Sat, Jun 8, 2024 at 5:31 AM yudhi s >> wrote: >> >>> Hello All, >>> >>> We have around 10 different partition tables for which the partition >>> maintenance is done using pg_partman ext

Re: Creating big indexes

On Sun, Jun 9, 2024 at 10:36 AM sud wrote: > > You can first create the index on the table using the "On ONLY"keyword, > something as below. > > CREATE INDEX idx ON ONLY tab(col1); > > Then create indexes on each partition in "concurrently" from multiple > sessions in chunks. > > CREATE INDEX CO

Re: Creating big indexes

On Sat, Jun 8, 2024 at 12:53 PM Lok P wrote: > Hello, > We have a few tables having size ~5TB and are partitioned on a timestamp > column. They have ~90 partitions in them and are storing 90 days of data. > We want to create a couple of indexes on those tables. They are getting the > incoming tra

Re: How to create efficient index in this scenario?

On Sun, 9 Jun 2024 at 09:45, Lok P wrote: > > On Sat, Jun 8, 2024 at 7:03 PM veem v wrote: > >> >> There is a blog below (which is for oracle), showing how the index should >> be chosen and it states , "*Stick the columns you do range scans on >> last in the index, filters that get equality pre

Re: How to create efficient index in this scenario?

On Sat, Jun 8, 2024 at 7:03 PM veem v wrote: > Hi , > It's postgres version 15.4. A table is daily range partitioned on a column > transaction_timestamp. It has a unique identifier which is the ideal for > primary key (say transaction_id) , however as there is a limitation in > which we have to i

Re: Questions on logical replication

On Sat, Jun 8, 2024 at 1:41 PM Koen De Groote wrote: > What I'm trying to do is upgrade a PG11 database to PG16, using logical > replication. > > The PG11 has an active and a standby, there are a handful of databases. On > particular one has a few tables just over 100GB, then a few 100 tables nea

Re: Questions on logical replication

On 6/8/24 10:40, Koen De Groote wrote: What I'm trying to do is upgrade a PG11 database to PG16, using logical replication. Have you looked at pg_upgrade?: https://www.postgresql.org/docs/current/pgupgrade.html The PG11 has an active and a standby, there are a handful of databases. On part

Re: Questions on logical replication

What I'm trying to do is upgrade a PG11 database to PG16, using logical replication. The PG11 has an active and a standby, there are a handful of databases. On particular one has a few tables just over 100GB, then a few 100 tables near 1GB. What I'd do is start a publication with no tables and ad

Re: Question on pg_cron

On Sat, 8 Jun, 2024, 9:53 pm Ron Johnson, wrote: > On Sat, Jun 8, 2024 at 5:31 AM yudhi s > wrote: > >> Hello All, >> >> We have around 10 different partition tables for which the partition >> maintenance is done using pg_partman extension. These tables have foreign >> key dependency between the

Re: Question on pg_cron

On Sat, Jun 8, 2024 at 5:31 AM yudhi s wrote: > Hello All, > > We have around 10 different partition tables for which the partition > maintenance is done using pg_partman extension. These tables have foreign > key dependency between them. We just called partman.run_maintanance_proc() > through p

Columnar Format Export in Postgres

Hey Postgres team, I have been working on adding support for columnar format export to Postgres to speed up analytics queries. I've created an extension that achieves this functionality here . I"m looking to improve the performance of this extension to

How to create efficient index in this scenario?

Hi , It's postgres version 15.4. A table is daily range partitioned on a column transaction_timestamp. It has a unique identifier which is the ideal for primary key (say transaction_id) , however as there is a limitation in which we have to include the partition key as part of the primary key, so i

Question on pg_cron

Hello All, We have around 10 different partition tables for which the partition maintenance is done using pg_partman extension. These tables have foreign key dependency between them. We just called partman.run_maintanance_proc() through pg_cron without any parameters and it was working fine. So w

Re: Long running query causing XID limit breach

On Thu, Jun 6, 2024 at 12:52 AM yudhi s wrote: > On Wed, Jun 5, 2024 at 3:52 PM Laurenz Albe > wrote: > >> >> There should never be a restart unless you perform one or the standby >> crashes. >> If you mean that you want to avoid a crash caused by a full disk on the >> standby, >> the answer is

Creating big indexes

Hello, We have a few tables having size ~5TB and are partitioned on a timestamp column. They have ~90 partitions in them and are storing 90 days of data. We want to create a couple of indexes on those tables. They are getting the incoming transactions(mainly inserts) 24/7 , which are mostly happeni