Thread: Using compression on TCP transfer
Hi! 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. 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 ? Andrus.
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 addedsslcompression=1to 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:
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
Hi!
>Updated doc reference:
>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
insert into test values ('test');
select * from test where test ='test '; -- note trailing space
does not return data.
Andrus.