[pgpool-general: 7502] Re: Use pgpool as a way to split a large database

[Tatsuo Ishii]

> Dear List,
> 
> We have a 1.3TB a single postgres instance in one database that houses multiple tenants.  Each tenant is given a dedicated schema and we have about 1000 tenants or so.  We would like to split up the server/database into smaller instances with each holding different set of tenants.  So server 1 will have tenants number 0-100.
> 
> Is it possible to use pgpool between our application and multiple postgres instances in a way that application can connect to pgpool using a single connection string and pgpool do the right routing?  All my reading says that pgpool only routes to different backends for SELECT queries.  Upon reading the source code, I find `is_select_query` function; if I patch the function to just return true almost unconditionally, do you think pgpool will route any requests?  Can we rely on other pgpool config (such as routing to specific nodes based on database)  to make this possible?
> 
> We are also wondering whether we can leverage slony mode and load balancing.  Does pgpool distribute all queries (read and write) to all bankends in this mode?

No I don't think so. In my understanding Slony does not suppose that
write queries are sent to "slave" PostgreSQL servers.

If I were you, I would try following setup:

- Assign unique application name to each tenant. e.g. tenant0, tenant1...

- Create 10 or so PostgreSQL servers. One is streaming replication
  primary. The others are standby. Each has identical database
  clusters, i.e.: each server has 1.3 TB database.

- Assign each tenant's SELECT query to one of standby servers. You can
  accomplish this by using app_name_redirect_preference_list.

With this setup, all write queries are sent to the primary
database. On the other hand read queries are sent to one of standby
servers. That is, write queries are not load balanced but read queries
are distributed among 10 servers. Also you have 10 backup servers and
this makes the database much more reliable than single PostgreSQL
server.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp