Re: How Big is Too Big for Tables?

On Wed, Jul 28, 2010 at 1:38 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * P Kishor (punk(dot)kish(at)gmail(dot)com) 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
=======================================================================