Inserts and bad performance

[Godfrin, Philippe E]

Greetings
I am inserting a large number of rows, 5,10, 15 million. The python code 
commits every 5000 inserts. The table has partitioned children.

At first, when there were a low number of rows inserted, the inserts would run 
at a good clip - 30 - 50K inserts per second. Now, after inserting oh say 1.5 
Billion rows, the insert rate has dropped to around 5000 inserts per second. I 
dropped the unique index , rebuilt the other indexes and no change. The 
instance is 16 vcpu and 64GB ram.

I'm perplexed, I can't see to find any reason for the slow down...
Thanks,
pg

Phil Godfrin | Database Administration
NOV
NOV US | Engineering Data
9720 Beechnut St | Houston, Texas 77036
M  281.825.2311
E   philippe.godf...@nov.com

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

My apologies for the dearth of details. No on both the cpu and errors. But I do 
believe it is IO related. I just can't find it. 
I thought maybe it was index splitting so I altered the unique index with 
filterfactor=40 and reindexed. No change. 
I then dropped the unique index. No change.
I thought maybe it was checkpoint timeouts, but there was no correlation.
Oddly enough other jobs running concurrently, are also inserting, most likely 
into different partitions, are running about 2x faster than others.

I'm rather perplexed.
pg


-Original Message-
From: Kenneth Marshall  
Sent: Wednesday, November 24, 2021 1:20 PM
To: Godfrin, Philippe E 
Cc: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL] Re: Inserts and bad performance

On Wed, Nov 24, 2021 at 07:15:31PM +0000, Godfrin, Philippe E wrote:
> Greetings
> I am inserting a large number of rows, 5,10, 15 million. The python code 
> commits every 5000 inserts. The table has partitioned children.
> 
> At first, when there were a low number of rows inserted, the inserts would 
> run at a good clip - 30 - 50K inserts per second. Now, after inserting oh say 
> 1.5 Billion rows, the insert rate has dropped to around 5000 inserts per 
> second. I dropped the unique index , rebuilt the other indexes and no change. 
> The instance is 16 vcpu and 64GB ram.
> 
> I'm perplexed, I can't see to find any reason for the slow down...
> Thanks,
> pg

Hi,

With not much information, it may be I/O related. CPU and RAM cannot fix that 
once items need to be written to disk. Are there any errors in the logs or CPUs 
maxxed out?

Regards,
Ken

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Hi Tom. Good point about the index paging out of the buffer. I did that and no 
change. I do have the shared buffers at 40GB, so there's a good bit there, but 
I also did all those things on the page you referred, except for using copy. At 
this point the data has not been scrubbed, so I'm trapping data errors and 
duplicates. I am curios though, as sidebar, why copy is considered faster than 
inserts. I was unable to get COPY faster than around 25K inserts a second 
(pretty fast anyway). Frankly, initially I was running 3 concurrent insert jobs 
and getting 90K ins/sec ! but after a certain number of records, the speed just 
dropped off.

pg

From: Tom Lane 
Sent: Wednesday, November 24, 2021 1:32 PM
To: Godfrin, Philippe E 
Cc: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL] Re: Inserts and bad performance

"Godfrin, Philippe E" 
mailto:philippe.godf...@nov.com>> writes:
> I am inserting a large number of rows, 5,10, 15 million. The python code 
> commits every 5000 inserts. The table has partitioned children.
> At first, when there were a low number of rows inserted, the inserts would 
> run at a good clip - 30 - 50K inserts per second. Now, after inserting oh say 
> 1.5 Billion rows, the insert rate has dropped to around 5000 inserts per 
> second. I dropped the unique index , rebuilt the other indexes and no change. 
> The instance is 16 vcpu and 64GB ram.

Can you drop the indexes and not rebuild them till after the bulk load is
done? Once the indexes exceed available RAM, insert performance is going
to fall off a cliff, except maybe for indexes that are receiving purely
sequential inserts (so that only the right end of the index gets touched).

Also see

https://www.postgresql.org/docs/current/populate.html<https://www.postgresql.org/docs/current/populate.html>

regards, tom lane

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

The notion of COPY blocks and asynchronously is very interesting

From: Gavin Roy 
Sent: Wednesday, November 24, 2021 1:50 PM
To: Godfrin, Philippe E 
Cc: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL] Re: Inserts and bad performance

On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E 
mailto:philippe.godf...@nov.com>> wrote:GreetingsI am 
inserting a large number of rows, 5,10, 15 million. The python code commits 
every 5000 inserts. The table has partitioned children.On the Python client 
side, if you're usi ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=Wr0OlPCq7rNfiTEZXZaOpA%3D%3DjZOFbItn0C5RxyoO%2BmXR2j9FVv%2BWzhFJYReW7ql2zdXPDV40mdS1DQpYOmBt2Oxoehf1bVTmKoJUhNrZa2mIi%2FQMp8dj%2B9IMl1T8FzHRYvXB5us%2BUoZgXp%2BbwqXCXYEsxTG8iZj8bV7I6oscimbLg1XRT039VTqG5EDwXI%2FlGEJpWpx1EVzIcXHenq8DwZLgCSkhj2TFk9HkbexFBWJa3mZxYASZ%2BLx4zI5WJuTtLUGhLcQi5YtrFmxK%2FhegJTn02LIFkfp7RuqaPEJ5b%2BmvbJ8AsY1UH99HbU1dTHOFyQrKRwBXKk1knkZ9ymsDQl7VgWH%2FDg%2FTpgX0URnz8tqnbDANTpMEMJZcEvbETRrqvBMlBcdZlbm2V7LiLwDiQgK3XxvyQpn2CU%2F6QxeZAZslAsvTt%2F3bWNEXmOgoEabPh96vDxjRSdEvVvVGy%2BUPtP36YKLarzhLq1nwAah0bPBgC2XSNlAi02os5URexqotMZjX5vlxMsfPVpncwWUj61%2FFTbVU04xkn2%2FuBm8Izm5oQFsq9iGBQENILj8LakGpFNY5FH1DJuKMEUba91X6mzcy4w2Ez1bPhdWCPFTy9ToiOt7F5vC4AoMD%2FzsxoJCOWQtq9OZMzqVSPaz19AicZdgiGm%2B98bZtbGBZKdIXNiM9YLLKWS9%2FxPaDhL%2FZYkVNUjo%3D>


On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E 
mailto:philippe.godf...@nov.com>> wrote:
Greetings
I am inserting a large number of rows, 5,10, 15 million. The python code 
commits every 5000 inserts. The table has partitioned children.

On the Python client side, if you're using psycopg, you should consider using 
using COPY instead of INSERT if you're not:

https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy<https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy>

And if using psycopg2, execute_batch might be of value:

https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch<https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch>

Regards,

Gavin

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Excellent idea David, silly me, I didn't think of that. For the other questions:
>How many partitions? 
14
>How many rows do they have when performance is slowing considerably? 
Not sure, maybe on the low millions
>Does this table get many updates or is it insert only? 
insert
>What version of PostgreSQL? 
13
>Are the inserts randomly distributed among the partitions or targeting one or 
>a few partitions? 
Sequentially one partition at a time, so each set of runs is inserting across 
each part.
>Are you able to capture an example and run it in a transaction with explain 
>(analyze, buffers, verbose) and then rollback?
Yes, I'm looking into that
pg


-Original Message-
From: David Rowley  
Sent: Wednesday, November 24, 2021 7:13 PM
To: Godfrin, Philippe E 
Cc: Tom Lane ; pgsql-general@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Inserts and bad performance

On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E  
wrote:
> Hi Tom. Good point about the index paging out of the buffer. I did that and 
> no change. I do have the shared buffers at 40GB, so there’s a good bit there, 
> but I also did all those things on the page you referred, except for using 
> copy. At this point the data has not been scrubbed, so I’m trapping data 
> errors and duplicates. I am curios though, as sidebar, why copy is considered 
> faster than inserts. I was unable to get COPY faster than around 25K inserts 
> a second (pretty fast anyway). Frankly, initially I was running 3 concurrent 
> insert jobs and getting 90K ins/sec ! but after a certain number of records, 
> the speed just dropped off.

EXPLAIN (ANALYZE, BUFFERS) works with INSERTs. You just need to be aware that 
using ANALYZE will perform the actual insert too. So you might want to use 
BEGIN; and ROLLBACK; if it's not data that you want to keep.

SET track_io_timing = on; might help you too.

David

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Hi Gavin – thanks I hadn’t realized that about psychopg. I’m on the earlier 
version, so I can’t use what you recommended at this point. But I did use 
copy_expert.

Interestingly enough the performance of the copy statement is only slightly 
better than the insert, as I was running inserts with 5000 values clauses. In 
the end, the current config couldn’t keep up with the WAL creation, so I turned 
all that off. But still no perf gains. I also turned off fsync and set the 
kernel settings to 10% and 98% for dirty pages…

I wonder if there’s a better load product than COPY But I’d still like to 
know what separates COPY from bulk inserts…
pf

From: Gavin Roy 
Sent: Wednesday, November 24, 2021 1:50 PM
To: Godfrin, Philippe E 
Cc: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL] Re: Inserts and bad performance

On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E 
mailto:philippe.godf...@nov.com>> wrote:GreetingsI am 
inserting a large number of rows, 5,10, 15 million. The python code commits 
every 5000 inserts. The table has partitioned children.On the Python client 
side, if you're usi ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=Wr0OlPCq7rNfiTEZXZaOpA%3D%3DjZOFbItn0C5RxyoO%2BmXR2j9FVv%2BWzhFJYReW7ql2zdXPDV40mdS1DQpYOmBt2Oxoehf1bVTmKoJUhNrZa2mIi%2FQMp8dj%2B9IMl1T8FzHRYvXB5us%2BUoZgXp%2BbwqXCXYEsxTG8iZj8bV7I6oscimbLg1XRT039VTqG5EDwXI%2FlGEJpWpx1EVzIcXHenq8DwZLgCSkhj2TFk9HkbexFBWJa3mZxYASZ%2BLx4zI5WJuTtLUGhLcQi5YtrFmxK%2FhegJTn02LIFkfp7RuqaPEJ5b%2BmvbJ8AsY1UH99HbU1dTHOFyQrKRwBXKk1knkZ9ymsDQl7VgWH%2FDg%2FTpgX0URnz8tqnbDANTpMEMJZcEvbETRrqvBMlBcdZlbm2V7LiLwDiQgK3XxvyQpn2CU%2F6QxeZAZslAsvTt%2F3bWNEXmOgoEabPh96vDxjRSdEvVvVGy%2BUPtP36YKLarzhLq1nwAah0bPBgC2XSNlAi02os5URexqotMZjX5vlxMsfPVpncwWUj61%2FFTbVU04xkn2%2FuBm8Izm5oQFsq9iGBQENILj8LakGpFNY5FH1DJuKMEUba91X6mzcy4w2Ez1bPhdWCPFTy9ToiOt7F5vC4AoMD%2FzsxoJCOWQtq9OZMzqVSPaz19AicZdgiGm%2B98bZtbGBZKdIXNiM9YLLKWS9%2FxPaDhL%2FZYkVNUjo%3D>


On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E 
mailto:philippe.godf...@nov.com>> wrote:
Greetings
I am inserting a large number of rows, 5,10, 15 million. The python code 
commits every 5000 inserts. The table has partitioned children.

On the Python client side, if you're using psycopg, you should consider using 
using COPY instead of INSERT if you're not:

https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy<https://www.psycopg.org/psycopg3/docs/basic/copy.html#copy>

And if using psycopg2, execute_batch might be of value:

https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch<https://www.psycopg.org/docs/extras.html?highlight=insert#psycopg2.extras.execute_batch>

Regards,

Gavin

RE: [EXTERNAL] Re: Inserts and bad performance

[Godfrin, Philippe E]

Right you are sir! I figured that out a few hours ago!
pg

From: Ron 
Sent: Wednesday, November 24, 2021 10:58 PM
To: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL] Re: Inserts and bad performance

On 11/24/21 1:15 PM, Godfrin, Philippe E wrote: [snip] I dropped the unique 
index , rebuilt the other indexes and no change. IMNSHO, this is the worst 
possible approach.  Drop everything except the unique index, and then (if 
possible) sort the input file by the unique key.   That ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ 
‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=MscIZbFcA20cZ6HlLPCzsg%3D%3Dup0ll%2Bu3Q7dCpCHjMRutwBFZuF6FlLMVKqLpzfn8crKDwB3aTItjQ7KgyC%2BQRsD5f8xbGX3jwMuXdtjE8CPSUKv%2Fnjpl7aIQazHJfSlvqupgF%2FVAjvz8Jlq7ZsTWUJt%2BWrXlTYCuU%2BCQOKhfWIIlqoUScpyv3co3gzlBlKNVgoCnnPt3fs8nGUhVxEcDxAJG9wNueH6iVn9jt7IZqlJqkRuTmS%2BLprH8Wpzbiccgs%2B%2Fe761zMa0BtyYusxWyTzOBV%2BlpQ%2FkcIStxNkVj3yVEQrTaZKxvBJ1%2BBjgcdUM3ElooA73KU%2Fw61vomhZ35fg%2BjjYpYuKP9oTWWQUuW%2FWsjpQTX%2BL45OIGuqTNH5fXB8BrPn4Y6b5ov0daaqUQGoAWwU5W5q3ZY6cXp7aUiHpF5GjNsdel3fZrHhxyVjwGII7YfDIjf21rgY9iMePiiqA6Q8VT6f18T8yyhIgcuVYOJ%2BLp3lBxaj%2Fv%2FEjQHbHECjy4LeFjie6WzC%2F4%2BkdBShGp5SA8plTtWPCeY6aaacWMN0RsqNcCXpybFU4smy8v0bb%2FeqOrfR8abX4%2FRB%2BVI5WpHUi0TihiOt%2BviFrXdWNSOopKTokuQp4DJQ2uqqh5tUZtlsXGeemkFCTgwy6WCX2Uemtupx1j4MZWdbuPQisdDvUE0XpSQARjcSjmUdHlWve%2FHd6wsLXc2abfpAW5tYy%2BL>
On 11/24/21 1:15 PM, Godfrin, Philippe E wrote:

[snip]

I dropped the unique index , rebuilt the other indexes and no change.

IMNSHO, this is the worst possible approach.  Drop everything except the unique 
index, and then (if possible) sort the input file by the unique key.   That'll 
increase buffered IO; otherwise, you're bopping all around the filesystem.

Using a bulk loader if possible would increase speeds
--
Angular momentum makes the world go 'round.

RE: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

[Godfrin, Philippe E]

On 2021-12-08 14:44:47 -0500, David Gauthier wrote:
> So far, the tables I have in my DB have relatively low numbers of records 
> (most
> are < 10K, all are < 10M).  Things have been running great in terms of
> performance.  But a project is being brainstormed which may require some 
> tables
> to contain a couple billion records.
[...]
> What else should I be worried about ?
>
> I suspect that part of why things are running really well so far is that the
> relatively small amounts of data in these tables ends up in the DB cache and
> disk I/O is kept at a minimum.  Will that no longer be the case once queries
> start running on these big tables ?

>
>Depends a lot on how good the locality of your queries is. If most read
>only the same parts of the same indexes, those will still be in the
>cache. If they are all over the place or if you have queries which need
>to read large parts of your tables, cache misses will make your
>performance a lot less predictable, yes. That stuff is also hard to
>test, because when you are testing a query twice in a row, the second
>time it will likely hit the cache and be quite fast.
>
>But in my experience the biggest problem with large tables are unstable
>execution plans - for most of the parameters the optimizer will choose
>to use an index, but for some it will erroneously think that a full
>table scan is faster. That can lead to a situation where a query
>normally takes less than a second, but sometimes (seemingly at random)
>it takes several minutes - users will understandably be upset about such
>behaviour. It is in any case a good idea to monitor execution times to
>find such problems (ideally before users complain), but each needs to be
>treated on an individual basis, and sometimes there seems to be no good
>solution.

To the OP, that's is a tall order to answer - basically that's wjhy DBA's still 
have
Jobs...

For Peter I have a question. What exactly causes 'unstable execution plans' ??

Besides not using bind variables, bad statistics, would you elaborate in what 
would
contribute to that instability?

RE: [EXTERNAL] Re: performance expectations for table(s) with 2B recs

[Godfrin, Philippe E]

>
>
>From: Peter J. Holzer hjp-pg...@hjp.at<mailto:hjp-pg...@hjp.at>
>Sent: Friday, December 10, 2021 3:43 PM
>To: 
>pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
>Subject: Re: [EXTERNAL] Re: performance expectations for table(s) with 2B recs
>
>On 2021-12-10 18:04:07 +, Godfrin, Philippe E wrote:
>> >But in my experience the biggest problem with large tables are unstable
>> >execution plans - for most of the parameters the optimizer will choose
>> >to use an index, but for some it will erroneously think that a full
>> >table scan is faster. That can lead to a situation where a query
>> >normally takes less than a second, but sometimes (seemingly at random)
>> >it takes several minutes
>[...]
>> For Peter I have a question. What exactly causes ‘unstable execution plans’ 
>> ??
>>
>> Besides not using bind variables, bad statistics, would you elaborate
>> in what would contribute to that instability?
>
>Not using bind variables and bad statistics are certainly big factors:
>
>On one hand not using bind variables gives a lot more information to the
>optimizer, so it can choose a better plan at run time. On the other hand
>that makes hard to predict what plan it will choose.
>
>Bad statistics come in many flavours: They might just be wrong, that's
>usually easy to fix. More problematic are statistics which just don't
>describe reality very well - they may not show a correlation, causing
>the optimizer to assume that two distributions are independent when they
>really aren't (since PostgreSQL 10 you can create statistics on multiple
>columns which helps in many but not all cases) or not show some other
>peculiarity of the data. Or they may be just so close to a flipping
>point that a small change causes the optimizer to choose a wildly
>different plan.
>
>Another source is dynamically generated SQL. Your application may just
>put together SQL from fragments or it might use something like
>SQLalchemy or an ORM. In any of these cases what looks like one query
>from a user's perspective may really be a whole family of related
>queries - and PostgreSQL will try to find the optimal plan for each of
>them. Which is generally a good thing, but it adds opportunities to mess
>up.
>
>hp
>
>--
>_ | Peter J. Holzer | Story must make more sense than reality.
>|_|_) | |
>| | | h...@hjp.at<mailto:h...@hjp.at> | -- Charles Stross, "Creative writing
>__/ | http://www.hjp.at/ | challenge!"

Good answer Peter, I agree wholeheartedly. I was curious if there was something 
specific to Postgresql .
phil

Numeric data types

[Godfrin, Philippe E]

Greetings Listees (frist time poster!)

Considering the difference between 64 bit and 32 bit numeric datatypes. We have 
source databases that are running 32 bit. They send their data to a larger 
cluster that is running 64 bit. Should there be something special done in order 
to accommodate the difference?
Thanks,
Pg

Phil Godfrin | Database Administrator
NOV
NOV US | Engineering Data
9720 Beechnut St | Houston, Texas 77036
M  281.825.2311
E   philippe.godf...@nov.com
The information contained in this transmission is for the personal and 
confidential use of the individual or entity to which it is addressed. If the 
reader is not the intended recipient, you are hereby notified that any review, 
dissemination, or copying of this communication is strictly prohibited. If you 
have received this transmission in error, please notify the sender immediately.

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

Thanks Bruce, I suppose you mean n32 -> n64 OK, but n64->n32 chance of 32-bit 
overflow...
pg

From: Bruce Momjian 
Sent: Monday, August 31, 2020 11:54 AM
To: Godfrin, Philippe E 
Cc: pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: Numeric data types

On Mon, Aug 31, 2020 at 04:38:05PM +, Godfrin, Philippe E wrote:
> Greetings Listees (frist time poster!)
>
>
>
> Considering the difference between 64 bit and 32 bit numeric datatypes. We 
> have
> source databases that are running 32 bit. They send their data to a larger
> cluster that is running 64 bit. Should there be something special done in 
> order
> to accommodate the difference?

How is the data sent? In almost every case, the translation should work
fine. I think 32-bit overflow should be your only concern here.

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

Fabulous, thanks much.

From: Bruce Momjian 
Sent: Monday, August 31, 2020 4:56 PM
To: Godfrin, Philippe E 
Cc: pgsql-gene...@postgresql.org
Subject: Re: [EXTERNAL] Re: Numeric data types

On Mon, Aug 31, 2020 at 05:32:23PM +, Godfrin, Philippe E wrote:
> Thanks Bruce, I suppose you mean n32 -> n64 OK, but n64->n32 chance of 32-bit
> overflow…

Uh, I was thinking that your 32-bit values could roll over and the other
end would be confused, but I can also see the 64-bit going above the
32-bit range. As long as everything stays < 32-bits, you should be
fine. We don't transfer binary values very often.

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

Frankly, I’m not certain, I believe the developers are using a messaging 
intermediary.
pg

From: Bruce Momjian 
Sent: Monday, August 31, 2020 5:19 PM
To: Godfrin, Philippe E 
Cc: pgsql-gene...@postgresql.org
Subject: Re: [EXTERNAL] Re: Numeric data types

On Mon, Aug 31, 2020 at 10:14:48PM +, Godfrin, Philippe E wrote:
> Fabulous, thanks much.

You still have not told us how you are transfering the data, so we can
be sure.

---

>
> From: Bruce Momjian mailto:br...@momjian.us>>
> Sent: Monday, August 31, 2020 4:56 PM
> To: Godfrin, Philippe E 
> mailto:philippe.godf...@nov.com>>
> Cc: pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>
> Subject: Re: [EXTERNAL] Re: Numeric data types
>
>
>
> On Mon, Aug 31, 2020 at 05:32:23PM +, Godfrin, Philippe E wrote:
> > Thanks Bruce, I suppose you mean n32 -> n64 OK, but n64->n32 chance of 
> > 32-bit
> > overflow…
>
> Uh, I was thinking that your 32-bit values could roll over and the other
> end would be confused, but I can also see the 64-bit going above the
> 32-bit range. As long as everything stays < 32-bits, you should be
> fine. We don't transfer binary values very often.
>
> --
> Bruce Momjian mailto:br...@momjian.us>> 
> https://momjian.us<https://momjian.us>
> EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>
>
> The usefulness of a cup is in its emptiness, Bruce Lee
>

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

RE: [EXTERNAL] Re: Numeric data types

[Godfrin, Philippe E]

Very well, thanks very much.
pg

From: Bruce Momjian 
Sent: Monday, August 31, 2020 5:31 PM
To: Godfrin, Philippe E 
Cc: pgsql-gene...@postgresql.org
Subject: Re: [EXTERNAL] Re: Numeric data types

On Mon, Aug 31, 2020 at 10:20:51PM +, Godfrin, Philippe E wrote:
> Frankly, I’m not certain, I believe the developers are using a messaging
> intermediary.

Oh, well, then you would need to find out if they are transfering the
value via text or binary. It is kind of hard to extract query results
as binary, but it is technically possible with binary COPY or triggers.

---

>
> pg
>
>
>
> From: Bruce Momjian mailto:br...@momjian.us>>
> Sent: Monday, August 31, 2020 5:19 PM
> To: Godfrin, Philippe E 
> mailto:philippe.godf...@nov.com>>
> Cc: pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>
> Subject: Re: [EXTERNAL] Re: Numeric data types
>
>
>
> On Mon, Aug 31, 2020 at 10:14:48PM +, Godfrin, Philippe E wrote:
> > Fabulous, thanks much.
>
> You still have not told us how you are transfering the data, so we can
> be sure.
>
> ---
>
> >
> > From: Bruce Momjian mailto:br...@momjian.us>>
> > Sent: Monday, August 31, 2020 4:56 PM
> > To: Godfrin, Philippe E 
> > mailto:philippe.godf...@nov.com>>
> > Cc: pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>
> > Subject: Re: [EXTERNAL] Re: Numeric data types
> >
> >
> >
> > On Mon, Aug 31, 2020 at 05:32:23PM +, Godfrin, Philippe E wrote:
> > > Thanks Bruce, I suppose you mean n32 -> n64 OK, but n64->n32 chance of
> 32-bit
> > > overflow…
> >
> > Uh, I was thinking that your 32-bit values could roll over and the other
> > end would be confused, but I can also see the 64-bit going above the
> > 32-bit range. As long as everything stays < 32-bits, you should be
> > fine. We don't transfer binary values very often.
> >
> > --
> > Bruce Momjian mailto:br...@momjian.us>> 
> > https://momjian.us<https://momjian.us>
> > EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>
> >
> > The usefulness of a cup is in its emptiness, Bruce Lee
> >
>
> --
> Bruce Momjian mailto:br...@momjian.us>> 
> https://momjian.us<https://momjian.us>
> EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>
>
> The usefulness of a cup is in its emptiness, Bruce Lee
>

--
Bruce Momjian mailto:br...@momjian.us>> 
https://momjian.us<https://momjian.us>
EnterpriseDB https://enterprisedb.com<https://enterprisedb.com>

The usefulness of a cup is in its emptiness, Bruce Lee

Partitioning with FDW and table size limits

[Godfrin, Philippe E]

Greetings,
In the case where you have a 'local' server, from which you are working with 
foreign tables. And the foreign tables are partitioned. As each of the 
partitioned tables is a table in its own right, is it correct to assume the 
table (relation) size limit of 32 TB applies? For example, provided I had the 
disk space, with 10 partitioned tables, I could have 320TB of data on 1 or more 
remote servers.
Am I correct?
Thanks,
phil

Phil Godfrin | Database Administrator
NOV
NOV US | Engineering Data
9720 Beechnut St | Houston, Texas 77036
M  281.825.2311
E   philippe.godf...@nov.com