Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pD5my-0001rp-SJ for pgsql-general@arkaria.postgresql.org; Wed, 04 Jan 2023 15:34:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pD5mx-0007U9-0Q for pgsql-general@arkaria.postgresql.org; Wed, 04 Jan 2023 15:34:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pD5mw-0007Rn-KZ for pgsql-general@lists.postgresql.org; Wed, 04 Jan 2023 15:34:46 +0000 Received: from mail-qt1-x830.google.com ([2607:f8b0:4864:20::830]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pD5mu-0001Bg-FW for pgsql-general@lists.postgresql.org; Wed, 04 Jan 2023 15:34:45 +0000 Received: by mail-qt1-x830.google.com with SMTP id j16so27458385qtv.4 for ; Wed, 04 Jan 2023 07:34:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id:from :to:cc:subject:date:message-id:reply-to; bh=N45vZ1Eh027DmInzvScCu//vkhr7k3yNXbg/s4w7zDc=; b=ktapYT28zHdgbohiYTYaP/omfegSgNDLwo7pds5/C4TaSVZzsdCSlwfnSpquTeIZTI ZA9zImv2s/2DFAbE5BOG508RCOdAV9pXthSFBzC27f5VhL50MnnDUfadtRXPoF+wNcm6 JdddQ1UmHPFaBxHr7G+ROmkGYpz7reSIYWiapoVMRv/b99pJn+lPHETsb0PzQmbmtMD2 wLJ+hHVn+xPDwO9ROfcW8rZ6KeWym76Hdhye0JNy20pmiydW5nA3UzxMug4Xad8LBxin u5iqpVKnY59RQPaXHMEJjYa2i1u82wv2hqloMW9QjOXECyTW+tT8CXeZqtC6woFcFkTP /atw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:content-language :references:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=N45vZ1Eh027DmInzvScCu//vkhr7k3yNXbg/s4w7zDc=; b=YS+912paxI+2ScT8GVeni/TIYZ0/u73Cp9kTJTggm0eNeeYiQafng6xJjTe56Y3OJ3 3a2Sr7qCE7YcXZScBze53HyROasq0GIde1Xm7/GppHv48CHjGBlqVk7QltN2O/qQefy5 xulLMZC9Yo3p6n4rnuzWpQltnRF4Yhc6uqeQdptuVhCtRdHnWnALXWqg/+4JuIo531KC Nb2G5JxOq926GCTZ5wZX+NQUh1xk+mekChfvw5BphoFb/TSMGms7EwQ2KX3DRJ1jTsB7 AHwEiaO830tZI4178eGRxsOP+f5noL2VGFDvgocACRBezaJe2q/YxBeObYFFzG3uip+Q J/lQ== X-Gm-Message-State: AFqh2kow7+6iIbesX7Gm7A9yPO1M1Su9dKofNpN6q5yNK4IKmaAWgpbw Kq4zxbKIeRiWq4NcL9wt4pXBGjXtJO8IBw== X-Google-Smtp-Source: AMrXdXs643Cf0opBFNORDQT6eITOii1u5WRkiB7lDmJIK0ofci6PA7fO9gEq7kd7fWbGJdWIKippLQ== X-Received: by 2002:ac8:4cc8:0:b0:3a8:e528:dab5 with SMTP id l8-20020ac84cc8000000b003a8e528dab5mr55197909qtv.0.1672846483189; Wed, 04 Jan 2023 07:34:43 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id r14-20020ac87eee000000b003a6a92a202esm20366373qtc.83.2023.01.04.07.34.42 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 04 Jan 2023 07:34:42 -0800 (PST) Message-ID: <4dee765f-2196-90b9-efa1-896859552989@gmail.com> Date: Wed, 4 Jan 2023 09:34:42 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: REINDEX vs VACUUM To: pgsql-general@lists.postgresql.org References: <70615d25-201f-ad4a-1291-806999ca36fb@rblst.info> Content-Language: en-US From: Ron In-Reply-To: <70615d25-201f-ad4a-1291-806999ca36fb@rblst.info> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I don't think VACUUM FULL (copy the table, create new indices and other metadata all in one command) actually vacuums tables.  It's a misleading name. Something like REBUILD TABLE would be a better name. On 1/4/23 07:25, Rébeli-Szabó Tamás wrote: > Here is my understanding: > > REINDEX recreates the index from scratch, using the data stored in the > underlying table. It is the same as dropping and recreating the index > manually, with regard to the impact on the index file. It can free up > physical space in the file system. REINDEX will not vacuum the index. > > VACUUM does many different things. One of them is vacuuming indexes (for > the underlying table that is being vacuumed). VACUUM will remove index > entries that are pointing to dead rows in the underlying table. VACUUM > will not rebuild the entire index. > > VACUUM recycles free index blocks (using FSM), but it does not (usually) > free up space for the file system physically. VACUUM FULL does that. > > VACUUM FULL will vacuum the index, but it will do it by making a copy of > the index (file) and reorganizing its content in order to free up space > physically. In that regard, it is like REINDEX. Both VACUUM FULL and > REINDEX will block reads from the index during the process (by taking an > ACCESS EXCLUSIVE lock). > > Regards, > > tamas > > 2022. 12. 31. 22:04 keltezéssel, Hao Zhang írta: >> What is the difference between reindex and vacuum's impact on index file? >> I deleted an indexed row. Running either vacuum or reindex shows the >> index entry for the row is removed from the index page. I was under the >> impression that only reindex will remove dangling index entries. I am >> guessing that vacuum will not shrink the index file and will only add >> deleted index entries in the free space file for the index? But REINDEX >> is recreating the index file from scratch so it is like vacuum full for >> index? >> >> Thanks > > -- Born in Arizona, moved to Babylonia.