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 1g1tR2-0007KZ-Ox for pgsql-admin@arkaria.postgresql.org; Mon, 17 Sep 2018 13:19:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g1tR0-0007iU-H8 for pgsql-admin@arkaria.postgresql.org; Mon, 17 Sep 2018 13:19:26 +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 1g1sne-0003gB-NT; Mon, 17 Sep 2018 12:38:46 +0000 Received: from mail-it0-x242.google.com ([2607:f8b0:4001:c0b::242]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1g1snc-0007tz-GV; Mon, 17 Sep 2018 12:38:46 +0000 Received: by mail-it0-x242.google.com with SMTP id h20-v6so10664696itf.2; Mon, 17 Sep 2018 05:38:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=nls6pf6hZbYX2S9HSw7wVwqo73pwBjDR0S+28J38MR0=; b=Y2FmpLCM5ucjoIYnqXt7K2hp/tuthr2h+7+fCTTp6o0w0i7jhs3lr8Y8DCoHQRbfcT oCCxih7atMIs1crn90NE8YPeDfOFM2VFT4rFcIkn5z0148ZKrABwY+OUG3uxjwM0MNxJ EYSWLQ7qmSyZ96IXHPfDXsxJyWH1Dw6v7JBT+rTUVsnNj9cojONf0Ig4ISWunqRtjIEj Rjp+kTuRsz+hLzfupr9ppw69RPlUVV7gvhOzXP2vFq7u/5E2EhS/T27dbIUBrDdNTaKK Lsm1uy1x2YH032y8ZsUWxPRgSHo7enCFYZCU+R7PqvZ05Nwoyv2wRQKtoTLpW+KURaTi lF4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=nls6pf6hZbYX2S9HSw7wVwqo73pwBjDR0S+28J38MR0=; b=cdH4dVBXzyU/gszdfSxOLXRMsvfaopMCfbgDCKlZT4g/+yV0RQV/gsYAQZAGPrAGzw to9GxRbFFuDklQBToddLNFzMgyBz1s9brFnpQ5CX9iildS4mUsb0qyKjL3qQv2ahx0Nk /naA1sou3a1o/svNss3Izm3F7czjFxOPeuBFvrPdJwGitLA83Birtf/w+mDP1gsSZHjg oKzQm9wa6XCePtG7kSRrrGOwEheBcIUdxs0sI7x+fjRcFK358OwL78VP0GKXhdSos56M n2atH6AYnekOkaVHnldMNpa75TLvc+iadeq1WuJzrgF1PGWsUoc14xp6oFJc8hwOnHyb /Mjw== X-Gm-Message-State: APzg51CaMY/nCphe+kWQ7GEBDDrMBSuEfywnfzxAU7+z5DsYGHtaE4r3 xN7N0vW94d2v4zYlgu5IWPnADFMjXxgLrazD6oUNqCrTwJU= X-Google-Smtp-Source: ANB0VdYx4tqcT7PAoAtLSmZL1DP4dVwmpzfS6YeVGzmO4DfziR807xynzQx6G+3/PLZd54h0ipk7KY+mkeLCtYFwKFM= X-Received: by 2002:a02:4f0e:: with SMTP id c14-v6mr22338784jab.20.1537187922157; Mon, 17 Sep 2018 05:38:42 -0700 (PDT) MIME-Version: 1.0 From: still Learner Date: Mon, 17 Sep 2018 18:08:33 +0530 Message-ID: Subject: Big image tables maintenance To: pgsql-admin@lists.postgresql.org, pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000000da62705761074f4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000000da62705761074f4 Content-Type: text/plain; charset="UTF-8" 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. 2. I can't maintain files on File system as the count is huge, 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? Each image retrieval is Currently, we are on pg 9.4 and moving to 10.5 soon. Thanks, GJ. --0000000000000da62705761074f4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi ,

I have a= 10 TB size table with multiple=C2=A0bytea=C2=A0columns (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 colu= mn from the 10TB size table, place it in a separate schema. The change shou= ld not result in any query change in the application.=C2=A0 Is it possible?= Doing this it should not affect the performance.=C2=A0

2. I can't maintain files on=C2=A0File=C2=A0system as the count is huge, so thinking of using any no-sql=C2=A0mostly mongo-DB, is it recommended? Or PostgreSQL i= tself can handle?=C2=A0

3. Taking the backup of 20= TB=C2=A0data,= =C2=A0is=C2=A0big=C2=A0task. Any more feas= ible solution other than online backup/pg_dump?

Ea= ch image retrieval=C2=A0is=C2=A0
Currently, we are on pg 9.4 and = moving to 10.5 soon.
=C2=A0
Thanks,
GJ.
--0000000000000da62705761074f4--