From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net>, Davin Shearer <davin(at)apache(dot)org>, PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: Emitting JSON to file using COPY TO |
Date: | 2023-12-03 17:11:34 |
Message-ID: | f875d187-b9a8-4def-896c-a399d05a8723@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On 12/3/23 11:03, Joe Conway wrote:
> From your earlier post, regarding constructing the aggregate -- not
> extensive testing but one data point:
> 8<--------------------------
> test=# copy foo to '/tmp/buf' (format json, force_array);
> COPY 10000000
> Time: 36353.153 ms (00:36.353)
> test=# copy (select json_agg(foo) from foo) to '/tmp/buf';
> COPY 1
> Time: 46835.238 ms (00:46.835)
> 8<--------------------------
Also if the table is large enough, the aggregate method is not even
feasible whereas the COPY TO method works:
8<--------------------------
test=# select count(*) from foo;
count
----------
20000000
(1 row)
test=# copy (select json_agg(foo) from foo) to '/tmp/buf';
ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 1073741822 bytes by 1
more bytes.
test=# copy foo to '/tmp/buf' (format json, force_array);
COPY 20000000
8<--------------------------
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2023-12-03 19:24:53 | Re: Emitting JSON to file using COPY TO |
Previous Message | Joe Conway | 2023-12-03 16:03:14 | Re: Emitting JSON to file using COPY TO |
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2023-12-03 17:52:12 | Re: logical decoding and replication of sequences, take 2 |
Previous Message | Tomas Vondra | 2023-12-03 16:46:03 | Re: Parallel CREATE INDEX for BRIN indexes |