On Wed, Jul 28, 2010 at 1:38 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote: > * P Kishor (punk.kish@xxxxxxxxx) wrote: >> Three. At least, in my case, the overhead is too much. My data are >> single bytes, but the smallest data type in Pg is smallint (2 bytes). >> That, plus the per row overhead adds to a fair amount of overhead. > > My first reaction to this would be- have you considered aggregating the > data before putting it into the database in such a way that you put more > than 1 byte of data on each row..? That could possibly reduce the > number of rows you have by quite a bit and also reduce the impact of the > per-tuple overhead in PG.. > each row is half a dozen single byte values, so, it is actually 6 bytes per row (six columns). Even if I combine them somehow, still the per row overhead (which, I believe, is about 23 bytes) is more than the data. But, that is not the issue. First, I can't really merge several days into one row. While it might make for fewer rows, it will complicate my data extraction and analysis life very complicated. The real issue is that once I put a 100 million rows in the table, basically the queries became way too slow. Of course, I could (and should) upgrade my hardware -- I am using a dual Xeon 3 GHz server with 12 GB RAM, but there are limits to that route. Keep in mind, the circa 100 million rows was for only part of the db. If I were to build the entire db, I would have about 4 billion rows for a year, if I were to partition the db by years. And, partitioning by days resulted in too many tables. I wish there were a way around all this so I could use Pg, with my available resources, but it looks bleak right now. > Thanks, > > Stephen > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkxQeSIACgkQrzgMPqB3kihjYgCeMx2awmTE4IfAHgtws8iKhteN > cnMAoIp2g2Zfo00GC7du16nwBht3Kt1O > =7tdl > -----END PGP SIGNATURE----- > > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
