Re: Almost happy ending (from "Data files became huge with no apparent reason" thread)

Bruce Momjian wrote:

> dfumagalli(at)tin(dot)it wrote:
>
>>Hi all,
>>
>>Al last I recieved a new disk and was able to VACUUM ANALYZE.
>>

[Snip]

>>My first try was to go stand alone and to reindex force the entire database,
>>but the rebuilt indexes still took the same space (I thought the reindex
>>would rebuild them from scratch, freeing unused index pages, but perhaps
>>I'm wrong, at least for PostgreSQL 7.1.1).
>>
>
> That is strange. How did you reindex, and did you reindex the toast
> tables too? 7.3, due out in a few months, has a reindex script that
> reindexes everything.

Step by step procedure:
1) I stopped the postmaster and made sure no process was still on (ps aux)
2) As postgres user I started postgres -O -P -D path etc.
3) A prompt appeared. Here I typed reindex proj_store force (I'm trying
to remember it since I'm not in the company the server is in).
4) Messages appeared stating indexes were being rebuilt (no errors shown).
5) I hit CTRL-D. Postgres exited.
6) I restarted the postmaster.

With regards to the toast tables, I don't know how to reindex / manage
them. But I don't think they are the culprit.

Just to be sure I append the output of:

select relname, relfilenode as node, reltoastrelid as relid,
reltoastidxid as idxid, relkind as k, relpages as pag from pg_class
order by relname

relname | node | relid | idxid | k | pag
---------------------------------+---------+--------+--------+---+------
audio_connectors | 258312 | 0 | 0 | r | 0
audio_connectors_pkey | 258325 | 0 | 0 | i | 1
audio_connectors_seq | 258293 | 0 | 0 | S | 10
cats_and_subcats | 258166 | 0 | 0 | r | 1
cats_and_subcats_pkey | 258182 | 0 | 0 | i | 2
cats_and_subcats_seq | 258147 | 0 | 0 | S | 10
companies | 242645 | 0 | 0 | r | 31
companies_millelumen | 242837 | 0 | 0 | r | 0
companies_millelumen_pkey | 242904 | 0 | 0 | i | 1
companies_millelumen_seq | 242818 | 0 | 0 | S | 10
companies_pkey | 242772 | 0 | 0 | i | 7
companies_seq | 242626 | 0 | 0 | S | 10
computer_vid_conns | 258347 | 0 | 0 | r | 0
computer_vid_conns_pkey | 258360 | 0 | 0 | i | 1
computer_vid_conns_seq | 258328 | 0 | 0 | S | 10
dep_names | 258382 | 0 | 0 | r | 1
dep_names_pkey | 258395 | 0 | 0 | i | 12
dep_names_seq | 258363 | 0 | 0 | S | 10
det_prod_aud_conns | 258667 | 0 | 0 | r | 0
det_prod_aud_conns_pkey | 258681 | 0 | 0 | i | 1
det_prod_aud_conns_seq | 258648 | 0 | 0 | S | 10
det_prod_comp_vid_conns | 258703 | 0 | 0 | r | 0
det_prod_comp_vid_conns_pkey | 258717 | 0 | 0 | i | 1
det_prod_comp_vid_conns_seq | 258684 | 0 | 0 | S | 10
det_prod_dep_consumpt | 258739 | 0 | 0 | r | 21
det_prod_dep_consumpt_pkey | 258764 | 0 | 0 | i | 3286
det_prod_dep_consumpt_seq | 258720 | 0 | 0 | S | 10
det_prod_vid_conns | 258631 | 0 | 0 | r | 0
det_prod_vid_conns_pkey | 258645 | 0 | 0 | i | 1
det_prod_vid_conns_seq | 258612 | 0 | 0 | S | 10
det_prod_vid_res | 258562 | 0 | 0 | r | 0
det_prod_vid_res_pkey | 258575 | 0 | 0 | i | 1
det_prod_vid_res_seq | 258543 | 0 | 0 | S | 10
det_prod_vid_stds | 258597 | 0 | 0 | r | 0
det_prod_vid_stds_pkey | 258609 | 0 | 0 | i | 1
det_prod_vid_stds_seq | 258578 | 0 | 0 | S | 10
det_turnover | 242794 | 0 | 0 | r | 6
det_turnover_pkey | 242815 | 0 | 0 | i | 2
det_turnover_seq | 242775 | 0 | 0 | S | 10
idx_cats_and_subcats_cs | 1094029 | 0 | 0 | i | 2
idx_cats_and_subcats_v | 1094032 | 0 | 0 | i | 2
idx_companies_c0 | 1094071 | 0 | 0 | i | 4
idx_companies_c1 | 1094080 | 0 | 0 | i | 5
idx_companies_c2 | 1094086 | 0 | 0 | i | 4
idx_companies_millelumenc | 1094101 | 0 | 0 | i | 1
idx_companies_n | 1094077 | 0 | 0 | i | 9
idx_companies_p | 1094083 | 0 | 0 | i | 4
idx_companies_u | 1094074 | 0 | 0 | i | 5
idx_companies_v0 | 1094089 | 0 | 0 | i | 5
idx_companies_v1 | 1094092 | 0 | 0 | i | 4
idx_dep_named | 1094035 | 0 | 0 | i | 2
idx_det_prod_aud_connsp | 1094062 | 0 | 0 | i | 1
idx_det_prod_comp_vid_connsp | 1094065 | 0 | 0 | i | 1
idx_det_prod_dep_consumptdp | 1094068 | 0 | 0 | i | 10
idx_det_prod_vid_connsp | 1094059 | 0 | 0 | i | 1
idx_det_prod_vid_resp | 1094053 | 0 | 0 | i | 1
idx_det_prod_vid_stdsp | 1094056 | 0 | 0 | i | 1
idx_det_turnover_c | 1094095 | 0 | 0 | i | 4
idx_det_turnover_t | 1094098 | 0 | 0 | i | 4
idx_products_b | 1094044 | 0 | 0 | i | 19
idx_products_b2 | 1094047 | 0 | 0 | i | 9
idx_products_csc | 1094041 | 0 | 0 | i | 15
idx_products_v | 1094050 | 0 | 0 | i | 9
idx_video_resolutions_hv | 1094038 | 0 | 0 | i | 2
pg_aggregate | 16960 | 0 | 0 | r | 1
pg_aggregate_name_type_index | 1092622 | 0 | 0 | i | 2
pg_am | 16653 | 0 | 0 | r | 1
pg_am_name_index | 1092616 | 0 | 0 | i | 2
pg_amop | 16685 | 0 | 0 | r | 2
pg_amop_opid_index | 1092617 | 0 | 0 | i | 2
pg_amop_strategy_index | 1092618 | 0 | 0 | i | 2
pg_amproc | 16867 | 0 | 0 | r | 1
pg_attrdef | 1215 | 17201 | 17213 | r | 3
pg_attrdef_adrelid_index | 1092605 | 0 | 0 | i | 2
pg_attribute | 1249 | 0 | 0 | r | 17
pg_attribute_relid_attnam_index | 17112 | 0 | 0 | i | 11
pg_attribute_relid_attnum_index | 17115 | 0 | 0 | i | 6
pg_class | 1259 | 0 | 0 | r | 3
pg_class_oid_index | 17118 | 0 | 0 | i | 2
pg_class_relname_index | 17121 | 0 | 0 | i | 4
pg_database | 1262 | 0 | 0 | r | 1
pg_description | 17086 | 17216 | 17228 | r | 10
pg_description_objoid_index | 1092623 | 0 | 0 | i | 5
pg_group | 1261 | 0 | 0 | r | 0
pg_group_name_index | 17127 | 0 | 0 | i | 1
pg_group_sysid_index | 17130 | 0 | 0 | i | 1
pg_index | 16579 | 0 | 0 | r | 2
pg_index_indexrelid_index | 1092611 | 0 | 0 | i | 2
pg_index_indrelid_index | 1092610 | 0 | 0 | i | 2
pg_indexes | 17350 | 0 | 0 | v | 10
pg_inheritproc | 17045 | 0 | 0 | r | 0
pg_inherits | 16567 | 0 | 0 | r | 0
pg_inherits_relid_seqno_index | 1092609 | 0 | 0 | i | 1
pg_ipl | 17033 | 0 | 0 | r | 0
pg_language | 16934 | 0 | 0 | r | 1
pg_language_name_index | 1092619 | 0 | 0 | i | 2
pg_language_oid_index | 1092620 | 0 | 0 | i | 2
pg_largeobject | 16948 | 0 | 0 | r | 0
pg_largeobject_loid_pn_index | 1092621 | 0 | 0 | i | 1
pg_listener | 17074 | 0 | 0 | r | 0
pg_listener_pid_relname_index | 1092624 | 0 | 0 | i | 1
pg_log | 1269 | 0 | 0 | s | 0
pg_opclass | 16642 | 0 | 0 | r | 1
pg_opclass_deftype_index | 1092614 | 0 | 0 | i | 2
pg_opclass_name_index | 1092615 | 0 | 0 | i | 2
pg_operator | 16617 | 0 | 0 | r | 9
pg_operator_oid_index | 1092612 | 0 | 0 | i | 4
pg_operator_oprname_l_r_k_index | 1092613 | 0 | 0 | i | 7
pg_proc | 1255 | 17231 | 17243 | r | 28
pg_proc_oid_index | 17166 | 0 | 0 | i | 5
pg_proc_proname_narg_type_index | 17169 | 0 | 0 | i | 20

As you may see, *_pkey primary keys are BIG. They are the only ones I
didn't dare to drop and re-create.

Ex.

products_pkey | 258540 | 0 | 0 | i | 1653

where the entire table takes 66 pages, or, worse

det_prod_dep_consumpt_pkey | 258764 | 0 | 0 | i | 3286

where the entire table takes 21 pages.

>
>>So I dropped all non-primary key indexes (they were auto-created as *_pkey)
>>and recreated them. I did not drop primary keys because I fear it could
>>break something about constraints. Now the database is 47MB and I'm pretty
>>happy with it. I only I could safely drop and recreate the primary keys
>>I'm sure the database would return to its original 26 MB.
>>
>>Now, three simple questions to the pros on this mailing list:
>>- Is there a cleaner way of recreating packed indexes?
>>
>
> Yes, 7.3 will have a reindexdb script.
>
>
>>- May I drop and recreate primary indexes without breaking rules, triggers,
>>constraints and sequences?
>>
>
> Not easily, no.
>

And "not easily"? I may make backups, go standalone, and do whatever
evil you may think ;)
BTW I have the scripts to re-create indexes or constraints and know how
to hack them, since I'm the programmer in charge for all (sql, programs,
db administration) for that company.

>
>>- We are evaluating PostgreSQL for a mission critical application, currently
>>managed by an Informix IDS 2000 server on a Sun Ultra 420 with Solaris 8
>>with raid disks and SAN for storage. Currently there are about 1000 concurrent
>>users (each with one to three ODBC sessions), with a CPU load of about 15%.
>>There are 30 databases: some have only some thousand records, while others
>>have a thousand tables with some of them totalling 10M+ records, with need
>>of sub-selects, joins and transactions (no constraints for now). Is PostgreSQL
>>able to manage *RELIABLY* and 24/7/365 this amount of data? Does the newest
>>
>
> You need 7.2.2, which was designed for 24 hour operation.
>
>
>>version come with some handy tool like the Informix Onstat (it reports users
>>sessions and executed queries given a connection id)? Does it use all the
>>
>
> There is pgmonitor on gborg.postgresql.org that displays active sessions
> and queries.
>
>
>>available processors or only one? How may we work around the periodic need
>>of a VACUUM on such big tables (and they are vastly modified every day),
>>without disrupting the server availability?
>>
>
> In 7.2.2, VACUUM doesn't lock tables so you can vacuum more easily.
>
>

Now that Informix (the current, very stable and working solution that
exceedingly meet the above specifications) is somehow "gone", we are
uncertain between chosing IBM's DB2 (but we don't need something so
sophisticated for our huge but simple stuff) and PostgreSQL.

I spent some time reading the list archives and a commonly reported
issue is postgres unable to reclaim unused space. This is of the most
concern to us, since we simply cannot afford the database being
unavailable (for example because the DB grew until filled the disks).
Further more, in this other company there are some tech guys, but they
are only expert in AS400 and general Solaris and cannot cope with some
"postgres-typical" emergencies, like the need to perform full VACUUMs
or, worse, dump / reload (and due to the sheer amount of data coming
from everywhere in the world there are many rows containing random
binary garbage, that I fear make reloads problematic at best).
There IS a db administrator that could care about some problems, but she
is in love with Informix and hates everything other (expecially open
source projects!) and won't help.

It would be acceptable, instead, to have a database that is never
optimized at 100% (i.e. it has "gaps" because of non full vacuums that
add for about another almost FIXED 30%), but that NEVER, NEVER grows out
of control. We may prepare a db maintenance plan that includes a full
vacuum every 6 months.

Thanks for your (and the other list lurkers) prompt responses, I for one
never had the same feedback for (highly) paid support.

My best regards,
Dario Fumagalli.