Table partition for Alfresco community 5.0, 5.2 and 7.1

[dba100]

hi,

am I allow to Table partition for any tablesd of Alfresco community 5.0, 5.2 and 7.1?

will it breaks any functions at all?

usually if the postgreSQL is reindexing and updating statistic, will alfresco slow down ?

[angelborroy]

You can apply partitioning, ALF_NODE_PROPERTIES is the largest table in the DB.

This is a simple project for Postgres 11: https://github.com/aborroy/alf-pg11-partitioner

It will impact in performance, so you need to design your partitioning strategy according to your use case.

[dba100]

"You can apply partitioning, ALF_NODE_PROPERTIES is the largest table in the DB."

is that also means we can only partition ALF_NODE_PROPERTIES table ?

"It will impact in performance, so you need to design your partitioning strategy according to your use case."

we just try to rebuidl index faster so we try to table partition any tables we want ! so will alfresco start not to working at some point when we partition too much tables?

"https://github.com/aborroy/alf-pg11-partitioner"

this is only for postgreSQL V11.xx ?

[afaust · Accepted solution]

You can technically partition other tables as well, as correct partitioning would be transparent to Alfresco, and it would work the same way it did with unpartitioned tables. The alf_node_properties table is just - from all experience - the biggest and most critical table in all of Alfresco, which is why Angel addressed it. And any existing experience with partitioning is also centered around only that table primarily. Partitioning e.g. alf_child_assoc could give some improvements to children-by-assoc-type lookups, while alf_node could improve both TMQ and SOLR tracking, provided it does not mess with handling of foreign key references from other tables to it.

[dba100]

hi,

tks.

but what will be the performance gain after partitioning alf_node_properties?

"could give some improvements to children-by-assoc-type lookups,"

what is the hildren-by-assoc-type lookups for ?

any URL/post tell us how to partition the rest of the 2 x tables above? 

[dba100]

tks man,

I take a look today again and I seems it is for linux based postgresQL, we are using PostgreSQL on Windows, how can I apply your script for Alfresco PostgreSQL 9.3.6 on windows ?

Recently I see this query is the blockers in Alfresco 5.0 PostgreSQL 9.3.6:

select
node.id as node_id,
node.version as node_version,
prop.qname_id as qname_id,
prop.locale_id as locale_id,
prop.list_index as list_index,
prop.actual_type_n as actual_type_n,
prop.persisted_type_n as persisted_type_n,
prop.boolean_value as boolean_value,
prop.long_value as long_value,
prop.float_value as float_value,
prop.double_value as double_value,
prop.string_value as string_value,
prop.serializable_value as serializable_value

from
alf_node node
join alf_node_properties prop on (prop.node_id = node.id)
WHERE node_id in
(
$1
,
$2
,
$3
,
$4
,

why this one is slow and is it related to the large size of alf_node node and alf_node_properties ?

what else can be done to improve the speed ?