[Fwd: Re: [UMN_MAPSERVER-USERS] postgres occupies more space than mysql???]

[Stephen Woodbridge]

-------- Original Message --------
Subject: Re: [UMN_MAPSERVER-USERS] postgres occupies more space than
mysql???
Date: Fri, 04 Feb 2005 14:11:59 -0500
From: Stephen Woodbridge <woodbri at swoodbridge.com>
To: Shashi Gireddy <gireddy at GMAIL.COM>
References: <5f447e3505020409081b40bab2 at mail.gmail.com>

Shashi,

Very interesting! I am about to embark on the same journey myself. I
have the whole Census Summary File 1 and 3 loaded into MySQL and it is
about 103 GB and only sparsely indexed. I hope it does not grow by a
factor of 10 like yours did.

Here are some ideas about size and speed.

1) check you row sizes, fixed length rows are large but faster to access
    up to a point. You can usually force a row to be variable sized by
    making sure you use VARCHAR() and not CHAR() for large text columns

2) when you are inserting your data, make sure you don't have any
    indexes on you tables, after your data has been inserted then add all
    the indexes at once.

This tricks work for MySQL, I have yet to play with postgres and postgis
so I have no idea if they will work there.

For others, the summary file data is just straight relational tables
with attributes for the various Census polygons. Once you have the
summary file data loaded and then load all the Tiger you will be able to
generate thematic maps but joining the attributes to the geometry and
classifying the geometry based on the attributes. This can't be done
with MySQL and shapefiles for geometry because JOIN only works for QUERY
not for classification.

-Steve W.

Shashi Gireddy wrote:
> Hi all,
>
> I have my SF1 data on mysql database, i wnated to migrate to postgres
> to get advantage of Postgis, I created a backup .sql file (whole data)
> whose size is 8GB but its actual database size in mysql is 700MB (it
> may be because insert into(bla,bla) values(bla, bla). but after i
> migrated to postgres it occupied 7GB and it is damn slow(it took
> 42hours for postgres to insert and its still running may take about 3
> more hours), can anybody suggest any idea how to reduce the size and
> make it faster.
>
> thanks alot in advance.
>
> --
> Shashi Kiran Reddy. Gireddy,
> Graduate Assistant,
> CBER, University of Alabama.
> http://www.cs.ua.edu/shashi
> Home: 205-752-5137 Cell: 205-657-1438
>