PostgreSQL connection reset on large tables

Question

I have a large PostgreSQL table - 2.8 millions rows; 2345 MB in size; 49 columns, mainly short VARCHAR fields, but with a large json field.

It's running on an Ubuntu 12.04 VM with 4GB RAM.

When I try doing a SELECT * against this table, my psql connection is terminated. Looking in the error logs, I just get:

2014-03-19 18:50:53 UTC LOG:  could not send data to client: Connection reset by peer
2014-03-19 18:50:53 UTC STATEMENT:  select * from all;
2014-03-19 18:50:53 UTC FATAL:  connection to client lost
2014-03-19 18:50:53 UTC STATEMENT:  select * from all;

Why is this happening? Is there a maximum amount of data that can be transferred or something - and is that configurable in postgres?

Having one large, wide table is dictated by the system we're using (I know it's not an ideal DB structure). Can postgres handle tables of this size, or will we keep having problems?

Thanks for any help, Ben

What's the nature of the network between client and server? I strongly suspect you've got issues with MTU, maybe problematic path MTU discovery? Alternately, an overzealous IDS or stateful packet-inspecting firewall? Alternately, the client app could be dying: when you say your connection is "terminated", what exactly do you mean? Show the output? — Craig Ringer
– Craig Ringer, Commented Mar 20, 2014 at 1:06
I'm SSHing into a local vagrant box, and then connecting to postgres with sudo -u postgres psql command. When I run the SELECT statement, I'm returned to the terminal. There are no errors messages at all (just the stuff I posted above from postgres log). I can reconnect immediately by re-running psql ..., so postgres is still running, just my session has been ended. — Ben Scott
– Ben Scott, Commented Mar 20, 2014 at 14:59

jjanes · Accepted Answer · 2014-03-19 19:33:34Z

5

Those messages in the server log just mean that the client went away unexpectedly. In this case, it probably died with an out of memory error.

By default psql loads the entire results into memory before displaying anything. That way it can best decide how to format the data. You can change that behavior by setting FETCH_COUNT

answered Mar 19, 2014 at 19:33

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

1 Comment

Ben Scott Over a year ago

Hi - that's interesting, so if the whole table cannot be loaded into memory, psql would terminate? I'll have a play with FETCH_COUNT now, and see if that works. Thanks for the help.

user3098199 · Accepted Answer · 2014-04-02 11:37:49Z

1

I have seen a similar issue, however, the issue I faced was not on the client side, but most probably on the postgres driver side. The query was required to fetch a lot of rows, and as a result, there could have been temporary memory spike requirement on postgres driver. As a result, the cursor that I was using to fetch the records closed, and I got the exactly same logs.

I would really love if someone validated if this is possible, one thing I am sure is that there was not any issue on the client side.

answered Apr 2, 2014 at 11:37

user3098199

311 silver badge5 bronze badges

Collectives™ on Stack Overflow

PostgreSQL connection reset on large tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related