To: pgsql-admin <pgsql-admin@postgresql.org>
From: Michal Merta <michal.merta@greycortex.com>
Subject: Amount of unused space on a storage device
Message-ID: <37bd3391-30db-292c-0cf9-110860461b0d@greycortex.com>
Date: Thu, 31 Aug 2017 16:52:05 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------B6B9B3167B1CF3182BDB3F1A"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------B6B9B3167B1CF3182BDB3F1A
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello everyone,

We have an OLAP database on dedicated RAID storage with a total capacity 
of 100TB. The transaction log and temp_tablespace are stored on a 
separated drive.
All tables are partitioned by timestamp of insert, the size of each 
partition is 1 week. All tables are insert only, no update or delete 
ever occurs. The amount of data stored peer week is ~1TB.

We use following maintenance algorithm:
   IF amount of free space on storage is lower than BOUNDARY, THEN
     backup all partitions for oldest week
     drop these partitions from database

Our desired behavior is to keep as much data as possible in database for 
analytical queries.

My question is:
Having 100TB capacity and data coming in at a fixed rate of ~1TB per 
week, what would be an optimal BOUNDARY? Or to put it another way, what 
is a reasonable amount of unused space on a storage device of this size?

Thanks & Regards,
Michal Merta

--------------B6B9B3167B1CF3182BDB3F1A
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hello everyone,<br>
    <br>
    We have an OLAP database on dedicated RAID storage with a total
    capacity of 100TB. The transaction log and temp_tablespace are
    stored on a separated drive.<br>
    All tables are partitioned by timestamp of insert, the size of each
    partition is 1 week. All tables are insert only, no update or delete
    ever occurs. The amount of data stored peer week is ~1TB.<br>
    <br>
    We use following maintenance algorithm:<br>
      IF amount of free space on storage is lower than BOUNDARY, THEN<br>
        backup all partitions for oldest week<br>
        drop these partitions from database<br>
    <br>
    Our desired behavior is to keep as much data as possible in database
    for analytical queries.<br>
    <br>
    My question is:<br>
    Having 100TB capacity and data coming in at a fixed rate of ~1TB per
    week, what would be an optimal BOUNDARY? Or to put it another way,
    what is a reasonable amount of unused space on a storage device of
    this size?<br>
    <br>
    Thanks &amp; Regards,<br>
    Michal Merta<br>
    <span style="font-size:12.8px"></span><span style="font-size:12.8px"></span>
  </body>
</html>

--------------B6B9B3167B1CF3182BDB3F1A--