Skip to main content

Optimize PostgreSQL Reporting Database Performance for High-Volume Environments

Einars Geidans

In high-volume SFTP environments, the PostgreSQL reporting database used by Cerberus becomes a bottleneck. Specifically, the "sessions" table grows rapidly and is subject to continuous updates (insert on session start, update on session close). This results in:

  • Extremely frequent checkpoints and WAL pressure.

  • Autovacuum either not triggering or lagging due to default thresholds, leading to dead tuple bloat.

  • Queries on the "sessions" table slowing down significantly as size grows.

  • Reporting data falling behind real-time by up to a week.

Observed Data:

  • "sessions" table has ~6.5M rows with >600k updates in a short window.

  • Dead tuples accumulate quickly (tens of thousands within hours).

  • Even with SSD tuning and increased WAL sizes, backlog persists.

Impact:
The reporting DB is not reliable under high load. Administrators cannot depend on timely reporting data, which undermines Cerberus’ value in auditing and compliance-heavy environments.

Feature Request:
Please consider optimizations for the reporting database, such as:

  1. Index optimization: provide recommended indexes for typical reporting queries.

  2. Bulk-write optimization: buffer session updates and write in batches instead of frequent row updates.

  3. Documentation: publish PostgreSQL tuning guidelines for heavy-traffic environments (checkpoint tuning, WAL sizing, autovacuum settings, etc.).

  4. Table partitioning support: The "sessions" table would also benefit from PostgreSQL partitioning, for example by week or month. Partitioning would keep active data small and fast while making VACUUM and maintenance far more efficient.

  5. Connection management: Another improvement would be better connection management. Adding support for pooling or configurable connection limits would help the cause.
3

Comments

5 comments

Date Votes
  • Connor Woolfolk
    Community Manager

    Hello Einars,

    Thanks so much for taking the time to submit this request. I'll go ahead and get this wrapped up and sent over to our Product team for their review. If we need any further information, we'll reach out here, or open up a ticket for a direct line of communication.

    0
  • Einars Geidans

    Hello,
    Any updates on the matter?

    0
  • Connor Woolfolk
    Community Manager

    Hello Einars,

    This is currently being discussed with my engineering and product teams. As soon as I have a material update to share, I will be sure to send you an update! Thanks so much for your patience, sometimes this process can take some time.

    0
  • Einars Geidans

    Thank You! Please keep us posted.

    0
  • Connor Woolfolk
    Community Manager

    Hello Einars,

    Thanks so much for your patience. I was able to discuss this with my product team, it looks like while there is not immediate action planned around PostGRES, they did say they will keep this on their radar. While we can't move forward with this enhancement right now, it may be possible to alleviate some of this bottleneck by archiving the previous fairly full DB, and cleaning it out. If you have any questions, we'll be more than happy to assist here, or through a ticket if you'd like a more direct route to us.

     

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post