PostgreSQL
PostgreSQL stands out as a sophisticated and enterprise-grade open-source relational database, offering robust support for both SQL (relational) and JSON (non-relational) queries. This well-established database management system has earned its reputation for stability through over two decades of community-driven development. This collaborative approach has played a pivotal role in ensuring its remarkable resilience, data integrity, and overall correctness. PostgreSQL serves as the go-to choice for numerous web, mobile, geospatial, and analytics applications, acting as their primary data repository or data warehouse.
Moreover, PostgreSQL has a rich history of accommodating advanced data types and optimizing performance to levels commonly found in commercial database solutions like Oracle and SQL Server. Below, you’ll find more advantages of PostgreSQL. Furthermore, PostgreSQL’s source code is available under an open-source license, providing you with the freedom to utilize, customize, and implement it as needed, all without incurring any charges.
PostgreSQL is an object-relational database management system, which implies that it allows you to store data in the form of objects featuring distinct properties. It leverages structured query language (SQL) as the interface for data retrieval and manipulation. This open-source database enjoys robust support from a dedicated developer community.
Moreover, PostgreSQL comes equipped with integrated features for data backup, replication, and access control. It’s noteworthy that PostgreSQL adheres to the ACID (Atomicity, Consistency, Isolation, Durability) principles, ensuring data integrity and reliability.
Difference Between MySQL and PostgreSQL
- While MySQL is exclusively a relational database, PostgreSQL distinguishes itself as an object-relational database, enabling the storage of data in the form of objects featuring distinct properties.
- PostgreSQL excels in scenarios demanding enterprise-level applications with frequent write operations and intricate queries. On the other hand, MySQL is an appropriate choice when initiating a project for prototyping, developing internal applications with fewer users, or establishing an information storage engine with a focus on read-heavy workloads and infrequent data updates.
- MySQL is a beginner-friendly option with a relatively shorter learning curve, making it quicker to initiate a new database project. Setting up MySQL, either as a standalone solution or in combination with other web development technologies like the LAMP stack, is a straightforward process. In contrast, PostgreSQL tends to present more challenges for those new to the database world. It often involves intricate infrastructure configuration and may require troubleshooting expertise.
- MySQL employs write locks to achieve genuine concurrency, meaning that when one user is making changes to a table, another user may need to wait until the operation is complete before modifying the table. On the other hand, PostgreSQL offers built-in support for Multiversion Concurrency Control (MVCC) without the need for read-write locks. This approach enables PostgreSQL databases to deliver superior performance, particularly when handling frequent and simultaneous write operations.
- PostgreSQL initiates a fresh system process with substantial memory allocation, roughly around 10 MB, for each user linked to the database. This approach demands memory-intensive resources to accommodate scaling for numerous users. In contrast, MySQL adopts a single process to handle multiple users. Consequently, MySQL databases exhibit superior performance in applications primarily focused on reading and presenting data to users.
Spring Boot PostgreSQL Example
Step1: Create an SB with JPA classes
I have created a Tradesman application in another blog, which has JPA connectivity, and controller APIs and uses an H2 DB, I will use that application as the Skelton for the PostgreSQL connectivity.
Step2: Add maven dependency for Postgres
Added a PostgreSQL dependency along with dependency of jsonb types which will use later for JPA operations
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>io.hypersistence</groupId>
<artifactId>hypersistence-utils-hibernate-62</artifactId>
<version>3.6.0</version>
</dependency>
Step3: Config Properties
Application Properties
logging.level.liquibase = INFO
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/tradesman
spring.datasource.username=postgres
spring.datasource.password=password
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect
Liquibase changes
<column name="resume" type="jsonb">
<constraints nullable="true" />
</column>
Step4: Code changes for Adding JSON Column
Request model : The request model takes a JsonNode input, which ensures the JSON can be sent by client.
@Data
@JsonIgnoreProperties(ignoreUnknown = true)
public class Person {
.......
private JsonNode resume;
}
PersonEntity : The entity takes a JSONB input
public class PersonEntity extends BaseDataEntityWithAudit {
.....
@Type(JsonType.class)
@Column(columnDefinition = "jsonb")
private String resume;
}
CreatePersonEntity : Takes a JSON and convert it to String to be stored in DB
private PersonEntity createPersonEntity(Tradesman tradesman) throws JsonProcessingException {
PersonEntity personEntity = new PersonEntity();
......
JsonNode resumeFields = tradesman.getPerson().getResume();
if (resumeFields != null) {
ObjectMapper objectMapper = new ObjectMapper();
String json = objectMapper.writeValueAsString(resumeFields);
personEntity.setResume(json);
}
return personEntity;
}
ReadJSON Column : The query reads JSON data as an Object and then we convert it to JsonNode
public List<ListOfTradeResponse> mapResponse(List<TradeResponse> tradeResponses) throws JsonProcessingException {
List<ListOfTradeResponse> listOfTradeResponses = new ArrayList<>();
for (TradeResponse tradesFromDB: tradeResponses) {
.....
ObjectMapper objectMapper = new ObjectMapper();
Object object = tradesFromDB.getResume();
JsonNode jsonNode = objectMapper.readTree(object.toString());
String yoe = jsonNode.get("yoe") != null ? jsonNode.get("yoe").asText() : "";
String status = jsonNode.get("yoe") != null ? jsonNode.get("status").asText() : "";
tradeResponse.setYoe(yoe);
tradeResponse.setStatus(status);
}
return listOfTradeResponses;
}
Step5: Run the Application
I have pushed the images to the docker repository, and run the docker-compose file to run the application.
docker-compose.yml
version: '3.8'
services:
app:
image: 'jonesjalapatgithub/tradesman:pg'
build:
context: .
container_name: tradesman_service
ports:
- "8080:8080"
depends_on:
- db
environment:
- SPRING_DATASOURCE_URL=jdbc:postgresql://db:5432/tradesman
- SPRING_DATASOURCE_USERNAME=postgres
- SPRING_DATASOURCE_PASSWORD=password
- SPRING_JPA_HIBERNATE_DDL_AUTO=update
db:
image: 'postgres:alpine'
container_name: db
environment:
- POSTGRES_USER=postgres
- POSTGRES_DB=tradesman
- POSTGRES_PASSWORD=password
docker-compose up
Step6: Invoke the Application
Create Tradesman with JSON type Input
DB insertion
Reads the number of tradespersons for a given trade
