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 1jLhhk-0008JG-ER for pgsql-admin@arkaria.postgresql.org; Tue, 07 Apr 2020 06:27:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jLhhj-0007H2-B2 for pgsql-admin@arkaria.postgresql.org; Tue, 07 Apr 2020 06:27:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jLhhi-0007GG-W3 for pgsql-admin@lists.postgresql.org; Tue, 07 Apr 2020 06:27:23 +0000 Received: from mail-wr1-x444.google.com ([2a00:1450:4864:20::444]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jLhhf-00021O-AK for pgsql-admin@postgresql.org; Tue, 07 Apr 2020 06:27:22 +0000 Received: by mail-wr1-x444.google.com with SMTP id j17so2402146wru.13 for ; Mon, 06 Apr 2020 23:27:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20150623.gappssmtp.com; s=20150623; h=message-id:subject:from:to:date:in-reply-to:references:user-agent :mime-version:content-transfer-encoding; bh=A9BCErHYzVKraa36e1+fCWubj3/4PaXr7Qdtes2UoQ4=; b=WwECG3KcYsyqt1RvM+ys0hEIK6Hv0De2uoqPKNcFEPwm9zbv60MnDtw9bx1305GXu7 Pxy3g1wTuoiUDnUgc36YU8FJWQHQCwgTShf0m0/Ts1vDlRZNjbqxPFInv+5i87Q9dT33 O05FKEyhZ1i+8h4JmfccdQocHtwsTHLBkgIhJumZYCH3A64T6SvJufQ08IDZ7QQjy+XM On3fAGsWc9pkrsvbn9csRY9Pfk8d2oisQ3ykhE0lYUqKdcjT9CROrR6hBxld39cgYKxv vGzWJ1QUo6q0ZOV1aqEAj3rI4eBLri17tsrFj5R48LfqWFoTbfOqAwqSekgefrFqOIuc 9mmg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:subject:from:to:date:in-reply-to :references:user-agent:mime-version:content-transfer-encoding; bh=A9BCErHYzVKraa36e1+fCWubj3/4PaXr7Qdtes2UoQ4=; b=qckyfIpIYusdmcATwFo5uwGKbBkGmQ8Q4Zqy89/PP0ZzY4e0NA9aaYoVZDhKJgwkHr mvirNH49j84BntGgeeuAWYZrQR2SHJvCABsbFKTXimhUCgvbgOFJk4ypVo+KjyUziLM/ gTSJTclCyKEnDrcz8JbC21ajiTg+EGVwtrSNkxUAWzAzOgJKBQVMJhKp0x7uO5Vysenj oSHNPaebcMqI8QBa+Ca1w7t6qpWlQp11YOJGH/qapw8WxuipG7YYFmncW1eOGvOvO0c6 ByOz+SWqArsQGKwj1TwMDi9mGzcMYVWYJmdjR5T+i6WHvKOl7JnhHtXhmZYbYKIGHxmC gpHA== X-Gm-Message-State: AGi0PuYbal2cDY170ULUcXnWm11blsoNM1SDZ136mfeS+n+pleod4hDS ctyf/BbBV5/P+1lV0oZH6b5t6A== X-Google-Smtp-Source: APiQypJqgAUtGt7D8pSjnU9uImbGFGTrFaOX5b20/GAn/1MXovC2v6XPaQDznYZPpmkeUkXb5LQHVA== X-Received: by 2002:adf:e942:: with SMTP id m2mr904635wrn.364.1586240838407; Mon, 06 Apr 2020 23:27:18 -0700 (PDT) Received: from dynamic-eo9lc8043u2qr9u6l-pd01.res.v6.highway.a1.net (dynamic-eo9lc8043u2qr9u6l-pd01.res.v6.highway.a1.net. [2001:871:60:9a62:6597:76d3:1e4c:440d]) by smtp.gmail.com with ESMTPSA id c20sm994189wmd.36.2020.04.06.23.27.17 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 06 Apr 2020 23:27:17 -0700 (PDT) Message-ID: Subject: Re: Significance of backend_xmin in pg_stat_activity view From: Laurenz Albe To: postgann2020 s , pgsql-admin@postgresql.org Date: Tue, 07 Apr 2020 08:27:16 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) MIME-Version: 1.0 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Mon, 2020-04-06 at 23:11 +0530, postgann2020 s wrote: > Could someone please explain what is the significance of backend_xmin in pg_stat_activity view. > > Is xmin same as backend_xmin? It is the backend's xmin horizon. That means that it is the oldest transaction ID whose effects may not be visible to the transaction running in the backend. Since transaction IDs are stored in each row to determine its visibility, the minimum of the "backend_xmin" of all backends determines the cut-off point beyond which all backends will agree on the visibility of tuples. This is for example relevant for VACUUM: it cannot remove any dead tuples that contain a transaction ID that is not older than any backend's "backend_xmin" (unless the tuple is "frozen", but that leads too far). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com