Thread: vacuum full fills up disk
Hello,
I have a table with about 10 million tuples of which you can find the
definition below. I just did an update which filled in the ss_trav_tot
field for every tuple. Since the database will be read only (no updates),
I decided to reclaim disk space by running a vacuum full. However, the
process is slowly but surely filling up my disk. Does vacuum full make a
copy of the data, or why is this happening ?
I'm now at 88% of space used (up from about 75% when I started vacuum
full) and it is still rising. I don't know how long the process will still
take and whether it will fill up the entire disk.
So what should I do ? Wait and see ? Stop the vacuum full and do something
else ? If yes, what ?
Moritz
Here's the table definition:
Column | Type | Modifiers
-------------+----------------------+-----------
id | integer | not null
com_res | integer |
parente | smallint |
nb_p_men | smallint |
a_naiss | smallint |
m_naiss | smallint |
j_naiss | smallint |
nationalite | smallint |
sexe | smallint |
etat_civil | smallint |
lieu_naiss | integer |
dom_1_avt | integer |
ss_res | character varying(4) |
natio_nais | smallint |
q2a_cat_p | smallint |
q2b_rev_s | character varying(2) |
lieu_scol | integer |
ag_der_sco | smallint |
q7_dip_b | smallint |
q7b1_prim | smallint |
q7b2_seci | smallint |
q7b3_secs | smallint |
q7b4_pl_ex | smallint |
q7b5_sup1 | smallint |
q7b5_sup2 | smallint |
q7b5_sup3 | smallint |
q8a_dip_e | smallint |
q8b_d_etr | character varying(3) |
phd_sec | smallint |
phd_sup1 | smallint |
phd_sup2 | smallint |
phd_etr | smallint |
dep_dif_do | smallint |
lieu_dep | integer |
nb_traj_j | smallint |
nb_jtraj_s | smallint |
long_traj | smallint |
moy_transp | smallint |
h_dep_a | integer |
tps_a | smallint |
h_dep_r | integer |
tps_r | smallint |
com_trav | integer |
q21_loc_tr | smallint |
activ_nace | integer |
prof | integer |
prof2 | integer |
tps_plein | smallint |
nb_h_sem | smallint |
statut | smallint |
nb_p_dge | smallint |
type_rev | smallint |
type_serv | character varying(2) |
contact_cl | smallint |
type_mat | smallint |
statut_acc | smallint |
prof_isco | smallint |
nace_2 | smallint |
ss_trav | character varying(4) |
ss_res_tot | character varying(9) |
ss_trav_tot | character varying(9) |
Indexes: idx_activnace_91,
idx_anaiss_91,
idx_comres_91,
idx_comtrav_91,
idx_domavt_91,
idx_lieudep_91,
idx_lieuscol_91,
idx_lnaiss_91,
idx_nace2_91,
idx_nation_91,
idx_nation_naiss_91,
idx_phdetr_91,
idx_phdsec_91,
idx_phdsup1_91,
idx_phdsup2_91,
idx_prof2_91,
idx_prof_91,
idx_profisco_91,
idx_ssres_91,
idx_ssrestot_91,
idx_sstrav_91
Primary key: rec91_pkey
"Moritz Lennert" <mlennert@club.worldonline.be> writes:
> I have a table with about 10 million tuples of which you can find the
> definition below. I just did an update which filled in the ss_trav_tot
> field for every tuple. Since the database will be read only (no updates),
> I decided to reclaim disk space by running a vacuum full. However, the
> process is slowly but surely filling up my disk. Does vacuum full make a
> copy of the data, or why is this happening ?
It has to temporarily make duplicate index entries for each row it
moves.
You might consider dropping the indexes, vacuum full, rebuild indexes.
(You might also ask yourself if you really need all those indexes...)
regards, tom lane
If the table is going to be read-only you should consider running the
'cluster' command instead.
regards,
thiago lima.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, March 19, 2003 11:57 AM
To: mlennert@club.worldonline.be
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] vacuum full fills up disk
"Moritz Lennert" <mlennert@club.worldonline.be> writes:
> I have a table with about 10 million tuples of which you can find the
> definition below. I just did an update which filled in the ss_trav_tot
> field for every tuple. Since the database will be read only (no
> updates), I decided to reclaim disk space by running a vacuum full.
> However, the process is slowly but surely filling up my disk. Does
> vacuum full make a copy of the data, or why is this happening ?
It has to temporarily make duplicate index entries for each row it
moves.
You might consider dropping the indexes, vacuum full, rebuild indexes.
(You might also ask yourself if you really need all those indexes...)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
> "Moritz Lennert" <mlennert@club.worldonline.be> writes: >> I have a table with about 10 million tuples of which you can find the >> definition below. I just did an update which filled in the ss_trav_tot >> field for every tuple. Since the database will be read only (no >> updates), >> I decided to reclaim disk space by running a vacuum full. However, the >> process is slowly but surely filling up my disk. Does vacuum full make a >> copy of the data, or why is this happening ? > > It has to temporarily make duplicate index entries for each row it > moves. > > You might consider dropping the indexes, vacuum full, rebuild indexes. Thanks, I'll try that. > (You might also ask yourself if you really need all those indexes...) Well, this is data which is used for research purposes with different researchers extracting different information. I created the indexes on an intuition of which fields might be the ones information is extracted by. What other criteria might I use to determine which indexes I need ? Moritz
> "Moritz Lennert" <mlennert@club.worldonline.be> writes: >> I have a table with about 10 million tuples of which you can find the >> definition below. I just did an update which filled in the ss_trav_tot >> field for every tuple. Since the database will be read only (no >> updates), >> I decided to reclaim disk space by running a vacuum full. However, the >> process is slowly but surely filling up my disk. Does vacuum full make a >> copy of the data, or why is this happening ? > > It has to temporarily make duplicate index entries for each row it > moves. > > You might consider dropping the indexes, vacuum full, rebuild indexes. Thanks, I'll try that. > (You might also ask yourself if you really need all those indexes...) Well, this is data which is used for research purposes with different researchers extracting different information. I created the indexes on an intuition of which fields might be the ones information is extracted by. What other criteria might I use to determine which indexes I need ? Moritz
Moritz Lennert said: >> "Moritz Lennert" <mlennert@club.worldonline.be> writes: >>> I have a table with about 10 million tuples of which you can find the >>> definition below. I just did an update which filled in the ss_trav_tot >>> field for every tuple. Since the database will be read only (no >>> updates), >>> I decided to reclaim disk space by running a vacuum full. However, the >>> process is slowly but surely filling up my disk. Does vacuum full make >>> a >>> copy of the data, or why is this happening ? >> >> It has to temporarily make duplicate index entries for each row it >> moves. >> >> You might consider dropping the indexes, vacuum full, rebuild indexes. > > Thanks, I'll try that. > That did the trick: I'm down to 25% disk usage from 75% before ! Thanks for the hint, Moritz
Moritz Lennert said: >> "Moritz Lennert" <mlennert@club.worldonline.be> writes: >>> I have a table with about 10 million tuples of which you can find the >>> definition below. I just did an update which filled in the ss_trav_tot >>> field for every tuple. Since the database will be read only (no >>> updates), >>> I decided to reclaim disk space by running a vacuum full. However, the >>> process is slowly but surely filling up my disk. Does vacuum full make >>> a >>> copy of the data, or why is this happening ? >> >> It has to temporarily make duplicate index entries for each row it >> moves. >> >> You might consider dropping the indexes, vacuum full, rebuild indexes. > > Thanks, I'll try that. > That did the trick: I'm down to 25% disk usage from 75% before ! Thanks for the hint, Moritz