Expand all | Collapse all

Informix storage limits

  • 1.  Informix storage limits

    Posted Tue June 02, 2020 12:16 PM
    Hi community,

    I am trying to raise the profile within HCL of a couple of issues related to how Informix stores data on disk and get them on the Informix roadmap. If you have a large system you will be familiar with them already I am sure.

    They are the Informix data pages per fragment limit of 16,775,134 and the rows per page limit of 255 rows per page. My opinion is that these limits are going to be more of a problem in the future as a lot of well-established systems are only getting bigger and there is a trend towards Big Data.

    The first is the most serious as, if you reach this limit for any table or table partition, no-one can no longer insert any data and dealing with the issue is often not straightforward or quick. Even if you don't ever have a catastrophe, planning around them, putting in place the necessary monitoring and re-organising tables affected can be a drain on DBA time and cause downtime for your business.

    I have updated someone else's existing RFE with more information regarding the pages per partition limit:

    And raised my own RFE about the rows per page limit:

    I would be grateful if you could vote for one or both of them.

    Thank you,

    Benjamin Thompson

  • 2.  RE: Informix storage limits

    Posted Tue June 02, 2020 12:24 PM
    Yes, that existing RFE on pages per fragment limit is probably the one I put out there a while back and the initial feedback I received was that it was not likely to get done, yet I agree this is an important limitation to get rid of.  I will vote for the other one as well.  I hope they get into the plans to address.

    Hal Maner
    M Systems International, Inc.

  • 3.  RE: Informix storage limits

    Posted Tue June 02, 2020 01:24 PM
    The problem is that to change the number of pages in a data partition beyond 2^24 one would have to change the ROWID to something similar to the ifx_row_id which is <partnum>:<rowid>. Perhaps a partnum:pagenum:slotnum with a 32bit partnum and pagenum and a 16bit slotnum rather than the 24bit page number and 8bit slot number. But that's a major change from a 32bit row address to an 80bit row address. Currently indexes on partitioned tables use ifx_row_id but indexes on non-fragmented tables use the simple rowid.


    Art S. Kagel, President and Principal Consultant
    ASK Database Management

    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

  • 4.  RE: Informix storage limits

    Posted Tue June 02, 2020 12:59 PM

    I remember Scott discussing this years ago, he wanted to make every fragmented by default as he claimed it would easier to add more fragments at a later date.  He usually knew what he was taking about





  • 5.  RE: Informix storage limits

    Posted Tue June 02, 2020 01:03 PM
    It would be good in Workgroup edition if use of partitioning for reasons of storage space was separated from PDQ, detach/drop, fragment elimination and other benefits of having Enterrprise Edition, otherwise this solution is only available to a small portion of users.

    Benjamin Thompson