Data Management

Workflows

• Schema Design: Define tables, relationships, constraints
• Migrations: Version control schema changes
• Indexing: Add indexes for query performance
• Backup: Ensure data recovery capability

Schema Design Principles

Normalization

• 1NF: Atomic values, no repeating groups
• 2NF: No partial dependencies
• 3NF: No transitive dependencies

When to Denormalize

• Read-heavy workloads
• Reporting/analytics
• Caching layers

Migration Best Practices (Liquibase + PostgreSQL)

Forward-Only Migrations

Each migration should be a single forward step.

<!-- src/main/resources/db/changelog/changes/001-create-users.xml -->
<databaseChangeLog>
  <changeSet id="001-create-users" author="dev">
    <createTable tableName="users">
      <column name="id" type="BIGSERIAL">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="email" type="VARCHAR(255)">
        <constraints nullable="false" unique="true"/>
      </column>
      <column name="created_at" type="TIMESTAMP WITH TIME ZONE" defaultValueComputed="CURRENT_TIMESTAMP">
        <constraints nullable="false"/>
      </column>
    </createTable>

    <createIndex indexName="idx_users_email" tableName="users">
      <column name="email"/>
    </createIndex>
  </changeSet>
</databaseChangeLog>

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - include:
      file: changes/001-create-users.xml
      relativeToChangelogFile: true

-- Alternative: SQL format with Liquibase
-- liquibase formatted sql

-- changeset dev:001-create-users
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
-- rollback DROP TABLE users;

Safe Migrations

• Add columns as nullable first
• Create indexes concurrently
• Never drop columns in the same deploy

Indexing Strategy

-- B-tree (default): Equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Partial index: When you query a subset
CREATE INDEX idx_active_users ON users(id) WHERE active = true;

-- Composite index: Multiple columns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

Connection Management (HikariCP + Spring Boot)

# application.yaml - HikariCP is default in Spring Boot
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/funnyapp
    username: ${DB_USER}
    password: ${DB_PASS}
    hikari:
      maximum-pool-size: 20              # Max connections
      minimum-idle: 5                     # Min idle connections
      connection-timeout: 2000            # Max wait for connection (ms)
      idle-timeout: 30000                 # Close idle connections after (ms)
      max-lifetime: 1800000               # Max connection lifetime (30 min)
      pool-name: FunnyAppPool
      leak-detection-threshold: 60000     # Detect connection leaks (1 min)

  jpa:
    hibernate:
      ddl-auto: none                      # Use Liquibase instead
    properties:
      hibernate:
        default_schema: public
        format_sql: true
        jdbc:
          batch_size: 20
        order_inserts: true
        order_updates: true

  liquibase:
    change-log: classpath:db/changelog/db.changelog-master.yaml
    enabled: true

// JPA Entity with proper relationships
@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_users_email", columnList = "email")
})
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String email;

    @Column(name = "created_at", nullable = false, updatable = false)
    @Builder.Default
    private Instant createdAt = Instant.now();

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Video> videos = new ArrayList<>();
}

// Repository with proper query optimization
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByEmail(String email);

    @Query("SELECT u FROM User u LEFT JOIN FETCH u.videos WHERE u.id = :id")
    Optional<User> findByIdWithVideos(@Param("id") Long id);

    @Query(value = "SELECT * FROM users WHERE created_at > :since ORDER BY created_at DESC",
           nativeQuery = true)
    List<User> findRecentUsers(@Param("since") Instant since);
}

Data Integrity

• Use foreign key constraints
• Add NOT NULL where appropriate
• Use CHECK constraints for validation
• Consider using ENUM types for fixed values