Performance appending to an array column

From: Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Performance appending to an array column
Date: 2017-09-21 20:06:06
Message-ID: CA+renyVoVqtcQ+KvxzABEeYuAJXrah90432RxEJCPrOFOxLU7Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm considering a table structure where I'd be continuously appending
to long arrays of floats (10 million elements or more). Keeping the
data in arrays gives me much faster SELECT performance vs keeping it
in millions of rows.

But since these arrays keep growing, I'm wondering about the UPDATE
performance. I was reading this commit message about improving
performance of *overwriting* individual array elements, and I was
wondering if there is anything similar for growing an array column?:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1dc5ebc9077ab742

Is there a faster way to append to an array than just this?:

UPDATE measurements
SET vals = vals || ARRAY[5.0, 4.2, 9.9]::float[]
;

Thanks!
Paul

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-09-21 20:25:32 Re: Performance appending to an array column
Previous Message Merlin Moncure 2017-09-21 19:29:08 Re: a JOIN to a VIEW seems slow