Re: primary key size is huge

[Alvaro Aguayo Garcia-Rada <aaguayo@xxxxxxxxxxxxxxx>]

Hi. The PK size may vary according to it's composition. For example, a PK with two or more fields will be larger than one with only one field; the column types may also affect the PK size. It's hard to say if the PK size is (or not) normal with knowing it's composition. Could you please send the CREATE TABLE statement, along with required ALTER TABLE ..... ADD CONSTRAINT statements, to get an idea of the table structure.

Being the table empty, it's indexes(including PK) SHOULD be empty, but that may not always happen. However, a REINDEX on tha table should clear them. BTW, was the table cleared using DELETE or TRUNCATE?

The PostgreSQL version used, 8.1.9, even when stable, is old. Considering such table size, I think you will get considerable performance benefits with PostgreSQL 9.5.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

---- Mackay, Beth (ES) wrote ----

My customer is concerned about the amount of RAM being used on their database server. 

 

I have a table with 172 GB of data, and it has a corresponding primary key, which also has 49 GB of data.  I found the sizes of the data using the "Finding the size of your biggest relations" section of this wiki: https://wiki.postgresql.org/wiki/Disk_Usage

I’m running PostgreSQL 8.1.9 (I know, it’s ancient and long past end of life but I can’t get my customer to upgrade).  They are running the autovacuum process.

 

Is that size primary key table expected?  If I have another copy of the database, this time with 93 MB of data in my table, it still has a corresponding 27 MB primary key.  Another curious thing is that sometimes I have no data in the table, but the primary key is still 21 MB.  In the case with no data in the table, data may have been in the table at one point but has since been deleted.

 

Would these large primary keys have any impact on the RAM used by the server?  Is there some way to reduce their sizes? 

 

Thanks,

Beth MacKay