Create support for table partioning in Postgres - Mendix Forum

Create support for table partioning in Postgres

47

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
4 answers

In the meantime, I have received feedback from Andrej Koelewijn about the Clustered Index feature available in PostgreSQL:

 

Postgres (and probably other database as well) does offer an interesting alternative to partitioning that we successfully used a few years ago with a Mendix app to speed up queries on active and archived records: cluster by index (https://www.postgresql.org/docs/16/sql-cluster.html). You can use this to reorder database records on disk based on an index, e.g., an index on an archive column. The cluster command was run using the java jdbc api in Mendix, so requires some java coding. Other downside is that while postgres is reordering the database records the table is locked for read and write…

 

You need to make sure that you regularly update/refresh the clustered indexes to maintain performance.

The main benefit is that this does not add complexities like the Partitions do, having to repeat field changes/additions/removals on all partitions, but all is managed by the database behind the scene.

Created

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