Re: pg_stat_io clarifications: background worker, writes and reads

Hi, In PostgreSQL, the pg_stat_io view provides detailed statistics on I/O operations. Background process perform maintenance tasks and other background operations essential to the functioning of the PostgreSQL database. They include processes such as: 1. Autovacuum Workers 2. WAL Writer 3. Backg

Re: pg_stat_io clarifications: background worker, writes and reads

Hi background workers are used to perform tasks on db e.g. I/O, replication, clone schema, vacuum etc, you can find more details on following links. https://www.postgresql.org/docs/current/bgworker.html#:~:text=PostgreSQL%20can%20be%20extended%20to,linked%20to%20the%20server's%20status . Regards

Re: Adding constraints faster

On Wed, May 15, 2024 at 2:09 AM Ron Johnson wrote: > On Tue, May 14, 2024 at 3:59 PM sud wrote: > >> * >> > ALTER TABLE ADD FOREIGN KEY ... NOT VALID. >> ALTER TABLE ... VALIDATE CONSTRAINT; >> >> > This is what we did, back in the PG 12.x period. VALIDATE CONSTRAINT was > almost instantane

Re: Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

On 2024-May-14, Dirschel, Steve wrote: > But when I try and run the command inside the procedure it throws this error: > > STATE: 25001 > MESSAGE: ALTER TABLE ... DETACH CONCURRENTLY cannot run inside a transaction > block > CONTEXT: SQL statement "alter table t2.test1 detach partition > t2.tes

pg_stat_io clarifications: background worker, writes and reads

Hello list, what is the "background worker" in the pg_stat_io statistics view? I'm reading the documentation but can't figure this one out knowing that it is not autovacuum or bgwriter. And I'm not aware of any extension I might have with registered background worker. Additionally, how can it be

Using ALTER TABLE DETACH PARTITION CONCURRENTLY inside a procedure

We have a custom procedure to add/drop partitions in Postgres. It has a main FOR LOOP to find tables needing to be processed. Inside that FOR LOOP there is a BEGIN so if a single table gets an error we catch the error in an exception. At the end of the END for the FOR LOOP it issues a commit.

Adding constraints faster

Hi, It's postgres version 15.4. We want to create foreign keys on three different partitioned tables which already have data in them in production. They all are referring to the same parent table which is also partitioned. All the tables(both parent and child) are having ~2TB+ in size each and havi

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

I have forgotten to mention that I have enable_partitionwise_aggregate=on in the global settings since the beginning. According to the docs: Enables or disables the query planner's use of partitionwise grouping or aggregation, which allows grouping or aggregation on partitioned tables to be perf

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Tue, 14 May 2024, David Rowley wrote: If you were to put the n_distinct_inherited estimate back to 200 and disable sort, you should see the costs are higher for the index plan. If that's not the case then there might be a bug. It seems more likely that due to the n_distinct estimate being so

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Tue, 14 May 2024, Dimitrios Apostolou wrote: It took long but if finished: ANALYZE Time: 19177398.025 ms (05:19:37.398) I see now that default_statistics_target is globally set to 500, so this is probably the reason it took so long. I guess with the default of 100, it would take approximat

Re: Valid until

Hi, What is the outcome of this query for the specified user? Does the database contain a value in the rolvaliduntil column? SELECT rolname, rolvaliduntil FROM pg_authid; Regards, M.Imtiaz On Tue, 14 May 2024 at 20:45, Tom Lane wrote: > Adrian Klaver writes: > > On 5/14/24 00:57, Rama Krishnan

Re: Valid until

Adrian Klaver writes: > On 5/14/24 00:57, Rama Krishnan wrote: >> I recently set a user's password validity to "2024-05-13", but despite >> this, the user is still able to connect to the database. Is this a bug, >> or is the "valid until" parameter just for identification purposes, >> indicatin

Re: Valid until

On 5/14/24 00:57, Rama Krishnan wrote: Hi team, I have a question about the "valid until" parameter in the "create role" command. I recently set a user's password validity to "2024-05-13", but despite this, the user is still able to connect to the database. Is this a bug, or is the "valid u

Valid until

Hi team, I have a question about the "valid until" parameter in the "create role" command. I recently set a user's password validity to "2024-05-13", but despite this, the user is still able to connect to the database. Is this a bug, or is the "valid until" parameter just for identification purpo

Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions

On Fri, 10 May 2024, Tom Lane wrote: Dimitrios Apostolou writes: Further digging into this simple query, if I force the non-parallel plan by setting max_parallel_workers_per_gather TO 0, I see that the query planner comes up with a cost much higher: Limit (cost=363.84..1134528847.47 r