The Mendix applications are getting older and older which is good. But is also means that table sizes can become huge. It is time that Mendix starts to support table partiioning (https://www.postgresql.org/docs/10/ddl-partitioning.html ) so that the data can be split up. Now you have to create your own archive tables and must wrestle with the problem of searching over different tables.
Regards,
Ronald
Hello Ronald & Mendix,
I agree with Ronald's idea about supporting table partitioning in Mendix, especially for older applications with large tables. PostgreSQL table partitioning offers significant benefits:
Improved Query Performance: By splitting large tables into smaller partitions, queries can be faster, as the database scans only relevant partitions.
Easier Maintenance: Partitioning allows easier data management, such as archiving old data without impacting current data performance.
Efficient Bulk Operations: Bulk inserts, updates, and deletes are more efficient on smaller partitions.
Example:
Consider an AuditTrail
table with 10 years of data, averaging 2 million records per year. Partitioning by year will significantly enhance performance.
SQL to create such table/partitioning:
CREATE TABLE AuditTrail (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMP NOT NULL,
-- other columns
) PARTITION BY RANGE (timestamp);
DO $$
DECLARE
year INT;
BEGIN
FOR year IN 2014..2023 LOOP
EXECUTE format('
CREATE TABLE AuditTrail_%s PARTITION OF AuditTrail
FOR VALUES FROM (''%s-01-01'') TO (''%s-12-31 23:59:59.999'');
', year, year, year);
END LOOP;
END $$;
Disadvantages of Partitioning:
Index Management: Indexes must be created and managed separately for each partition.
Ongoing Maintenance: Requires regular maintenance to ensure partitions remain balanced and efficient. Although you can already create partitions for the years to come in above example to keep maintenance low.
Table column changes: If you add or drop a column from the main table, you must perform the same operation on each partition manually.
But Mendix should be capable of generating the DDL for automating the database maintenance (adding/removing/changing columns and indexes) the low code way I believe.
Can you consider planning support for this?
It would make Mendix even better suited for large database apps.
Thanks in advance!
This is not planned
Can anyone from Mendix tell us whether supporting this is planned for development?