what is actually stored in serializable_Value column in alf_node_properties table? The column is image data type

[myamcclure]

In Alf_node_properties table, serializable_value column is image data type. Not all the rows has the value. This table is about 300 Gbs. Index is 220 Gb. The content in the column looks like this.

0xACED0005737200196A617661782E63727970746F2E5365616C65644F626A6563743E363DA6C3B754700200045B000D656E636F646564506172616D737400025B425B0010656E63727970746564436F6E74656E7471007E00014C0009706172616D73416C677400124C6A6176612F6C616E672F537472696E673B4C00077365616C416C6771007E00027870757200025B42ACF317F8060854E002000078700000000A040891FB3247DC3DBAFF7571007E000400000058A328511623646833924F16AF0CB659220A41462AC85CBF220C4AD8ED78C9084C7781DE293827DBFD88043226CA2AE2D30FCBD515D6DF69C837B3AA36389E63326FB30F3CBD159A359CABBC9F11B9D8449297889DC7BD1E187400064445536564657400174445536564652F4342432F504B43533550616464696E67

[afaust]

The serializable_value will almost always contain the result of a Java object serialisation. It depends on your usage pattern and any custom content models, how much / little data is stored in that column. By default, only a few technical properties will be stored in that column, in addition to "overly long" text values (text longer than 1024 characters - fewer on some databases - will be stored here rather than in string_value).

If you define any property as d:any or dath, this column will be the default column to store the property value.

[myamcclure]

Axel,

Thank you for the answer. So , it is not an image in the column. I am curious that why alfresco uses image data type. Image data type will be deprecated in future SQL server version. Thanks.

Mya

[afaust]

I don't know which tool you are using to look at the database that displays this as an "image data type". It is a plain old BLOB, nothing special. Or are you referring to MS SQL server? Luckily I haven't had to work with that weird hack of a database yet as an administrator / developer...

[myamcclure]

Yes.  The database is on MS SQL Server.  alf_node_properties table is about 300 GBs , Index size is bigger than data. There are 71 tables and only these tables have good amount of data. and others have less than a hundred.  We have history data that we want to move to a separate database. Does alfresco support table partitioning? Any other archiving strategies? Thank you.

alf_node_properties 989,982,628
alf_node_aspects 214,856,056
alf_child_assoc 49,901,217
alf_node 49,900,284
alf_transaction 38784766
alf_content_data 32988960
alf_content_url 16494597
alf_node_assoc 16494374

[afaust]

The data distribution is normal for an Alfresco system. alf_node_properties is almost always going to be the largest table, and due to support of transactional metadata queries (TMQs), the required indices will always be larger (combined) than the actual data.

Alfresco does not officially support table partitioning, since the techniques for that are different for the supported databases, and it would mean extreme effort to support all upgrade scripts against all possible techniques of partitioning. Since you are using MS SQL, you must be using Alfresco Enterprise and have an active support subscription. If you were to partition your tables you would be running the risk of not being suppported any more in any future issues you may have with the database.

Technically speaking though it is definitely possible and I have done this in a couple of (PostgreSQL-based) systems. The contents of alf_node_properties can be partitioned quite nicely based on the qname_id column, and one could create partitions based on the semantic content model.

In terms of "archiving", it depends on your specific requirements. Do the historic data need to be accessible after archiving? Then I am afraid there is no approach that would allow you to reduce the data in your database, except maybe cleaning up your version histories. If the content does not need to be accessible anymore, you could of course export any sub-structures which you no longer need and delete the nodes from the system.

By default, Alfresco only supports export as Alfresco Content Package (ACP) which bundles metadata, ACLs and content (no version history / no process information), and can be used to re-import the data. Be aware though that an ACP is just a ZIP file, will be created within the space for temporary files (and be limited by available space) and the export will be done in a single, sequential process. ACPs and the tooling arround them have not been designed for high volume export/import.

There is no support in Alfresco for having part of the data in a separate database. Of course if a specific database supports distributing individual tables across a database server farm, provides a transparent / transactionally consistent view, and supports distributed foreign keys / referential integrity, then again, it would be technically possible.

I don't believe many customers so far have had the necessity to split data (including customers with way more data / documents than in your specific case).

[myamcclure]

Thank you Axel. Your answers are very helpful