[defect]: PostgreSQL SQL module performance degradation with large datasets and insufficient indexing

[simeononsecurity]

What type of defect/bug is this?

Unexpected behaviour (obvious or verified by project member)

How can the issue be reproduced?

How can the issue be reproduced?

1. Set up FreeRADIUS 3.2.x (Tested in 3.2.3 - 3.2.7) with PostgreSQL backend (tested on versions 16 and 17)
2. Configure standard radacct, radpostauth, and cui tables using default schema
3. Generate accounting traffic to accumulate 2+ million records in radacct table
4. Observe query timeouts and performance degradation
5. Scale to 10+ million records - performance becomes unacceptable even with buffered/decoupled accounting

High-Level Suggestions for Resolution

Database Schema Issues:

1. Insufficient Indexing: Default PostgreSQL schema lacks indexes for common WHERE clauses and query patterns used by FreeRADIUS
2. Monolithic Table Design: radacct, radpostauth, and cui tables grow indefinitely without built-in archival strategy
3. Partitioning Limitations: Standard partitioning approaches conflict with radacctid uniqueness constraints

Performance Scaling Problems:

1. Query Timeout Threshold: Performance degrades significantly after ~2 million records without additional indexing
2. Index Effectiveness Limit: Even with custom indexes, performance issues emerge around 10 million records
3. Hardware Dependency: Requires NVMe storage and significant hardware upgrades to maintain performance

Architectural Recommendations:

1. Enhanced Default Indexing: Include performance-optimized indexes in default PostgreSQL schema
2. Built-in Archival Strategy: Provide recommended data retention and archival procedures
3. Partitioning Guidance: Document partition-friendly schema modifications for high-volume deployments
4. Load Distribution: Improve buffered SQL processing with intelligent load balancing
5. Monitoring Integration: Include performance monitoring recommendations for production deployments

Impact:

This affects any FreeRADIUS deployment using PostgreSQL at scale, particularly ISPs and large organizations processing millions of accounting records. Current workarounds require significant database expertise and custom schema modifications.

Log output from the FreeRADIUS daemon

N/A

Relevant log output from client utilities

N/A

Backtrace from LLDB or GDB

N/A

[simeononsecurity]

At a high level, we've evaluated the other database offerings (MySQL, MariaDB, etc.) and they would all suffer from the same fundamental issues - insufficient indexing strategies, monolithic table growth, and lack of built-in archival mechanisms for high-volume production deployments.

We acknowledge this is primarily a SQL/database design issue rather than a core FreeRADIUS problem. However, improving these aspects from the FreeRADIUS documentation and defaults side would significantly enhance the production viability of FreeRADIUS deployments. The reality is that not everyone can get by with the bare minimum configurations offered in the current documentation - many production environments require robust, scalable database strategies that can handle millions of records efficiently.

I'm currently working on cleaning up some of my production examples and configurations that address these scaling challenges. I plan to submit those as concrete suggestions and potential patches in the coming days/weeks to help improve the out-of-box experience for high-volume deployments.

[alandekok]

Some comments:

Insufficient Indexing: Default PostgreSQL schema lacks indexes for common WHERE clauses and query patterns used by FreeRADIUS

I'm not sure which indexes are missing? The common ones are there.

More indexes are OK, but adding indexes also have a cost.

Monolithic Table Design: radacct, radpostauth, and cui tables grow indefinitely without built-in archival strategy

The server doesn't come with a set of cron jobs to do automatic cleanups. Those can be added, but there are caveats. Most people will have unique / legal requirements for cleanups. So there can't be a default set added.

Partitioning Limitations: Standard partitioning approaches conflict with radacctid uniqueness constraints

The solution is largely "don't partition". And the raddacctid field isn't unique, radacctuniqueid is. And that should very much be unique.

Query Timeout Threshold: Performance degrades significantly after ~2 million records without additional indexing

Adding indexes only goes so far.

Index Effectiveness Limit: Even with custom indexes, performance issues emerge around 10 million records

If you have 10M users, you can shard the DB. If you don't have 10M users, you shouldn't be storing 10M rows. If you do need 10M rows for CALEA purposes, the solution is to shard the DB.

The default configuration (schema, queries) are intended to work for nearly everyone, nearly all of the time. For high-volume sites, the best approach is to give guidance and tools. But it will be difficult to make a default configuration which works for both small sites, and all large ones.

We'll review any patches / code which are sent over. We're always happy to make the server better.

[WayFi-Simeon]

Some comments:

Insufficient Indexing: Default PostgreSQL schema lacks indexes for common WHERE clauses and query patterns used by FreeRADIUS

I'm not sure which indexes are missing? The common ones are there.

More indexes are OK, but adding indexes also have a cost.

Monolithic Table Design: radacct, radpostauth, and cui tables grow indefinitely without built-in archival strategy

The server doesn't come with a set of cron jobs to do automatic cleanups. Those can be added, but there are caveats. Most people will have unique / legal requirements for cleanups. So there can't be a default set added.

Partitioning Limitations: Standard partitioning approaches conflict with radacctid uniqueness constraints

The solution is largely "don't partition". And the raddacctid field isn't unique, radacctuniqueid is. And that should very much be unique.

Query Timeout Threshold: Performance degrades significantly after ~2 million records without additional indexing

Adding indexes only goes so far.

Index Effectiveness Limit: Even with custom indexes, performance issues emerge around 10 million records

If you have 10M users, you can shard the DB. If you don't have 10M users, you shouldn't be storing 10M rows. If you do need 10M rows for CALEA purposes, the solution is to shard the DB.

The default configuration (schema, queries) are intended to work for nearly everyone, nearly all of the time. For high-volume sites, the best approach is to give guidance and tools. But it will be difficult to make a default configuration which works for both small sites, and all large ones.

We'll review any patches / code which are sent over. We're always happy to make the server better.

---

Thank you for the detailed feedback. Let me clarify some points:

Regarding Indexing:
You're right that the common indexes are there. I have specific suggestions for additional indexes that address query patterns we've observed in production - I'll provide those in a follow-up comment with concrete examples.

Regarding Archival Strategy:
I understand the legal/compliance complexity around automatic cleanups. My approach isn't deletion-based archival, but rather a "hot/warm" table strategy. The concept is:

• Keep a working radacct table with only active sessions and recent closed sessions (24-48 hours)
• Use a background process (pg_cron) to move completed sessions to an archive table
• This keeps the primary working tables small while preserving all data for compliance

This isn't about deleting data - it's about keeping the tables FreeRADIUS actively queries as lean as possible. I'm working on code examples for this approach.

Regarding Partitioning:
You're absolutely correct about radacctuniqueid vs radacctid - my mistake. The PostgreSQL partitioning limitation is that unique constraints must include the partition key. So partitioning on acctstarttime alone fails because radacctuniqueid can't span partitions. Partitioning on (acctstarttime, radacctuniqueid) creates other complications. If there are alternative approaches to this, I'd welcome suggestions.

Regarding Scale and Sharding:
Our use case is proxy-based with potentially unlimited randomized identifiers. We're not storing 10M users, but rather 10M+ accounting sessions from high-traffic APs (hundreds/thousands of sessions per AP per day). This accumulates quickly in proxy scenarios where we need records for upstream/downstream accountability.

PostgreSQL doesn't have native sharding like some NoSQL databases. While there are solutions like Citus or manual federation, they add significant operational complexity and building things in a way that pushes this off till later is highly appreciated.

Regarding Default vs. Production Configurations:
I completely agree that defaults should work for most users most of the time. My goal isn't to change defaults, but to provide better guidance and optional configurations for production scale. Perhaps documentation sections like "Production Scaling Considerations" or optional schema variants could help bridge this gap.

I'm preparing concrete examples and will submit them for review. The intent is to help FreeRADIUS deployments that have grown beyond the "small site" category but aren't quite at the "enterprise sharding" level.

[alandekok]

Our use case is proxy-based with potentially unlimited randomized identifiers.
...
PostgreSQL doesn't have native sharding like some NoSQL databases.

The solution here is simple. Use multiple databases. Since the identifiers are randomized, you can use a keyed load balancing section to select one of N different SQL databases.

FreeRADIUS will automatically shard the data for you.