Re: Compressed binary field

Em 11/09/2012 14:00, Kevin Grittner escreveu:
> Edson Richter <edsonrichter@hotmail.com> wrote:
>
>> there is no problem. Just trying to reduce database size
>
>> Actual database size = 8Gb
>> Backup size = 1.6Gb (5x smaller)
>>
>> Seems to me (IMHO) that there is room for improvement in database
>> storage (we don't have many indexes, and biggest tables are just
>> the ones with bytea fields). That's why I've asked for experts
>> counseling.
>
> What version of PostgreSQL is this?
9.1.5 on Linux x64 (CentOS 5)
>
> How are you measuring the size?
For storage, du -h --max-depth 1 on data directory gives me the amount
of data.
> Where is the space going?  (Heap files?  TOAST files?  Index files?
> WAL files?  Free space maps?  Visibility maps?  Server logs?
> Temporary files?)
Biggest objects are just the tables with files.
> You aren't creating a separate table with one row for each binary
> object, are you?  I only ask this because in an earlier post you
> mentioned having a quarter million files in the database, and in a
> production database which has been running for years with over 400
> user tables and lots of indexes I only have about 4000 files in the
> whole database cluster.  A separate table for each object would be
> disastrous for both performance and space usage.
I've 2 tables that held all these objects. Structure is

create table MYTABLE (id bigint not null primary key, mimetype
varchar(100) null, bytea datafile null)


Regards,

Edson.

>
> -Kevin
>
>