Advice on fetching large number of rows with jdbc client with fixed setFetchSize property

[bhushan...@gmail.com]

I am using Postgres version 9.4.14 with Cent OS 6.9 . My core tables have hundreds of millions of records and have large web clients which access postgres using jdbc client. As part of our client needs we export last 7 days data from postgres db . We have common framework using in house ORM based on JDBC. There are 5 steps we do as part of export,

1. Set JDBC property, where setFetchSize set to 10K
2. Formulate the SQL SELECT query with necessary where criteria
3. Prepare the SELECT query
4. Send bind param to this query
5. Fetch result of the above query in batches


In my case above results in fetching approximate 780K records, and I end up doing 78 iterations. Out of 78, most iterations finishes in matter of seconds. But there are few iterations which takes like 30-40 MINUTES. And I do not understand why.

I need advice,
1. first is this the right way to fetch large datasets from db ? what other alternatives I have?
2. Since I am bringing results in batches, does postgres repeats the query for every batch or how is the pagination done at db level ?
3. How can I debug slowness in iterations where it take 30-40 minutes.


Sincerely,
Bhushan

{code}
duration: 3353.494 ms bind <unnamed>/C_255:
duration: 136616.504 ms execute <unnamed>/C_255:
duration: 4044.062 ms execute fetch
duration: 5604.555 ms execute fetch
duration: 6739.133 ms execute fetch
duration: 5515.646 ms execute fetch
duration: 4787.372 ms execute fetch
duration: 5981.755 ms execute fetch
duration: 5347.695 ms execute fetch
duration: 5234.821 ms execute fetch
duration: 66140.751 ms execute fetch
duration: 259259.782 ms execute fetch
duration: 5021.827 ms execute fetch
duration: 5321.758 ms execute fetch
duration: 3974.821 ms execute fetch
duration: 7140.601 ms execute fetch
duration: 3444.857 ms execute fetch
duration: 2501.242 ms execute fetch
duration: 10085.228 ms execute fetch
duration: 6526.959 ms execute fetch
duration: 7748.472 ms execute fetch
duration: 5011.091 ms execute fetch
duration: 3654.584 ms execute fetch
duration: 6310.684 ms execute fetch
duration: 11663.159 ms execute fetch
duration: 4335.106 ms execute fetch
duration: 5802.454 ms execute fetch
duration: 4728.780 ms execute fetch
duration: 4905.621 ms execute fetch
duration: 2395.726 ms execute fetch
duration: 4717.428 ms execute fetch
duration: 4860.125 ms execute fetch
duration: 5566.658 ms execute fetch
duration: 5812.773 ms execute fetch
duration: 4269.370 ms execute fetch
duration: 6532.245 ms execute fetch
duration: 4880.061 ms execute fetch
duration: 3142.641 ms execute fetch
duration: 5027.824 ms execute fetch
duration: 6409.005 ms execute fetch
duration: 7131.011 ms execute fetch
duration: 4428.909 ms execute fetch
duration: 7018.613 ms execute fetch
duration: 3998.722 ms execute fetch
duration: 5925.292 ms execute fetch
duration: 6600.967 ms execute fetch
duration: 6491.373 ms execute fetch
duration: 3610.761 ms execute fetch
duration: 2461.907 ms execute fetch
duration: 2106.069 ms execute fetch
duration: 2338.225 ms execute fetch
duration: 2439.384 ms execute fetch
duration: 6560.654 ms execute fetch
duration: 6129.915 ms execute fetch
duration: 6232.821 ms execute fetch
duration: 5273.512 ms execute fetch
duration: 5170.681 ms execute fetch
duration: 3366.137 ms execute fetch
duration: 3110.553 ms execute fetch
duration: 3637.209 ms execute fetch
duration: 3329.326 ms execute fetch
duration: 6291.210 ms execute fetch
duration: 5444.250 ms execute fetch
duration: 5209.395 ms execute fetch
duration: 5307.647 ms execute fetch
duration: 4630.627 ms execute fetch
duration: 4978.659 ms execute fetch
duration: 2616.587 ms execute fetch
duration: 5931.865 ms execute fetch
duration: 5932.119 ms execute fetch
duration: 6713.955 ms execute fetch
duration: 3623.926 ms execute fetch
duration: 4356.517 ms execute fetch
duration: 5908.876 ms execute fetch
duration: 5683.423 ms execute fetch
duration: 2897838.851 ms execute fetch
duration: 42130.725 ms execute fetch
duration: 764191.431 ms execute fetch
duration: 8673.684 ms execute fetch
duration: 20050.957 ms execute fetch
{code}