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 1q4DZo-0006TZ-SI for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 04:36: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 1q4DZn-0007d0-A1 for pgsql-general@arkaria.postgresql.org; Wed, 31 May 2023 04:36: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 1q4DZm-0007cr-Vo for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 04:36:46 +0000 Received: from mail-pf1-x42a.google.com ([2607:f8b0:4864:20::42a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1q4DZk-002pdh-Fm for pgsql-general@lists.postgresql.org; Wed, 31 May 2023 04:36:45 +0000 Received: by mail-pf1-x42a.google.com with SMTP id d2e1a72fcca58-64d5b4c400fso5938532b3a.1 for ; Tue, 30 May 2023 21:36:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1685507803; x=1688099803; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=P4skzs31KtITGuckK7gzslYHwDYNa50GBIjS/DFSCvo=; b=j3XeMOqrMwtVpH8GLKkocM2PXemt+HN2bkd3RF9X/3T8x0sw08oHny3cS+XUFOGeC6 6IcbhxvBswlDsWjbAcE00yKDDUI2R6XPaLFJBF26w+xQT2IHV1gvhA2TzI9teylZ//Ny dXIIxXvqOmNeETgc7JHUA9HaUxCU9k/dnv1PFS7YsMzRg26o68FxTeRiucP5gGpxyt1y /Cy4EgC4RpezulE/hD2DtpBuFwTSwhHJsJqEpWOp2FGPSgB/L3pNnqIIiyLmuIu1AC2U lpYE3EvX44J5An7JI2HPP7vKEXI7kZ1yUyv5PcgVQw1vr8FyD+i15DJ+11MdkQ0XXZPz OVzQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1685507803; x=1688099803; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=P4skzs31KtITGuckK7gzslYHwDYNa50GBIjS/DFSCvo=; b=d6VZTnHLJgp9dq7IPO5V5yZ01ag1xMcPBYpMLXTVHMPmZGdxki6JhkFFnM+dOZ7Vqn DjPZBSAIjXWNxPrPENqrekoTG4NOQU4bpdxZV+VI2X3cHX5RiPmwRV8ax7toLruXOPl9 GvbIi5QU+GEwKX54YUdBDKQuAhDVIhxJJrZmAA70lvkQQlhmOFLkdcPateHhdh4i2WHi yV9foAuyFCrjSzX5SgSk7crzYlvznUut9w0oWnUtGXGWSONzN8Q1NzIbJZI1HG3CKua2 hT3vlFHJRICx+6j4bi+mjYdcIrXI4bE8upBVR0nAsstey+JVzkA8Fj9ZyMtb+YniUAH3 4UKw== X-Gm-Message-State: AC+VfDyQ8Of0dWffm3Rl7Qbu/8iuLMFnd1wnDHad5od3+A1oCHpci6Gy mrxQpQJ4aGnYE1uk00T2QBENoDjEXz0= X-Google-Smtp-Source: ACHHUZ4c8oexzKrpkU52GUhIy/Orjjr8alO7rtvYu3r0FLg1TmMPeomtgsuaY6zQSkGwCT9YRLXr4w== X-Received: by 2002:a05:6a00:c82:b0:649:dd0d:69d1 with SMTP id a2-20020a056a000c8200b00649dd0d69d1mr5385904pfv.31.1685507802374; Tue, 30 May 2023 21:36:42 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:bbfc:35de:25c9:68c4? ([2601:681:5a00:f790:bbfc:35de:25c9:68c4]) by smtp.gmail.com with ESMTPSA id p18-20020aa78612000000b0064d3bccca8bsm2430600pfn.9.2023.05.30.21.36.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 30 May 2023 21:36:41 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------QCH71PC4KUX1VjHiHqYyiVeG" Message-ID: <67ffdd7a-8870-c560-d5e4-c53f237f6ae3@gmail.com> Date: Tue, 30 May 2023 22:36:40 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: speed up full table scan using psql Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------QCH71PC4KUX1VjHiHqYyiVeG Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 5/30/23 22:25, Lian Jiang wrote: > hi, > > I am using psql to periodically dump the postgres tables into json > files which are imported into snowflake. For large tables (e.g. 70M > rows), it takes hours for psql to complete. Using spark to read the > postgres table seems not to work as the postgres read only replication > is the bottleneck so spark cluster never uses >1 worker node and the > working node timeout or out of memory. > > Will vertical scaling the postgres db speed up psql? Or any thread > related parameter of psql can help? Thanks for any hints. > > Regards > Lian Have you looked into COPY command?  Or CopyManager java class? --------------QCH71PC4KUX1VjHiHqYyiVeG Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 5/30/23 22:25, Lian Jiang wrote:
hi,

I am using psql to periodically dump the postgres tables into json files which are imported into snowflake. For large tables (e.g. 70M rows), it takes hours for psql to complete. Using spark to read the postgres table seems not to work as the postgres read only replication is the bottleneck so spark cluster never uses >1 worker node and the working node timeout or out of memory.

Will vertical scaling the postgres db speed up psql? Or any thread related parameter of psql can help? Thanks for any hints.

Regards
Lian
Have you looked into COPY command?  Or CopyManager java class?

--------------QCH71PC4KUX1VjHiHqYyiVeG--