Postgres SELECT * from large table

Question

Working with a Postgres table containing large records, nearly 7m. I understand SELECT * FROM table is large enough to fit in memory, so db connection is lost after a long delay waiting for query result (can only execute SELECT * FROM table LIMIT n).

I need to process each record going through sequentially until the last. What is the way to do this?

You should see if you can do this work in the database. Why do you need to process each record individually in the application? — Gordon Linoff
– Gordon Linoff, Commented Apr 22, 2019 at 11:12
@GordonLinoff I want to fetch each record and send (to an application) as JSON. — arilwan
– arilwan, Commented Apr 22, 2019 at 11:16
What is the exact error message you get when you run that statement? Which Programming language are you using? How are you planning to handle 7 million JSON values in your application? — user330315
– user330315, Commented Apr 22, 2019 at 11:21
@a_horse_with_no_name This is a one-time upload of previously collected environmental datasets, that I want to do to a different platform (FIWARE precisely). Because the dataset isn't in the FIWARE standards, each record should be processed to conform to FIWARE model before forwarding to a publish/subscribe application on the FIWARE platform. — arilwan
– arilwan, Commented Apr 22, 2019 at 11:27
I don't think the delay is caused by waiting for the query result, Instead, it is probaly caused by the application , processing the first few records, and not reading the rest. You can test this by dumping to .csv or .tsv and work from there. — wildplasser
– wildplasser, Commented Apr 22, 2019 at 11:43

Jeet Kumar · Accepted Answer · 2019-04-22 14:51:33Z

2

You can get in chunks of n records.

select * from table where tableId>currentTableId and tableId<currentTableId*chunk_size

Explation:

Lets say you have 100 records and your memory contains 10 records at a time.

you query for execute 10 time from your application(any ORM)

 int totalRecords = executeQuery('select count(*) from table');
    int chunkSize = 10;
    int chunks = totalRecords/chunkSize ;
    while(chuks>0){
    executeQuery('select * from table where tableId>currentTableId and tableId<currentTableId*chunkSize ');
chunk-=chunkSize;
    }

This code is in a very abstract way. It's just a hint for you.

edited Apr 22, 2019 at 14:51

answered Apr 22, 2019 at 11:31

Jeet Kumar

5474 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arilwan Over a year ago

thank you for your answer. Can you please add a little explanation to this?

Collectives™ on Stack Overflow

Postgres SELECT * from large table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related