if you want an effect like strings stored as integers use an enum type, the SQL syntax for interacting with the table will be the same, except you'll need to cast if you want to do string operations on the new column;
CREATE TYPE example_category AS ENUM (
'category1', 'category2', 'category3', 'category4', 'category5',
'category6', 'category7', 'category8', 'category9');
ALTER TABLE tmp_strings ALTER COLUMN category
TYPE example_category USING category::example_category;
I think enums use 4 bytes (possibly 5?)
there is nothing on PostgreSQL side to deal with this.
JDBC side i know nothing about but quick google search points to no
A solution i use when working with large data sets is to fetch only a few records at a time 1000 to 10000.
Select * from tablename where tablename.id > 1 ordered by id limit 1000
next set of queries
Select * from tablename where tablename.id > 1000 ordered by id limit 1000
Some people call this asynchronous fetching/paging.
Some background
Most db connections run in synchronize mode by default, "command sent" client waits until all data is delivered. This makes the app appear to hang for long periods of time, this makes the users think the app has crashed or something else has gone wrong.
Using asynchronous technique the data is continuously fetch and delivered using a background process/thread while the rest of the code is free to continue working giving the user the appearance the application is very snappy.
Its allot harder to write code around fetching only a few records at a time but can be worth it on big data sets, as the app does not have to wait on all the data
There are several tools to make this easier, such as creating cursor on the server and fetch only X records from the server. But there are draw backs to cursors, eating server resources, required to run clean up code.
Some client libraries makes this easy by directly supporting cursor other do not support this feature JDBC does not appear to support this so it will have to be written.
Best Answer
A PostgreSQL server connection dropping after 10-15 minutes is almost certainly being caused by a state-tracking firewall (possibly using Network Address Translation (NAT)) between the client and the server. Many such firewalls have default timeouts of 15 minutes (900 seconds).
The three server-side parameters,
tcp_keepalives_idle,tcp_keepalives_interval,tcp_keepalives_countare designed to help in these situations. See the documentation located here: http://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLEThere are also client-side parameters for this:
keepalives,keepalives_idle,keepalives_interval,keepalives_count, which you can set on connection. See the documentation located here: http://www.postgresql.org/docs/9.2/static/libpq-connect.html#LIBPQ-KEEPALIVES