Re: Database Bloat

On Mon, Aug 20, 2012 at 2:33 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 08/20/12 11:46 AM, elliott wrote:
>>
>> envdb=# \d astgtm2_n60e073;
>> Table "public.astgtm2_n60e073"
>> Column | Type | Modifiers
>> --------+---------+-----------
>> lat | real |
>> lon | real |
>> alt | integer |
>> Indexes:
>> "q3c_astgtm2_n60e073_idx" btree (q3c_ang2ipix(lon, lat)) CLUSTER
>
>
> so, you DO have an index. what type does this function
> q3c_ang2ipix(real,real) return ? googling it suggested a bigint, which
> means every 12 byte (real,real,int) row has a corresponding b-tree entry of
> 8 bytes plus b-tree stuff. I see you used cluster on this, did you do the
> CLUSTER after populating the table, and before checking the
> pg_total_relation_size that you reported as 20X your data ?

Apparently it returns a bigint:
https://www.google.com/search?q=q3c_ang2ipix&rlz=1C1CHKZ_enUS437US437&sugexp=chrome,mod=17&sourceid=chrome&ie=UTF-8

Anyways, the overhead for vary narrow tuples is going to be higher for
very narrow tables. Your tuple is only 12 bytes. Each tuple has 23
bytes of overhead typically see:
(http://www.postgresql.org/docs/9.1/interactive/storage-page-layout.html).

Also, measuring total database size is pretty silly because there's a
number of things that are global and don't increase as your data
increases. To get table size, try doing

SELECT pg_size_pretty(pg_relation_size('astgtm2_n60e073'));
SELECT pg_size_pretty(pg_relation_size('q3c_astgtm2_n60e073_idx'));

merlin