Thread: 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