Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a8REa-0004TU-CK for pgsql-admin@arkaria.postgresql.org; Mon, 14 Dec 2015 11:24:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a8REZ-0004K7-69 for pgsql-admin@arkaria.postgresql.org; Mon, 14 Dec 2015 11:24:03 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a8REX-0004Iv-9O for pgsql-admin@postgresql.org; Mon, 14 Dec 2015 11:24:01 +0000 Received: from mail-wm0-x22e.google.com ([2a00:1450:400c:c09::22e]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1a8REU-000113-1O for pgsql-admin@postgresql.org; Mon, 14 Dec 2015 11:23:59 +0000 Received: by mail-wm0-x22e.google.com with SMTP id p66so39141335wmp.0 for ; Mon, 14 Dec 2015 03:23:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:mime-version:from:subject:date:to:content-type; bh=P2nq/rQmOMCdD63MuPThfub9cvjpmN7U0VpIaqZL5FI=; b=YRjgIydkiCti32a5nwLsxiHhkCPoiyevQGli8z/aQECIXk8wjg0SgkFEnrZEF6IoGn Vv3OCvdO/Haxxba3f18n7sP+rrIsn7FjxLSrp1+FG1hPNwR3xwyBRYT/54fQfaz0xVJT n81Qy6tT/vu1/2vr4YEh/BbgyBBiDq5TXQ2qtqn3bRBmMREUitKCu2cXqp+5CjCg/JUe ZUR7cqHqOprmMRMRg68SmHhux9j6N1JXBfq/ThwOF6PMyJRUi7kUWXbJjzQ3t8Twp+Ig n4rvp+1MnDzMdIqKvsBQk+kB3UY1ujnibxml3AosuRr+NiRdEhlQwYgvzFC+dK2hxni5 trkw== X-Received: by 10.195.11.101 with SMTP id eh5mr37360342wjd.104.1450092236299; Mon, 14 Dec 2015 03:23:56 -0800 (PST) Received: from [192.168.1.252] (hst-239-20.splius.lt. [62.80.239.20]) by smtp.gmail.com with ESMTPSA id h67sm15575905wmf.17.2015.12.14.03.23.55 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 14 Dec 2015 03:23:55 -0800 (PST) Message-ID: <566ea6cb.46b71c0a.17f65.5010@mx.google.com> MIME-Version: 1.0 From: Marius Subject: Re: Table size - optimization Date: Mon, 14 Dec 2015 13:24:48 +0200 To: "drum.lucas@gmail.com" , "pgsql-admin@postgresql.org" Content-Type: multipart/alternative; boundary="_3B6BB722-E7A1-8C2D-3BCD-A7B38E98BA20_" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_3B6BB722-E7A1-8C2D-3BCD-A7B38E98BA20_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" You could simply rename old table, create new one and all in same transacti= on. No data move required. -----Original Message----- From: drum.lucas@gmail.com Sent: 2015.12.14 06:37 To: pgsql-admin@postgresql.org Subject: [ADMIN] Table size - optimization Feedlog is a central area where theres been a lot of data dumped together a= nd the beast has grown. Whats a better way to design the db table(s) for feedlog so we can get a ve= ry large performance gain. Is there a way we can create a feedlog-history table or yearly tables eg fe= edlog-2014 and move old data out of the original table so its a kean fast t= able? What code would need to change to deal with that? Some data : Size: 8 GB =20 relname | relkind | reltuples | relpages -------------------+---------+-------------+---------- feedlog | r | 6.60508e+07 | 9974857 Thanks, Lucas = --_3B6BB722-E7A1-8C2D-3BCD-A7B38E98BA20_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"
You could simply rename old table, create new one and all= in same transaction. No data move required.
Fr= om: drum.lucas@gmail.com
Sent: 2015.12.14 06:37
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Table siz= e - optimization

Feedlog is a central a= rea where theres been a lot of data dumped together and the beast has grown= .

Whats a better way to design the db table(s) for= feedlog so we can get a very large performance gain.

<= div>Is there a way we can create a feedlog-history table or yearly tables e= g feedlog-2014 and move old data out of the original table so its a kean fa= st table?

What code would need to change to deal w= ith that?

Some data :

<= div>
Size: 8 GB
      
relname      | relkind |  reltuples  = | relpages
-------------------+---------+-------------+----------=
 feedlog        | r      = ; | 6.60508e+07 |  9974857



Thanks,
Lucas
= =0A= = --_3B6BB722-E7A1-8C2D-3BCD-A7B38E98BA20_--