From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: SV: Insertion time is very high for inserting data in postgres |
Date: | 2021-02-11 13:51:54 |
Message-ID: | 20210211135154.GA1603@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2021-02-10 11:10:41 +0000, Niels Jespersen wrote:
> >Fra: prachi surangalikar <surangalikarprachi100(at)gmail(dot)com>
> >We are using Postgres 12.2.1 for fetching per minute data for about 25
> machines but running parallely via a single thread in python.
> >But suddenly the insertion time has increased to a very high level, about 30
>> second for one machine.
>
> >We are in so much problem as the data fetching is becoming slow.
>
> >if anyone could help us to solve this problem it would be of great help to us.
>
> Get your data into a Text.IO memory structure and then use copy https://
> www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from
>
>
>
> This is THE way of high-performant inserts using Postgres.
True, but Prachi wrote that the insert times "suddenly ... increased to a
very high level". It's better to investigate what went wrong than to
blindly make some changes to the code.
As a first measure I would at least turn on statement logging and/or
pg_stat_statements to see which statements are slow, and then
investigate the slow statements further. auto_explain might also be
useful.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2021-02-11 14:03:41 | Re: Which partition scheme makes sense for my time based IoT-datagrams? |
Previous Message | Laurenz Albe | 2021-02-11 12:27:07 | Re: Assistance on PostgreSQL DB |