How to use schema in sharding resolve?

[nexen505]

Hi! Question for version 5.1.1.

Let's assume that we have the following sharding scheme.
We have one database instance with each shard mapped to the different database schema. Shards should be delegated to the single DataSource for this database because we should have one connection pool for all the schemas. So we need generated SQL-queries of every shard to be schema-specific.

Is this configuration possible? How to configure datasources and their aliases as schema-specific for the correctly generated SQL-queries?

[strongduanmu]

Hi @nexen505, if you want to use schema, you can config datasource like following:

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:postgresql://127.0.0.1:5432/test?currentSchema=schema_0
    username: postgres
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:postgresql://127.0.0.1:5432/test?currentSchema=schema_1
    username: postgres
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

Then you can include the schemaName in the executed statement, for example: select * from sharding_db.t_order;

[nexen505]

Thank you for your answer but I meant database schema, not the sharding `schemaName. I'll clarify my question here.

Let's assume that we have two databases:

databases:
  shard_db_0:
    url: jdbc:postgresql://127.0.0.1:5432/shard_db_0
    username: postgres
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  shard_db_1:
    url: jdbc:postgresql://127.0.0.1:5432/shard_db_1
    username: postgres
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

Database shard_db_0 have two database schemas: bucket_0, bucket_1. Database shard_db_1 have two database schemas too: bucket_2, bucket_3.

So I want the sharding to use these bucket-schemas as separate shards. Example:

• SQL-query for bucket_0 should be translated into select * from bucket_0.table for database shard_db_0.
• SQL-query for bucket_1 should be translated into select * from bucket_1.table for database shard_db_0.
• SQL-query for bucket_2 should be translated into select * from bucket_2.table for database shard_db_1.
• SQL-query for bucket_3 should be translated into select * from bucket_3.table for database shard_db_1.

Is it possible to configure such sharding topology? If possible, how it should be done?

[strongduanmu]

@nexen505 Sorry, ShardingSphere does not support the actual schema in postgresql now, we will consider enhancing this feature in the future.

[nexen505]

Thank you for the answer!

[strongduanmu]

If you have any good suggestions about postgresql schema support, you are welcome to put them into the community discussion.

[DeoLeung]

plus one here, we do multi tenant on postgres by schema, it will be best that proxy mode support sharding based on schema(and better comes with an example config) :)

[nexen505]

Just for your information.

I solved the discussing problem with usage of schema-fixed datasources:

1. I create a datasource for each node.
2. For every bucket with fixed schema I create another datasource that wraps the original one and provides connections with the set schema.
   That is ok as a solution now, but it requires the additional SQL-query for every connection obtained (e.g. PostgreSQL uses search_path for that and this way does not always work with PgBouncer).

In library another way to do such thing can be implemented:

• Library can provide a way to configure a table mapping in the sharding algorithm.
• Library can provide a way to specify a default schema for every ShardingSphereDataSource delegate.

[chj3737]

why not consider rewrittering using dbschema.tablename or support 3-level actual nodes?

[tristaZero]

Hello guys!

Thanks for your input! Good news, we are developing the PostgreSQL logic scheme feature! And it's supposed to be released next 1.5 months.

@strongduanmu I suppose we have such an issue, could we paste it here to let more guys interested in this one know the process.

[strongduanmu]

Hi @nexen505, you can refer to this issue——#14004.

Currently we are trying to support PostgreSQL schema creation and deletion, as well as support for schema usage by setting SEARCH_PATH.

Schema-based sharding is not listed in this plan for the time being, because we think that schema-based sharding has little value and cannot solve the performance bottleneck of a single database instance. So, I would like to ask why you need to shard based on schema?

[nexen505]

Hi!

This way of sharding is proposed by our DBA team. They think that this type of sharding provides convenient ways to manage resharding by.

[nexen505]

Also I've described current solution of my problem here.

[tristaZero]

Hello @nexen505 Yep, your solution for 5.1.1 release before (included) are correct, and we will support more about PostgreSQL custom schema and DB with sharding in next release.

[nexen505]

Looking forward to see the new feature - thank you!