Using compression on TCP transfer

Hi,

See the section about sslcompression in https://www.postgresql.org/docs/9.2/libpq-connect.html. It should be your answer.

I added

sslcompression=1

to psqlodbc connection string but log file shows that connection is still uncompressed:

LOG: connection authorized: user=me database=mydb SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)

Maybe because according to TLSv1.3 standard, compression is no more allowed or psqlodbc does not pass it to pglib.

How to compress ?

Andrus.

Sorry, forgot the reply all :-)

On Tue, Mar 31, 2020 at 11:39 AM Andrus <kobruleht2@hot.ee> wrote:

Hi,

See the section about sslcompression in

https://www.postgresql.org/docs/9.2/libpq-connect.html. It should be your
answer.

I added

sslcompression=1

to psqlodbc connection string but log file shows that connection is still
uncompressed:

LOG: connection authorized: user=me database=mydb SSL enabled
(protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)

Maybe because according to TLSv1.3 standard, compression is no more
allowed or psqlodbc does not pass it to pglib.

How to compress ?

Andrus.

Updated doc reference:
https://www.postgresql.org/docs/12/libpq-connect.html

They mention that compression is insecure and disabled by default. Taking
this into account, compression will require that both ODBC and PostgreSQL
are set up with compression enabled. I could not figure out quickly whether
this requires also recompiling the code...

--
Olivier Gautherot
Tel: +33 6 02 71 92 23
https://www.linkedin.com/in/ogautherot/

Hi!

Updated doc reference:
https://www.postgresql.org/docs/12/libpq-connect.html
They mention that compression is insecure and disabled by default. Taking this into account, compression will require that both ODBC and PostgreSQL are set >up with compression enabled. I could not figure out quickly whether this requires also recompiling the code...

I added

Pqopt={sslcompression=1};

to psqlodbc connection string but log file shows that it still not compressed.

I’m using Debian 10 and Postgres 12 installed from postgres repository.
How to check is will it support compression or not.

Andrus.

On Tue, 2020-03-31 at 11:29 +0300, Andrus wrote:

Databases contain lot of char(n) type fields containing ascii characters.
Most of fields contain less characters than field width.
Changing them to varchar is not easy.

It is a simple ALTER TABLE.

Database is accessed from Debian Postgres 12.2 over internet using psqlodbc
with
TLS v1.3.
Mostly results seelct commands are sent and results are retrieved.
Clients have 10-20Mbit download speeds, and 5-20 Mbit upload speeds.

Will data compression increase speed ?
If yes, how to implement this ?

You'd have to use an OpenSSL library with compression support enabled.

But that will improve speed only if your workload is network bound,
not CPU bound (in which case performance will suffer).

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hi!

It is a simple ALTER TABLE.

Client is Visual FoxPro application. It sends data with trailing spaces
sometimes and sometimes not.
In case of varchar field values will appear in database sometimes with
trailing spaces and sometimes without.
This requires major application re-design which much is more expensive than
continuing using char fields.

You'd have to use an OpenSSL library with compression support enabled.

Should I change OpenSSL installed from standard repository in Debian server
or can it changed only for PostgreSql.
How ?

But that will improve speed only if your workload is network bound,
not CPU bound (in which case performance will suffer).

Server has lot of cores. Top shows that CPU usage is small.

Brausers and web servers use compression widely. Apache and IIS enable
static content compression by default.
Compression should be built in in Postgres.

Andrus.

On Tue, 2020-03-31 at 15:13 +0300, Andrus wrote:

It is a simple ALTER TABLE.

Client is Visual FoxPro application. It sends data with trailing spaces
sometimes and sometimes not.
In case of varchar field values will appear in database sometimes with
trailing spaces and sometimes without.
This requires major application re-design which much is more expensive than
continuing using char fields.

A simple BEFORE INSERT OR UPDATE trigger would take care of that.

You'd have to use an OpenSSL library with compression support enabled.

Should I change OpenSSL installed from standard repository in Debian server
or can it changed only for PostgreSql.
How ?

I don't know if Debian's binaries are built with compression support,
probably not. You can build OpenSSL yourself and make PostgreSQL
use these binaries.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hi!

In case of varchar field values will appear in database sometimes with
trailing spaces and sometimes without.
This requires major application re-design which much is more expensive than
continuing using char fields.

A simple BEFORE INSERT OR UPDATE trigger would take care of that.

Changing char to varchar will break commands where trailing space is used in comparison.

For example query

create table test ( test char(10) );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space

does not return data anymore if your recommendation is used:

create table test ( test varchar );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space

In production 'test ' is query parameter coming from application with possible trailing space(s).

Adding trigger does not fix this.
How to fix this without re-writing huge number of sql commands?

Andrus.

Hi Andrus,

Le sam. 4 avr. 2020 à 10:09, Andrus <kobruleht2@hot.ee> a écrit :

Hi!

In case of varchar field values will appear in database sometimes with
trailing spaces and sometimes without.
This requires major application re-design which much is more expensive

than

continuing using char fields.

A simple BEFORE INSERT OR UPDATE trigger would take care of that.

Changing char to varchar will break commands where trailing space is used
in comparison.

For example query

create table test ( test char(10) );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space

does not return data anymore if your recommendation is used:

create table test ( test varchar );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space

In production 'test ' is query parameter coming from application with
possible trailing space(s).

Adding trigger does not fix this.
How to fix this without re-writing huge number of sql commands?

In the end, your question is more at application level than database
itself. The real question is: which one is correct? With or without
trailing space?

If you decide that it's without, you could apply a TRIM in a trigger on
each INSERT and UPDATE. Then, you could replace the table by a view of the
same name and implement the TRIM on SELECT there. This way, you don't have
to touch anything in the application.

Hope it helps
Olivier

Hi!
Thank you.

If you decide that it's without, you could apply a TRIM in a trigger on each INSERT and UPDATE. Then, you could replace the table by a view of the same name >and implement the TRIM on SELECT there. This way, you don't have to touch anything in the application.

How you provide sample code how to create view or othe method test so that my select statement returns data.

Currently select in code

create table test ( test varchar );
insert into test values ('test');
select * from test where test ='test '; -- note trailing space

does not return data.

Andrus.