[wp-hackers] Plugins and network activations

[Otto]

On Tue, Jun 28, 2011 at 12:05 PM, Mike Schinkel
<mikeschinkel at newclarity.net> wrote:
> I've been thinking about this a lot lately as we're exploring building a SaaS using WordPress as a base.
>
> I definitely agree that the current process does not scale to have to add tables when a plugin needs tables to all sites.  So you proposed one option to create a shared table that has blog_id as a field so only one table needs to be added. But doesn't that effectively create a hybrid architecture where parts of the data are sharded and parts are not?
>
> My understanding is that WordPress uses a sharding approach to enable scalability, yet it is recommending to not use sharding for extensions; isn't that contradictory? If sharding is the appropriate architecture for WordPress' tables why is it not the appropriate architecture for plugin's tables?
>
> Or is this simple a case of "Yes, it's not a great situation but it's all we got at the moment?"  And if that is the case, maybe it would make sense to come up with a better solution (i.e. a way that it is possible to add new tables to other blogs without timeout?)

If you've noticed, WordPress itself avoids changing its tables as much
as possible. Every time they do, they have to write special code to
handle wp.com and its 25 million sites in a scalable upgrade
mechanism.

Scaling will always be hard. You just have to determine the best way
to support it for your specific custom data. There's plenty of
options:

1. Don't use separate tables. This is the best approach, but not
feasible in all cases.
2. Use one global table. Works, but if the dataset is large or the
number of blogs is large, then sharding may be preferred.
3. One or more tables per site. Scaling this is hard for having to
create and/or alter tables. You might be able to get away with a
create-when-not-there approach, as long as your check for "not-there"
is optimized. If not, then you have to give admins a way to
create/upgrade all sites. WordPress does this with a network admin
approach, with the upgrade-network functionality, when the tables have
to be altered. In this case, avoiding alters is the best policy
because of the very-large-sites scaling problems.


-Otto