Re: page is not marked all-visible but visibility map bit is set in relation "pg_statistic"
On 7/11/24 19:21, Gus Spier wrote: > AWS RDS Postgres Aurora version 14.4 > I believe RDS and Aurora are two separate products, so I'm a bit confused by this ... > Error log shows: page is not marked all-visible but visibility map bit is > set in relation "pg_statistic" > > To me, that sounds ominous. But, there does not appear to be any great > performance hit. The applications are chugging along nicely. The end-users > have not yet gathered at my door, waving torches and pitch-forks. > > What is the correct course of action in this case? > I'd say you need to report this to AWS support - we have no idea what changes they made in Aurora, and AFAIK they made a lot of changes in this area. That is not to say we don't have any bugs in this area - see for example the discussion in [1], but we are not in position to investigate issues on a proprietary product. regards [1] https://www.postgresql.org/message-id/CAH2-WznuNGSzF8v6OsgjaC5aYsb3cZ6HW6MLm30X0d65cmSH6A%40mail.gmail.com -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Running psql in a docker container
On July 11, 2024 3:31:37 PM EDT, Adrian Klaver wrote: >On 7/11/24 11:14, H wrote: >> On July 11, 2024 11:06:02 AM GMT-04:00, Adrian Klaver > wrote: >>> On 7/11/24 07:57, H wrote: I used to successfully run psql server 13 in a CentOS 7 docker >>> container with CentOS 7 as the host operating system. I have now >>> upgraded that system to Rocky Linux 9 as the host operating system >and >>> modifying my container to also use Rocky Linux 9 and psql server 16. I can successfully get the container up and running and can connect >>> to it. However, I have not managed to modify the initdb statement to >>> use en_US.UTF-8 for all databases. In the old container I used: su - postgres -c "/usr/pgsql-13/bin/initdb -D >/var/lib/pgsql/13/data/ >>> -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" but this does not work with psql 16. I tried: su - postgres -c "/usr/pgsql-16/bin/initdb -D >/var/lib/pgsql/16/data/ >>> -E 'UTF-8' --lc-lang='en_US'" > >1) Per: > >https://www.postgresql.org/docs/16/app-initdb.html > >lc_lang does not exist and I get: > >/usr/lib/postgresql/16/bin/initdb: unrecognized option >'--lc-lang=en_US' > >2) On Ubuntu 22.04 doing: > > /usr/lib/postgresql/16/bin/initdb -D postgres/16/data/ -E 'UTF-8' >--lc-collate='en_US.utf8' --lc-ctype='en_US.utf8' > >I get: > >postgres=# \l >List of databases >-[ RECORD 1 ]-+ >Name | postgres >Owner | aklaver >Encoding | UTF8 >Locale Provider | libc >Collate | en_US.utf8 >Ctype | en_US.utf8 >ICU Locale| >ICU Rules | >Access privileges | >-[ RECORD 2 ]-+ >Name | template0 >Owner | aklaver >Encoding | UTF8 >Locale Provider | libc >Collate | en_US.utf8 >Ctype | en_US.utf8 >ICU Locale| >ICU Rules | >Access privileges | =c/aklaver + > | aklaver=CTc/aklaver >-[ RECORD 3 ]-+ >Name | template1 >Owner | aklaver >Encoding | UTF8 >Locale Provider | libc >Collate | en_US.utf8 >Ctype | en_US.utf8 >ICU Locale| >ICU Rules | >Access privileges | =c/aklaver + > | aklaver=CTc/aklaver > >3) What does locale -a return on your machine? > > and variations thereof with no success. >>> >>> Without a definition of what '... no success' means there is no real >>> way >>> to answer this. >>> Any suggestions on what I have missed? Thank you. >> >> Good question! ”No success” meant that I have not been able to figure >out how to have new databases default to en_US.UTF-8, instead they >default to C.UTF-8. >> >> What is the proper syntax for pgsql 16 for this? I could not get the >example given in the docs to work... You are right, the locale is now called en_US.utf8 and with that correction I get the expected result.
Re: Running psql in a docker container
On July 11, 2024 3:48:42 PM EDT, Tom Lane wrote: >H writes: >> Understood but how should formulate the initdb statement to >accomplish what I want on pgsql 16 since the syntax I used for pgsql 13 >does not work in my container? > >You still haven't shown us the actual error message, so we're all >just guessing. > >I will offer a guess though. This *should* work, since Rocky 9 >is a direct descendant of RHEL/CentOS 7. The only reason I can >think why it wouldn't is that you haven't installed the OS package >that defines en_US.UTF-8. Try doing "locale -a" and see if >en_US.UTF-8 is among the listed locales. > >On my RHEL8 box, it looks like glibc-locale-source is what >provides most non-C locales. > > regards, tom lane I run locale på in the container and found that the appropriate locale is called en_US.utf8, ie. not en_US.UTF-8... Problem now solved! Thank you all!
Re: Running psql in a docker container
H writes: > Understood but how should formulate the initdb statement to accomplish what I > want on pgsql 16 since the syntax I used for pgsql 13 does not work in my > container? You still haven't shown us the actual error message, so we're all just guessing. I will offer a guess though. This *should* work, since Rocky 9 is a direct descendant of RHEL/CentOS 7. The only reason I can think why it wouldn't is that you haven't installed the OS package that defines en_US.UTF-8. Try doing "locale -a" and see if en_US.UTF-8 is among the listed locales. On my RHEL8 box, it looks like glibc-locale-source is what provides most non-C locales. regards, tom lane
Re: Running psql in a docker container
On 7/11/24 11:14, H wrote: On July 11, 2024 11:06:02 AM GMT-04:00, Adrian Klaver wrote: On 7/11/24 07:57, H wrote: I used to successfully run psql server 13 in a CentOS 7 docker container with CentOS 7 as the host operating system. I have now upgraded that system to Rocky Linux 9 as the host operating system and modifying my container to also use Rocky Linux 9 and psql server 16. I can successfully get the container up and running and can connect to it. However, I have not managed to modify the initdb statement to use en_US.UTF-8 for all databases. In the old container I used: su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" but this does not work with psql 16. I tried: su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-lang='en_US'" 1) Per: https://www.postgresql.org/docs/16/app-initdb.html lc_lang does not exist and I get: /usr/lib/postgresql/16/bin/initdb: unrecognized option '--lc-lang=en_US' 2) On Ubuntu 22.04 doing: /usr/lib/postgresql/16/bin/initdb -D postgres/16/data/ -E 'UTF-8' --lc-collate='en_US.utf8' --lc-ctype='en_US.utf8' I get: postgres=# \l List of databases -[ RECORD 1 ]-+ Name | postgres Owner | aklaver Encoding | UTF8 Locale Provider | libc Collate | en_US.utf8 Ctype | en_US.utf8 ICU Locale| ICU Rules | Access privileges | -[ RECORD 2 ]-+ Name | template0 Owner | aklaver Encoding | UTF8 Locale Provider | libc Collate | en_US.utf8 Ctype | en_US.utf8 ICU Locale| ICU Rules | Access privileges | =c/aklaver + | aklaver=CTc/aklaver -[ RECORD 3 ]-+ Name | template1 Owner | aklaver Encoding | UTF8 Locale Provider | libc Collate | en_US.utf8 Ctype | en_US.utf8 ICU Locale| ICU Rules | Access privileges | =c/aklaver + | aklaver=CTc/aklaver 3) What does locale -a return on your machine? and variations thereof with no success. Without a definition of what '... no success' means there is no real way to answer this. Any suggestions on what I have missed? Thank you. Good question! ”No success” meant that I have not been able to figure out how to have new databases default to en_US.UTF-8, instead they default to C.UTF-8. What is the proper syntax for pgsql 16 for this? I could not get the example given in the docs to work... -- Adrian Klaver adrian.kla...@aklaver.com
Re: Running psql in a docker container
On July 11, 2024 2:20:43 PM EDT, Tom Lane wrote: >H writes: >> Good question! ”No success” meant that I have not been able to figure >out how to have new databases default to en_US.UTF-8, instead they >default to C.UTF-8. > >The default for an installation is determined at initdb time, either >with an explicit locale switch or from the environment locale >settings. > > regards, tom lane Understood but how should formulate the initdb statement to accomplish what I want on pgsql 16 since the syntax I used for pgsql 13 does not work in my container?
Re: Running psql in a docker container
On Thu, Jul 11, 2024 at 11:16 AM H wrote: > What is the proper syntax for pgsql 16 for this? I could not get the > example given in the docs to work... > The documentation says this still works: su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" If it isn't for you please show the evidence of the non-working-ness. David J.
Re: Running psql in a docker container
H writes: > Good question! ”No success” meant that I have not been able to figure out how > to have new databases default to en_US.UTF-8, instead they default to C.UTF-8. The default for an installation is determined at initdb time, either with an explicit locale switch or from the environment locale settings. regards, tom lane
Re: Running psql in a docker container
On July 11, 2024 11:06:02 AM GMT-04:00, Adrian Klaver wrote: >On 7/11/24 07:57, H wrote: >> I used to successfully run psql server 13 in a CentOS 7 docker >container with CentOS 7 as the host operating system. I have now >upgraded that system to Rocky Linux 9 as the host operating system and >modifying my container to also use Rocky Linux 9 and psql server 16. >> >> I can successfully get the container up and running and can connect >to it. However, I have not managed to modify the initdb statement to >use en_US.UTF-8 for all databases. >> >> In the old container I used: >> >> su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ >-E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" >> >> but this does not work with psql 16. I tried: >> >> su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ >-E 'UTF-8' --lc-lang='en_US'" >> >> and variations thereof with no success. > >Without a definition of what '... no success' means there is no real >way >to answer this. > >> >> Any suggestions on what I have missed? >> >> Thank you. >> >> Good question! ”No success” meant that I have not been able to figure out how to have new databases default to en_US.UTF-8, instead they default to C.UTF-8. What is the proper syntax for pgsql 16 for this? I could not get the example given in the docs to work...
page is not marked all-visible but visibility map bit is set in relation "pg_statistic"
AWS RDS Postgres Aurora version 14.4 Error log shows: page is not marked all-visible but visibility map bit is set in relation "pg_statistic" To me, that sounds ominous. But, there does not appear to be any great performance hit. The applications are chugging along nicely. The end-users have not yet gathered at my door, waving torches and pitch-forks. What is the correct course of action in this case? Regards, Gus Spier
Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.
Hello, Thank you to all who responded. There is a follow up question. Our admin tried the following: A host that wad been running postgresql11 was upgraded to Alma9 (from SL7) and postgresql15. They then built postgresql11 on that host from sources. Then they run pg_upgrade from 11 to 15. It worked and psql to db is not accompanied by "collation version" warning. This was unexpected to me based on my experience that I related on this thread. Is this a legit procedure? To remind, what did no work: - upgrade to 15 on SL7 host, setup stream, replication to Alma9 host. psql top replica complains about "The database was created using collation ..." Advice is appreciated P.S.: where I can still find postgresql11 RPMs for Alma9? Buiding from sourcres is OK, but a bit of a hassle. From: Daniel Verite Sent: Monday, June 24, 2024 3:48 AM To: Dmitry O Litvintsev Cc: pgsql-generallists.postgresql.org Subject: Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34. [EXTERNAL] – This message is from an external sender Dmitry O Litvintsev wrote: > Just want to make clear (sorry I am slow on uptake). I should first > REINDEX and then ALTER DATABASE xxx REFRESH COLLATION VERSION, or > first ALTER and then REINDEX or does the order of these action > matter at all? The order does not matter. The ALTER DATABASE command will simply update the pg_database.datcollversion field with the current version of libc. That will stop the warning being issued, but it doesn't have any other concrete effect. Best regards, -- Daniel Vérité https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresql.verite.pro_=DwIFaQ=gRgGjJ3BkIsb5y6s49QqsA=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU=F7VKeBFcE7ctVYy8fHvYvWPu4XkawA0hCuQOkYZk28e1uHpd_pb21GOrRMy9JB7a=M6qlhocjLWWgy8tVbTGTDEewC5JWHAfVztgV_XTx8Lg= Twitter: @DanielVerite
Re: Query on partitioned table needs memory n_partitions * work_mem
On Thursday, July 11, 2024, Dimitrios Apostolou wrote:I wonder how the postgres development community is > > tracking all these issues, I've even started forgetting the ones I have > found, and I'm sure I have previously reported (on this list) a couple of > should-be-easy issues that would be ideal for beginners. > > https://wiki.postgresql.org/wiki/Todo David J.
Re: Query on partitioned table needs memory n_partitions * work_mem
On Thu, 11 Jul 2024, Tom Lane wrote: Dimitrios Apostolou writes: The TABLE test_runs_raw has 1000 partitions on RANGE(workitem_n). So don't do that. Adding partitions is not cost-free. I understand that, they also add an administrative cost that I'd rather avoid. But I ended up adding all these partitions because of performance issues on a multi-billion rows table. There is probably some message from me on this list a couple of years ago. At the moment I have a work-around. I'm thankful that everyone is willing to provide workarounds to all potential issues/bugs I have presented, but unfortunately workarounds are not fixes, one will hit the same wall again at some point. My current concern is **reporting my findings responsibly**. I want to provide as much data needed to pinpoint the issue, so that the developers know exactly what's going on. Having right data is half the fix. A way to track the issue would be nice. I might revisit it and even try to submit a patch. I wonder how the postgres development community is tracking all these issues, I've even started forgetting the ones I have found, and I'm sure I have previously reported (on this list) a couple of should-be-easy issues that would be ideal for beginners. Regards, Dimitris
Re: Running psql in a docker container
On 7/11/24 07:57, H wrote: I used to successfully run psql server 13 in a CentOS 7 docker container with CentOS 7 as the host operating system. I have now upgraded that system to Rocky Linux 9 as the host operating system and modifying my container to also use Rocky Linux 9 and psql server 16. I can successfully get the container up and running and can connect to it. However, I have not managed to modify the initdb statement to use en_US.UTF-8 for all databases. In the old container I used: su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" but this does not work with psql 16. I tried: su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-lang='en_US'" and variations thereof with no success. Without a definition of what '... no success' means there is no real way to answer this. Any suggestions on what I have missed? Thank you. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Postgresql range_agg() Return empty list
Your update expression does not handle empty ranges properly, but convert them into complete ranges. https://dbfiddle.uk/Ia6wESpL On Thu, Jul 11, 2024 at 5:55 PM Han Tang wrote: > > Hi > > Hope you are doing well! > > I am using range_agg() function, it works fine with original table value > > Query Result > > But when I try to do some update for range value, it will give back an empty > list > > Select range_agg(b.r) > From ( > Select int8range(lower(bin_range)+1, upper(bin_range)+5) as r > From bin_data) as b; > > Query Result > > I test with the same query in fiddle but no issue there > > https://dbfiddle.uk/1MRn8hn6 > > All these query are running on pgAdmin4, wondering why it happens > > Thanks > Han > >
Running psql in a docker container
I used to successfully run psql server 13 in a CentOS 7 docker container with CentOS 7 as the host operating system. I have now upgraded that system to Rocky Linux 9 as the host operating system and modifying my container to also use Rocky Linux 9 and psql server 16. I can successfully get the container up and running and can connect to it. However, I have not managed to modify the initdb statement to use en_US.UTF-8 for all databases. In the old container I used: su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" but this does not work with psql 16. I tried: su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-lang='en_US'" and variations thereof with no success. Any suggestions on what I have missed? Thank you.
Re: Query on partitioned table needs memory n_partitions * work_mem
Dimitrios Apostolou writes: > The TABLE test_runs_raw has 1000 partitions on RANGE(workitem_n). So don't do that. Adding partitions is not cost-free. regards, tom lane
Re: Query on partitioned table needs memory n_partitions * work_mem
Thank you for the feedback.
So I've managed to reduce the query to a rather simple one:
SELECT
workitem_n, test_executable_n,
bool_or(test_resulttype_n IN (2,3))
FROM
test_runs_raw
GROUP BY
workitem_n, test_executable_n
LIMIT 10;
The TABLE test_runs_raw has 1000 partitions on RANGE(workitem_n). All the
columns are various integer types. There is an index on workitem_n.
On Thu, 11 Jul 2024, David Rowley wrote:
On Thu, 11 Jul 2024 at 13:19, Dimitrios Apostolou wrote:
I have a table with 1000 partitions on PostgreSQL 16.
I notice that a fairly complicated query of the form:
SELECT ... GROUP BY ... LIMIT ...
causes the postgres backend process to grow insanely very fast, and the
kernel OOM killer to kill it rather soon.
It seems it tries to allocate at least 1000 * work_mem.
-> Append
-> HashAggregate
-> Seq Scan
-> ... 1000 more hashagg+seqscans
Is this allocation pattern (workmem * n_partitions) expected under any
scenario? I can't find it documented. AFAIU the backend should allocate
up to (depth_of_execution_plan * work_mem) (putting aside the
hash_mem_multiplier and the parallel workers).
Not depth of execution plan. It relates to the number of nodes in the
plan which allocate work_mem or work_mem * hash_mem_multiplier.
There is some documentation in [1]:
"Note that a complex query might perform several sort and hash
operations at the same time"
The latest query is not complex at all and I don't see it doing 1000s of
operations at the same time. By "number of nodes" would you add up all
HashAggregate nodes under an Append node? Here is part of the EXPLAIN
ANALYZE output:
Limit (cost=0.01..28.00 rows=10 width=7) (actual time=43120.466..43292.246
rows=10 loops=1)
Output: test_runs_raw.workitem_n, test_runs_raw.test_executable_n,
(bool_or((test_runs_raw.test_resulttype_n = ANY ('{2,3}'::integer[]
Buffers: shared hit=96 read=883975
I/O Timings: shared read=16284.731
-> Append (cost=0.01..3416299633.71 rows=1220556171 width=7) (actual
time=42968.794..43139.855 rows=10 loops=1)
Buffers: shared hit=96 read=883975
I/O Timings: shared read=16284.731
-> HashAggregate (cost=0.01..0.02 rows=1 width=7) (actual
time=10.662..10.663 rows=0 loops=1)
Output: test_runs_raw.workitem_n,
test_runs_raw.test_executable_n, bool_or((test_runs_raw.test_resulttype_n = ANY
('{2,3}'::integer[])))
Group Key: test_runs_raw.workitem_n,
test_runs_raw.test_executable_n
Batches: 1 Memory Usage: 24kB
-> Seq Scan on public.test_runs_raw__part_max20k test_runs_raw
(cost=0.00..0.00 rows=1 width=8) (actual time=9.960..9.961 rows=0 loops=1)
Output: test_runs_raw.workitem_n,
test_runs_raw.test_executable_n, test_runs_raw.test_resulttype_n
-> HashAggregate (cost=0.01..0.02 rows=1 width=7) (actual
time=1.913..1.914 rows=0 loops=1)
Output: test_runs_raw_1.workitem_n,
test_runs_raw_1.test_executable_n, bool_or((test_runs_raw_1.test_resulttype_n =
ANY ('{2,3}'::integer[])))
Group Key: test_runs_raw_1.workitem_n,
test_runs_raw_1.test_executable_n
Batches: 1 Memory Usage: 24kB
-> Seq Scan on public.test_runs_raw__part_max40k
test_runs_raw_1 (cost=0.00..0.00 rows=1 width=8) (actual time=1.031..1.031 rows=0
loops=1)
Output: test_runs_raw_1.workitem_n,
test_runs_raw_1.test_executable_n, test_runs_raw_1.test_resulttype_n
[ 1000s of similar HashAggregate nodes ... ]
Settings: temp_buffers = '32MB', work_mem = '32MB', effective_io_concurrency =
'300', max_parallel_workers_per_gather = '0', enable_hashjoin = 'off',
enable_partitionwise_join = 'on', enable_partitionwise_aggregate = 'on',
random_page_cost = '1.1', effective_cache_size = '6GB', from_collapse_limit =
'24', join_collapse_limit = '24'
Planning:
Buffers: shared hit=377
Planning Time: 1503.800 ms
Execution Time: 56515.185 ms
(5382 rows)
Memory usage on each HashAggregate is logged as 24KB (many HashAggregates
are missing that info though), I guess the EXPLAIN output is missing some
important part of the allocations here since I'm seeing MBs of allocations
per node.
I can't help but see this as a bug. I see many issues:
* postgres is not reading from partitions in parallel, but one after the
other. It shouldn't need all this memory simultaneously.
* The memory is unnecessarily allocated early on, before any partitions
are actually aggregated. I know this because I/O is slow on this device
and the table sizes are huge, it's simply not possible that postgres
went through all partitions and blew up the memory. That would take
hours, but the OOM happens seconds after I start the query.
* The memory is not only allocated by the planner, but it's actually
accessed. Libc's malloc() has no problem allocating gigabytes more than
what I have available, growing the VSZ memory size
Re: Dropping column from big table
On 2024-Jul-11, Ron Johnson wrote: > Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time > (depending on whether or not you populate the column with a default value). Actually, ADD COLUMN with a default does not rewrite the entire table either, starting from pg11. "Major enhancements in PostgreSQL 11 include: [...] * Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default." https://www.postgresql.org/docs/11/release-11.html -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Re: Dropping column from big table
On Thu, Jul 11, 2024 at 3:41 AM sud wrote: > > > On Thu, 11 Jul, 2024, 12:46 pm Ron Johnson, > wrote: > >> On Wed, Jul 10, 2024 at 11:28 PM sud wrote: >> >>> >>> >>> >>> Thank you so much. When you said *"you can execute one of the forms of >>> ALTER TABLE that performs a rewrite* >>> *of the whole table."* Does it mean that post "alter table drop column" >>> the vacuum is going to run longer as it will try to clean up all the rows >>> and recreate the new rows? But then how can this be avoidable or made >>> better without impacting the system performance >>> >> >> "Impact" is a non-specific word. "How much impact" depends on how many >> autovacuum workers you've set it to use, and how many threads you set in >> vacuumdb. >> >> >>> and blocking others? >>> >> >> VACUUM never blocks. >> >> Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of >> time (depending on whether or not you populate the column with a default >> value). >> >> I'd detach all the partitions from the parent table, and then add the new >> column to the not-children in multiple threads, add the column to the >> parent and then reattach all of the children. That's the fastest method, >> though takes some time to set up. >> > > > Thank you so much. > > Dropping will take it's own time for post vacuum however as you rightly > said, it won't be blocking which should be fine. > > In regards to add column, Detaching all partitions then adding column to > the individual partition in multiple sessions and then reattaching looks to > be a really awesome idea to make it faster. > Do both the DROP and ADD in the same "set". Possibly in the same statement (which would be fastest if it works), and alternatively on the same command line. Examples: psql --host=foo.example.com somedb -c "ALTER TABLE bar_p85 DROP COLUMN splat, ADD COLUMN barf BIGINT;" psql --host=foo.example.com somedb -c "ALTER TABLE bar_p85 DROP splat;" -c ALTER TABLE bar_p85 ADD COLUMN barf BIGINT;" My syntax is probably wrong, but you get the idea. However one doubt, Will it create issue if there already exists foreign key > on this partition table or say it's the parent to other child > partition/nonpartition tables? > (Note that detached children have FK constraints.) It'll certainly create an "issue" if the column you're dropping is part of the foreign key. It'll also cause a problem if the table you're dropping from or adding to is the "target" of the FK, since the source can't check the being-altered table during the ALTER TABLE statement. Bottom line: you can optimize for: 1. minimized wall time by doing it in multiple transactions (which *might* bodge your application; we don't know it, so can't say for sure), OR 2. assured consistency (one transaction where you just ALTER the parent, and have it ripple down to the children); it'll take much longer, though. One other issue: *if* adding the new column requires a rewrite, "ALTER parent" *might* (but I've never tried it) temporarily use an extra 2TB of disk space in that single transaction. Doing the ALTERs child by child minimizes that, since each child's ALTER is it's own transaction. Whatever you do... test test test. >
Re: Dropping column from big table
On Thu, 2024-07-11 at 13:10 +0530, sud wrote: > Dropping will take it's own time for post vacuum however as you > rightly said, it won't be blocking which should be fine. I am not certain if you understood this correctly. Dropping a column is fast, but doesn't reclaim the space. VACUUM won't block anything, but won't reclaim the space. VACUUM (FULL) will block everything, but will also not reclaim the space. You'd need to use a form of ALTER TABLE that rewrites the table, as indicated in the documentation. However, such an operation will block all access to the table for a long time, and it will temporarily need much more space, because it has to hold both the old and the new copy of the table. Yours, Laurenz Albe
Re: Dropping column from big table
On Thu, 11 Jul, 2024, 12:46 pm Ron Johnson, wrote: > On Wed, Jul 10, 2024 at 11:28 PM sud wrote: > >> >> >> >> Thank you so much. When you said *"you can execute one of the forms of >> ALTER TABLE that performs a rewrite* >> *of the whole table."* Does it mean that post "alter table drop column" >> the vacuum is going to run longer as it will try to clean up all the rows >> and recreate the new rows? But then how can this be avoidable or made >> better without impacting the system performance >> > > "Impact" is a non-specific word. "How much impact" depends on how many > autovacuum workers you've set it to use, and how many threads you set in > vacuumdb. > > >> and blocking others? >> > > VACUUM never blocks. > > Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time > (depending on whether or not you populate the column with a default value). > > I'd detach all the partitions from the parent table, and then add the new > column to the not-children in multiple threads, add the column to the > parent and then reattach all of the children. That's the fastest method, > though takes some time to set up. > Thank you so much. Dropping will take it's own time for post vacuum however as you rightly said, it won't be blocking which should be fine. In regards to add column, Detaching all partitions then adding column to the individual partition in multiple sessions and then reattaching looks to be a really awesome idea to make it faster. However one doubt, Will it create issue if there already exists foreign key on this partition table or say it's the parent to other child partition/nonpartition tables?
Re: Dropping column from big table
On Wed, Jul 10, 2024 at 11:28 PM sud wrote: > > On Thu, Jul 11, 2024 at 2:52 AM Adrian Klaver > wrote: > >> >> https://www.postgresql.org/docs/current/sql-altertable.html >> >> "The DROP COLUMN form does not physically remove the column, but simply >> makes it invisible to SQL operations. Subsequent insert and update >> operations in the table will store a null value for the column. Thus, >> dropping a column is quick but it will not immediately reduce the >> on-disk size of your table, as the space occupied by the dropped column >> is not reclaimed. The space will be reclaimed over time as existing rows >> are updated. >> >> To force immediate reclamation of space occupied by a dropped column, >> you can execute one of the forms of ALTER TABLE that performs a rewrite >> of the whole table. This results in reconstructing each row with the >> dropped column replaced by a null value. >> " >> >> > Thank you so much. When you said *"you can execute one of the forms of > ALTER TABLE that performs a rewrite* > *of the whole table."* Does it mean that post "alter table drop column" > the vacuum is going to run longer as it will try to clean up all the rows > and recreate the new rows? But then how can this be avoidable or made > better without impacting the system performance > "Impact" is a non-specific word. "How much impact" depends on how many autovacuum workers you've set it to use, and how many threads you set in vacuumdb. > and blocking others? > VACUUM never blocks. Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of time (depending on whether or not you populate the column with a default value). I'd detach all the partitions from the parent table, and then add the new column to the not-children in multiple threads, add the column to the parent and then reattach all of the children. That's the fastest method, though takes some time to set up.
