PostgreSQL Connection Pooling with Pgbouncer on CentOS 6

PostgreSQL is quite heavy to establish connection. PostgreSQL deploy concept of process to establish connection from clients. If your connection just connect to database for short period of time, it will consume a lot of resource for each connection. To overcome this issue, connection pooling concept is utilized to handle short-time connection. Connection pooling enble connection resource reusable, so connection will be reuse among incoming connections from clients. Pgbouncer is one of the connection pooling solution that is available. It is very light weight and very easy to setup. In this post I will show how to install and configure pgbouncer to pgpool that I have created in previous post. My deployment concept is shown following . Read more.

Scale out table by partitioning with Postgresql-12

Partition and Sharding is the method to scale out your database when you are dealing with huge volume of data. Table partition and sharding come up with pros and cons, it is the technique to break data table in to multiple pieces and spread them across databases. It will improve database performance and query runtime to be more faster. I think post I'll demonstrate how to partition table and sharding table. Read more.

Load Balancing PostgreSQL Cluster with Repmgr and Pg-Pool-II

In the previous post, I have demonstrate how to create PostgreSQL-12 replication server. I will work well for distribute the connection to both master and slave. But problem is when you create a resource to connect to both databases you need to create two data sources separately which is not realistic the practical application. You need to access to single point of the resource and your connection will be distributed automatically. You don't need to manage connection to the datasource manually. Postgresql provides the solution for this problem by Pg-Pool-II which you have multiple databases (master / slave). You can access to the datasource from one single point of access. Pg-Pool will handle distribution of the connection automatically. So, in this post I will show you how to use Pg-Pool-II to handle request to your database cluster automatically. Read more

Wildfly JPA enable Second Level Cache

Caching is very import technique to improve performance on inquiry. Especially, if you are working with a heavy query that need to join many tables or to retrieve huge amount of the data from the database. Without caching, every single time that data query is executed, disk access happened. This is not a practical manner to handle data inquiry and lead to poor system performance. Accessing data without fetching data directly from the disk could increase data inquiring speed dramatically. Caching inquiry result in the memory and reuse that data is very common practice once it come to handle heavy query or huge amount of data. Read more

Postgres 12 Master / Slave Stream Replication

In this post, I demonstrate how to set up Postgresql 12 master / slave stream (by Write Ahead Log) replication server. By utilizing this feature, you reduce bottleneck for the database access by distribute access to both master and slave. In this scenario, Write operation will be occured only on master server. For read operation will be distributed to both master and slave. Master / Slave replication will allow you to scale out database server horizontally. Read more

Deploy Architecture (Nginx + Wildfly + PostgreSQL)

Once deploy application (web or enterprise application), the most common pattern is three tier pattern which includes web application, application server and database server. In this post, I will demonstrate how to use Nginx / Apache Web Server as web server to deploy application. NginX / Apache Web Server has the comparability to perform load balancing which could help to dispatch request to the application server. The difference between NginX / Apache Web Server is how it handle the request. Nginx use concept of NIO (Non-Blocking IO) which mean it uses one thread to handle all requests  that come to the server. But for Apache Web Server, it handle each request comes to the server by creating new thread for each request. Read more

Wildfly 10 Create XA Datasource for Distribute Transaction

In the real work application, you have many changes to work with multiple resource. One of them is database or messaging service. Especially, for the database, if you work with the databases. Tables could be on different databases distributed across network. Many time, you need to implement business logic that required to perform transaction across those datasources. This kind of transaction is know as distributed transaction. JAVA EE (Wildfly) provides XA datasource to implemented distributed transaction across datasources. XA uses two phase commit to manage distributed transaction. Read more