Thread: questions on toast tables
I have a toast table that is referenced by a single user table define below: CREATE TABLE casedocument ( pid varchar(12) NOT NULL, createdt timestamp NOT NULL, descr varchar(40), docformat varchar(10) NOT NULL, version int4 NOT NULL, casepid varchar(12) NOT NULL, createuserpid varchar(12) NOT NULL, typepid varchar(12) NOT NULL, sent bool DEFAULT false, active bool DEFAULT true, auxpid varchar(12), CONSTRAINT copycasedoc_pkey PRIMARY KEY (pid) ) WITH OIDS; There are 40k rows in the user table The toast table contains 5781417 pages Something does not seem right here. 1) should the user table even be relying on a toast table 2) the 40k rows and data sizes do not seem to equal the number of pages in the related toast table. 3) I know that once upon a time the table had a bytea column, but that was dropped. Do I need to do a full vacuum on that table to get rid of the related toast data? thanks -- Warren Little Chief Technology Officer Meridias Capital Inc 1018 W Atherton Dr Salt Lake City, UT 84123 ph: 866.369.7763
Warren Little <warren.little@meridiascapital.com> writes:
> 3) I know that once upon a time the table had a bytea column, but that
> was dropped. Do I need to do a full vacuum on that table to get rid of
> the related toast data?
Yup. (I take it it was a pretty darn bulky bytea column, too)
regards, tom lane
Tom, I'll run the vacuum over the weekend and see how that goes. And, yes, large pdf documents (4-24mb a piece). thanks On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote: > Warren Little <warren.little@meridiascapital.com> writes: > > 3) I know that once upon a time the table had a bytea column, but that > > was dropped. Do I need to do a full vacuum on that table to get rid of > > the related toast data? > > Yup. (I take it it was a pretty darn bulky bytea column, too) > > regards, tom lane -- Warren Little Chief Technology Officer Meridias Capital Inc 1018 W Atherton Dr Salt Lake City, UT 84123 ph: 866.369.7763
I am now a little confused. I ran the following with all but localhost connections disabled vacuumdb --full --verbose -t casedocument -d tigris > vacfull.log 2>&1 which produced the following output: INFO: vacuuming "public.casedocument" INFO: "casedocument": found 0 removable, 39663 nonremovable row versions in 852 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 138 to 1953 bytes long. There were 1855 unused item pointers. Total free space (including removable row versions) is 65168 bytes. 0 pages are or will become empty, including 0 at the end of the table. 90 pages containing 14644 free bytes are potential move destinations. CPU 0.02s/0.00u sec elapsed 0.24 sec. INFO: index "copycasedoc_pkey" now contains 39663 row versions in 387 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.56 sec. INFO: "casedocument": moved 0 row versions, truncated 852 to 852 pages DETAIL: CPU 0.00s/0.01u sec elapsed 0.03 sec. INFO: vacuuming "pg_toast.pg_toast_24216115" INFO: "pg_toast_24216115": found 0 removable, 23125332 nonremovable row versions in 5781284 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 45 to 2030 bytes long. There were 1 unused item pointers. Total free space (including removable row versions) is 185874460 bytes. 0 pages are or will become empty, including 0 at the end of the table. 24019 pages containing 24668944 free bytes are potential move destinations. CPU 217.21s/16.97u sec elapsed 1745.04 sec. INFO: index "pg_toast_24216115_index" now contains 23125332 row versions in 94046 pages DETAIL: 0 index row versions were removed. 1427 index pages have been deleted, 1427 are currently reusable. CPU 4.60s/1.05u sec elapsed 49.34 sec. INFO: "pg_toast_24216115": moved 0 row versions, truncated 5781284 to 5781284 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Not sure if I'm reading the output correctly, but it appears no rows in the toast table were removed. What else could be holding onto the data in "pg_toast_24216115" ??? On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote: > Warren Little <warren.little@meridiascapital.com> writes: > > 3) I know that once upon a time the table had a bytea column, but that > > was dropped. Do I need to do a full vacuum on that table to get rid of > > the related toast data? > > Yup. (I take it it was a pretty darn bulky bytea column, too) > > regards, tom lane -- Warren Little Chief Technology Office Meridias Capital Inc 1018 W Atherton Dr SLC, UT 84123 ph 866.369.7763
selecting pg_attributes for the casedocument table shows there is column with the following attributes 24216115|"........pg.dropped.2........"|0|0|-1|2|0|-1|-1|f|"x"|"i"|f|f| t|t| I also noticed a few other tables have a similar "dropped" column. Could this be the reference to the toast table that is preventing the vacuum from deleting the toast data? And what purges "dropped" columns if not a full vacuum. On Sat, 2006-04-29 at 06:52 -0600, Warren Little wrote: > I am now a little confused. > > I ran the following with all but localhost connections disabled > > vacuumdb --full --verbose -t casedocument -d tigris > vacfull.log 2>&1 > > which produced the following output: > > INFO: vacuuming "public.casedocument" > INFO: "casedocument": found 0 removable, 39663 nonremovable row > versions in 852 pages > DETAIL: 0 dead row versions cannot be removed yet. > Nonremovable row versions range from 138 to 1953 bytes long. > There were 1855 unused item pointers. > Total free space (including removable row versions) is 65168 bytes. > 0 pages are or will become empty, including 0 at the end of the table. > 90 pages containing 14644 free bytes are potential move destinations. > CPU 0.02s/0.00u sec elapsed 0.24 sec. > INFO: index "copycasedoc_pkey" now contains 39663 row versions in 387 > pages > DETAIL: 0 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 0.01s/0.00u sec elapsed 0.56 sec. > INFO: "casedocument": moved 0 row versions, truncated 852 to 852 pages > DETAIL: CPU 0.00s/0.01u sec elapsed 0.03 sec. > INFO: vacuuming "pg_toast.pg_toast_24216115" > INFO: "pg_toast_24216115": found 0 removable, 23125332 nonremovable row > versions in 5781284 pages > DETAIL: 0 dead row versions cannot be removed yet. > Nonremovable row versions range from 45 to 2030 bytes long. > There were 1 unused item pointers. > Total free space (including removable row versions) is 185874460 bytes. > 0 pages are or will become empty, including 0 at the end of the table. > 24019 pages containing 24668944 free bytes are potential move > destinations. > CPU 217.21s/16.97u sec elapsed 1745.04 sec. > INFO: index "pg_toast_24216115_index" now contains 23125332 row > versions in 94046 pages > DETAIL: 0 index row versions were removed. > 1427 index pages have been deleted, 1427 are currently reusable. > CPU 4.60s/1.05u sec elapsed 49.34 sec. > INFO: "pg_toast_24216115": moved 0 row versions, truncated 5781284 to > 5781284 pages > DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. > VACUUM > > Not sure if I'm reading the output correctly, but it appears no rows in > the toast table were removed. > What else could be holding onto the data in "pg_toast_24216115" ??? > > > > > > > > On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote: > > Warren Little <warren.little@meridiascapital.com> writes: > > > 3) I know that once upon a time the table had a bytea column, but that > > > was dropped. Do I need to do a full vacuum on that table to get rid of > > > the related toast data? > > > > Yup. (I take it it was a pretty darn bulky bytea column, too) > > > > regards, tom lane -- Warren Little Chief Technology Officer Meridias Capital Inc 1018 W Atherton Dr SLC, UT 84123 ph 866.369.7763
Warren Little <warren.little@meridiascapital.com> writes:
> Could this be the reference to the toast table that is preventing the
> vacuum from deleting the toast data? And what purges "dropped" columns
> if not a full vacuum.
Actually, the way that toast references work is that they'll go away at
the next update of the row containing the reference. The reason you've
still got a pile of unremovable toast data is evidently that a lot of
the parent table's rows have remained untouched since the wide bytea
column existed. (We choose not to do this housekeeping immediately
during DROP COLUMN, but to defer it until the next row update.)
One way to clean up the junk would be to do a trivial full-table update
("UPDATE foo SET f1 = f1") and then VACUUM FULL, but there are other
ways that are more efficient. If you're using a PG version released
within the last year, CLUSTER will do the job nicely.
regards, tom lane
Tom,
thanks much for your help, the cluster command did the trick.
fyi running 8.1.2
On Sat, 2006-04-29 at 14:48 -0400, Tom Lane wrote:
> Warren Little <warren.little@meridiascapital.com> writes:
> > Could this be the reference to the toast table that is preventing the
> > vacuum from deleting the toast data? And what purges "dropped" columns
> > if not a full vacuum.
>
> Actually, the way that toast references work is that they'll go away at
> the next update of the row containing the reference. The reason you've
> still got a pile of unremovable toast data is evidently that a lot of
> the parent table's rows have remained untouched since the wide bytea
> column existed. (We choose not to do this housekeeping immediately
> during DROP COLUMN, but to defer it until the next row update.)
>
> One way to clean up the junk would be to do a trivial full-table update
> ("UPDATE foo SET f1 = f1") and then VACUUM FULL, but there are other
> ways that are more efficient. If you're using a PG version released
> within the last year, CLUSTER will do the job nicely.
>
> regards, tom lane
--
Warren Little
Chief Technology Officer
Meridias Capital Inc
1018 W Atherton Dr
SLC, UT 84123
ph 866.369.7763