# ADPG 14.2_arenadata1 maintenance

## 1. Features

- Deploy PostgreSQL 14.2 on the single host.
- Perform initdb.
- Support PostGIS and all contrib extensions.
- Manage sysctl parameters of the host.
- Manage postgresql.conf and pg_hba.conf settings.

## 2. Deployment

Here is the diagram of ADPG deployment with ADCM.

Highlights:
- All ADPG configurations have to be set in ADCM and than be applied with **Reconfigure & Reload** or **Reconfigure & Restart** actions. All changes made by user on host will be rewritten with reconfigure actions.
- Some ADPG parameters can be applied with **Reconfigure & Reload**. Another ones can by applied only with restarting service. This information can be found in official PostgreSQL documentation, for example for [resource consumption parameters](https://www.postgresql.org/docs/current/runtime-config-resource.html).
- Extensions are installed with **Install postgis** and **Install contrib** actions.
- **postgresql.conf** section has two sections: base parameters and custom section in advanced mode.
- There are several **postgresql.conf** settings in ADPG service configuration. They should be revised before cluster installation as they depends on host's hardware. For details, see [documentation](https://docs-new.arenadata.io/en/ADPG/1.0/how-to/recommended-settings.html).
- shared_buffers
- max_worker_processes
- max_parallel_workers
- max_parallel_workers_per_gather
- max_parallel_maintenance_worker
- max_connections
- listen_addresses
- port
- Custom section consists of any postgresql.conf parameters.
- We add some new **postgresql.conf** settings in the next release:
- effective_cache_size
- maintenance_work_mem
- work_mem
- min_wal_size
- max_wal_size
- wal_keep_size
- hugepages
- superuser_reserved_connections
- It's recommended to enable pg_stat_statements extension.

### 2.1. Tunning postgresql.conf parameters
- **shared_buffers** is about a quarter of RAM size. It can be increased to 0.75 RAM depending on the database load.
- **effective_cache_size** is equal to quarter of RAM size.
- **max_connections** ~= 2-4 x CPU (it's recommended to limit real conections with pgBouncer [< 100 connection])
- [PGTune](https://pgtune.leopard.in.ua/) is fine in the most cases.
- [Cybertec PostgreSQL configurator](https://pgconfigurator.cybertec-postgresql.com/) is another one advanced tool.

### 2.2. Logging parameters

## 3. Limitations

- Backup with default tools: **pgdump** and **pg_basebackup**.
- No pgBouncer package.
- No monitoring integration.
- There is the way to connect ADPG clusters (cluster consists of the single instance) to HA cluster. But there were problems with update to the feature bundles.

## 4. Scaling best practices

### 4.1. Hardware recomendation

- 1 CPU per 4-8 GB RAM.
- 1 CPU per 1 Tb of data (OLTP load).
- Reducing of hardware have to be done with multiplier x2 if performance of hardware is too high during database load testing.
- EC2 [i4i](https://aws.amazon.com/ec2/instance-types/i4i/) instancies are generally excellent choice for high OLTP load databases.
.

### 4.2. Database optimizations

#### 4.2.1. OS settings

- Swap size is about quarter of RAM size (min 16 Gb).
- Set **vm.swappiness** to the low values (DataEgret recommends to use 1).
- Set **kernel.numa_balancing = 0** to disable NUMA balancing.
- Use hugepages for shared mememy. Enable them with **vm.nr_hugepages** and **vm. nr_overcommit_hugepages** sysctl settings. It has to correlate with **shared_buffers** postgresql.conf parameter.
- Set ```vm.dirty_bytes = 1073741824``` and ```vm.dirty_background_bytes = 268435456``` in case of huge databases with RAM usage > 256 Gb.

### 4.2.2. Database settings
- **superuser_reserved_connections** - 10-20
- It's recommended to set ```hugepages=on`` to prevent starting of database without hugepages support.
- Set bg_writer_* settings to aggressive high values (```bgwriter_delay = 10ms```, ```bgwriter_lru_maxpages = 5000```, ```bgwriter_lru_multiplier = 10```, ```bgwriter_flush_after = 2MB```).
- Set ```checkpoint_timeout = 1h```. max_wal_size have to be corresponded with generated WAL files for 1h of database activity.