Platform built-in optimizations
All the code generated by the OutSystems Platform enforces a series of rules and optimizations to maximize your applications scalability and performance. All the optimizations below are applied automatically to the database accesses:
- Database connection pooling avoids excessive use of operating system, network, and database resources. Find out more about connection pooling in the OutSystems Platform here.
- Smart release of connections reduces database operating system, network, and database resources consumption.
- Optimized database fields fetch ensures only data that is effectively used in the application is fetched from the database regardless what the developer coded.
- Smart database to memory algorithms assure minimal memory is used by only loading datasets into memory as a last resort.
- Automated indexing of primary and foreign keys optimizes the most common database queries.
- Smart commit, rollback and release of transactions assures no database update is left forgotten and no database update is done in case errors occur. Learn more about transactions in the OutSystems Platform here.
- Queries automatic instrumentation and monitoring to detect underperforming queries of your applications without requiring any explicit coding for that purpose.
- Database timeout containment eliminates the risk of heavy queries affecting your application and database environment performance.
Optimizing database access
The following are recommendations to further improve performance in database access:
- Index often used fields
- Remove unused indexes
- Isolate large text of binary fields in separate entities
- Avoid cross-server joins which are very inefficient
- Apply recommendations from the installation checklist
- Avoid using preparation data in screen actions
- Utilize caching provided by platform whenever possible
The following articles detail some best practices on database modeling and access:
Databases can be scaled up to support larger workloads as needed. It is key to use the performance analysis tools provided by the platform to analyze workloads and bottlenecks in database access. To further identify performance bottlenecks, you can also use the database's analysis tools to analyze workloads (e.g. query plan analyzers)
Up until now, all scalability requirements or database performance bottlenecks identified in our install base have been properly tackled through the proper use of the following strategies (but not limited to):
- Proper database maintenance plans, indexing, or query optimization.
- Query hints (e.g. NOLOCK) on specific queries identified as bottlenecks during the performance tuning
- Denormalization of some data, to support critical data access
- Use of application caching mechanisms (Platform built-in or external)
In fact, "the best way to optimize access to a database is not to access it at all". Focused application caching strategies are key to optimize workloads of your applications.
The caching mechanisms provided by the Platform will cover most of the scenarios in this purpose. Otherwise, focused (distributed) application caching solutions, that cache specifically the operations that are identified as sensitive/bottlenecks, are usually easier to implement and maintain in the long term than a replicated (large piece of the) datacenter. Check this for a summary of how that is done with the Platform
Scaling out databases - Sharding
OutSystems does not support database sharding (write vs read-only replicas, or in other words, having the platform/applications execute all read operations on a predefined instances of a clustered database server).
Even though this is a possible scalability approach for transactional databases and, similar to what we do with (e.g.) multi-tenancy, the platform could help automating the logic that is required to route writes vs reads in a sharded database environment, we have no plans to implement such capability in our roadmap.
As described above, the multiple strategies to optimize database access have been successful for the optimization of all data access bottlenecks. With this in mind, we are not prioritizing support / automation of sharding for OutSystems databases in our roadmap.