Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
From: <soumik.bhattacharjee@kpn.com>
To: <theophilusx@gmail.com>, <pgsql-admin@lists.postgresql.org>
CC: <peter@osdev.nl>, <pgsql-admin@postgresql.org>
Subject: RE: Postgres and Java Microservices Multithreading
Thread-Topic: Postgres and Java Microservices Multithreading
Thread-Index: 
 AdWqpn/R4t61EWEpQvqpXv9jDosTyAAql+uwAAVvOmD///FoAP//4a/wgAA8SoD//+uhMA==
Date: Thu, 5 Dec 2019 14:30:31 +0000
Message-ID: 
 <2A91BEF8171A5349931391E0C721CC537570555B@CPEMS-KPN501.KPNCNL.LOCAL>
References: 
 <2A91BEF8171A5349931391E0C721CC5375705457@CPEMS-KPN501.KPNCNL.LOCAL>
 <251962385fdc49da0da54e22588aabd6fbc2e0fa.camel@osdev.nl>
 <2A91BEF8171A5349931391E0C721CC53757054D3@CPEMS-KPN501.KPNCNL.LOCAL>
 <87tv6ezwk8.fsf@gmail.com>
In-Reply-To: <87tv6ezwk8.fsf@gmail.com>
Accept-Language: nl-NL, en-US
MIME-Version: 1.0
Precedence: bulk

Thanks Tim, your response covered all :-)


-----Original Message-----
From: Tim Cross <theophilusx@gmail.com>=20
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=20
> Oracle.(We have 15+ Microservices running in Oracle with parallel=20
> 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 reall=
y helpful.
>
>
>

Oracle and Postgres are very different DBMS. While the functionality is bas=
ically equivalent with respect to CRUD operations, the underlying architect=
ure is very different. This means how query plans are derived, how indexes,=
 locks etc work are very different and will need different strategies to op=
timise performance. You won't just be able to create the DB schemas in post=
gres, 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 relia=
ble level of performance.

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

Assuming (as Peter stated) your Java microservices are separate Java softwa=
re (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 withi=
n the database. Connection pooling will likely be a critical component, esp=
ecially 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 t=
he actual query in many micro service type applications.

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

Even if someone can provide the use cases or other examples, they are unlik=
ely to be very relevant. Too much depends on the specifics of your applicat=
ion, your schema design (tables, data types, indexes, number of columns, ta=
ble 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. Ho=
wever, the bad news is it sounds like the migration effort has been woefull=
y under estimated. Based on your questions, I would also suggest you need t=
o get someone on your team experienced with PG.

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

Tim

--
Tim Cross