Well, the first thing I would do is to drop the icky string parsing everywhere and replace it with PostgreSQL native types. To store the replication status on each record similar to your current solution:
CREATE TYPE replication_status AS ENUM (
'no_action',
'replicate_record',
'record_replicated',
'error_1',
'error_2',
'error_3'
);
ALTER TABLE t ADD COLUMN rep_status_array replication_status[];
This costs you a little more storage space -- enum values are 4 bytes instead of 1 and arrays have some overhead. However, by teaching the database your concepts instead of hiding them, you can write things like:
-- find all records that need to be replicated to host 4
SELECT * FROM t WHERE rep_status_array[4] = 'replicate_record';
-- find all records that contain any error status
SELECT * FROM t WHERE rep_status_array &&
ARRAY['error_1', 'error_2', 'error_3']::replication_status[];
You can put a GIN index right on rep_status_array if that helps your use case, but it's better to look at your queries and create indexes specifically for what you use:
CREATE INDEX t_replication_host_4_key ON t ((rep_status_array[4]));
CREATE INDEX t_replication_error_key ON t (id)
WHERE rep_status_array && ARRAY['error_1', 'error_2', 'error_3']::replication_status[];
That said, given 200 tables, I'd be tempted to split this out into a single replication status table -- either one row with an array of statuses or one row per host, depending on how the rest of the replication logic works. I'd still use that enumeration:
CREATE TABLE adhoc_replication (
record_id bigint not null,
table_oid oid not null,
host_id integer not null,
replication_status status not null default 'no_action',
primary key (record_id,table_oid,host_id)
);
PostgreSQL internally assigns each table an OID (try SELECT *, tableoid FROM t LIMIT 1), which is a convenient stable numeric identifier within a single database system. Put another way, it changes if the table is dropped and recreated (which can happen if you e.g. dump and restore the database), and for this same reason it's very likely different between development and production. If you'd rather have these situations work in exchange for breaking when you add or rename a table, use an enumeration instead of an OID.
Using a single table for all replication would allow you to easily re-use triggers and queries and such, decoupling most replication logic from the data it's replicating. It also allows you to query based on status for a given host across all your origin tables by referencing a single index, which could be important.
As for table size, PostgreSQL can definitely handle 10 million rows in the same table. If you went with a dedicated replication-related table, you could always partition per host. (Partitioning by table makes little sense to me; it seems worse than storing replication status on each upstream row.) Which way to partition or whether or not it's appropriate at all depends entirely on what kind of questions you intend to ask your database, and what sort of activity happens on the base tables. (Partitioning means maintaining many smaller blobs instead of a few large ones, and potentially accessing many smaller blobs to perform a single operation.) It's really a matter of choosing when you want your disk seeks to happen.
