22.1. Postgres Pro Shardman Overview #
Postgres Pro Shardman is a distributed system, which has both advantages and disadvantages inherent to such systems. Besides, Postgres Pro Shardman is primarily designed for OLTP load. OLAP queries to Postgres Pro Shardman are also possible, but only pretty simple of them. If you want to load an OLTP system with OLAP functionality, bear in mind that the lists of analytic and aggregate SQL functions to be sent to other shards for execution are highly limited.
Porstgres Pro Shardman does not have a single DB server, instead there is a pool of shard servers with equal privileges.
Special attention should be paid to type casts in queries because inclusion of a type casting function in a query condition can make it impossible to be pushed down to a remote server.
For a detailed description of the main terms, refer to Glossary.
When connecting to the database server, a client must specify the database name in its connection request. It is not possible to access more than one database per connection. However, clients can open multiple connections to the same database. Database-level security has two components: access control (see Section 20.1), managed at the connection level, and authorization control (see Section 5.8), managed via the grant system.
As Postgres Pro Shardman can store several databases, each one in its shard, it is advised to consider carefully whether the benefits outweigh the risks and limitations. In particular, the impact that having a shared WAL (see Chapter 28) has on backup and recovery options. While individual databases in the cluster are isolated when considered from the user's perspective, they are closely bound from the database administrator's point-of-view.
Databases are created with the CREATE DATABASE
command (see Section 22.3) and destroyed with the DROP DATABASE
command (see Section 22.6). To determine the set of existing databases, examine the pg_database
system catalog. for example
SELECT datname FROM pg_database;
The psql program's \l
meta-command and -l
command-line option are also useful for listing the existing databases.
Note
The SQL standard calls databases “catalogs”, but there is no difference in practice.
Taking into account the above features and limitations of the RDBMS, we will provide two simple examples of the transition from a regular to a distributed database schema.