Re: primary key size is huge

Try to check bloat :

https://wiki.postgresql.org/wiki/Show_database_bloat

If you see that table and primary key index are heavily bloated. You can
use reindex, cluster etc to fix that bloat, shrink table/index and gain the
wasted space back. However, these are blocking operations. I dont think
concurrent index creation existed in 8.1.

Again, please upgrade to a newer version asap.

Regards,
Asad

On Wed, Mar 30, 2016 at 4:52 PM, Alvaro Aguayo Garcia-Rada <
aaguayo(at)opensysperu(dot)com> wrote:

> 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*
> <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
>
>
>
>