Re: Large databases, performance

On Mon, 07 Oct 2002 15:07:29 +0530, "Shridhar Daithankar"
<shridhar_daithankar(at)persistent(dot)co(dot)in> wrote:
>Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All
>numbers include indexes. This is really going to be a problem when things are
>deployed. Any idea how can it be taken down?

Shridhar,

if i'm not mistaken, a char(n)/varchar(n) column is stored as a 32-bit
integer specifying the length followed by as many characters as the
length tells. On 32-bit Intel hardware this structure is aligned on a
4-byte boundary.

For your row layout this gives the following sizes (look at the "phys
size" column):

| Field Field Null Indexed phys mini
| Name Type size
|--------------------------------------------
| type int no no 4 4
| esn char (10) no yes 16 11
| min char (10) no yes 16 11
| datetime timestamp no yes 8 8
| opc0 char (3) no no 8 4
| opc1 char (3) no no 8 4
| opc2 char (3) no no 8 4
| dpc0 char (3) no no 8 4
| dpc1 char (3) no no 8 4
| dpc2 char (3) no no 8 4
| npa char (3) no no 8 4
| nxx char (3) no no 8 4
| rest char (4) no no 8 5
| field0 int yes no 4 4
| field1 char (4) yes no 8 5
| field2 int yes no 4 4
| field3 char (4) yes no 8 5
| field4 int yes no 4 4
| field5 char (4) yes no 8 5
| field6 int yes no 4 4
| field7 char (4) yes no 8 5
| field8 int yes no 4 4
| field9 char (4) yes no 8 5
| ----- -----
| 176 116

Ignoring nulls for now, you have to add 32 bytes for a v7.2 heap tuple
header and 4 bytes for ItemIdData per tuple, ending up with 212 bytes
per tuple or ca. 85 GB heap space for 432000000 tuples. Depending on
fill factor similar calculations give some 30 GB for your index.

Now if we had a datatype with only one byte for the string length,
char columns could be byte aligned and we'd have column sizes given
under "mini" in the table above. The columns would have to be
rearranged according to alignment requirements.

Thus 60 bytes per heap tuple and 8 bytes per index tuple could be
saved, resulting in a database size of ~ 85 GB (index included). And
I bet this would be significantly faster, too.

Hackers, do you think it's possible to hack together a quick and dirty
patch, so that string length is represented by one byte? IOW can a
database be built that doesn't contain any char/varchar/text value
longer than 255 characters in the catalog?

If I'm not told that this is impossibly, I'd give it a try. Shridhar,
if such a patch can be made available, would you be willing to test
it?

What can you do right now? Try using v7.3 beta and creating your
table WITHOUT OIDS. This saves 8 bytes per tuple; not much, but
better save 4% than nothing.

Servus
Manfred