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 1ipEpN-0006iZ-2x for pgsql-admin@arkaria.postgresql.org; Wed, 08 Jan 2020 17:09:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ipEpL-0002e0-Hg for pgsql-admin@arkaria.postgresql.org; Wed, 08 Jan 2020 17:09:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ipEpL-0002ds-3C for pgsql-admin@lists.postgresql.org; Wed, 08 Jan 2020 17:09:03 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ipEpG-0005Ki-3p for pgsql-admin@lists.postgresql.org; Wed, 08 Jan 2020 17:09:01 +0000 Received: by mail-oi1-x231.google.com with SMTP id l9so3278654oii.5 for ; Wed, 08 Jan 2020 09:08:57 -0800 (PST) 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-transfer-encoding:content-language; bh=RJwj7qR7tViMVRXSo7g9h45RU8PpGGw3VJtVbMGwYq8=; b=rwP2d8+EurDw5eJECKMNswfvc0kRrom/Fn05/bDqpwymRWaUkSKy/QZzSwu5Go3QA9 4dPzlBm+WtAuFVmWUVGMij2cHcMskJQayUPnJrUeihvjcHZsCC82xx61vqSvQ4BBbBnf V5LocwM22G0kQv88BolTOLDH7Jiil7QBbKDpmjPZUo7psl41gUsilZCTgNrJm6NN06Oa /806MRe+HHAD20L+wjCb8rW5iQ5G6HLwL6zoWzVke8GeWo49gl1Eixn5MAALIvFNY9Kt PZD0DpLrc6GyCDxog7pO/PXZT+ZSmOIoF877y/YqzJTfa2H1Yyq9A1xWprnSCXrcVZXV IO/w== 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-transfer-encoding :content-language; bh=RJwj7qR7tViMVRXSo7g9h45RU8PpGGw3VJtVbMGwYq8=; b=XYaxxYJgbFL/zcQDWKo1DgQiAJvFam9LbXlRp1w8MV5TDJMTgre39jy0e/ZBe+4xeO 9VMq1barHFi7C9jttl99I9YJZGKFx9IR7TtQZraJpRCIHza/5caN4wehZI4N37zYRZKp KL/kB3xOaO2VReBjnLZiJ+7PCkJnM4WQGu2zdnNDOllMFIOQ83gdFe/Ma+ysZb2GXbHg m3QM1QN+Hgcn0k9a7I0r7tkt+XRwq/FsDj9CICTLG7+pWWRJNUy4X+E0V7UfYzP/R7ui EhQZTSVvd2V1TVcoP3hY/GNpE91W7aSXQT2oRfrh/et3QxfMlZJRRP/5KBQLtqkJ3LfN hLww== X-Gm-Message-State: APjAAAXz0DlBBB2ZdimpZRB0cLYP0Q633Wdeovy3105WBgl53nnsGhwj 6FdFC/9IHDsaFubYvq2DlVeFIqEPgd0= X-Google-Smtp-Source: APXvYqyBgJyUjKLmFYQHJj2HKhZjq/ns/DyXGcC6WixDjeuwX3gdUTIWB9MYfCmhqfdA8TWfx0BcYA== X-Received: by 2002:aca:6108:: with SMTP id v8mr3738257oib.96.1578503337127; Wed, 08 Jan 2020 09:08:57 -0800 (PST) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id l23sm1288286otf.23.2020.01.08.09.08.56 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 08 Jan 2020 09:08:56 -0800 (PST) Subject: Re: COPY from STDIN vs file with large CSVs To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: Date: Wed, 8 Jan 2020 11:08:56 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 1/8/20 10:54 AM, Wells Oliver wrote: > I have a CSV that's ~30GB. Some 400m rows. Would there be a meaningful > performance difference to run COPY from STDIN using: cat f.csv | psql > "COPY .. FROM STDIN WITH CSV" versus just doing "COPY ... FROM 'f.csv' > WITH CSV"? > > Thanks. It took about four hours to copy one and I felt that was a little > much. catting the file starts another process, and opens a pipe.  That can't be faster than "COPY ... FROM ... WITH CSV". pg_bulkload (which might be in your repository) is probably what you really want. -- Angular momentum makes the world go 'round.