Skip to content

PostgreSQL

PostgreSQL is an open-source, object-relational database management system (RDBMS). It is known for its robustness, scalability, and compliance with SQL standards, making it a popular choice for enterprise applications and projects that require reliability and data integrity.

Key Features

1. ACID Compliance

PostgreSQL is fully compliant with the ACID (Atomicity, Consistency, Isolation, and Durability) properties, ensuring that transactions are processed reliably and that data remains consistent even in the event of failures.

2. Advanced Data Types

PostgreSQL supports a wide variety of data types, including:

  • Primitive types (integers, numerics, strings, booleans)
  • Structured types (date/time, arrays, JSON, XML)
  • Geometric types (points, lines, polygons)
  • User-defined custom types

3. Extensibility

One of PostgreSQL's most powerful features is its extensibility. Users can:

  • Create custom data types
  • Define custom functions
  • Implement procedural languages (PL/pgSQL, PL/Python, PL/Java, etc.)
  • Add extensions that provide additional functionality

4. Advanced Features

PostgreSQL offers many advanced features, including:

  • Replication (synchronous and asynchronous)
  • Table partitioning
  • Various index types (B-tree, Hash, GiST, SP-GiST, GIN, BRIN)
  • Complex queries with Common Table Expressions (CTEs) and window functions
  • Triggers and rules for automation
  • Support for distributed transactions

Usage in the Project

In our microservices project, PostgreSQL is used as the primary database to store user data, feedback requests, and responses. Each microservice can have its own schema within the database, maintaining data isolation while sharing the same infrastructure.

Configuration in Docker Compose

services:
  db:
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: root
      POSTGRES_DB: feedback
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

Connection from Microservices

Java (Spring Boot)

spring.datasource.url=jdbc:postgresql://${DB_HOST}:${DB_PORT}/feedback
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASS}
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update

Python (FastAPI with Peewee)

from peewee import PostgresqlDatabase

database = PostgresqlDatabase(
    'feedback',
    user='postgres',
    password='root',
    host='db',
    port=5432
)

Data Modeling

Schema for User Microservice

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    senha VARCHAR(255),
    cargo VARCHAR(255) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    pdm_id INTEGER,
    ativo BOOLEAN DEFAULT TRUE,
    data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data_edicao TIMESTAMP,
    data_desativacao TIMESTAMP,
    FOREIGN KEY (pdm_id) REFERENCES usuarios(id)
);

Schema for Feedback Request Microservice

CREATE TABLE feedback_requests (
    id SERIAL PRIMARY KEY,
    requester_id INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP,
    rejected_at TIMESTAMP,
    edited_at TIMESTAMP
);

CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    feedback_request_id INTEGER NOT NULL,
    text TEXT NOT NULL,
    FOREIGN KEY (feedback_request_id) REFERENCES feedback_requests(id)
);

CREATE TABLE appraisers (
    id SERIAL PRIMARY KEY,
    feedback_request_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    responded_at TIMESTAMP,
    FOREIGN KEY (feedback_request_id) REFERENCES feedback_requests(id)
);

Schema for Feedback Response Microservice

CREATE TABLE feedback_responses (
    id SERIAL PRIMARY KEY,
    request_id INTEGER NOT NULL,
    appraiser_id INTEGER NOT NULL,
    responded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE question_responses (
    id SERIAL PRIMARY KEY,
    feedback_response_id INTEGER NOT NULL,
    question_id INTEGER NOT NULL,
    text TEXT NOT NULL,
    FOREIGN KEY (feedback_response_id) REFERENCES feedback_responses(id)
);

Best Practices with PostgreSQL

1. Indexing

Use indexes to improve query performance:

CREATE INDEX idx_usuarios_email ON usuarios(email);
CREATE INDEX idx_feedback_requests_requester_id ON feedback_requests(requester_id);

2. Transactions

Use transactions to ensure data consistency:

@Transactional
public void criarSolicitacaoFeedback(SolicitacaoDTO dto) {
    // Database operations
}

3. Connection Pooling

Configure a connection pool to efficiently manage database connections:

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000

4. Migrations

Use database migration tools to manage schema changes:

// Example with Flyway
spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true

Monitoring and Maintenance

1. Statistics

Collect statistics regularly to optimize the query planner:

ANALYZE;

2. Vacuum

Run VACUUM regularly to reclaim space and maintain performance:

VACUUM ANALYZE;

3. Backup

Implement a regular backup strategy:

pg_dump -U postgres -d feedback -f backup.sql

Conclusion

PostgreSQL is an excellent choice for data storage in microservices-based applications due to its reliability, advanced features, and flexibility. In our project, it provides a solid foundation for storing and managing user data, feedback requests, and responses, ensuring data integrity and consistency across the entire application.