Обсуждение: the "/usr/local/pgsql/data" directory size

Hi,

I've been evaluating postgres for the past few days and I really like it.

But my targeted system is semi-embedded.  I'm going to have about 32MB of
RAM
and about 256MB of disk space.  Most of the embedded db's that I've
evaluated
just don't make the grade (even some non-embedded ones that I looked at.)
Our
data size will be near 250MB in worst Case and more like 225MB in average
case
situations.

So if I allocate 25MB for user programs like postgres (the OS and other
system
files are actually in a ROM) I'll be able to cover most situations.

Now, after compiling postgres and running initdb I noticed a the data
directory
is a little over 23MB.  I understand that much of this is allocated to
system
tables but is there anyway to (significantly) decrease the overall size
here?
For example the template1 db probably resides in here.  would it be possible
to
create a user db and then delete the template1 db or even simply use the
template1 db?

Thanks guys,
Al

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail

Al Bean wrote:
> Now, after compiling postgres and running initdb I noticed a the data
> directory is a little over 23MB.  I understand that much of this is
> allocated to system tables but is there anyway to (significantly) decrease
> the overall size here? For example the template1 db probably resides in
> here.  would it be possible to create a user db and then delete the
> template1 db or even simply use the template1 db?
>

You should be able to drop the template1 database and create your user
database from template0, or just use template1 for your user database as you
suggested. I think lack of template1 may adversely affect some of the client
utilities, so test carefully.

Other things I can think of:

1. Reduce NAMEDATALEN in ~/src/include/postgres_ext.h: in 7.3 (just released
and highly recommended) NAMEDATALEN was increased based on popular demand to
64. It was 32 (31 usable characters for table and other object names). If you
can use 32 or even less it will reduce the template and user database size
some. Not sure how low you can safely take this.

2. Reduce INDEX_MAX_KEYS in src/include/pg_config.h.in: again increased from
16 to 32 in 7.3. If you can live with functions and indexes with no more than
16 arguments, this will also save you space. I *think* you can safely take
this down to 8, but hopefully someone else will comment on that.

Both of these items will make your database and client incompatible with the
rest of the world of Postgres users, but I'm guessing that is OK for an
embedded application.

HTH,

Joe

On Thu, 5 Dec 2002, Al Bean wrote:

> Hi,
>
> I've been evaluating postgres for the past few days and I really like it.
>
> But my targeted system is semi-embedded.  I'm going to have about 32MB of
> RAM
> and about 256MB of disk space.  Most of the embedded db's that I've
> evaluated
> just don't make the grade (even some non-embedded ones that I looked at.)
> Our
> data size will be near 250MB in worst Case and more like 225MB in average
> case
> situations.
>
> So if I allocate 25MB for user programs like postgres (the OS and other
> system
> files are actually in a ROM) I'll be able to cover most situations.
>
> Now, after compiling postgres and running initdb I noticed a the data
> directory
> is a little over 23MB.  I understand that much of this is allocated to
> system
> tables but is there anyway to (significantly) decrease the overall size
> here?
> For example the template1 db probably resides in here.  would it be possible
> to
> create a user db and then delete the template1 db or even simply use the
> template1 db?

That's not going to work.  Postgresql generally has about a 3:1 ratio
(very rough approximation) of used storage versus the actual data stored.
I.e. storing 250 megs of data is going to use about 750 Megs of drive
space.

I.e. you'll either need more drive space or you'll need to use something
else.

If you're doing simple hash table lookup type stuff, look at using dbm
style databases.  There's a gdbm system built into most Linux boxen, and
equivalents for most other unix OSes.

Joe Conway <mail@joeconway.com> writes:
> Al Bean wrote:
>> Now, after compiling postgres and running initdb I noticed a the data
>> directory is a little over 23MB.

> Other things I can think of:
> 1. Reduce NAMEDATALEN
> 2. Reduce INDEX_MAX_KEYS

Neither of those are likely to change the disk footprint a lot, since
the system catalogs aren't a significant proportion of any real-world
database, I should think.

The majority of the initial footprint is the first xlog segment file
(16MB).   For a low-traffic installation you could consider reducing
the xlog segment size.  This'd have less impact on interoperability
than the other changes, too, since it's not visible to clients.

            regards, tom lane

Tom Lane wrote:
>>Other things I can think of:
>>1. Reduce NAMEDATALEN
>>2. Reduce INDEX_MAX_KEYS
>
> Neither of those are likely to change the disk footprint a lot, since
> the system catalogs aren't a significant proportion of any real-world
> database, I should think.

I was thinking of the benchmarking results we did for 7.3.

This one:
   http://archives.postgresql.org/pgsql-hackers/2002-08/msg00258.php
showed about 12 to 15% increase in individual database size when changing from
  INDEX_MAX_KEYS = 16 to INDEX_MAX_KEYS = 32.

And this one:
   http://archives.postgresql.org/pgsql-hackers/2002-08/msg00333.php
showed about 11% increase increase in individual database size when changing
from NAMEDATALEN = 32 to NAMEDATALEN = 64.

For an embedded application, these kinds of savings might be important.

Joe