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-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