Re: Compression In Postgresql 9.6 - Mailing list pgsql-general

From Shital A
Subject Re: Compression In Postgresql 9.6
Date
Msg-id CAMp7vw_CFcxE9i223M_6TkFpoy4GJMCacoEv1t3TG-1wcotrVw@mail.gmail.com
Whole thread Raw
In response to Re: Compression In Postgresql 9.6  (Kenneth Marshall <ktm@rice.edu>)
List pgsql-general


On Mon, 5 Aug 2019, 18:57 Kenneth Marshall, <ktm@rice.edu> wrote:
> >Hi,
> >
> >On RHEL/Centos you can use VDO filesystem compression to make an archive
> >tablespace to use for older data. That will compress everything.
>
> Doesn't this imply that either his table is partitioned or he
> regularly moves records from the main table to the archive table?
>

Hi,

Yes, he will need to do something to meet his goal of both a 100k TPS
and have older archives online. He could also use something like
postgres_fdw to have the archives on a seperate server completely.

Regards,
Ken


Thanks for the suggestions guys ! 

After checking i am thinking about following approach:

1. Create a FS on a separate drive on the server with VDO

2. Create a tablespace on FS created above for storing the historical/less update intensive data

3. Other tablespaces remain on non compressed FS

4. Use table partitioning and create the tables in tablespace created in step 2.

- will this complicate the DB design? In terms of replication, backup and restores
- Can this give optimum performance. 

Let me know your views ! 

Thank You ! 



pgsql-general by date:

Previous
From: Luca Ferrari
Date:
Subject: vacuum & free space map
Next
From: VO Ipfix
Date:
Subject: Preventing in-session 'set role' commands