Thread: Why are tables sizes so big?
I have a Win32 app connecting to a PostgreSQL server via Delphi -> BDE -> ODBC -> PostgreSQL. The app takes CSV files and stores them on the PostgreSQL server. An empty CSV file makes a table that is 8kB in size on the server and that is fine but for every 1kB of text in the CSV file the PostgreSQL file gains about 5kB. The CSV file has 5 text fields that I change to char size 10, float, float, int and float. That in it's self sould make the data on the server much smaller, but it is not, it is bigger, why?
God_Of_Pain wrote: > > I have a Win32 app connecting to a PostgreSQL server via Delphi -> BDE -> > ODBC -> PostgreSQL. The app takes CSV files and stores them on the > PostgreSQL server. An empty CSV file makes a table that is 8kB in size on > the server and that is fine but for every 1kB of text in the CSV file the > PostgreSQL file gains about 5kB. > > The CSV file has 5 text fields that I change to char size 10, float, float, > int and float. That in it's self sould make the data on the server much > smaller, but it is not, it is bigger, why? char = 10 + 4 floats= 8 * 3 int = 4 oid = 4 That's at least 46 bytes per row plus indexes - which can take up a lot. Does that look plausible? - Richard Huxton
As an aside you, and anyone else who uses Delphi / C++ builder, may want to try the components at the following link:
http://www.zeos.dn.ua/develop/zeosdbo-5.2.3-beta.zip
With Homepage:
they are replacements for the standard data access components and directly connect to PostgreSQL (and other dbs). They are still in beta but seem to work better (and much faster) than ODBC / BDE
Regards
Ben
> -----Original Message-----
> From: God_Of_Pain [mailto:silicontao_roy@technologist.com]
> Sent: 05 March 2001 16:35
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Why are tables sizes so big?
>
>
> I have a Win32 app connecting to a PostgreSQL server via
> Delphi -> BDE ->
> ODBC -> PostgreSQL. The app takes CSV files and stores them on the
> PostgreSQL server. An empty CSV file makes a table that is
> 8kB in size on
> the server and that is fine but for every 1kB of text in the
> CSV file the
> PostgreSQL file gains about 5kB.
>
> The CSV file has 5 text fields that I change to char size 10,
> float, float,
> int and float. That in it's self sould make the data on the
> server much
> smaller, but it is not, it is bigger, why?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
Richard Huxton <dev@archonet.com> writes:
>> The CSV file has 5 text fields that I change to char size 10, float, float,
>> int and float. That in it's self sould make the data on the server much
>> smaller, but it is not, it is bigger, why?
> char = 10 + 4
> floats= 8 * 3
> int = 4
> oid = 4
> That's at least 46 bytes per row plus indexes - which can take up a lot.
Actually, the per-row overhead is not just an OID: there are several
other system fields too. On a typical machine it's going to be 32 bytes
not four. Possibly more if you have nulls in the row; see
src/include/access/htup.h if you're interested.
regards, tom lane