Create support for table partioning in Postgres - Mendix Forum

Create support for table partioning in Postgres

44

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

 

asked
3 answers

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:

  1. Improved Query Performance: By splitting large tables into smaller partitions, queries can be faster, as the database scans only relevant partitions.

  2. Easier Maintenance: Partitioning allows easier data management, such as archiving old data without impacting current data performance.

  3. 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!

Created

This is not planned

Created

Can anyone from Mendix tell us whether supporting this is planned for development?

Created