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 1pc8y3-000056-Rv for pgsql-general@arkaria.postgresql.org; Tue, 14 Mar 2023 18:01: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 1pc8y2-00011W-Pg for pgsql-general@arkaria.postgresql.org; Tue, 14 Mar 2023 18:01:46 +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 1pc8y2-00011M-8Q for pgsql-general@lists.postgresql.org; Tue, 14 Mar 2023 18:01:46 +0000 Received: from outbound2r.ore.mailhop.org ([54.200.129.228]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pc8xz-0003c3-QE for pgsql-general@lists.postgresql.org; Tue, 14 Mar 2023 18:01:45 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1678816903; cv=none; d=outbound.mailhop.org; s=arc-outbound20181012; b=EhrxiPFhNqcX/Kbr+gdaJ3zW93vNFBxNTeRGeWeHrMgvKRSt7+G15EbIW/bxFjk9kr/LLaJNL3Gx1 WyI0uNTjq/Fkt4MzUn0eRvqcJduK+zk0tHkd5WzDIlyNDLUHlKez4kcbFbXSsE+S4bJ0CGl1uvKobg O5cTPFnC//MZQrnJSMWGoUV+yCAOAwqJkppglA4hNMNBfrfsCZB3RX/PScmEe6I6rzvsK0DLzeKGh0 9h4HmfwG94S4iy5FKygzxDSLLEJlyIZQZkG352pHZgFAfBrUT5ksadX/bvg+CuBbOI+O4h3a9TcGb0 shuYMfj9xO8dcqyvdFGdXLwH7Xd/Jbg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=outbound.mailhop.org; s=arc-outbound20181012; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:dkim-signature:dkim-signature:dkim-signature:from; bh=kP0fI7cHBdyW7gxMw8iaSoj5PgSEs+Z/yp/Uh77WN2U=; b=a9fsdkJQiGsSkpCWtcuQCSz9RTXk1mUNz7jXc5HDcc/T9r69IXSe4KiU4LFdA0lk7k/wFlj4WK6KJ uZfPb/3SDRIOE3xIRqlZ2asnoBLLKgyfhoUUTivgL4z/XZ0u/vioS7X1CF0J87iwAEUERR2zUfgkW+ bWQGlHHpvN8tIkDiGoRKuev2fhalKrzxYvn7Yo73BJWPuHQXXoi8Dm+Gt/bMvXpybYRpxD8wICBU5m 7Asl41D2l8jsOBJkmTET2lx3M6K7fJEITPqfa7qQlwALXXWdJ1kXkQw0LZAEZY20Tj8GB2iD46T7TQ JvGxlYr1yrChMz79mbTvpkG4Y3ZRGVg== ARC-Authentication-Results: i=1; outbound4.ore.mailhop.org; spf=pass smtp.mailfrom=lists.simkin.ca smtp.remote-ip=172.105.109.24; dkim=pass header.d=lists.simkin.ca header.s=athena header.a=rsa-sha256 header.b=Yc1OHUya; dmarc=pass header.from=lists.simkin.ca; arc=none header.oldest-pass=0; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lists.simkin.ca; s=duo-1610662143981-d3c4c825; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:from; bh=kP0fI7cHBdyW7gxMw8iaSoj5PgSEs+Z/yp/Uh77WN2U=; b=nRcWQg00n4hmrBaeGy0/Q6Rs1ziyUty/jbOB7MYKqtnypIKbrHYffBqUN2IL3i/FBEVVbHv+nLFD3 DTDf+wF8C/r8XHPjhVCHkANxvagZG6R0vAtNB+ysevvg88lpQohSJ7uXe2bQrxFmap0u5i5Cv5zdf+ /2AtHjhguHiuS9vw= DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outbound.mailhop.org; s=dkim-high; h=mime-version:content-type:references:in-reply-to:date:to:from:subject: message-id:from; bh=kP0fI7cHBdyW7gxMw8iaSoj5PgSEs+Z/yp/Uh77WN2U=; b=W2xuFZJj7Rf9H9UoN4Y69IH+k+ck0ro+txkaD003gNUO5i0ISkBsN8p4soV+V7elZ/oIAvK0Nbi20 xuFraXVvBAnD+krem4WL7RAtap2p4ySQgWESk8Eu8zLVZm/XqsRu8FbWSCzUvYsOwqa+/HhCEQRb4c dycCEyikyM/Le83M+k9TV6f1fp4WPvfGwAxLXqQ+I6MsLttPN97qx2PfeJx54Y01u8p2k7O1NZ5wH9 xwuiM+nMAgA5j008VsvJnYldvOIWfk3qLoPQywpcqacnUZMdkfECGfzief3GRGXPz+ksCPACqVgXMp X/w1lwWy9m7QSZWSGOf0KVUmwTOoapw== X-Originating-IP: 172.105.109.24 X-MHO-RoutePath: c2lta2lu X-MHO-User: 4557c109-c292-11ed-9e7d-95702c074b68 X-Report-Abuse-To: https://support.duocircle.com/support/solutions/articles/5000540958-duocircle-standard-smtp-abuse-information X-Mail-Handler: DuoCircle Outbound SMTP Received: from athena.simkin.ca (athena.simkin.ca [172.105.109.24]) by outbound4.ore.mailhop.org (Halon) with ESMTPSA id 4557c109-c292-11ed-9e7d-95702c074b68; Tue, 14 Mar 2023 18:01:41 +0000 (UTC) Received: from skynet.simkin.ca (d154-20-71-66.bchsia.telus.net [154.20.71.66]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (No client certificate requested) by athena.simkin.ca (Postfix) with ESMTPSA id 1813DC6BB7CD for ; Tue, 14 Mar 2023 11:01:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=lists.simkin.ca; s=athena; t=1678816894; bh=kP0fI7cHBdyW7gxMw8iaSoj5PgSEs+Z/yp/Uh77WN2U=; h=Subject:From:To:Date:From:To:Subject:Date:Reply-To; b=Yc1OHUyalQDBpCiENC+w4sBCKNxU9ZHaEM4d2me+n7eu14IIG84yDThh2ty87jnOy u0WbwiUCkX/50SnIZP228ECl/OJcK+7P1OR6PaGNXsE1X4XOwH3xJldMO04nDprwGY FhkfJctw06ET756NSF0WaP/2Ry6HaRDkH9djYIRf0H/8EQ0sPqYDIvI33TjjCy0yFk gXfSiyghksNzcQShWWbhMsz6pw/KVHZa9fnX+l/tCUyBDURc3qvO/vomGOu1Plb3AW jEeiW7RiazShhODqcWwV5wF74duyodPC4HGWIoxGIRgHBrttmiFpF+4ZmWIkMP7sy9 HrksKPoQzZfsw== Received: from skynet.simkin.ca (skynet.simkin.ca [IPv6:::1]) by skynet.simkin.ca (Postfix) with ESMTP id 9DFCA22D1C88D for ; Tue, 14 Mar 2023 11:01:35 -0700 (PDT) Message-ID: <20e62f76c0b6e98c390fe020a6da64f072e343be.camel@lists.simkin.ca> Subject: Re: Issues Scaling Postgres Concurrency From: Alan Hodgson To: pgsql-general@lists.postgresql.org Date: Tue, 14 Mar 2023 11:01:35 -0700 In-Reply-To: References: Content-Type: multipart/alternative; boundary="=-wDkcHLE2J/ev8v1VhaAV" X-PGP-Key: fp="EF82 6F8D A3BA A096 FFA3 05F2 A7A0 40A5 34E5 AA0B"; id="0x34E5AA0B"; get= User-Agent: Evolution 3.46.3 MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-wDkcHLE2J/ev8v1VhaAV Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, 2023-03-13 at 12:24 -0400, Harrison Borges wrote: > Hello everyone. >=20 > I=E2=80=99m running into severe performance problems with Postgres as I > increase the number of concurrent requests against my backend. I=E2=80=99= ve > identified that the bottleneck is Postgres, and to simplify the > test case, I created an endpoint that only does a count query on a > table with ~500k rows. At 5 concurrent users, the response time was > 33ms, at 10 users it was 60ms, and at 20 users it was 120ms. I'm no expert on high concurrency, but for something this simple I'd expect that you're just CPU bottlenecked. Count in PostgreSQL actually has to read all the rows in the table. And yeah you can't do too many of them at the same time. --=-wDkcHLE2J/ev8v1VhaAV Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable
On Mon, 2023-03-13 at 12:24 -0400, Harrison B= orges wrote:
Hello everyon= e.

I=E2=80=99m running into severe performance problems = with Postgres as I increase the number of concurrent requests against my ba= ckend. I=E2=80=99ve identified that the bottleneck is Postgres, and to simp= lify the test case, I created an endpoint that only does a count query on a= table with ~500k rows. At 5 concurrent users, the response time was 33ms, = at 10 users it was 60ms, and at 20 users it was 120ms.

I'm no expert on high concurrency, but for somethin= g this simple I'd expect that you're just CPU bottlenecked. Count in Postgr= eSQL actually has to read all the rows in the table. And yeah you can't do = too many of them at the same time.
--=-wDkcHLE2J/ev8v1VhaAV--