Part 10 – Production Best Practices, Performance Tuning and Enterprise Architecture

Posted on by Rahul Mittal

Introduction

A Java application that works in development may fail in production because of:

  • Missing indexes
  • Poor schema design
  • Connection pool exhaustion
  • Large transactions
  • N+1 queries
  • Missing pagination
  • Unoptimized SQL
  • Excessive logging
  • Improper caching

Production systems require much more than:

repository.save(entity);

A production-ready application requires:

✅ Proper schema design

✅ Connection pool tuning

✅ Monitoring

✅ Query optimization

✅ Caching

✅ Transaction management

✅ Deployment standards

This article concludes the Oracle and JPA series by focusing on production systems.

Enterprise Application Architecture

Client
   ↓
API Gateway
   ↓
Controller
   ↓
Service
   ↓
Repository
   ↓
JPA/Hibernate
   ↓
Connection Pool
   ↓
Oracle Database

Each layer should have a clear responsibility.

Schema Standards

Every table should follow consistent naming.

Tables

EMPLOYEE
ORDER_HEADER
ORDER_ITEM

Primary Keys

EMP_ID
ORDER_ID

Foreign Keys

DEPT_ID
CUSTOMER_ID

Constraints

PK_EMPLOYEE
FK_EMPLOYEE_DEPARTMENT
UK_EMPLOYEE_EMAIL

Recommended Audit Columns

Every business table:

CREATED_DATE TIMESTAMP NOT NULL

CREATED_BY VARCHAR2(50)

UPDATED_DATE TIMESTAMP

UPDATED_BY VARCHAR2(50)

VERSION NUMBER

Soft Delete

Avoid:

DELETE FROM EMPLOYEE;

Instead:

STATUS='INACTIVE'

or

IS_DELETED='Y'

Advantages:

  • Auditability
  • Recovery
  • Historical reporting

Example

@Column(name = "IS_DELETED")
private String deleted;

Connection Pool Tuning

Spring Boot uses HikariCP.

Recommended Settings

spring.datasource.hikari.maximum-pool-size=20

spring.datasource.hikari.minimum-idle=5

spring.datasource.hikari.connection-timeout=30000

spring.datasource.hikari.idle-timeout=300000

spring.datasource.hikari.max-lifetime=1800000

Pool Size Recommendations

CPU CoresPool Size
25
410
820
1630

Large pools:

❌ More memory

❌ More context switching

❌ Reduced throughput

SQL Logging

Development:

logging.level.org.hibernate.SQL=DEBUG

logging.level.org.hibernate.orm.jdbc.bind=TRACE

Production:

OFF

Avoid logging sensitive data.

Pagination

Bad:

repository.findAll();

Good:

PageRequest.of(0,20);

Generated SQL:

OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY

DTO Projections

Avoid:

Employee entity

Return:

EmployeeSummaryDto

Advantages:

  • Smaller payloads
  • Faster serialization
  • Less memory

Index Best Practices

Create indexes on:

✅ Primary keys

✅ Foreign keys

✅ Search columns

✅ Frequently filtered columns

Avoid indexes on:

❌ Frequently updated columns

❌ Low-selectivity columns

Example

Good:

CREATE INDEX IDX_EMP_EMAIL
ON EMPLOYEE(EMAIL);

Bad:

CREATE INDEX IDX_STATUS
ON EMPLOYEE(STATUS);

When only:

ACTIVE
INACTIVE

exist.

Composite Indexes

CREATE INDEX IDX_ORDER
ON ORDERS
(
    CUSTOMER_ID,
    STATUS
);

Best for:

WHERE CUSTOMER_ID=?
AND STATUS=?

Statistics

Oracle optimizer depends on statistics.

Gather:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    USER,
    'EMPLOYEE');

Explain Plan

Always analyze slow SQL.

EXPLAIN PLAN FOR
SELECT ...

Query Optimization Checklist

QuestionRecommendation
SELECT * used?No
Index available?Yes
Pagination used?Yes
Full scan?Avoid
Large joins?Review

Flyway

Database migrations.

Example:

V1__Create_employee.sql

V2__Add_index.sql

V3__Create_department.sql

Liquibase

XML/YAML migration approach.

Advantages:

  • Version control
  • Rollback
  • Automated deployments

Never Use

ddl-auto=update

Production:

ddl-auto=validate

Batch Processing

hibernate.jdbc.batch_size=50

Benefits:

  • Fewer round trips.
  • Faster inserts.

Monitoring

Important metrics:

  • Active connections
  • Slow SQL
  • Deadlocks
  • Transaction duration
  • Cache hit ratio

Oracle Views

V$SESSION

V$SQL

V$LOCK

V$TRANSACTION

Slow SQL Detection

Look for:

Elapsed Time

Buffer Gets

Disk Reads

AWR Reports

Automatic Workload Repository.

Provides:

  • Top SQL
  • Wait events
  • CPU usage
  • Memory usage

ASH Reports

Active Session History.

Useful for:

  • Blocking sessions
  • Performance issues
  • Locking problems

Logging Best Practices

Log:

✅ Request IDs

✅ Transaction IDs

✅ Error codes

Avoid:

❌ Passwords

❌ Personal data

❌ Large payloads

Exception Handling

Map:

ExceptionHTTP
EntityNotFound404
Validation400
Duplicate Key409
Unexpected500

Caching Strategy

DataCache
Reference DataYes
ConfigurationYes
ReportsYes
TransactionsNo

Redis Use Cases

  • Master data
  • Country lists
  • Product catalogs
  • Configuration

Second-Level Cache

Useful for:

  • Read-heavy systems
  • Lookup tables

Avoid for:

  • Frequently updated entities

API Design

Avoid:

Employee Entity

Return:

EmployeeResponse

Benefits:

  • Versioning
  • Security
  • Smaller payloads

Security Best Practices

✅ Dedicated DB users

✅ Password rotation

✅ Least privilege

✅ Separate schemas

✅ Auditing

Production Checklist

Database

✅ Indexes

✅ Statistics

✅ Constraints

✅ Audit columns

JPA

✅ LAZY loading

✅ DTOs

✅ Pagination

✅ Version column

Transactions

✅ Short transactions

✅ Timeouts

✅ Optimistic locking

Operations

✅ Monitoring

✅ Alerting

✅ Logging

Example Enterprise Table

EMPLOYEE
---------
EMP_ID

NAME

EMAIL

STATUS

CREATED_DATE

CREATED_BY

UPDATED_DATE

UPDATED_BY

VERSION

Common Production Problems

ProblemRoot Cause
Slow APIsN+1
High DB CPUMissing indexes
Connection exhaustionLeaks
DeadlocksLong transactions
Memory issuesLarge result sets
Slow startupEAGER loading

Enterprise Recommendations

OLTP Systems

  • Small transactions
  • Optimistic locking
  • Pagination

Reporting Systems

  • Read-only transactions
  • DTOs
  • Batch processing

Microservices

  • UUID keys
  • DTO APIs
  • Event-driven integration

Banking Systems

  • Sequence keys
  • Pessimistic locking
  • Strong auditing

Interview Questions

Why avoid ddl-auto=update?

Uncontrolled schema changes.

Why use pagination?

Reduce memory and SQL load.

Why use DTOs?

Smaller object graphs.

Why gather statistics?

Optimizer decisions.

What causes pool exhaustion?

Connection leaks.

Why use audit columns?

Tracking changes.

Complete Series Summary

PartTopic
Part 1Oracle Fundamentals and Schema Design
Part 2Users, Roles and Security
Part 3SQL and Query Optimization
Part 4JDBC Connectivity
Part 5JPA and Hibernate
Part 6Relationships and Joins
Part 7Composite Keys
Part 8Lazy Loading and Performance
Part 9Transactions and Locking
Part 10Production Best Practices

Final Recommendations for Java Developers

Database Design

✅ Use meaningful datatypes.

✅ Add constraints.

✅ Keep schemas normalized.

JPA

✅ Prefer LAZY.

✅ Use DTOs.

✅ Add version columns.

Performance

✅ Use indexes.

✅ Use pagination.

✅ Avoid N+1 queries.

Transactions

✅ Keep them short.

✅ Use optimistic locking.

Production

✅ Monitor everything.

✅ Version database changes.

✅ Separate environments.

Conclusion

Oracle, JPA, and Hibernate are powerful technologies, but performance and maintainability depend more on design decisions than frameworks.

A strong backend developer understands:

  • Data modeling
  • SQL optimization
  • Transaction management
  • JPA performance
  • Database operations
  • Production troubleshooting

Those skills remain valuable regardless of the framework or architecture used in future applications.

Leave a Reply

Your email address will not be published. Required fields are marked *