Description PostgreSQL

Technical Characteristics

Two database engine versions are available in the service: PostgreSQL 11 and 12. The following flavor configurations are available for creating a database cluster:

  • 2 vCPU, 4 GB RAM, 32 GB local disk;
  • 2 vCPU, 8 GB RAM, 64 GB local disk;
  • 4 vCPU, 16 GB RAM, 128 GB local disk;
  • 8 vCPU, 32 GB RAM 256 GB local disk;
  • 16 vCPU, 64 GB RAM 512 GB local disk;
  • 32 vCPU, 128 GB RAM 1024 GB local disk.

Database clusters can only be created in private and public networks:

  • Private network — you can only connect to the database cluster from the selected private network;
  • Public network — you can connect to the database cluster from the Internet.

Database Engine Settings

Database engine settings are selected specifically to ensure high database performance. The settings parameters depend on the selected computing resources configuration for the database cluster.

If the database cluster configuration is changed, the following parameters will be also changed automatically:

shared_buffers 
effective_cache_size 
maintenance_work_mem
max_worker_processes 
max_parallel_workers 
autovacuum_max_workers 
vacuum_cost_limit 
max_parallel_workers_per_gather 
max_maintenance_workers 

To view all database engine settings, connect to the database and run the show all command.

How It Works

Step 1

The user needs to fill out the form for creating a cluster:

  • select the computing resources configuration;
  • specify the basic replication settings (the number of servers in the database cluster);
  • select a subnet where the cluster will be available. If the subnet has not been created yet, you can create it in the Network section.

The service creates a database cluster.

You can rename, delete a cluster, or change its settings: increase or decrease the number of replicas and change the server configuration.

You cannot change the addresses of servers or the subnet that the cluster is connected to.

Step 2

After creating the cluster, you need to create users and databases.

Then you need to assign database access rights to users. This can be done both from the database card and from the user card.

You can create up to 50 databases and up to 50 users in a cluster. All names must be unique.

Step 3

The connection string in the Settings page helps to connect to the cluster and get started.

Creating Users and Databases

After creating a cluster, the user can create databases and users within this cluster.

First, you need to create a user. This is necessary because when creating a database, you must select an owner-user.

Ownership of objects created by remote users will be transferred to the database owner.

Database owner has access to this database only while being its owner. If you want to change the owner, but so that the former owner still has access, you need to grant them access to the database.

Follow these steps to create a user:

  1. Go to the database cluster page.
  2. Go to the Users tab.
  3. Click Create.
  4. Enter the user’s name and set a password. Please note that you need to save your password, as it will not be stored in the Control panel.

Follow these steps to create a database:

  1. Go to the Databases tab.
  2. Click Create.
  3. Enter the database name, select the owner-user, and configure LC_CTYPE and LC_COLLATE.

The character set locale is responsible for the classification of characters and their case differences. The sorting locale determines the settings for comparing strings and characters, and also affects sorting. You need to configure locales so that the data in the databases is processed correctly. For more information about locales, see the PostgreSQL documentation.

After creating databases and users, you can provide users access to specific databases.

Connecting Extensions to the Database

Follow these steps to connect extensions via the interface:

  1. Go to the Databases tab within a cluster.
  2. Expand the accordion with settings in the database card.
  3. Click Add next to Extensions.
  4. Select all required extensions from the list and save the settings.

Extensions are connected to each database separately.

Connection Pool

PostgreSQL clusters use PgBouncer for managing connection pools. PgBouncer minimizes overhead when configuring new connections to PostgreSQL. You can configure the connection pool on the Settings page when creating a cluster or during its operation.

To configure it, specify the values for the following parameters:

Pooling mode

Strategy for connecting the application server to the database.

In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling:

  • Transaction — connection is assigned to a client for the duration of a transaction. This mode allows each client to use the pool for the duration of a single transaction at a time.

    If there are not enough available connections in the pool to handle the transactions submitted, the additional transactions are queued to run once connections are available.

    Transaction mode is useful when you have a large number of clients that maintain connections.

    This mode is enabled by default.

  • Session — connection is assigned when a client connects. This mode allows clients to continue issuing requests until they disconnect from the database, which maintains the client’s connection the entire time.

    If there are not enough available connections in the pool to handle all the connecting clients, the additional clients are queued up to connect once an existing client disconnects.

    Session mode is useful when your application uses prepared statements, advisory locks, listen/notify, or other features which operate on a session level rather than on a transaction level.

  • Statement — multi-statement transactions are not allowed.

    This mode is the most restrictive one and only allows a single statement at a time before moving on to the next in the queue. This means queries with multiple statements are not allowed and will fail.

Statement mode is useful when you force autocommit for each client, meaning each transaction is limited to a single statement.

Pool size

How many concurrent connections are allowed from the pool to PostgreSQL.

The pool size determines the minimum number of concurrent connections allowed from the pool to PostgreSQL.

Default pool size is 30. You can increase this value up to 50.