Overview

Overview

DBaaS for PostgreSQL is fully integrated into the Data Center Designer and has a dedicated API. You may also launch it via automation tools like Terraform and Ansible.

Compatibility: DBaaS gives you access to the capabilities of the PostgreSQL database engine. This means that the code, applications, and tools you already use today with your existing databases can be used with DBaaS. IONOS Cloud currently supports PostgreSQL versions 12, 13, 14, and 15.

Deprecation Notice: Version 11 is currently still supported but will reach end of life on 9 Nov 2023 (see Postgresql documentation). It will soon be removed from IONOS Cloud.

Locations: As of December 2022, DBaaS is offered in all IONOS Cloud Locations.

Features

• Scalable: Fully managed clusters that can be scaled on demand.

• High availability: Multi-node clusters with automatic node failure handling.

• Security: Communication between clients and the cluster is encrypted using TLS certificates from Let's Encrypt.

• Upgrades: Customer-defined maintenance windows, with minimal disruption due to planned failover (approx. few seconds for multi-node clusters).

• Backup: Base backups are carried out daily, with Point-in-Time recovery for one week.

• Cloning: Customers also have the option to clone clusters via backups.

• Restore: Databases can be restored in place or to a different target cluster.

• Resources: Offered on Enterprise VM, with a dedicated CPU, storage, and RAM. Storage options are SSD or HDD, with SSD now including encryption-at-rest.

• Network: DBaaS supports private LANs.

• Extensions: DBaaS supports several PostgreSQL Extensions.

Platform Tasks

Note: IONOS Cloud doesn’t allow superuser access for PostgreSQL services. However, most DBA-type actions are still available through other methods.

DBaaS services offered by IONOS Cloud:

Our platform is responsible for all back-end operations required to maintain your database in optimal operational health.

• Database installation via the DCD or the DBaaS API.

• Pre-set database configuration and configuration management options.

• Automation of backups for a period of 7 days.

• Regular patches and upgrades during maintenance.

• Disaster recovery via automated backup.

• Service monitoring: both for the database and the underlying infrastructure.

Customer database administration duties:

Tasks related to the optimal health of the database remain the responsibility of the customer. These include:

• Optimisation.

• Data organisation.

• Creation of indexes.

• Updating statistics.

• Consultation of access plans to optimize queries.

Logs: The logs that are generated by a database are stored on the same disk as the database. We provide logs for connections, disconnections, waiting for locks, DDL statements, any statement that ran for at least 500 ms, and any statement that caused an error (see PostgreSQL documentation). Currently, we do not provide an option to change this configuration.

To conserve disk space, log files are rotated according to size. Logs should not consume more than 175 MB of disk storage. The files are continuously monitored and log messages are shipped to a central storage location with a retention policy of 30 days.

Tutorial - Learn how to access logs

Write-Ahead Logs: PostgreSQL uses Write Ahead Logs (WAL) for continuous archiving and point-in-time recovery. These logs are created in addition to the regular logs.

Every change to the database is recorded in the WAL record. WALs are generated along with daily base backups and offer a consistent snapshot of the database as it was at that time. WALs and backups are automatically deleted after 7 days, which is the earliest point in time you can recover from. Please consult PostgreSQL WAL documentation for more information.

Password encryption: Client libraries must support SCRAM-SHA-256 authentication. Make sure to use an up-to-date client library.

Connection encryption: All client connections are encrypted using TLS; the default SSL mode is prefer and clients cannot disable it. Server certificates are issued by Let's Encrypt and the root certificate is ISRG Root X1. This needs to be made available to the client for verify-ca and verify-full to function.

Certificates are issued for the DNS name of the cluster which is assigned automatically during creation and will look similar to pg-abc123.postgresql.de-txl.ionos.com. It is available via the IONOS API as the dnsName property of the cluster resource.

Here is how to verify the certificate using the psql command line tool:

curl https://crt.sh/?d=9314791 > ca.crt
export PGSSLROOTCERT=$(pwd)/ca.crt
export PG_SSLMODE=verify-full
psql -h pg-abc123.postgresql.de-txl.ionos.com -U dbadmin postgres

Resource Usage

Resource quotas: Each customer contract is allotted a resource quota. The available number of CPUs, RAM, storage, and database clusters is added to the default limitations for a VDC contract.

• 16 CPU Cores

• 32 GB RAM

• 1500 GB Disk Space

• 10 database clusters

• 5 nodes within a cluster

Additionally, a single instance of your database cluster can not exceed 16 cores and 32GB RAM.

Calculating RAM Requirements: The RAM size must be chosen carefully. There is 1 GB of RAM reserved to capture resource reservation for OS system daemons. Additionally, internal services and tools use up to 500 MB of RAM. To choose a suitable RAM size, the following formula must be used.

ram_size = base_consumption + X * work_mem + shared_buffers

• The base_consumption and reservation of internal services is approximately 1500 MB.

• X is the number of parallel connections. The value work_mem is set to 8 MB by default.

• The shared_buffersis set to about 15% of the total RAM.

Calculating Disk Requirements:

The requested disk space is used to store all the data that Postgres is working with, incl. database logs and WAL segments. Each Postgres instance has its storage (of the configured size). The operating system and applications are kept separately (outside of the configured storage) and are managed by IONOS.

If the disk runs full Postgres will reject write requests. Make sure that you order enough margin to keep the Postgres cluster operational. You can monitor the storage utilization in DCD.

WAL segments: In normal operation mode, older WAL files will be deleted once they have been replicated to the other instances and backed up to archive. If either of the two shipments is slow or failing then WAL files will be kept until the replicas and archive catch up again. Account for enough margin, especially for databases with high write load.

Log files: Database log files (175 MB) and auxiliary service log files (~100 MB) are stored on the same disk as the database.

Limitations

Connection Limits: The value for max_connections is calculated based on RAM size.

RAM size	max_connections
2GB	128
3GB	256
4GB	384
5GB	512
6GB	640
7GB	768
8GB	896
> 8GB	1000

The superuser needs to maintain the state and integrity of the database, which is why the platform reserves 11 connections for internal use: connections for superusers (see superuser_reserved_connections), for replication.

CPU: The total upper limit for CPU cores depends on your quota. A single instance cannot exceed 16 cores.

RAM: The total upper limit for RAM depends on your quota. A single instance cannot exceed 32 GB.

Storage: The upper limit for storage size is 2 TB.

Backups: Storing cluster backups in an IONOS S3 Object Storage is limited to the last 7 days.

Performance Considerations

Database instances are placed in the same location as your specified LAN, so network performance should be comparable to other machines in your LAN.

Estimates: A test with pgbench (scaling factor 1000, 20 connections, duration 300 seconds, not showing detailed logs) and a single small instance (2 cores, 3 GB RAM, 20 GB HDD) resulted in around 830 transactions per second (read and write mixed) and 1100 transactions per second (read-only). For a larger instance (4 cores, 8 GB RAM, 600GB Premium SSD) the results were around 3400 (read and write) and 19000 (read-only) transactions per second. The database was initialized using pgbench -i -s 1000 -h <ip> -U <username> <dbname>. For benchmarking the command line used was pgbench -c 20 -T 300 -h <ip> -U <username> <dbname> for the read/write tests, and pgbench -c 20 -T 300 -S -h <ip> -U <username> <dbname> for the read-only tests.

Note: To cite the pgbench docs: "It is very easy to use pgbench to produce completely meaningless numbers". The numbers shown here are only ballpark figures and there are no performance guarantees. The real performance will vary depending on your workload, the IONOS location, and several other factors.

Activate Extensions

Available PostgreSQL extensions

There are several PostgreSQL extensions preinstalled, that you can enable for your cluster. You can enable the extension by logging into your cluster and executing:

create extension <EXTENSION> CASCADE;

The following table shows which extensions are enabled by default and which can be enabled (PostgreSQL version 12):

Extension	Enabled	Version	Description
plpython3u	X	1.0	PL/Python3U untrusted procedural language
pg_stat_statements	X	1.7	track execution statistics of all SQL statements executed
intarray		1.2	functions, operators, and index support for 1-D arrays of integers
pg_trgm		1.4	text similarity measurement and index searching based on trigrams
pg_cron		1.3	Job scheduler for PostgreSQL
set_user		3.0	similar to SET ROLE but with added logging
timescaledb		2.4.2	Enables scalable inserts and complex queries for time-series data
tablefunc		1.0	functions that manipulate whole tables, including crosstab
pg_auth_mon	X	1.1	monitor connection attempts per user
plpgsql	X	1.0	PL/pgSQL procedural language
pg_partman		4.5.1	Extension to manage partitioned tables by time or ID
hypopg		1.1.4	Hypothetical indexes for PostgreSQL
postgres_fdw	X	1.0	foreign-data wrapper for remote PostgreSQL servers
btree_gin		1.3	support for indexing common datatypes in GIN
pg_stat_kcache	X	2.2.0	Kernel statistics gathering
citext		1.6	data type for case-insensitive character strings
pgcrypto		1.3	cryptographic functions
earthdistance		1.1	calculate great-circle distances on the surface of the Earth
postgis		3.2.1	PostGIS geometry and geography spatial types and functions
cube		1.4	data type for multidimensional cubes

Note: With select * from pg_available_extensions; you will see more available extensions, but many of them can't be enabled or used without superuser rights and thus aren't listed here.