Subject: Performance

From: Anex Hul <[email-masked]>
Date: Sun, 15 Dec 2024 19:22:13 -0600

Hello everyone,

Testing 100 million records data import from Azure blob storage to Azure
postgresql. I did run the test 5 times and the time it took keep increasing
for each run.
Is there know justification for this linear increment of the time it took
for same size of data?

---

From: Ron Johnson <[email-masked]>
Date: Sun, 15 Dec 2024 21:14:53 -0500

> Is there know justification for this linear increment of the time it took

1. What version of PG is it?  ("SELECT VERSION();" should tell you.)
2. Are you truncating the table after each test run, or deleting all
records, or appending?
3. Is the blob data stored in BYTEA column data, or are you using the
(discouraged) "Large Objects"?
4. How are you loading the blob data?

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Rui DeSousa <[email-masked]>
Date: Sun, 15 Dec 2024 23:22:02 -0500

> On Dec 15, 2024, at 8:22 PM, Anex Hul <[email-masked]> wrote:
> 
> Hello everyone,
> 
> Testing 100 million records data import from Azure blob storage to Azure postgresql. I did run the test 5 times and the time it took keep increasing for each run. 
> Is there know justification for this linear increment of the time it took for same size of data?

Check you I/O quotas; you might have hit quota limits and being throttled.

---

From: Anex Hul <[email-masked]>
Date: Mon, 16 Dec 2024 08:05:12 -0600

Thank you all for your response.

Show quoted text

PG Version 16

created new schema for each run.

Blob storage

used the Import data using a COPY statement, followed this doc

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pg-azure-storage?tabs=portal-01%2Cportal-02%2Cportal-03%2Cportal-04%2Cportal-05

---

From: Ron Johnson <[email-masked]>
Date: Mon, 16 Dec 2024 09:20:17 -0500

On Mon, Dec 16, 2024 at 9:05 AM Anex Hul <[email-masked]> wrote:
[snip]

> (discouraged) "Large Objects"?
Postgresql does not know what "Blob storage" means.

> https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pg-azure-storage?tabs=portal-01%2Cportal-02%2Cportal-03%2Cportal-04%2Cportal-05
If you're using a Microsoft extension, then you'd better ask Microsoft.