Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9963-0005LJ-26 for pgsql-admin@arkaria.postgresql.org; Wed, 27 Mar 2019 14:00:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h9961-0002GT-4m for pgsql-admin@arkaria.postgresql.org; Wed, 27 Mar 2019 14:00:01 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9960-0002GM-Rz for pgsql-admin@lists.postgresql.org; Wed, 27 Mar 2019 14:00:00 +0000 Received: from mail-oi1-x22e.google.com ([2607:f8b0:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h995y-0008Dm-Qh for pgsql-admin@lists.postgresql.org; Wed, 27 Mar 2019 14:00:00 +0000 Received: by mail-oi1-x22e.google.com with SMTP id y84so12893786oia.12 for ; Wed, 27 Mar 2019 06:59:58 -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=b9M3xuxiFSzzBCeZnm4CJFDuFEHw6HVXARjwroyVMU0=; b=k5iQWAGP0nf+fYxSF+Mz/G6TjZqDyOmodEoz2bWt8OjOkL4ClTQtgAFJf7JikbaT+8 9q5+48ddwVOe6urgkNhTjTNUs/JzD9HUTKOtTReKxTcquwEz9KqQbvECVzd+b9ba53aq ETCtcKmzhzMOB3bQ3+ila2pl94EQml6Y2qOw8P5OVpqEJokaoBLa6TNvJcoyRKYKgo21 mqdlrHmAUmRk32+RoWkFQy1pqwEWmZGMOy7ZkKmCPmbvz1ZHwpc5IoInZr42bYK1UGJe aWVjnGzVd1pcZ/WGpPBxoiD7H9ECaRDohV0xd5hXCV+Q8Lht2aK77rscHlmvrP7S3c6p 4HDw== 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=b9M3xuxiFSzzBCeZnm4CJFDuFEHw6HVXARjwroyVMU0=; b=Pmi+lhu7qkPrs3W14thX4t05fy2ySJPwn4CsReSm4V8ZHa7qEdY/HipMP+mEo4W+LD 6PBfwwp/wbCiylBvdsQNb7atcuicmAXraKMX3GtTzwRU561TlhndiabhK79tUL6D40o+ Gii8iblxfL1wYmHSvvgAD/H6N1zSS4kp7LFK5gtSidgz4K32qNChCoHPGVf0Y9JE8xTB dEPcqoCkSwHXssmjMXn7n3f4ryx0ZE1HHlkwfVAlE1lp0Xiwd4My5YGnboxWUv537lpz 3ONtWwbcTl2j/M0w/THSLxxbxLmw/DT3n4I0shyq3nexTT0KeGnt1vxNjLtYA9bUAh/F gxyQ== X-Gm-Message-State: APjAAAWF9mDMEUL+kYQ6+QKO66ch9AdY9Ufnm4IEcJ2/mlepYtUz5CrH J5tyYGr57dbZ8s9632rLprKw/VLPSvbY3g== X-Google-Smtp-Source: APXvYqw1OJ5svyc+smEZmW/jggleu99bsnHL8SUoejilCr4Dr5c2rvgOLBGk5JBmsvqu90//AjuQtQ== X-Received: by 2002:aca:b408:: with SMTP id d8mr18528504oif.15.1553695196309; Wed, 27 Mar 2019 06:59:56 -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 v19sm8730366otk.63.2019.03.27.06.59.54 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 27 Mar 2019 06:59:55 -0700 (PDT) Subject: Re: Unique Index Disk Consumption To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <9008124b-a440-9419-1a0f-fffd711fe476@gmail.com> Date: Wed, 27 Mar 2019 08:59:54 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------83B6B4E6D7483AE64388BA97" 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. --------------83B6B4E6D7483AE64388BA97 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 3/27/19 8:50 AM, Moin Akther wrote: > Dear Experts, > > on a table we have 2 unique index's and when i check the table and unique > index size, size of index's are twice the size of table. > > if have drop both the unique index's and create the new one but again > after some time index size is almost twice the size of table. > > Is Unique Index consume more disk space ? How big is each row? Are the rows compressible? How big are PK fields? > > How can i manage this kind scenario ? as droping and recreating unique > index on the existing data is taking lot of time. Why do you *need* to? -- Angular momentum makes the world go 'round. --------------83B6B4E6D7483AE64388BA97 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit On 3/27/19 8:50 AM, Moin Akther wrote:
Dear Experts,

on a table we have 2 unique index's and when i check the table and unique index size, size of index's are twice the size of table.

if have drop both the unique index's and create the new one but again after some time index size is almost twice the size of table.

Is Unique Index consume more disk space ?

How big is each row?

Are the rows compressible?

How big are PK fields?


How can i manage this kind scenario ? as droping and recreating unique index on the existing data is taking lot of time.

Why do you need to?


--
Angular momentum makes the world go 'round.
--------------83B6B4E6D7483AE64388BA97--