pg_attribute growing and growing and growing

-----Original Message-----
From: Brian Hirt

Hi,

I'm having a bit of trouble with the pg_attribute table growing larger
and larger and larger. Actually that's now the real problem, it's
the indexes that are the real problem. I run a site that get's a fair
amount of traffic and we use temporary table extensively for some more
complex queries (because by breaking down the queries into steps,
we can get
better performance than by letting postgres plan the query poorly) I
assume that creating a temporary table and then dropping it will cause
the pg_attribute table to grow because our pg_attribute grows by
about 15MB
per day and if it isn't vacuumed nightly the system slows down very
quickly. After "vacuum analyze pg_attribute", the pg_attribute table is
back to it's normal small size. However, the two indexes on
pg_attribute do
not shrink at all. The only way I've found to get around this is to
dump, drop, create, reload the database. I don't really want to trust
that to a script and I don't really like having the system down that much.

If you could stop postmaster,you could reacreate indexes
of pg_attribute as follows.

1) shutdown postmaster(using pg_ctl stop etc).
2) backup the index files of pg_attributes somewhere for safety.
3) invoke standalone postgres
postgres -P -O your_database_name
4) recreate indexes of pg_attribute
reindex table pg_attribute force;
5) exit standalone postgres
6) restart postmaster

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Brian Hirt <bhirt@mobygames.com> writes:

I run a site that get's a fair amount of traffic and we use temporary
table extensively for some more complex queries (because by breaking
down the queries into steps, we can get better performance than by
letting postgres plan the query poorly) I assume that creating a
temporary table and then dropping it will cause the pg_attribute table
to grow because our pg_attribute grows by about 15MB per day and if it
isn't vacuumed nightly the system slows down very quickly. After
"vacuum analyze pg_attribute", the pg_attribute table is back to it's
normal small size. However, the two indexes on pg_attribute do not
shrink at all.

Indexes in general are not shrunk by vacuum. The only clean solution
I see for this is to convert vacuum to do the "drop/rebuild index"
business internally --- but AFAICS we can't do that safely without some
sort of file versioning solution. See past threads in pghackers.

Possibly a better short-term attack is to eliminate the need for so
many temp tables. What's your gripe about bad planning, exactly?

Another possibility, which just screams HACK but might fix your problem,
is to swap the order of the columns in the two indexes on pg_attribute:

foo=# \d pg_attribute_relid_attnam_index
Index "pg_attribute_relid_attnam_index"
Attribute | Type
-----------+------
attrelid | oid
attname | name
unique btree

foo=# \d pg_attribute_relid_attnum_index
Index "pg_attribute_relid_attnum_index"
Attribute | Type
-----------+----------
attrelid | oid
attnum | smallint
unique btree

Since table OIDs keep increasing, this formulation ensures that new
entries will always sort to the end of the index, and so space freed
internally in the indexes can never get re-used. Swapping the column
order may eliminate that problem --- but I'm not sure what if any
speed penalty would be incurred. Thoughts anyone?

regards, tom lane

foo=# \d pg_attribute_relid_attnam_index
Index "pg_attribute_relid_attnam_index"
Attribute | Type
-----------+------
attrelid | oid
attname | name
unique btree

foo=# \d pg_attribute_relid_attnum_index
Index "pg_attribute_relid_attnum_index"
Attribute | Type
-----------+----------
attrelid | oid
attnum | smallint
unique btree

Since table OIDs keep increasing, this formulation ensures that new
entries will always sort to the end of the index, and so space freed
internally in the indexes can never get re-used. Swapping the column
order may eliminate that problem --- but I'm not sure what if any
speed penalty would be incurred. Thoughts anyone?

Isn't pg_attribute often accessed with a "where oid=xxx" restriction
to get all cols for a given table ?

Andreas

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

Since table OIDs keep increasing, this formulation ensures that new
entries will always sort to the end of the index, and so space freed
internally in the indexes can never get re-used. Swapping the column
order may eliminate that problem --- but I'm not sure what if any
speed penalty would be incurred. Thoughts anyone?

Isn't pg_attribute often accessed with a "where oid=xxx" restriction
to get all cols for a given table ?

Hmm, good point. I don't think the system itself does that --- AFAIR
it just looks up specific rows by relid+name or relid+num --- but making
this change would make the indexes useless for applications that make
that kind of query.

Oh well, back to the drawing board...

regards, tom lane