Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g1tqN-0000sW-GL for pgsql-admin@arkaria.postgresql.org; Mon, 17 Sep 2018 13:45:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g1tqL-0001cS-M0 for pgsql-admin@arkaria.postgresql.org; Mon, 17 Sep 2018 13:45:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g1tqL-0001bQ-CX for pgsql-admin@lists.postgresql.org; Mon, 17 Sep 2018 13:45:37 +0000 Received: from mail-oi0-x244.google.com ([2607:f8b0:4003:c06::244]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g1tq5-00014A-Lr for pgsql-admin@lists.postgresql.org; Mon, 17 Sep 2018 13:45:36 +0000 Received: by mail-oi0-x244.google.com with SMTP id x197-v6so18908653oix.5 for ; Mon, 17 Sep 2018 06:45:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=cEJdWXi1lUp7O3w6iGdQ7Qxp327d3COpvp+jlGCiUJM=; b=Lvk1pn9vPSGRqp6UI01yQfJbl8s2+6++c0X2ty6lyujUAp6BypEii998wTjaiUUluF 9wcpU/tLbjtZD2lpBUmM8AGpVjtql+wkGUwS7/N8sBL9OfOREeIdmf1wOIjK8sUCxF8t cUyMVRFJ/qF80zlXsliaE3Kv9R9PJBm4xSGWaWHWbKzzIMprWeJt98CmJ0ZHcpyNQlvY fdP7fWN2fG+2/Nc7PgBTJIGfYrfQ0Y7S2F0kwPcwOLw1yIhH8HAjPyrAWqVmepdxZn5K w3Q+82aXIbPn9+XLcdteMCiWTY3MldsmvJuDxuArv0qUqBc8kyc2CvLKwEYZ5EmF26Ly fzZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=cEJdWXi1lUp7O3w6iGdQ7Qxp327d3COpvp+jlGCiUJM=; b=eG5AOLK7qHMdGitkp1X80Cg/5TNV8lDQTBGj/v6+y7kL424mDA6MFz2jMOtZrxUxCb tklZrbQ69TMqO3dXM8O5E33cFMMU5VA6ZaCBvILS65ly07zWJfSar6c6vu4l+q71M5T5 WUyYZsCOZxDcK3M2ZU9jH5tBt+xyc4XjTLtCOBmFeNHnvan0hYKXu6D+hI5nebjhOeZ9 4G8mk8erukPkImejlMKe7afl/yo1JimraSgsMJwaXVNWPUXHDNPO4X3rV6+D4tN/Ta25 /FuIUX4rRu9nUtmqhERp9L0lmxxEnuoOGQR5ZX83mY2o8D2DSHveLG5/p7XPs877lOnL 4Neg== X-Gm-Message-State: APzg51A19c299A4hzYp+o17z0543ZWlyPU99p5Q9LIGPJ2nX+UimKpVH q9GH8Bewxs1E2gzJqygEfkRzPQ/A X-Google-Smtp-Source: ANB0Vdb4vhPe19936hrnrzxaqB1Z8pMOcG9+cXrErs4ITEQX+pqHkNm7Pzk6T41IYDLA8rXRo6QjAA== X-Received: by 2002:aca:4b0d:: with SMTP id y13-v6mr18324990oia.198.1537191918910; Mon, 17 Sep 2018 06:45:18 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id n30-v6sm6554865otb.73.2018.09.17.06.45.17 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 17 Sep 2018 06:45:17 -0700 (PDT) Subject: Re: Big image tables maintenance To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: Date: Mon, 17 Sep 2018 08:45:17 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------6E1DA2BCD4A4ADCFB96FF40B" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------6E1DA2BCD4A4ADCFB96FF40B Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 09/17/2018 07:38 AM, still Learner wrote: > Hi , > > I have a 10 TB size table with multiple bytea columns (image & doc)and > makes 20TB of DB size. I have a couple of issues to maintain the DB. > > 1. I Would like to separate the image column from the 10TB size table, > place it in a separate schema. The change should not result in any query > change in the application.  Is it possible? Doing this it should not > affect the performance. That's called "vertical partitioning", which I don't think Postgres supports. > > 2. I can't maintain files on File system as the count is huge, Eh? *You* aren't supposed to maintain the files on the filesystem; *Postgres* is. > so thinking of using any no-sql mostly mongo-DB, is it recommended? Or > PostgreSQL itself can handle? > > 3. Taking the backup of 20TB data, is big task. Any more feasible solution > other than online backup/pg_dump? pgbackrest and barman are popular options. (We have a database like yours, though only 3TB, and have found that pg_dump runs a *lot* faster with "--compress=0".  The backups are 2.25x larger than the database, though...) > > Each image retrieval is > Currently, we are on pg 9.4 and moving to 10.5 soon. > Thanks, > GJ. -- Angular momentum makes the world go 'round. --------------6E1DA2BCD4A4ADCFB96FF40B Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 09/17/2018 07:38 AM, still Learner wrote:
Hi ,

I have a 10 TB size table with multiple bytea columns (image & doc)and makes 20TB of DB size. I have a couple of issues to maintain the DB.

1. I Would like to separate the image column from the 10TB size table, place it in a separate schema. The change should not result in any query change in the application.  Is it possible? Doing this it should not affect the performance.

That's called "vertical partitioning", which I don't think Postgres supports.


2. I can't maintain files on File system as the count is huge,

Eh?  You aren't supposed to maintain the files on the filesystem; Postgres is.

so thinking of using any no-sql mostly mongo-DB, is it recommended? Or PostgreSQL itself can handle? 

3. Taking the backup of 20TB data, is big task. Any more feasible solution other than online backup/pg_dump?

pgbackrest and barman are popular options.

(We have a database like yours, though only 3TB, and have found that pg_dump runs a lot faster with "--compress=0".  The backups are 2.25x larger than the database, though...)


Each image retrieval is 
Currently, we are on pg 9.4 and moving to 10.5 soon.
 
Thanks,
GJ.

--
Angular momentum makes the world go 'round.
--------------6E1DA2BCD4A4ADCFB96FF40B--