Investigating Performance and Reliability in an Enterprise ERP

Context

Within a centralized multi-school ERP engagement through NeoSOFT, production and reliability concerns appeared across cache, database, attendance request paths, middleware latency, and connection pooling.

Problem

Operational paths showed related but distinct pressure: Redis memory growth, PostgreSQL CPU load, materialized-view refresh scheduling, attendance query amplification from repeated record-level operations, middleware latency, and connection-pool sizing concerns.

Constraints

• • Incomplete or uneven observability in places
• • Need to protect live institutional operations
• • Limited maintenance windows
• • Client confidentiality — incident identifiers stay private
• • No public percentage improvements — measured effect requires an agreed baseline and validation window

My role

Investigation, technical coordination, and prioritizing corrective work with the delivery and infrastructure teams.

Team contribution

These were collaborative actions across engineering and infrastructure contributors. This write-up distinguishes observation, immediate stabilization, later investigation, proposed follow-up, and unmeasured outcomes.

Observation

• • Redis memory growth tied to an unbounded or poorly retained key pattern.
• • PostgreSQL CPU pressure under operational and refresh workloads.
• • Materialized-view refresh scheduling contributing to database load.
• • Attendance paths amplifying database work through repeated record-level operations.
• • Middleware latency investigated on critical request paths.
• • Database connection-pool sizing raised as a reliability concern.

Immediate stabilization

• • Clean up oversized Redis data and increase capacity for immediate stabilization.
• • Expand selected PostgreSQL capacity and address chosen indexes/authorization query pressure where evidence supported action.
• • Pause materialized-view refreshes while reviewing their scheduling and production cost.

Optimization and investigation

Later investigation examined authorization queries, refresh scheduling, attendance request amplification, middleware latency on critical paths, and connection-pool behavior. These were treated as related contributors to load rather than a single root cause.

Proposed follow-up

Proposed follow-up included validating a bulk attendance redesign and attending to connection-pool sizing, retention controls, monitoring, and a measurement plan. Those items remain follow-up pending validation.

Measured result and recorded status

• • Immediate stabilization actions recorded for Redis cleanup/capacity and selected PostgreSQL capacity/index work.
• • Materialized-view refreshes paused while scheduling was reviewed.
• • No public percentage latency or CPU improvement claims — measured results were not approved for publication.
• • Bulk attendance redesign, pool sizing, retention, and monitoring remain proposed follow-up.

Lessons

• • Bound retained data explicitly.
• • Treat reporting refreshes as production workloads.
• • Measure database work per user action when investigating API load.
• • Separate observation, stabilization, investigation, and follow-up before claiming outcomes.