Thread: index file's growing big
Hi, I wrote a web+commandline based package with PostgreSQL database backend. My problem is that as time goes by the index file grows to a huge size [80Mb]. If I dump and then reload it the database in question I'll get the very same data with a quite small index file. So, there's no inconsistency or corrupt database, but it's still annoying that I must handle manually. What I think is tha the command-line app I wrote for populating the database uses some wrong strategy, I mean there's no erroneous SQL phares, just may be it uses wrong tactics that's why it grows the index file to a quite big size. Any ideas? > Zoltan Sebestyen > NETvisor Kft > 1111 Muegyetem rkpt.9. Fsz.8. > Budapest, Hungary. > Fax: (+36-1) 463-4396 > Tel: (+36-1) 463-4392, 463-4393 > > "Programming is an art, not a science, > and not all programmers are Picassos." > > <<Sebestyén Zoltán.vcf>>
Attachment
Zoltan - It sounds like this is a very active table in you app, with lots of inserts and deletes, right? Basically what's happening is that deletes from the underlying table don't remove entries from the index file, just marks them unused. And inserts add a new record at the end. This monotonic growth of index files is a known problem. I assume you're using v. 6.5.3 or earlier: I believe the current 7.0 beta will reuse index file entries, rather than append them, although it still doesn't 'vacuum' them of unused entries, if I understand correctly. The dump->reload solves the problem for you by rebuilding the index. So, rather than dumping the whole table, just drop and rebuild the index in question. This can still take a long time if it's a big table, but my guess is that it'll work for you. Ross On Mon, Mar 06, 2000 at 12:23:29PM +0100, zoltan.sebestyen@netvisor.hu wrote: > Hi, > > I wrote a web+commandline based package with PostgreSQL database backend. > My problem is that as time goes by the index file grows to a huge size > [80Mb]. If I dump and then reload it the database in question I'll get the > very same data with a quite small index file. So, there's no inconsistency > or corrupt database, but it's still annoying that I must handle manually. > What I think is tha the command-line app I wrote for populating the > database uses some wrong strategy, I mean there's no erroneous SQL phares, > just may be it uses wrong tactics that's why it grows the index file to a > quite big size. > > Any ideas? > > > Zoltan Sebestyen > > NETvisor Kft > > 1111 Muegyetem rkpt.9. Fsz.8. > > Budapest, Hungary. > > Fax: (+36-1) 463-4396 > > Tel: (+36-1) 463-4392, 463-4393 > > > > "Programming is an art, not a science, > > and not all programmers are Picassos." > > > > > <<Sebestyén Zoltán.vcf>>
Uh, why not just "vacuum" the table? You want to do this periodically anyway, as Postgres does not re-use TABLE entries either, and the table file itself will grow without bound if you don't do this once in a while. Further, if the tables are large you want to use "vacuum analyze" to reorder the indices in a fashion that makes queries more efficient. -- -- Karl Denninger (karl@denninger.net) Web: http://childrens-justice.org Isn't it time we started putting KIDS first? See the above URL for a plan to do exactly that! On Mon, Mar 06, 2000 at 10:24:33AM -0600, Ross J. Reedstrom wrote: > Zoltan - > It sounds like this is a very active table in you app, with lots of > inserts and deletes, right? Basically what's happening is that deletes > from the underlying table don't remove entries from the index file, > just marks them unused. And inserts add a new record at the end. > > This monotonic growth of index files is a known problem. I assume you're > using v. 6.5.3 or earlier: I believe the current 7.0 beta will reuse > index file entries, rather than append them, although it still doesn't > 'vacuum' them of unused entries, if I understand correctly. > > The dump->reload solves the problem for you by rebuilding the index. So, > rather than dumping the whole table, just drop and rebuild the index in > question. This can still take a long time if it's a big table, but my > guess is that it'll work for you. > > Ross > > > On Mon, Mar 06, 2000 at 12:23:29PM +0100, zoltan.sebestyen@netvisor.hu wrote: > > Hi, > > > > I wrote a web+commandline based package with PostgreSQL database backend. > > My problem is that as time goes by the index file grows to a huge size > > [80Mb]. If I dump and then reload it the database in question I'll get the > > very same data with a quite small index file. So, there's no inconsistency > > or corrupt database, but it's still annoying that I must handle manually. > > What I think is tha the command-line app I wrote for populating the > > database uses some wrong strategy, I mean there's no erroneous SQL phares, > > just may be it uses wrong tactics that's why it grows the index file to a > > quite big size. > > > > Any ideas? > > > > > Zoltan Sebestyen > > > NETvisor Kft > > > 1111 Muegyetem rkpt.9. Fsz.8. > > > Budapest, Hungary. > > > Fax: (+36-1) 463-4396 > > > Tel: (+36-1) 463-4392, 463-4393 > > > > > > "Programming is an art, not a science, > > > and not all programmers are Picassos." > > > > > > > > <<Sebestyén Zoltán.vcf>> > > > > ************ >
On Mon, Mar 06, 2000 at 10:46:19AM -0600, Karl Denninger wrote: > Uh, why not just "vacuum" the table? > > You want to do this periodically anyway, as Postgres does not re-use TABLE > entries either, and the table file itself will grow without bound if you > don't do this once in a while. > > Further, if the tables are large you want to use "vacuum analyze" to reorder > the indices in a fashion that makes queries more efficient. > Well, with a recent version, that will help keep the index from growing as fast, but it will still grow. That's the point of my garbled description. However, if Zoltan is _not_ vacuuming regularly, that's a problem as well. If he wasn't, though, I'd have expected him to complain about the size of the table file, not the index file. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Karl Denninger <karl@Denninger.Net> writes: > Further, if the tables are large you want to use "vacuum analyze" to reorder > the indices in a fashion that makes queries more efficient. Just a side comment here: VACUUM ANALYZE has nothing whatever to do with "reordering" indexes. What it does that a plain VACUUM does not do is gather statistics about the table contents (minimum, maximum, and most common value of each column, at present). These stats are then used by the optimizer to make better planning choices. At least we'd like to think they're better, Lord willin' an' the creek don't rise... regards, tom lane