On 5/30/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
>
>
>
> Thanks for response.
>
> I know the VACUUM is recovers the space left behind by deleting data from db
> but this
>
> is very expensive operator and it holds the locks for this database which
> will be the big impact for other operations like inserting/updating the
> database.
>
> I can read tye size of file in the operating system but without VACUUM the
> database first the size is not accurate.
Well, it depends on how one defines the "size of the database." That
is such a misleading concept. In my view, the file size *is* the size
of the database with or without VACUUM-ing. VACUUM simply "changes"
the database ("changes" is in quotes) by recovering the deleted space.
There are so many other ways one could envision the "size" of the database --
1. The number of rows in tables
2. #1 above + all the space held by INDEXes (which can be substantial,
if not more than the tables themselves)
3. Any additional space held by the database's internal administrative
overhead -- TRIGGERs, VIEWs, schema tables, etc. all which are mystery
to mortals.
Then, while exactly the same database will be exactly the same bytes,
on the disk it will occupy different number of bytes depending on the
operating system and disk size.
To be absolutely clear, you have to ask for not the size of the
database, but the size of the file in which the database is held. Its
correct size is read from the filesystem (easy to do in SQLite, more
complicated in other db that scatter crap around on all kinds of
locations). The size at any given time depends on whether or not you
have run VACUUM.
So, there you have it. If you don't want to VACUUM it, you still get
the file size, but with deleted space included. If you VACUUM it, you
get the file size with no air pockets.
>
> So it seems like it is the must to ran the VACUUM before read the size of
> the file.
>
> Thanks,
>
> JP
>
>
> ----- Original Message ----
> From: P Kishor <[EMAIL PROTECTED]>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Thursday, May 29, 2008 5:40:23 PM
> Subject: Re: [sqlite] get the actual database size.
>
> On 5/29/08, Joanne Pham <[EMAIL PROTECTED]> wrote:
> > Hi All,
> > I have the database which has a lot of insertion and deletion.
> > Is there anyway that I can get the actual database size without running
> VACUUM.
>
> Your question implies that VACUUM lets you "get the database size."
> No, it doesn't. VACUUM recovers the space left behind by deleting data
> from the db.
>
> To find out the size of the database, just read the size of the file
> in the operating system.
>
> Or, maybe you are asking something completely different that I don't get.
>
>
> > Thanks,
> > JP
> >
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
