Question on full vacuum clearing waste space
Hi I am testing full vacuum with pg 10.10 on AWS RDS. I noticed for some tables, the number of waste bytes stays at a few MB after I run full vacuum. I double-checked that there are no long running transactions, no orphaned prepared transactions and no abandoned replication slots. Here is output from full vacuum for one of the tables: VACUUM(FULL, ANALYZE, VERBOSE) app_events_users vacuuming "app_events_users" "app_events_users": found 0 removable, 1198881 nonremovable row versions in 13369 pages analyzing "licensing.app_events_users" "app_events_users": scanned 13369 of 13369 pages, containing 1198881 live rows and 0 dead rows; 3 rows in sample, 1198881 estimated total rows What else can prevent full vacuum from reclaiming all waste space ? Thank you
Re: Question on full vacuum clearing waste space
Thank you for the quick response. I ran the script from https://wiki.postgresql.org/wiki/Show_database_bloat, which shows "app_event_users" table has 3751936 as wastedbytes. On Sun, Jun 7, 2020 at 12:32 AM Mohamed Wael Khobalatte < mkhobala...@grubhub.com> wrote: > > On Sat, Jun 6, 2020 at 11:24 PM Wenjun Che wrote: > >> Hi >> >> I am testing full vacuum with pg 10.10 on AWS RDS. I noticed for some >> tables, the number of waste bytes stays at a few MB after I run full >> vacuum. I double-checked that there are no long running transactions, no >> orphaned prepared transactions and no abandoned replication slots. >> >> Here is output from full vacuum for one of the tables: >> >> VACUUM(FULL, ANALYZE, VERBOSE) app_events_users >> vacuuming "app_events_users" >> "app_events_users": found 0 removable, 1198881 nonremovable row versions >> in 13369 pages >> analyzing "licensing.app_events_users" >> "app_events_users": scanned 13369 of 13369 pages, containing 1198881 live >> rows and 0 dead rows; 3 rows in sample, 1198881 estimated total rows >> >> What else can prevent full vacuum from reclaiming all waste space ? >> >> Thank you >> > > What "waste query" are you running? Those tend to be estimates only. > Vacuum Full clearly did its job from that log you shared. > -- Wenjun Che VP of Engineering | OpenFin wen...@openfin.co *Move Fast. Break Nothing.* www.openfin.co | @openfintech
Change work_mem for one user
Hello We are running pg 10.10 on AWS RDS. I want to increase work_mem for one user with following command: ALTER ROLE test_user SET work_mem TO '50 MB'; After I run the command and log in as test_user, "show work_mem" still shows the default 4MB. Thank you -- Wenjun Che VP of Engineering | OpenFin wen...@openfin.co *Move Fast. Break Nothing.* www.openfin.co | @openfintech
Re: Change work_mem for one user
Hello Tom Thank you very much for the quick response. I just realized the issue is caused by the tool, DataGrid, I am using. In DataGrid, when I close a tab, it does not actually disconnect from the database so it does not re-login when I open a new tab. Again. thank you for the help. On Tue, Jan 26, 2021 at 9:05 PM Tom Lane wrote: > Wenjun Che writes: > > We are running pg 10.10 on AWS RDS. I want to increase work_mem for one > > user with following command: > > ALTER ROLE test_user SET work_mem TO '50 MB'; > > After I run the command and log in as test_user, "show work_mem" still > > shows the default 4MB. > > Hmm, works for me: > > regression=# create user test_user; > CREATE ROLE > regression=# ALTER ROLE test_user SET work_mem TO '50 MB'; > ALTER ROLE > regression=# show work_mem; > work_mem > -- > 4MB > (1 row) > > regression=# \c - test_user > You are now connected to database "regression" as user "test_user". > regression=> show work_mem; > work_mem > -- > 50MB > (1 row) > > Perhaps you also have a setting at the per-database level, or > per-user-and-database level? The latter would definitely override > a per-user setting; I don't recall offhand whether per-database > overrides per-user. Look into pg_db_role_setting to see what > there is. > > regards, tom lane > -- Wenjun Che VP of Engineering | OpenFin wen...@openfin.co *Move Fast. Break Nothing.* www.openfin.co | @openfintech
