RE: Postgres and Java Microservices Multithreading

There is no reason why it shouldn't work as long as you use the
database only as a data storage and all the java-stuff is as a separate
layer in your software.Conversion problems can happen on data-types,
constraints and/or triggers. If you use stored-procedures, those should
be converted / rewritten as well..
A bit of a strange question as this is basic datbase knowledge.... ??/
Esp. if you're working at KPN.
On Thu, 2019-12-05 at 12:22 +0000, soumik.bhattacharjee@kpn.com wrote:

Hi Experts,

Few more details as discussed with application team.

In parallel processing Application will create multiple threads
towards our PG database to retrieve/ update data.

Application will send multiple parallel requests to DB on same table
to SELECT data.Application will send multiple parallel requests to DB
on same table to UPDATE dataApplication will send multiple parallel
requests to DB on same table to INSERT data

Thanks..

From: Bhattacharjee, Soumik

Sent: donderdag 5 december 2019 10:48

To: 'pgsql-admin@lists.postgresql.org' <
pgsql-admin@lists.postgresql.org>; 'pgsql-admin@postgresql.org' <
pgsql-admin@postgresql.org>

Subject: RE: Postgres and Java Microservices Multithreading

Hi Experts,

Please let me know if you have some use cases or documentations
around this.

Any response will help me a lot in this context.

Thanks in advance.

From: Bhattacharjee, Soumik

Sent: woensdag 4 december 2019 14:34

To: 'pgsql-admin@lists.postgresql.org' <
pgsql-admin@lists.postgresql.org>;
pgsql-admin@postgresql.org

Subject: Postgres and Java Microservices Multithreading

Hi Experts,

To set the context we are operating in Oracle 11g R2 and our
applications are deployed in private cloud and all 15 + Java micro
services are multithreaded.

And we are doing database migration from Oracle 11gR2 to
Postgres 12.

So if anyone can share your experience
or share some documentations/use cases if we can maintain the same
configurations for Java micro services with multithreading as we are
using in
Oracle and same works in Postgres too? – (We just plan to switch the
data source from Oracle to Postgres- will that work? )

Thanks..

--
Open Systems Development B.V.
Peter M. Groen
Managing Director
Rooseveltstraat 18-P
2321 BM Leiden
+31 6 12 08 95 62
email : peter@osdev.nl
skype : peter_m_groen

Thanks Peter for your response, appreciate much.

But I think the applications wont behave the same way as with Oracle.(We have 15+ Microservices running in Oracle with parallel processing)

Below I just checked and testing in Test Env.

https://www.postgresql.org/docs/12/parallel-query.html
https://dzone.com/articles/postgresql-connection-pooling-part-1-pros-amp-cons

If anyone have some use cases/real time project executions will be really helpful.

Thanks..

From: Peter M. Groen <peter@osdev.nl>
Sent: donderdag 5 december 2019 13:30
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>; pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
Subject: Re: Postgres and Java Microservices Multithreading

There is no reason why it shouldn't work as long as you use the database only as a data storage and all the java-stuff is as a separate layer in your software.
Conversion problems can happen on data-types, constraints and/or triggers. If you use stored-procedures, those should be converted / rewritten as well..

A bit of a strange question as this is basic datbase knowledge.... ??/ Esp. if you're working at KPN.

--
Open Systems Development B.V.
Peter M. Groen
Managing Director
Rooseveltstraat 18-P
2321 BM Leiden
+31 6 12 08 95 62
email : peter@osdev.nl<mailto:peter@osdev.nl>
skype : peter_m_groen

On Thu, 2019-12-05 at 12:22 +0000, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
Hi Experts,

Few more details as discussed with application team.

In parallel processing Application will create multiple threads towards our PG database to retrieve/ update data.

1. Application will send multiple parallel requests to DB on same table to SELECT data.
2. Application will send multiple parallel requests to DB on same table to UPDATE data
3. Application will send multiple parallel requests to DB on same table to INSERT data

Thanks..

From: Bhattacharjee, Soumik
Sent: donderdag 5 december 2019 10:48
To: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>; 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>>
Subject: RE: Postgres and Java Microservices Multithreading

Hi Experts,

Please let me know if you have some use cases or documentations around this.

Any response will help me a lot in this context.

Thanks in advance.

From: Bhattacharjee, Soumik
Sent: woensdag 4 december 2019 14:34
To: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>; pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Subject: Postgres and Java Microservices Multithreading

Hi Experts,

To set the context we are operating in Oracle 11g R2 and our applications are deployed in private cloud and all 15 + Java micro services are multithreaded.

And we are doing database migration from Oracle 11gR2 to Postgres 12.

So if anyone can share your experience or share some documentations/use cases if we can maintain the same configurations for Java micro services with multithreading as we are using in Oracle and same works in Postgres too? – (We just plan to switch the data source from Oracle to Postgres- will that work? )

Thanks..

"parallel query" refers to parallelism within a single query, not at all related to the questions you're asking

connection pooler is something you might want to investigate, but that really depends on total number of connections across your microservices, and how connection pooling within your clients might be handled. If your clients make persistent connections, and they're not making all that many (maybe not more than 10-20 each across the 15), you wouldn't need it. If client connections come and go, or your clients use higher number of connections, then yes, put pgbouncer between clients & PG.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/

On Dec 5, 2019, at 6:33 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> wrote:

Dear Sender,

I am not checking mails on this id, please send mail to my office outlook mail id - SB00567728@TechMahindra.com

Thanks..

LOL, IMO not worth trying to help this guy...

soumik.bhattacharjee@kpn.com writes:

Thanks Peter for your response, appreciate much.

But I think the applications wont behave the same way as with Oracle.(We have 15+ Microservices running in
Oracle with parallel processing)

Below I just checked and testing in Test Env.

https://www.postgresql.org/docs/12/parallel-query.html

https://dzone.com/articles/postgresql-connection-pooling-part-1-pros-amp-cons

If anyone have some use cases/real time project executions will be really helpful.

Oracle and Postgres are very different DBMS. While the functionality is
basically equivalent with respect to CRUD operations, the underlying
architecture is very different. This means how query plans are derived,
how indexes, locks etc work are very different and will need different
strategies to optimise performance. You won't just be able to create the
DB schemas in postgres, point your Java microservices to PG and expect
everything to perform in the same way. A lot more effort will be
required in order to get a reliable level of performance.

With regard to your questions concerning parallelism of the java
micro services and the links you posted, I wonder if your confused
regarding how/where you believe this parallel processing is performed.

Assuming (as Peter stated) your Java microservices are separate Java
software (i.e. not Java functions embeded in your 11g db), then what
your dealing with is concurrent connections to your database to perform
CRUD operations. This is distinct to parallel execution of the actual
SQL statements within the database. Connection pooling will likely be a
critical component, especially if the SQL statements are small/simple,
but with a high volume. The overhead of establishing a connection can
easily be as much or more than the actual query in many micro service
type applications.

Likewise, the extent to which your SQL statements are able to take
advantage of parallel processing within the database are likely to be
less relevant compared to correctly structuring transactions
(commits/rollbacks), contention and locking in the database tables.
This in turn depends a lot on the type of application and relationship between
frequency and complexity of queries, updates and inserts.

Even if someone can provide the use cases or other examples, they are
unlikely to be very relevant. Too much depends on the specifics of your
application, your schema design (tables, data types, indexes, number of
columns, table sizes etc), the processing profile and required
performance metrics and how the DB is tuned.

The good news is I suspect PG will be able to satisfy your
requirements. However, the bad news is it sounds like the migration
effort has been woefully under estimated. Based on your questions, I
would also suggest you need to get someone on your team experienced with
PG.

I spent 15+ years working with Oracle before moving to working with
Postgres and after 2 years, I'm still learning every day. The databases
I work with are large (appro 8Tb) and on a slow day we process 1.5
billion updates and inserts and have a number of REST and GrapQL API's
which process large numbers of queries for a number of applications
requiring real-time data. Very little of my Oracle knowledge has been
relevant to working with PG.

Tim

--
Tim Cross

Thanks for your response Scott.

It was really valuable, this OOO message was meant for other projects :-) apologies for the same.

Thanks..

-----Original Message-----
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: donderdag 5 december 2019 14:47
To: pgsql-admin@lists.postgresql.org
Subject: Postgres and Java Microservices Multithreading

On Dec 5, 2019, at 6:33 AM, <soumik.bhattacharjee@kpn.com> <soumik.bhattacharjee@kpn.com> wrote:

Dear Sender,

I am not checking mails on this id, please send mail to my office outlook mail id - SB00567728@TechMahindra.com

Thanks..

LOL, IMO not worth trying to help this guy...

Thanks Tim, your response covered all :-)

-----Original Message-----
From: Tim Cross <theophilusx@gmail.com>
Sent: donderdag 5 december 2019 15:17
To: pgsql-admin@lists.postgresql.org
Cc: peter@osdev.nl; pgsql-admin@postgresql.org
Subject: Re: Postgres and Java Microservices Multithreading

soumik.bhattacharjee@kpn.com writes:

Thanks Peter for your response, appreciate much.

But I think the applications wont behave the same way as with
Oracle.(We have 15+ Microservices running in Oracle with parallel
processing)

Below I just checked and testing in Test Env.

https://www.postgresql.org/docs/12/parallel-query.html

https://dzone.com/articles/postgresql-connection-pooling-part-1-pros-a
mp-cons

If anyone have some use cases/real time project executions will be really helpful.

Oracle and Postgres are very different DBMS. While the functionality is basically equivalent with respect to CRUD operations, the underlying architecture is very different. This means how query plans are derived, how indexes, locks etc work are very different and will need different strategies to optimise performance. You won't just be able to create the DB schemas in postgres, point your Java microservices to PG and expect everything to perform in the same way. A lot more effort will be required in order to get a reliable level of performance.

With regard to your questions concerning parallelism of the java micro services and the links you posted, I wonder if your confused regarding how/where you believe this parallel processing is performed.

Assuming (as Peter stated) your Java microservices are separate Java software (i.e. not Java functions embeded in your 11g db), then what your dealing with is concurrent connections to your database to perform CRUD operations. This is distinct to parallel execution of the actual SQL statements within the database. Connection pooling will likely be a critical component, especially if the SQL statements are small/simple, but with a high volume. The overhead of establishing a connection can easily be as much or more than the actual query in many micro service type applications.

Likewise, the extent to which your SQL statements are able to take advantage of parallel processing within the database are likely to be less relevant compared to correctly structuring transactions (commits/rollbacks), contention and locking in the database tables.
This in turn depends a lot on the type of application and relationship between frequency and complexity of queries, updates and inserts.

Even if someone can provide the use cases or other examples, they are unlikely to be very relevant. Too much depends on the specifics of your application, your schema design (tables, data types, indexes, number of columns, table sizes etc), the processing profile and required performance metrics and how the DB is tuned.

The good news is I suspect PG will be able to satisfy your requirements. However, the bad news is it sounds like the migration effort has been woefully under estimated. Based on your questions, I would also suggest you need to get someone on your team experienced with PG.

I spent 15+ years working with Oracle before moving to working with Postgres and after 2 years, I'm still learning every day. The databases I work with are large (appro 8Tb) and on a slow day we process 1.5 billion updates and inserts and have a number of REST and GrapQL API's which process large numbers of queries for a number of applications requiring real-time data. Very little of my Oracle knowledge has been relevant to working with PG.

Tim

--
Tim Cross

Yep.. Figured... Techmahindra is selling "expertise" to companies like
KPN while their questions are on beginners level....
--
Open Systems Development B.V.
Peter M. Groen
Managing Director
Rooseveltstraat 18-P
2321 BM Leiden
+31 6 12 08 95 62
email : peter@osdev.nl
skype : peter_m_groen

I apologize for the tone of my response.

I just went through having someone email me directly for tech support, and to get my response through was expected to go through one of those "prove you're a human" things.

So I was in a bad mood about emails being bounced, and didn't consider the obvious option that you'd be reading the list for responses.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/

No issues Scott.

Thanks...

-----Original Message-----
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: donderdag 5 december 2019 17:16
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres and Java Microservices Multithreading

I apologize for the tone of my response.

I just went through having someone email me directly for tech support, and to get my response through was expected to go through one of those "prove you're a human" things.

So I was in a bad mood about emails being bounced, and didn't consider the obvious option that you'd be reading the list for responses.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/

In PostgreSQL 12.1 Documentation chapter 4.2.7. Aggregate Expressions it says

The syntax of an aggregate expression is one of the following:
...
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
...

I believe this is incorrect in the case where the DISTINCT is on a comma-separated list of expressions.
It would imply that this is legal

select count(DISTINCT parent_id , name) from mytable

but that is rejected with
ERROR: function count(bigint, text) does not exist

whereas

select count(DISTINCT ( parent_id , name) ) from mytable

is accepted.

So I think to handle all cases the line in the doc should read

aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

I don't know how to indicate that those extra parentheses can be omitted if the list has only one expression.

Cheers, John Lumby

On Thu, Dec 5, 2019 at 3:18 PM John Lumby <johnlumby@hotmail.com> wrote:

In PostgreSQL 12.1 Documentation chapter 4.2.7. Aggregate Expressions it
says

The syntax of an aggregate expression is one of the following:
...
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [
FILTER ( WHERE filter_clause ) ]
...

I believe this is incorrect in the case where the DISTINCT is on a
comma-separated list of expressions.
It would imply that this is legal

It is...you didn't get a syntax error.

select count(DISTINCT parent_id , name) from mytable

but that is rejected with
ERROR: function count(bigint, text) does not exist

The error is that while the query is syntactically correct in order to
execute it as written a function would need to exist that does not. As far
as a general syntax diagram goes it has correctly communicated what is
legal.

whereas

select count(DISTINCT ( parent_id , name) ) from mytable

is accepted.

Correct, converting the two individual columns into a "tuple" allows the
default tuple distinct-making infrastructure to be used to execute the
query.

So I think to handle all cases the line in the doc should read

aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [
FILTER ( WHERE filter_clause ) ]

I don't know how to indicate that those extra parentheses can be omitted
if the list has only one expression.

Then I would have to say the proposed solution to this edge case is worse
than the problem. I also don't expect there to be a clean solution to
dealing with the complexities of expressions at the syntax diagram level.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thu, Dec 5, 2019 at 3:18 PM John Lumby <johnlumby@hotmail.com> wrote:

whereas
select count(DISTINCT ( parent_id , name) ) from mytable
is accepted.

Correct, converting the two individual columns into a "tuple" allows the
default tuple distinct-making infrastructure to be used to execute the
query.

Yeah. This might be more intelligible if it were written

select count(DISTINCT ROW(parent_id, name) ) from mytable

However, the SQL committee in their finite wisdom have decreed that
the ROW keyword is optional. (As long as there's more than one
column expression; the need for that special case is another reason
why omitting ROW isn't really a nice thing to do.)

regards, tom lane

On 12/05/19 18:06, David G. Johnston wrote:
On Thu, Dec 5, 2019 at 3:18 PM John Lumby <<mailto:johnlumby@hotmail.com>johnlumby@hotmail.com<mailto:johnlumby@hotmail.com>> wrote:
In PostgreSQL 12.1 Documentation chapter 4.2.7. Aggregate Expressions it says

The syntax of an aggregate expression is one of the following:
...
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
...

I believe this is incorrect in the case where the DISTINCT is on a comma-separated list of expressions.
It would imply that this is legal

It is...you didn't get a syntax error.

Hmm, even though true, I think this is unhelpful.
If a reference document states that the syntax for a something-or-other construct is one of the following diagrams,
then I expect that the diagrams are valid for *every* kind of something-or-other, not just some.
Yet the diagram I quote always results in being rejected in the case of COUNT -
which I consider to be as good as saying it is invalid syntax.

select count(DISTINCT parent_id , name) from mytable

but that is rejected with
ERROR: function count(bigint, text) does not exist

The error is that while the query is syntactically correct in order to execute it as written a function would need to exist that does not. As far as a general syntax diagram goes it has correctly communicated what is legal.

whereas

select count(DISTINCT ( parent_id , name) ) from mytable

is accepted.

Correct, converting the two individual columns into a "tuple" allows the default tuple distinct-making infrastructure to be used to execute the query.

So I think to handle all cases the line in the doc should read

aggregate_name (DISTINCT ( expression [ , ... ] ) [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

I don't know how to indicate that those extra parentheses can be omitted if the list has only one expression.

Then I would have to say the proposed solution to this edge case is worse than the problem. I also don't expect there to be a clean solution to dealing with the complexities of expressions at the syntax diagram level.

Yes, I see what I suggested is not ideal either. But I think something needs to be changed.

How about replacing "expression [ , ... ]" by "parameter_list" in the description, and then stating that parameter_list can be either a single expression or , if the particular aggregate function accepts it (for which, consult that function's reference), a comma-separated list of expressions.

David J.

John Lumby <johnlumby@hotmail.com> writes:

On 12/05/19 18:06, David G. Johnston wrote:

Then I would have to say the proposed solution to this edge case is worse than the problem. I also don't expect there to be a clean solution to dealing with the complexities of expressions at the syntax diagram level.

Yes, I see what I suggested is not ideal either. But I think something needs to be changed.

How about replacing "expression [ , ... ]" by "parameter_list" in the description, and then stating that parameter_list can be either a single expression or , if the particular aggregate function accepts it (for which, consult that function's reference), a comma-separated list of expressions.

That's just as wrong. As we tried to explain before, the
parenthesized-list syntax is a row constructor, so it only works
in cases where the aggregate function can accept a composite
argument type. Most don't.

Moreover, the very same thing holds in *any* expression context,
not only aggregate arguments. So if we took this seriously there
would have to be a lot of other places plastered with equally
confusing/unhelpful addenda.

regards, tom lane

Tom Lane wrote

How about replacing "expression [ , ... ]"  by "parameter_list" in the description,
and then stating that parameter_list can be either a single expression or ,
 if the particular aggregate function accepts it (for which,   consult that function's reference),
  a comma-separated list of expressions.

That's just as wrong.  As we tried to explain before, the
parenthesized-list syntax is a row constructor, so it only works
in cases where the aggregate function can accept a composite
argument type.  Most don't.

But surely not *all* cases of a multi-expression parameter list of an aggregate function are row constructors are they?    What about

select parent_id, name, GROUPING(parent_id , name), count(*) FROM mytable GROUP BY ROLLUP(parent_id , name);

In the piece "GROUPING(parent_id , name)" ,
is "(parent_id , name)" a row constructor or a list of two expressions?

Or are you saying those are one and the same thing?

Cheers, John

John Lumby wrote:
<
< Tom Lane wrote
<
< > > How about replacing "expression [ , ... ]" by "parameter_list" in the description,
< > > and then stating that parameter_list can be either a single expression or ,
< > > if the particular aggregate function accepts it (for which, consult that function's reference),
< > > a comma-separated list of expressions.
< >
< > That's just as wrong. As we tried to explain before, the
< > parenthesized-list syntax is a row constructor, so it only works
< > in cases where the aggregate function can accept a composite
< > argument type. Most don't.
< >
<
< But surely not *all* cases of a multi-expression parameter list of an aggregate function are row constructors are they? What about
<
< select parent_id, name, GROUPING(parent_id , name), count(*) FROM mytable GROUP BY ROLLUP(parent_id , name);
<
< In the piece "GROUPING(parent_id , name)" ,
< is "(parent_id , name)" a row constructor or a list of two expressions?
<
< Or are you saying those are one and the same thing?
<

I think I can answer my own question - No they are not the same - because :

select parent_id, name, GROUPING(ROW(parent_id , name)), count(*) FROM mytable GROUP BY ROLLUP(parent_id , name);
ERROR: arguments to GROUPING must be grouping expressions of the associated query level
LINE 1: select parent_id, name, GROUPING(ROW(parent_id , name)), cou...
^