You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by dj...@apache.org on 2025/06/13 04:15:17 UTC
(cloudberry-site) 02/02: V2: Move bootcamp pages to Docs
This is an automated email from the ASF dual-hosted git repository.
djwang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry-site.git
commit ad076c128d79cb854589bd787d18846cda9b6265
Author: Dianjin Wang <wa...@gmail.com>
AuthorDate: Thu Jun 12 16:20:13 2025 +0800
V2: Move bootcamp pages to Docs
---
docs/benchmark/performance-benchmark-tpcds.md | 2 +-
docs/benchmark/performance-benchmark-tpch.md | 2 +-
.../benchmark/performance-benchmark-tpcds.md | 2 +-
.../benchmark/performance-benchmark-tpch.md | 2 +-
versioned_docs/version-2.x/deployment/sandbox.md | 173 ++++
.../tutorial/backup-and-recovery-operations.md | 144 ++++
.../version-2.x/tutorial/crash-course.md | 946 +++++++++++++++++++++
.../tutorial/create-and-prepare-database.md | 200 +++++
.../version-2.x/tutorial/create-tables.md | 148 ++++
.../version-2.x/tutorial/create-users-and-roles.md | 219 +++++
.../version-2.x/tutorial/data-loading.md | 382 +++++++++
versioned_docs/version-2.x/tutorial/index.md | 11 +
...oduction-to-cloudberry-in-database-analytics.md | 213 +++++
...tion-to-database-and-cloudberry-architecture.md | 64 ++
.../tutorial/queries-and-performance-tuning.md | 666 +++++++++++++++
versioned_sidebars/version-2.x-sidebars.json | 29 +-
16 files changed, 3197 insertions(+), 6 deletions(-)
diff --git a/docs/benchmark/performance-benchmark-tpcds.md b/docs/benchmark/performance-benchmark-tpcds.md
index ece58a981..a9b06cb31 100644
--- a/docs/benchmark/performance-benchmark-tpcds.md
+++ b/docs/benchmark/performance-benchmark-tpcds.md
@@ -1,5 +1,5 @@
---
-title: "[103-2] TPC-DS: Decision Support Benchmark for Apache Cloudberry"
+title: "TPC-DS: Decision Support Benchmark for Apache Cloudberry"
description: Run the TPC-DS benchmark automatically on an existing Apache Cloudberry cluster.
---
diff --git a/docs/benchmark/performance-benchmark-tpch.md b/docs/benchmark/performance-benchmark-tpch.md
index fad51a6ed..b0c33cd1f 100644
--- a/docs/benchmark/performance-benchmark-tpch.md
+++ b/docs/benchmark/performance-benchmark-tpch.md
@@ -1,5 +1,5 @@
---
-title: "[103-1] TPC-H: Decision Support Benchmark for Apache Cloudberry"
+title: "TPC-H: Decision Support Benchmark for Apache Cloudberry"
description: Run the TPC-H benchmark automatically on an existing Apache Cloudberry cluster.
---
diff --git a/docs/benchmark/performance-benchmark-tpcds.md b/versioned_docs/version-2.x/benchmark/performance-benchmark-tpcds.md
similarity index 97%
copy from docs/benchmark/performance-benchmark-tpcds.md
copy to versioned_docs/version-2.x/benchmark/performance-benchmark-tpcds.md
index ece58a981..a9b06cb31 100644
--- a/docs/benchmark/performance-benchmark-tpcds.md
+++ b/versioned_docs/version-2.x/benchmark/performance-benchmark-tpcds.md
@@ -1,5 +1,5 @@
---
-title: "[103-2] TPC-DS: Decision Support Benchmark for Apache Cloudberry"
+title: "TPC-DS: Decision Support Benchmark for Apache Cloudberry"
description: Run the TPC-DS benchmark automatically on an existing Apache Cloudberry cluster.
---
diff --git a/docs/benchmark/performance-benchmark-tpch.md b/versioned_docs/version-2.x/benchmark/performance-benchmark-tpch.md
similarity index 97%
copy from docs/benchmark/performance-benchmark-tpch.md
copy to versioned_docs/version-2.x/benchmark/performance-benchmark-tpch.md
index fad51a6ed..b0c33cd1f 100644
--- a/docs/benchmark/performance-benchmark-tpch.md
+++ b/versioned_docs/version-2.x/benchmark/performance-benchmark-tpch.md
@@ -1,5 +1,5 @@
---
-title: "[103-1] TPC-H: Decision Support Benchmark for Apache Cloudberry"
+title: "TPC-H: Decision Support Benchmark for Apache Cloudberry"
description: Run the TPC-H benchmark automatically on an existing Apache Cloudberry cluster.
---
diff --git a/versioned_docs/version-2.x/deployment/sandbox.md b/versioned_docs/version-2.x/deployment/sandbox.md
new file mode 100644
index 000000000..078c8be4e
--- /dev/null
+++ b/versioned_docs/version-2.x/deployment/sandbox.md
@@ -0,0 +1,173 @@
+---
+title: Get Started with Sandbox
+description: Learn how to quickly set up and connect to a Cloudberry in a Docker environment.
+---
+
+This document guides you on how to quickly set up and connect to a Apache Cloudberry in a Docker environment. You can try out Apache Cloudberry by performing some basic operations and running SQL commands.
+
+:::caution
+This guide is intended for testing or development. DO NOT use it for production.
+:::
+
+## Prerequisites
+
+Make sure that your environment meets the following requirements:
+
+- Platform requirement: Any platform with Docker runtime. For details, refer to [Get Started with Docker](https://www.docker.com/get-started/).
+- Other dependencies: Git, SSH, and internet connection
+
+## Build the Sandbox
+
+When building and deploying Cloudberry in Docker, you will have 2 different deployment options as well as different build options.
+
+**Deployment Options**
+
+1. **Single Container** (Default) - With the single container option, you will have the coordinator as well as the Cloudberry segments all running on a single container. This is the default behavior when deploying using the `run.sh` script provided.
+2. **Multi-Container** - Deploying with the multi-container option will give you a more realistic deployment of what actual production Cloudberry clusters look like. With multi-node, you will have the coordinator, the standby coordinator, and 2 segment hosts all on their own respective containers. This is to both highlight the distributed nature of Apache Cloudberry as well as highlight how high availability (HA) features work in the event of a server (or in this case a container) failin [...]
+
+
+
+**Build Options**
+
+1. Compile with the source code of the latest Apache Cloudberry (released in [Apache Cloudberry Release Page](https://github.com/apache/cloudberry/releases)). The base OS will be Rocky Linux 9 Docker image.
+2. Method 2 - Compile with the latest Apache Cloudberry [main](https://github.com/apache/cloudberry/tree/main) branch. The base OS will be Rocky Linux 9 Docker image.
+
+Build and deploy steps:
+
+1. Start Docker Desktop and make sure it is running properly on your host platform.
+
+2. Download the repository [apache/cloudberry-bootcamp](https://github.com/apache/cloudberry-bootcamp) to the target machine.
+
+ ```shell
+ git clone https://github.com/apache/cloudberry-bootcamp.git
+ ```
+
+3. Enter the repository and run the `run.sh` script to start the Docker container. This will start the automatic installation process. Depending on your environment, you may need to run this with `sudo` command.
+
+ - For latest Cloudberry release running on a single container:
+
+ ```shell
+ cd cloudberry-bootcamp/000-cbdb-sandbox
+ ./run.sh
+ ```
+ - For latest Cloudberry release running across multiple containers:
+
+ ```shell
+ cd cloudberry-bootcamp/000-cbdb-sandbox
+ ./run.sh -m
+ ```
+ - For latest main branch running on a single container:
+
+ ```shell
+ cd cloudberry-bootcamp/000-cbdb-sandbox
+ ./run.sh -c main
+ ```
+
+ - For latest main branch running across multiple containers:
+
+ ```shell
+ cd cloudberry-bootcamp/000-cbdb-sandbox
+ ./run.sh -c main -m
+ ```
+
+ Once the script finishes without error, the sandbox is built and running successfully. The `docker run` and `docker compose` commands use the `--detach` option allowing you to ssh or access the running Cloudberry instance remotely.
+
+ Please review `run.sh` script for additional options (e.g. setting Timezone in running container, only building container). You can also execute `./run.sh -h` to see the usage.
+
+## Connect to the database
+
+:::note
+When deploying the multi-container Cloudberry environment it may take extra time for the database to initialize, so you may need to wait a few minutes before you can execute the psql prompt successfully. You can run `docker logs cbdb-cdw -f` to see the current state of the database initialization process, you'll know the process is finished when you see the "Deployment Successful" output.
+:::
+
+You can now connect to the database and try some basic operations.
+
+1. Connect to the Docker container from the host machine:
+
+ ```shell
+ docker exec -it cbdb-cdw /bin/bash
+ ```
+
+ If it is successful, you will see the following prompt:
+
+ ```shell
+ [gpadmin@cdw /]$
+ ```
+
+2. Log into Apache Cloudberry in Docker. See the following commands and example outputs:
+
+ ```shell
+ [gpadmin@cdw ~]$ psql # Connects to the database with the default database name "gpadmin".
+
+ # psql (14.4, server 14.4)
+ # Type "help" for help.
+ ```
+
+ ```sql
+ gpadmin=# SELECT VERSION(); -- Checks the database version.
+
+ PostgreSQL 14.4 (Apache Cloudberry 1.0.0 build dev) on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 10.2.1 20210130 (Red Hat 10.2.1-11), 64-bit compiled on Oct 24 2023 10:24:28
+ (1 row)
+ ```
+
+Now you have a Apache Cloudberry and can continue with [Apache Cloudberry Tutorials Based on Docker Installation](../tutorial/)! Enjoy!
+
+## Working with your Apache Cloudberry Docker environment
+
+When working with the Apache Cloudberry Docker environment there are a few commands that will be useful to you.
+
+### Stopping Your Single Container Deployment With Docker
+
+To stop the **single container** deployment while _keeping the data and state_ within the container, you can run the command below. This means that you can later start the container again and any changes you made to the containers will be persisted between runs.
+
+```shell
+docker stop cbdb-cdw
+```
+
+To stop the **single container** deployment and also remove the volume that belongs to the container, you can run the following command. Keep in mind this will remove the volume as well as the container associated which means any changes you've made inside of the container or any database state will be wiped and unrecoverable.
+
+```shell
+docker rm -f cbdb-cdw
+```
+
+### Stopping Your Multi-Container Deployment With Docker
+
+To stop the **multi-container** deployment while _keeping the data and state_ within the container, you can run the command below. This means that you can later start the container again and any changes you made to the containers will be persisted between runs.
+
+```shell
+docker compose -f docker-compose-rockylinux9.yml stop
+```
+
+To stop the **multi-container** deployment and also remove the network and volumes that belong to the containers, you can run the command below. Running this command means it will delete the containers as well as remove the volumes that the containers are associated with. This means any changes you've made inside of the containers or any database state will be wiped and unrecoverable.
+
+```shell
+docker compose -f docker-compose-rockylinux9.yml down -v
+```
+
+### Starting A Stopped Single Container Cloudberry Docker Deployment
+
+If you've run any of the commands above that keep the Docker volumes persisted between shutting the containers down, you can use the following commands to bring that same deployment back up with it's previous state.
+
+To start a **single container** deployment after it was shut down, you can simply run the following
+
+```shell
+docker start cbdb-cdw
+```
+
+### Starting A Stopped Multi-Container Cloudberry Docker Deployment
+
+To start a **multi-container** deployment after it was shut down, you can run the following command.
+
+```shell
+docker compose -f docker-compose-rockylinux9.yml start
+```
+
+:::note
+When starting a previously stopped Cloudberry Docker environment, you'll need to manually start the database back up. To do this, just run the following commands once the container(s) are back up and running. The `gpstart` command is used for starting the database, and -a is a flag saying to start the database without prompting (non-interactive).
+
+```shell
+docker exec -it cbdb-cdw /bin/bash
+
+[gpadmin@cdw /] gpstart -a
+```
+:::
\ No newline at end of file
diff --git a/versioned_docs/version-2.x/tutorial/backup-and-recovery-operations.md b/versioned_docs/version-2.x/tutorial/backup-and-recovery-operations.md
new file mode 100644
index 000000000..4289fb51b
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/backup-and-recovery-operations.md
@@ -0,0 +1,144 @@
+---
+title: "Lesson 6: Backup and Restore Operations"
+description: Learn how to backup and restore your data in the Apache Cloudberry.
+---
+
+:::info
+
+The Apache Cloudberry does not include the utility `gpbackup` by default. It's maintained separately. Please follow the [README](https://github.com/apache/cloudberry-gpbackup) to install `gpbackup` before using it.
+
+:::
+
+The parallel dump utility `gpbackup` backs up the Apache Cloudberry coordinator instance and each active segment instance at the same time.
+
+By default, gpbackup creates dump files in the backups subdirectory.
+
+Several dump files are created for the coordinator, containing database information such as DDL statements, the Apache Cloudberry system catalog tables, and metadata files. gpbackup creates dump files for each segment.
+
+You can perform full or incremental backups. To restore a database to its state when an incremental backup was made, it will restore the previous full backup and all subsequent incremental backups.
+
+
+
+Each file created for a backup begins with a 14-digit timestamp key that identifies the backup set the file belongs to.
+
+gpbackup can be run directly in a terminal on the coordinator host, or you can add it to crontab on the coordinator host to schedule regular backups.
+
+The parallel restore utility `gprestore` takes the timestamp key generated by gpbackup, validates the backup set, and restores the database objects and data into a distributed database in parallel. Parallel restore operations require a complete backup set created by gpbackup, a full backup and any required incremental backups.
+
+
+
+The gpbackup utility provides flexibility and verification options for use with the automated backup files produced by gpbackup or with backup files moved from the Apache Cloudberry array to an alternate location.
+
+## Exercises
+
+These exercises will walk through how to create a full backup of your database and then restore a table.
+
+1. To run a full backup, use `gpbackup --dbname database --backup-dir /path/for/backup`. This will backup the entire database to the directory given.
+
+ ```bash
+ [gpadmin@cdw tmp]$ gpbackup --dbname tutorial --backup-dir /tmp/
+ ```
+
+ ```
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-gpbackup version = 1.2.7-beta1+dev.7
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Greenplum Database Version = oudberry Database 1.0.0 build dev
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Starting backup of database tutorial
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Backup Timestamp = 20230727102819
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Backup Database = tutorial
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Gathering table state information
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Acquiring ACCESS SHARE locks on tables
+ Locks acquired: 28 / 28 [==============================================================] 100.00% 0s
+ 20230727:10:28:19 gpbackup:gpadmin:cdw:020061-[INFO]:-Gathering additional table metadata
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Getting storage information
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Metadata will be written to /tmp/gpseg-1/backups/20230727/20230727102819/gpbackup_20230727102819_metadata.sql
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Writing global database metadata
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Global database metadata backup complete
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Writing pre-data metadata
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Pre-data metadata metadata backup complete
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Writing post-data metadata
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Post-data metadata backup complete
+ 20230727:10:28:20 gpbackup:gpadmin:cdw:020061-[INFO]:-Writing data to file
+ Tables backed up: 11 / 11 [============================================================] 100.00% 8s
+ 20230727:10:28:28 gpbackup:gpadmin:cdw:020061-[INFO]:-Data backup complete
+ 20230727:10:28:28 gpbackup:gpadmin:cdw:020061-[INFO]:-Skipped data backup of 3 external/foreign table(s).
+ 20230727:10:28:28 gpbackup:gpadmin:cdw:020061-[INFO]:-See /home/gpadmin/gpAdminLogs/gpbackup_20230727.log for a complete list of skipped tables.
+ 20230727:10:28:29 gpbackup:gpadmin:cdw:020061-[INFO]:-Found neither /usr/local/cloudberry/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
+ 20230727:10:28:29 gpbackup:gpadmin:cdw:020061-[INFO]:-Email containing gpbackup report /tmp/gpseg-1/backups/20230727/20230727102819/gpbackup_20230727102819_report will not be sent
+ 20230727:10:28:29 gpbackup:gpadmin:cdw:020061-[INFO]:-Backup completed successfully
+ ```
+
+
+ This runs a full backup of the database created during the previous exercises.
+
+2. To view the backups:
+
+ ```bash
+ $ ls -al /tmp/
+
+ drwxrwxr-x 3 gpadmin gpadmin 21 Jul 27 10:28 gpseg-1
+ drwxrwxr-x 3 gpadmin gpadmin 21 Jul 27 10:28 gpseg1
+ drwxrwxr-x 3 gpadmin gpadmin 21 Jul 27 10:28 gpseg0
+ ```
+
+3. Now, that we have a full backup let's remove data from a table to simulate a failure.
+
+ ```sql
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ tutorial=# select count(*) from faa.otp_r;
+ count
+ ---------
+ 1024552
+ (1 row)
+ ```
+
+ This should return 1024552 rows in the table. Let's truncate the table and then check the row count:
+
+ ```sql
+ tutorial=# truncate table faa.otp_r;
+ TRUNCATE TABLE
+ tutorial=# select count(*) from faa.otp_r;
+ count
+ -------
+ 0
+ (1 row)
+
+ tutorial=#
+ ```
+
+ The report should now show 0 rows in the table.
+
+4. Let's restore the data that was lost. First, exit from the psql shell by typing `\q` then issue the gprestore command(14 digital timestamp information could be got from previous gpbackup output, also you may record it down once you have done any backup before.):
+
+ ```bash
+ gprestore --include-table faa.otp_r --data-only --backup-dir /tmp/ --timestamp 20230727102819
+ ```
+
+ ```
+ 20230727:10:38:29 gprestore:gpadmin:cdw:020373-[INFO]:-Restore Key = 20230727102819
+ 20230727:10:38:30 gprestore:gpadmin:cdw:020373-[INFO]:-gpbackup version = 1.2.7-beta1+dev.7
+ 20230727:10:38:30 gprestore:gpadmin:cdw:020373-[INFO]:-gprestore version = 1.2.7-beta1+dev.7
+ 20230727:10:38:30 gprestore:gpadmin:cdw:020373-[INFO]:-Greenplum Database Version = oudberry Database 1.0.0 build dev
+ 20230727:10:38:30 gprestore:gpadmin:cdw:020373-[INFO]:-Restoring sequence values
+ 20230727:10:38:30 gprestore:gpadmin:cdw:020373-[INFO]:-Sequence values restore complete
+ Tables restored: 1 / 1 [===============================================================] 100.00% 5s
+ 20230727:10:38:35 gprestore:gpadmin:cdw:020373-[INFO]:-Data restore complete
+ 20230727:10:38:35 gprestore:gpadmin:cdw:020373-[INFO]:-Found neither /usr/local/cloudberry/bin/gp_email_contacts.yaml nor /home/gpadmin/gp_email_contacts.yaml
+ 20230727:10:38:35 gprestore:gpadmin:cdw:020373-[INFO]:-Email containing gprestore report /tmp/gpseg-1/backups/20230727/20230727102819/gprestore_20230727102819_20230727103829_report will not be sent
+ 20230727:10:38:35 gprestore:gpadmin:cdw:020373-[INFO]:-Restore completed successfully
+ ```
+
+5. Finally, verify the row count
+
+ ```
+ [gpadmin@cdw tmp]$ psql -U gpadmin tutorial
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ tutorial=# select count(*) from faa.otp_r;
+ count
+ ---------
+ 1024552
+ (1 row)
+ ```
+
+The table should show 1024552 rows again as it was prior to the truncate call.
diff --git a/versioned_docs/version-2.x/tutorial/crash-course.md b/versioned_docs/version-2.x/tutorial/crash-course.md
new file mode 100644
index 000000000..f2edd69e4
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/crash-course.md
@@ -0,0 +1,946 @@
+---
+title: "Apache Cloudberry Crash Course"
+description: If you want to learn the Apache Cloudberry quickly, follow this crash course.
+---
+
+This crash course provides an extensive overview of Apache Cloudberry, an open-source Massively Parallel Processing (MPP) database. It covers key concepts, features, utilities, and hands-on exercises to become proficient with Cloudberry.
+
+Topics include:
+
+- [Lesson 0. Prerequisite](#lesson-0-prerequisite)
+- [Lesson 1. Where to read the official documentation](#lesson-1-where-to-read-the-official-documentation)
+- [Lesson 2. How to install Cloudberry](#lesson-2-how-to-install-cloudberry)
+- [Lesson 3. Cluster architecture](#lesson-3-cluster-architecture)
+- [Lesson 4. Management utilities](#lesson-4-management-utilities)
+- [Lesson 5. Start and stop a cluster](#lesson-5-start-and-stop-a-cluster)
+- [Lesson 6. Check cluster state](#lesson-6-check-cluster-state)
+- [Lesson 7. How Cloudberry segment mirroring works](#lesson-7-how-cloudberry-segment-mirroring-works)
+- [Lesson 8. Cloudberry's fault tolerance and segment recovery](#lesson-8-cloudberrys-fault-tolerance-and-segment-recovery)
+- [Lesson 9. Set up and manage the standby coordinator instance in Cloudberry](#lesson-9-set-up-and-manage-the-standby-coordinator-instance-in-cloudberry)
+- [Lesson 10. Expand a cluster](#lesson-10-expand-a-cluster)
+- [Lesson 11. Check cluster performance](#lesson-11-check-cluster-performance)
+- [Lesson 12. User data and table distribution](#lesson-12-user-data-and-table-distribution)
+- [Lesson 13. Database catalog](#lesson-13-database-catalog)
+- [Lesson 14. Cloudberry data directories](#lesson-14-cloudberry-data-directories)
+- [Lesson 15. Instance processes](#lesson-15-instance-processes)
+ - [coordinator processes:](#coordinator-processes)
+ - [primary processes:](#primary-processes)
+ - [mirror processes:](#mirror-processes)
+- [Lesson 16. Database log files](#lesson-16-database-log-files)
+- [Lesson 17. Table types in Cloudberry: heap, AO, and AOCO](#lesson-17-table-types-in-cloudberry-heap-ao-and-aoco)
+- [Lesson 18. External tables](#lesson-18-external-tables)
+- [Lesson 19. Workload management](#lesson-19-workload-management)
+
+## Lesson 0. Prerequisite
+
+Before starting this crash course, spend some time going through the [Apache Cloudberry Tutorials Based on Single-Node Installation](https://github.com/apache/cloudberry-bootcamp/tree/main/000-cbdb-sandbox/configs) to get familiar with Apache Cloudberry and how it works.
+
+## Lesson 1. Where to read the official documentation
+
+Take a quick look at the official [Cloudberry Documentation](https://cloudberry.apache.org/docs). No need to worry if you do not understand everything.
+
+## Lesson 2. How to install Cloudberry
+
+To begin your journey with Cloudberry, you are expected to install Cloudberry in your preferred environment. The following options are available:
+
+- For testing or trying out Cloudberry in a sandbox environment, see [Install Cloudberry in a Sandbox](https://github.com/apache/cloudberry-bootcamp/tree/main/000-cbdb-sandbox/configs).
+- For deploying Cloudberry in other environments (including the production environment) and the prerequisite software/hardware configuration, see [Cloudberry Deployment Guide](http://localhost:3000/docs/next/deployment/).
+
+## Lesson 3. Cluster architecture
+
+A Cloudberry cluster has one coordinator host (usually named `cdw`) and multiple segment hosts (usually named `sdwXX`).
+
+:::note
+
+If someone is referring to `cdw`, he is referring to the "coordinator host". Similarly, when somebody is referring to "sdw10", he is referring to the 10th segment host.
+
+:::
+
+A coordinator host usually contains only one instance - the coordinator instance. The segment hosts might contain many worker instances. Every instance has its own set of processes, data directory, and listening port. For example, usually, the listening port of the coordinator instance (where all clients will connect to) is `5432`.
+
+Every segment instance has its own listening port, and the base port is specified in the cluster configuration file.
+
+Instances can have 2 roles - primary and mirror. Primary instances serve database queries. Mirror instances simply track and record data changes in primary instances, but do not serve database queries. If the primary instance goes down for some reason, then the corresponding mirror instance transitions to the primary role and starts serving queries (the original primary instance, currently down, is marked as mirror).
+
+The cluster information is stored in the `gp_segment_configuration` system table, which looks like this (use the "psql" command to connect to the database to execute queries):
+
+```shell
+[gpadmin@cdw ~]$ psql
+
+psql (14.4, server 14.4)
+Type "help" for help.
+```
+
+```sql
+gpadmin=# SELECT * FROM gp_segment_configuration;
+
+ dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir
+------+---------+------+----------------+------+--------+-------+----------+---------+--------------------------------
+ 1 | -1 | p | p | n | u | 5432 | cdw | cdw | /data0/database/coordinator/gpseg-1
+ 2 | 0 | p | p | n | u | 40000 | cdw | cdw | /data0/database/primary/gpseg0
+ 3 | 1 | p | p | n | u | 40001 | cdw | cdw | /data0/database/primary/gpseg1
+(3 rows)
+```
+
+The columns of this system are described as follows.
+
+- `dbid`: uniquely identifies a segment.
+- `content`: uniquely identifies segment pairs (primary and mirror). The primary and the corresponding mirror will have the same `content` ID, but different `dbid` values. The coordinator has the `content` value of `-1`. The worker instances have incremental content values of `0`, `1`, `2`, `3`...
+- `role`: the current role of the segment.
+- `preferred_role`: the role of the segment in the original configuration. Note that if an original mirror instance has taken over and become primary now, the role will be changed. This column records the original role.
+- `mode`: the mode of the segment. The value options are `s` (in sync), `c` (in change tracking), and `r` (in recovery).
+- `status`: the status of the segment. The value options are `u` (up) and `d` (down).
+- `port`: the listening port of the segment. For clients, only the listening port of the coordinator is important. The segment listening ports are important for the coordinator to communicate with them.
+- `hostname`: the hostname of the segment.
+- `address`: each host can have different network controllers with different IP addresses and different names associated.
+- `datadir`: the data directory where data is stored for each segment.
+
+**Exercise**
+
+Connect to the Cloudberry cluster that you have created and take a look at the `gp_segment_configuration` table. Try to learn the rows and columns. Take a look at the cluster configuration file that you used to create the cluster.
+
+## Lesson 4. Management utilities
+
+Management utilities in Cloudberry are command-line tools used to administer and manage the database cluster. Some key points:
+
+- They allow performing tasks like starting, stopping, and configuring the database.
+- Help monitor the health and status of the cluster.
+- Used for maintenance like recovering nodes and rebalancing data.
+- Help scale out the cluster by expanding with more nodes.
+- Utilities work across coordinator, standby coordinator, and multiple segment instances.
+
+In summary, management utilities are command-line programs and scripts used by DBAs to administer, monitor, maintain and manage a Cloudberry cluster. The following are some common utilities.
+
+- `gpstop`: stops database cluster.
+- `gpstart`: starts database cluster.
+- `psql`: a command-line client.
+- `gpconfig`: shows or changes configuration parameters.
+- `gpdeletesystem`: deletes a cluster.
+- `pg_dump`, `gpbackup`, `gprestore`: performs backup and restore operations.
+- `gpinitstanby`, `gpactivatestandby`: manages the standby coordinator instance.
+- `gprecoverseg`: recovers segment.
+- `gpfdist`, `gpload`: operates with external tables.
+- `gpssh`, `gpscp`, `gpssh-exkeys`: for cluster navigation.
+- Logging - all utilities write log files under `~/gpAdminLogs/` - one file per day
+
+**Exercise**
+
+Read the help information for these tools (`<tool_name> --help`).
+
+## Lesson 5. Start and stop a cluster
+
+- Start a Cloudberry cluster using `gpstart`:
+
+ ```shell
+ [gpadmin@cdw ~]$ gpstart -a
+ ```
+
+ ```shell
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Starting gpstart with args: -a
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Gathering information and validating the environment...
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Cloudberry Binary Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Cloudberry Catalog Version: '302206171'
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Starting Coordinator instance in admin mode
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-CoordinatorStart pg_ctl cmd is env GPSESSID=0000000000 GPERA=None $GPHOME/bin/pg_ctl -D /data0/database/coordinator/gpseg-1 -l /data0/database/coordinator/gpseg-1/log/startup.log -w -t 600 -o " -p 5432 -c gp_role=utility " start
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Setting new coordinator era
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Coordinator Started...
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Shutting down coordinator
+ 20230823:16:14:23:004256 gpstart:cdw:gpadmin-[INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-Process results...
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:- Successful segment starts = 4
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:- Failed segment starts = 0
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:- Skipped segment starts (segments are marked down in configuration) = 0
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-Successfully started 4 of 4 segment instances
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-Starting Coordinator instance cdw directory /data0/database/coordinator/gpseg-1
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-CoordinatorStart pg_ctl cmd is env GPSESSID=0000000000 GPERA=45b5ca734de32094_230823161423 $GPHOME/bin/pg_ctl -D /data0/database/coordinator/gpseg-1 -l /data0/database/coordinator/gpseg-1/log/startup.log -w -t 600 -o " -p 5432 -c gp_role=dispatch " start
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-Command pg_ctl reports Coordinator cdw instance active
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-Connecting to db template1 on host localhost
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-No standby coordinator configured. skipping...
+ 20230823:16:14:24:004256 gpstart:cdw:gpadmin-[INFO]:-Database successfully started
+ ```
+
+- Stop a Cloudberry cluster using `gpstop`:
+
+ ```shell
+ [gpadmin@cdw ~]$ gpstop -a
+ ```
+
+ ```shell
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Starting gpstop with args: -a
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Gathering information and validating the environment...
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Commencing Coordinator instance shutdown with mode='smart'
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Coordinator segment instance directory=/data0/database/coordinator/gpseg-1
+ 20230823:16:14:18:004143 gpstop:cdw:gpadmin-[INFO]:-Stopping coordinator segment and waiting for user connections to finish ...
+ server shutting down
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-Attempting forceful termination of any leftover coordinator process
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-Terminating processes for segment /data0/database/coordinator/gpseg-1
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-No standby coordinator host configured
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-Targeting dbid [2, 4, 3, 5] for shutdown
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait...
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-0.00% of jobs completed
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-100.00% of jobs completed
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait...
+ 20230823:16:14:19:004143 gpstop:cdw:gpadmin-[INFO]:-0.00% of jobs completed
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:-100.00% of jobs completed
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:- Segments stopped successfully = 4
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:- Segments with errors during stop = 0
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:-Successfully shutdown 4 of 4 segment instances
+ 20230823:16:14:20:004143 gpstop:cdw:gpadmin-[INFO]:-Database successfully shutdown with no errors reported
+ ```
+
+**Exercise**
+
+Read the log entries for `gpstop` and `gpstart`, and try to understand what they mean. Read and exercise the different options for `gpstart` and `gpstop`.
+
+## Lesson 6. Check cluster state
+
+- Check the state of a cluster using `gpstate`.
+
+ `gpstate` is the utility that can give you information about the state of the cluster. You can add different options to see different aspects of the cluster state.
+
+ ```shell
+ [gpadmin@cdw ~]$ gpstate
+ ```
+
+ ```shell
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-Starting gpstate with args:
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-local Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-coordinator Cloudberry Version: 'PostgreSQL 14.4 (Apache Cloudberry 1.0.0 build dev) on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 10.2.1 20210130 (Red Hat 10.2.1-11), 64-bit compiled on Aug 9 2023 14:45:43'
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-Gathering data from segments...
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-Cloudberry instance status summary
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Coordinator instance = Active
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Coordinator standby = No coordinator standby configured
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total segment instance count from metadata = 4
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Primary Segment Status
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total primary segments = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total primary segment valid (at coordinator) = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total primary segment failures (at coordinator) = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid files missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid files found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid PIDs missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid PIDs found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of /tmp lock files missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of /tmp lock files found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number postmaster processes missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number postmaster processes found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Mirror Segment Status
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total mirror segments = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total mirror segment valid (at coordinator) = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total mirror segment failures (at coordinator) = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid files missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid files found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid PIDs missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of postmaster.pid PIDs found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of /tmp lock files missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number of /tmp lock files found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number postmaster processes missing = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number postmaster processes found = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number mirror segments acting as primary segments = 0
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:- Total number mirror segments acting as mirror segments = 2
+ 20230823:16:17:41:004530 gpstate:cdw:gpadmin-[INFO]:-----------------------------------------------------
+ ```
+
+- Check the status of a cluster by querying the `gp_segment_configuration` system table. The `gp_segment_configuration` table displays the "coordinator instance" information about the state of the cluster.
+
+**Exercise**
+
+Check the cluster state and try to collect the information using `gpstate` or `gp_segment_configuration`.
+
+## Lesson 7. How Cloudberry segment mirroring works
+
+**Cloudberry mirroring overview:**
+
+Each segment instance in a Apache Cloudberry has 2 possible roles: primary and mirror.
+
+- Primary role: serves user queries.
+- Mirror role: tracks and records data changes from the primary using WAL replication but does not serve user queries.
+
+If the primary segment instance encounters an issue and goes down, its corresponding mirror segment takes over the primary role to ensure continuity in serving queries. The system will then mark the original, now non-functional, primary segment as a mirror.
+
+**Segment modes**
+
+A segment can exist in one of the following modes:
+
+- `s`: In sync.
+- `c`: Change tracking.
+- `r`: In recovery.
+
+The ideal state for both primary and mirror segments is to be up and in-sync (represented as `P:u/s` and `M:u/s`).
+
+**Scenarios for Cloudberry mirroring**
+
+- Scenario 1: When the mirror instance goes down, the following process occurs.
+
+ 1. The primary segment transitions from the in-sync mode to the change tracking mode (`P:u/c` and `M:d/s`).
+ 2. All data changes are recorded and stored.
+ 3. The DBA, upon noticing this, investigates and starts a recovery for the mirror instance using the `gprecoverseg` command.
+ 4. Both the primary and mirror segments transition to the recovery mode (`P:u/r`, `M:u/r`).
+ 5. Post recovery, both the primary and mirror segments revert to the in-sync mode (`P:u/s`, `M:u/s`).
+
+ This scenario remains transparent to users because the primary segment is still functional. Therefore, queries continue to run without interruptions.
+
+- Scenario 2: When the primary instance goes down, the following process occurs.
+
+ 1. All active sessions on the primary instance are terminated.
+ 2. The database system marks the primary instance as "down" and promotes the mirror instance to the primary role, enabling it to track changes (`M:d/s`, `P:u/c`).
+ 3. The DBA, upon detecting this situation, investigates and starts recovering the mirror using the `gprecoverseg` command.
+ 4. On starting the recovery, both primary and mirror segments transition to the recovery mode `(M:u/r`, `P:u/r`).
+ 5. Once the recovery is completed, both segments switch to the in-sync mode (`M:u/s`, `P:u/s`).
+
+ After this recovery, the roles of the instances have been changed (the original primary is now a mirror). To revert the segments to their original roles, you need to rebalance them. This can be done by either:
+
+ - Using the `gpstop` and `gpstart` commands.
+ - Using the `gprecoverseg -r` command for rebalancing.
+
+**Add mirrors to an existing cluster**
+
+If your Cloudberry cluster was initially created without mirrors, you can use the `gpaddmirrors` utility to add mirrors to the existing cluster. See the following example:
+
+```shell
+[gpadmin@cdw ~]$ gpaddmirrors
+```
+
+<details>
+
+<summary>The example output of gpaddmirrors</summary>
+
+```shell
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Starting gpaddmirrors with args:
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-local Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-coordinator Cloudberry Version: 'PostgreSQL 14.4 (Apache Cloudberry 1.0.0 build dev) on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 10.2.1 20210130 (Red Hat 10.2.1-11), 64-bit compiled on Aug 9 2023 14:45:43'
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Successfully finished pg_controldata /data0/database/primary/gpseg0 for dbid 2:
+stdout: pg_control version number: 13000700
+Catalog version number: 302206171
+Database system identifier: 7270424369249924934
+Database cluster state: in production
+pg_control last modified: Wed 23 Aug 2023 03:59:52 PM CST
+Latest checkpoint location: 0/50EBA18
+Latest checkpoint's REDO location: 0/50EB9E0
+Latest checkpoint's REDO WAL file: 000000010000000000000001
+Latest checkpoint's TimeLineID: 1
+Latest checkpoint's PrevTimeLineID: 1
+Latest checkpoint's full_page_writes: on
+Latest checkpoint's NextXID: 0:762
+Latest checkpoint's NextGxid: 1
+Latest checkpoint's NextOID: 13266
+Latest checkpoint's NextRelfilenode: 12002
+Latest checkpoint's NextMultiXactId: 1
+Latest checkpoint's NextMultiOffset: 0
+Latest checkpoint's oldestXID: 752
+Latest checkpoint's oldestXID's DB: 1
+Latest checkpoint's oldestActiveXID: 761
+Latest checkpoint's oldestMultiXid: 1
+Latest checkpoint's oldestMulti's DB: 1
+Latest checkpoint's oldestCommitTsXid:0
+Latest checkpoint's newestCommitTsXid:0
+Time of latest checkpoint: Wed 23 Aug 2023 03:59:52 PM CST
+Fake LSN counter for unlogged rels: 0/3E8
+Minimum recovery ending location: 0/0
+Min recovery ending loc's timeline: 0
+Backup start location: 0/0
+Backup end location: 0/0
+End-of-backup record required: no
+wal_level setting: replica
+wal_log_hints setting: off
+max_connections setting: 60
+max_worker_processes setting: 13
+max_wal_senders setting: 10
+max_prepared_xacts setting: 250
+max_locks_per_xact setting: 128
+track_commit_timestamp setting: off
+Maximum data alignment: 8
+Database block size: 32768
+Blocks per segment of large relation: 32768
+WAL block size: 32768
+Bytes per WAL segment: 67108864
+Maximum length of identifiers: 64
+Maximum columns in an index: 32
+Maximum size of a TOAST chunk: 8140
+Size of a large-object chunk: 8192
+Date/time type storage: 64-bit integers
+Float8 argument passing: by value
+Data page checksum version: 1
+Mock authentication nonce: 94edd2a762753f7d12faff1737ffb338dec6e01a90eb3d509c6afc67e78bf58e
+File encryption method:
+
+stderr:
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Successfully finished pg_controldata /data0/database/primary/gpseg1 for dbid 3:
+stdout: pg_control version number: 13000700
+Catalog version number: 302206171
+Database system identifier: 7270424369087874885
+Database cluster state: in production
+pg_control last modified: Wed 23 Aug 2023 03:59:52 PM CST
+Latest checkpoint location: 0/50EBA18
+Latest checkpoint's REDO location: 0/50EB9E0
+Latest checkpoint's REDO WAL file: 000000010000000000000001
+Latest checkpoint's TimeLineID: 1
+Latest checkpoint's PrevTimeLineID: 1
+Latest checkpoint's full_page_writes: on
+Latest checkpoint's NextXID: 0:762
+Latest checkpoint's NextGxid: 1
+Latest checkpoint's NextOID: 13266
+Latest checkpoint's NextRelfilenode: 12002
+Latest checkpoint's NextMultiXactId: 1
+Latest checkpoint's NextMultiOffset: 0
+Latest checkpoint's oldestXID: 752
+Latest checkpoint's oldestXID's DB: 1
+Latest checkpoint's oldestActiveXID: 761
+Latest checkpoint's oldestMultiXid: 1
+Latest checkpoint's oldestMulti's DB: 1
+Latest checkpoint's oldestCommitTsXid:0
+Latest checkpoint's newestCommitTsXid:0
+Time of latest checkpoint: Wed 23 Aug 2023 03:59:52 PM CST
+Fake LSN counter for unlogged rels: 0/3E8
+Minimum recovery ending location: 0/0
+Min recovery ending loc's timeline: 0
+Backup start location: 0/0
+Backup end location: 0/0
+End-of-backup record required: no
+wal_level setting: replica
+wal_log_hints setting: off
+max_connections setting: 60
+max_worker_processes setting: 13
+max_wal_senders setting: 10
+max_prepared_xacts setting: 250
+max_locks_per_xact setting: 128
+track_commit_timestamp setting: off
+Maximum data alignment: 8
+Database block size: 32768
+Blocks per segment of large relation: 32768
+WAL block size: 32768
+Bytes per WAL segment: 67108864
+Maximum length of identifiers: 64
+Maximum columns in an index: 32
+Maximum size of a TOAST chunk: 8140
+Size of a large-object chunk: 8192
+Date/time type storage: 64-bit integers
+Float8 argument passing: by value
+Data page checksum version: 1
+Mock authentication nonce: baf9fb0b44c5cc558357b266024336445d958e35f8896fcd94b5ef2143ad052d
+File encryption method:
+
+stderr:
+20230823:16:02:50:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Heap checksum setting consistent across cluster
+Enter mirror segment data directory location 1 of 2 >
+/data0/database/mirror
+Enter mirror segment data directory location 2 of 2 >
+/data0/database/mirror
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Cloudberry Add Mirrors Parameters
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:---------------------------------------------
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Cloudberry coordinator data directory = /data0/database/coordinator/gpseg-1
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Cloudberry coordinator port = 5432
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Batch size = 16
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Segment batch size = 64
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:---------------------------------------------
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Mirror 1 of 2
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:---------------------------------------------
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance host = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance address = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance directory = /data0/database/primary/gpseg0
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance port = 40000
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance host = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance address = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance directory = /data0/database/mirror/gpseg0
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance port = 41000
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:---------------------------------------------
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Mirror 2 of 2
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:---------------------------------------------
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance host = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance address = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance directory = /data0/database/primary/gpseg1
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Primary instance port = 40001
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance host = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance address = cdw
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance directory = /data0/database/mirror/gpseg1
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:- Mirror instance port = 41001
+20230823:16:03:16:003517 gpaddmirrors:cdw:gpadmin-[INFO]:---------------------------------------------
+
+Continue with add mirrors procedure Yy|Nn (default=N):
+> y
+20230823:16:03:30:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Starting to create new pg_hba.conf on primary segments
+20230823:16:03:31:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Successfully modified pg_hba.conf on primary segments to allow replication connections
+20230823:16:03:31:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-2 segment(s) to add
+20230823:16:03:31:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Setting up the required segments for recovery
+20230823:16:03:31:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Running recovery for the required segments
+cdw (dbid 4): pg_basebackup: base backup completed
+cdw (dbid 5): pg_basebackup: base backup completed
+20230823:16:03:33:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Updating configuration with new mirrors
+20230823:16:03:33:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Starting mirrors
+20230823:16:03:33:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-era is 45b5ca734de32094_230823155950
+20230823:16:03:33:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Commencing parallel segment instance startup, please wait...
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Process results...
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-******************************************************************
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Mirror segments have been added; data synchronization is in progress.
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Data synchronization will continue in the background.
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-Use gpstate -s to check the resynchronization progress.
+20230823:16:03:34:003517 gpaddmirrors:cdw:gpadmin-[INFO]:-******************************************************************
+```
+<br />
+</details>
+
+**Exercise**
+
+Add mirrors to your cluster. If your cluster already has mirrors, delete the cluster and recreate one without mirrors (removing mirror segments is not supported).
+
+## Lesson 8. Cloudberry's fault tolerance and segment recovery
+
+**Cloudberry's fault tolerance system**
+
+Cloudberry's Fault Tolerant Service (FTS) continuously watches over the cluster to ensure uninterrupted work with active segments. If a segment goes down, the system makes adjustments to ensure a primary instance is always ready for every content. A situation where both the primary and mirror instances of a content fail is called a "double fault," rendering the database unusable.
+
+**Working principle of the FTS service**
+
+The FTS service operates from the coordinator node using the "ftsprober" process. This process periodically scans segments. If it identifies any changes compared to the last saved configuration, it makes the necessary adjustments. Details about these adjustments are stored in the coordinator log file and the `gp_configuration_history` table, while the status of each instance is updated in the `gp_segment_configuration` table.
+
+The following is a preview of the `gp_configuration_history` table:
+
+```sql
+gpadmin=# SELECT * FROM gp_configuration_history;
+
+ time | dbid | desc
+-------------------------------+------+--------------------------------------------------------------------------------
+ 2023-08-23 16:03:33.917057+08 | 4 | gpaddmirrors: segment config for resync: inserted mirror segment configuration
+ 2023-08-23 16:03:33.917057+08 | 5 | gpaddmirrors: segment config for resync: inserted mirror segment configuration
+ 2023-08-23 16:03:52.784083+08 | 2 | FTS: update role, status, and mode for dbid 2 with contentid 0 to p, u, and s
+ 2023-08-23 16:03:52.78515+08 | 4 | FTS: update role, status, and mode for dbid 4 with contentid 0 to m, u, and s
+ 2023-08-23 16:03:52.794198+08 | 3 | FTS: update role, status, and mode for dbid 3 with contentid 1 to p, u, and s
+ 2023-08-23 16:03:52.794214+08 | 5 | FTS: update role, status, and mode for dbid 5 with contentid 1 to m, u, and s
+(6 rows)
+```
+
+**Recover a downed segment**
+
+If a segment is down, you can recover it using the `gprecoverseg` utility. This tool will read the `gp_segment_configuration` table, identify the downed segment, and try to recover it using its related primary segment. However, for this recovery to work, the primary segment must be active.
+
+**Investigate segment failures**
+
+1. Check the `gp_segment_configuration` table to find the downed segments and their mirrors.
+2. Read the coordinator log file to determine when the failure occurred. Look for log entries that start with "FTS:".
+3. Read the `gp_configuration_history` table to see a list of logged events.
+4. Check the primary and mirror log files to understand why the segment failed.
+5. Once you know the cause of the failure, use `gprecoverseg` to restore the segments.
+6. To keep an eye on the recovery, use the `gpstate -e` command.
+
+**Exercise**
+
+1. Find the processes related to one of your database instances and stop it.
+2. Watch the `gp_segment_configuration` table for any changes.
+3. Next, read the coordinator log file and again check the `gp_segment_configuration` table.
+4. To fix the failed segment, use the recovery tools.
+5. Confirm in the `gp_segment_configuration` table that the segment has been recovered.
+
+## Lesson 9. Set up and manage the standby coordinator instance in Cloudberry
+
+**Understand the role of the standby coordinator**
+
+In the Cloudberry architecture, the coordinator instance can be a single point of failure. To avoid this, it is recommended to set up and maintain a "standby coordinator" instance. Typically, this standby instance is set up on a distinct server, referred to as the "standby coordinator server" or "scdw".
+
+**Manage standby coordinator**
+
+- To create the standby coordinator (from the coordinator server), use `gpinitstandby -s scdw`.
+- If the standby coordinator falls out-of-sync, you can resynchronize it using `gpinitstandby -n`.
+- To remove the standby coordinator, use `gpinitstandby -r`.
+
+After setting up the standby instance, you can query the `gp_segment_configuration` table. In the table output, the new standby instance will be visible, which is identified by a `content` value of `-1` and a `role` value of `'m'`.
+
+**Promote the standby coordinator**
+
+If the coordinator server or coordinator instance is unavailable, the standby coordinator can step in to take its place. To promote the standby coordinator to the role of the coordinator, run the `gpactivatestandby` command from the standby server.
+
+Upon running this command, the roles change: the standby coordinator becomes the coordinator, while the original coordinator gets demoted to the role of the standby instance, although it remains offline. You can query the `gp_segment_configuration` table again to notice these changes.
+
+**Exercise**
+
+1. Create the standby coordinator.
+2. Remove the standby coordinator.
+3. Create the standby coordinator again.
+4. Activate this standby coordinator to make it the primary coordinator.
+5. Create another standby coordinator on the original coordinator server.
+6. Activate this new standby coordinator.
+
+Throughout these steps, keep an eye on the `gp_segment_configuration` table. This will help you understand the changes in roles and the state of each instance.
+
+## Lesson 10. Expand a cluster
+
+If you need to expand a cluster, you can use the `gpexpand` tool. You can add new hosts or more segments to the current hosts.
+
+**Steps to expand a cluster**
+
+1. Run `gpexpand` and answer questions.
+
+ Run "gpexpand". In this step, you will be guided to answer questions about what you want to do. After answering all the questions, it makes a "configuration file" and stops. You should look at this file to check whether it is doing what you want.
+
+2. Run `gpexpand -i <config_file>` to create new instances for the cluster.
+
+ This step creates the new instances of the cluster. These new instances will show up in the `gp_segment_configuration` table. The cluster is now expanded, but the new instances do not have the data from users yet.
+
+ `gpexpand` also makes a new area called the `gpexpand` schema. This area has a list of tables that need to be spread out in the next step.
+
+3. Run `gpexpand` again to redistribute or spread out the data.
+
+ Here, the data, which was only in the old instances, is now spread out to the expanded cluster. You can do this step many times by setting a time limit with the `-d` option. When all data is spread out, `gpexpand` stops.
+
+4. Finish up.
+
+ Now, the cluster is expanded and the data is spread out. The `gpexpand` schema is still there. You can look at it if you need to. It will not cause any problems.
+
+ But if you want to expand the cluster again, `gpexpand` will tell you to remove it using `gpexpand -c` before it can continue.
+
+**Exercise**
+
+1. Run `gpexpand` to add more segments to your current servers.
+2. Run `gpexpand` again to add segments on new servers.
+3. Look at the `gp_segment_configuration` table to see how it changes with each action.
+
+## Lesson 11. Check cluster performance
+
+You can use the `gpcheckperf` utility to check performance on a set of hosts (cluster). This utility can be used to check the following:
+
+- I/O performance per host (with the `-r -d` options)
+
+ > Note: gpcheckperf will use the `dd` command to perform read test and write test on each host.
+
+- Network performance (with the `-r -n|N|M` options)
+
+ > Note: `-n` means sequential. `-N` means parallel with even number of hosts. `-M` means full matrix mode.
+
+- Memory bandwidth test per host (with the `-r -s` options)
+
+ > Note: The utility uses the STREAM benchmark program to measure sustainable memory bandwidth (in MB/s).
+
+**Exercise**
+
+Run `gpcheckcat` with the various options and interpret the results.
+
+## Lesson 12. User data and table distribution
+
+In Cloudberry, the coordinator instance does not store user data. Segments store user data, and the data is not shared between segments.
+
+
+All Cloudberry Database tables are distributed. When you create or alter a table, you optionally specify DISTRIBUTED BY (hash distribution), DISTRIBUTED RANDOMLY (random distribution), or DISTRIBUTED REPLICATED (fully distributed) to determine the table row distribution.
+
+Consider the following points when deciding on a table distribution policy.
+
+- *Even Data Distribution* — For the best possible performance, all segments should contain equal portions of data. If the data is unbalanced or skewed, the segments with more data must work harder to perform their portion of the query processing. Choose a distribution key that is unique for each record, such as the primary key.
+- *Local and Distributed Operations* — Local operations are faster than distributed operations. Query processing is fastest if the work associated with join, sort, or aggregation operations is done locally, at the segment level. Work done at the system level requires distributing tuples across the segments, which is less efficient. When tables share a common distribution key, the work of joining or sorting on their shared distribution key columns is done locally. With a random distributi [...]
+- *Even Query Processing* — For best performance, all segments should handle an equal share of the query workload. Query workload can be skewed if a table's data distribution policy and the query predicates are not well matched. For example, suppose that a sales transactions table is distributed on the customer ID column (the distribution key). If a predicate in a query references a single customer ID, the query processing work is concentrated on just one segment.
+
+The replicated table distribution policy (DISTRIBUTED REPLICATED) should be used only for small tables. Replicating data to every segment is costly in both storage and maintenance, and prohibitive for large fact tables. The primary use cases for replicated tables are to:
+
+- remove restrictions on operations that user-defined functions can perform on segments, and
+- improve query performance by making it unnecessary to broadcast frequently used tables to all segments.
+
+
+In the following example, we can see the vaule "1" for c1 column is alwasy located in the same segment (gp_segment_id=1) if the table is distributed by hash.
+
+```
+gpadmin=# create table t1 (c1 int) distributed by (c1);
+CREATE TABLE
+
+gpadmin=# insert into t1 values (1);
+INSERT 0 1
+gpadmin=# insert into t1 values (1);
+INSERT 0 1
+gpadmin=# insert into t1 values (1);
+INSERT 0 1
+gpadmin=# insert into t1 values (1);
+INSERT 0 1
+
+gpadmin=# select gp_segment_id, * from t1;
+ gp_segment_id | c1
+---------------+----
+ 1 | 1
+ 1 | 1
+ 1 | 1
+ 1 | 1
+(4 rows)
+
+```
+
+In the following example, we can see the vaule "1" for c1 column is randomly distributed on all segments (gp_segment_id=0 and gp_segment_id=1) if the table is distributed randomly.
+
+```
+gpadmin=# create table t2 (c1 int) distributed randomly;
+CREATE TABLE
+
+gpadmin=# insert into t2 values (1);
+INSERT 0 1
+gpadmin=# insert into t2 values (1);
+INSERT 0 1
+gpadmin=# insert into t2 values (1);
+INSERT 0 1
+gpadmin=# insert into t2 values (1);
+INSERT 0 1
+
+gpadmin=# select gp_segment_id, * from t2;
+ gp_segment_id | c1
+---------------+----
+ 0 | 1
+ 1 | 1
+ 1 | 1
+ 1 | 1
+(4 rows)
+```
+
+Exercise: Reproduce the above with your own table and observe the effects.
+
+## Lesson 13. Database catalog
+
+System tables prefixed with *gp_* relate to the parallel features of Cloudberry Database. Tables prefixed with *pg_* are either standard PostgreSQL system catalog tables supported in Cloudberry Database, or are related to features Cloudberry that provides to enhance PostgreSQL for data warehousing workloads. Note that the global system catalog for Cloudberry Database resides on the coordinator instance.
+
+Exercise: Run the following query to get a list of catalog table names.
+
+```
+select tablename from pg_tables where schemaname='pg_catalog';
+```
+
+## Lesson 14. Cloudberry data directories
+
+In the coordinator instance, the data directory is located at `/data0/database/coordinator/gpseg-1`.
+
+The following are the contents in the data directory of the coordinator instance and coordinator instance:
+
+```shell
+[gpadmin@cdw gpseg-1]$ ls -tlr
+```
+
+```shell
+total 156
+-rw------- 1 gpadmin gpadmin 3 Aug 23 15:59 PG_VERSION
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_twophase
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_tblspc
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_snapshots
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_serial
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_replslot
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_notify
+drwx------ 4 gpadmin gpadmin 4096 Aug 23 15:59 pg_multixact
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_dynshmem
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_cryptokeys
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_commit_ts
+-rw------- 1 gpadmin gpadmin 88 Aug 23 15:59 postgresql.auto.conf
+-rw------- 1 gpadmin gpadmin 1636 Aug 23 15:59 pg_ident.conf
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_xact
+drwx------ 3 gpadmin gpadmin 4096 Aug 23 15:59 pg_wal
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_subtrans
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_distributedlog
+-rw------- 1 gpadmin gpadmin 31760 Aug 23 15:59 postgresql.conf
+-rw------- 1 gpadmin gpadmin 10 Aug 23 15:59 internal.auto.conf
+-rw-rw-r-- 1 gpadmin gpadmin 860 Aug 23 15:59 gpssh.conf
+drwx------ 6 gpadmin gpadmin 4096 Aug 23 15:59 base
+-rw-rw-r-- 1 gpadmin gpadmin 4723 Aug 23 15:59 pg_hba.conf
+-rw------- 1 gpadmin gpadmin 38 Aug 23 16:14 current_logfiles
+-rw------- 1 gpadmin gpadmin 112 Aug 23 16:14 postmaster.opts
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:14 pg_stat
+-rw------- 1 gpadmin gpadmin 130 Aug 23 16:14 gpsegconfig_dump
+-rw------- 1 gpadmin gpadmin 88 Aug 23 16:14 postmaster.pid
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:14 log
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:14 global
+drwx------ 4 gpadmin gpadmin 4096 Aug 23 16:39 pg_logical
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:44 pg_stat_tmp
+```
+
+In the segment instances, the data directory is located at `/data0/database/primary/gpseg0` and `/data0/database/primary/gpseg1`. The following are the contents in the data directory of a segment instance:
+
+```shell
+[gpadmin@cdw gpseg-1]$ cd /data0/database/primary/gpseg0
+[gpadmin@cdw gpseg0]$ ls -ltr
+```
+
+```shell
+total 180
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_twophase
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_tblspc
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_snapshots
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_serial
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_notify
+drwx------ 4 gpadmin gpadmin 4096 Aug 23 15:59 pg_multixact
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_dynshmem
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_cryptokeys
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_commit_ts
+-rw------- 1 gpadmin gpadmin 3 Aug 23 15:59 PG_VERSION
+-rw------- 1 gpadmin gpadmin 1636 Aug 23 15:59 pg_ident.conf
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_xact
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_subtrans
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 15:59 pg_distributedlog
+-rw------- 1 gpadmin gpadmin 31637 Aug 23 15:59 postgresql.conf
+-rw------- 1 gpadmin gpadmin 10 Aug 23 15:59 internal.auto.conf
+-rw------- 1 gpadmin gpadmin 4915 Aug 23 16:03 pg_hba.conf
+drwx------ 7 gpadmin gpadmin 4096 Aug 23 16:03 base
+drwx------ 3 gpadmin gpadmin 4096 Aug 23 16:03 pg_replslot
+drwx------ 3 gpadmin gpadmin 4096 Aug 23 16:03 pg_wal
+-rw------- 1 gpadmin gpadmin 120 Aug 23 16:03 postgresql.auto.conf
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:14 log
+-rw------- 1 gpadmin gpadmin 38 Aug 23 16:14 current_logfiles
+-rw------- 1 gpadmin gpadmin 112 Aug 23 16:14 postmaster.opts
+-rw------- 1 gpadmin gpadmin 89 Aug 23 16:14 postmaster.pid
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:14 pg_stat
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:29 global
+drwx------ 4 gpadmin gpadmin 4096 Aug 23 16:44 pg_logical
+-rw------- 1 gpadmin gpadmin 32768 Aug 23 16:44 fts_probe_file.bak
+drwx------ 2 gpadmin gpadmin 4096 Aug 23 16:44 pg_stat_tmp
+```
+
+**Exercise**
+
+Explore the data directory and subdirectories. Take a look at the configuration files.
+
+## Lesson 15. Instance processes
+
+- postgres process: the process with the data directory in its name (-D ...) - this process is the parent for all other database processes and it handles connections to this instance
+
+- logger process: writes log entries in the log file
+
+- stats collector process: statistics collector is a subsystem that supports collection and reporting of information about server activity. Presently, the collector can count accesses to tables and indexes in both disk-block and individual-row terms.
+
+- background writer process: writes "dirty" (new or modified) buffers to the on-disk datafiles.
+
+- sweeper process: related to workload management and prioritization
+
+- checkpoint process: performs checkpoints
+
+- walwriter process: periodically flushes the WAL buffer to disk
+
+- ftsprobe process: runs in the coordinator node, and periodically polls the segments to maintain the status of each segment.
+
+- walsender process: runs in primary segment, and sends the WALs to the mirror segment
+
+- walreceiver process: runs in mirror segment, and receives WALs from primary segment
+
+- conXXX: user connection worker process
+
+
+#### coordinator processes:
+
+```
+[gpadmin@cdw ~]$ ps aux|grep 5432
+gpadmin 4409 0.0 0.4 209960 39776 ? Ss 16:14 0:00 /usr/local/cloudberry/bin/postgres -D /data0/database/coordinator/gpseg-1 -p 5432 -c gp_role=dispatch
+gpadmin 4410 0.0 0.0 45544 5160 ? Ss 16:14 0:00 postgres: 5432, coordinator logger process
+gpadmin 4412 0.0 0.1 210256 9484 ? Ss 16:14 0:00 postgres: 5432, checkpointer
+gpadmin 4413 0.0 0.0 210124 7440 ? Ss 16:14 0:00 postgres: 5432, background writer
+gpadmin 4414 0.0 0.1 210124 10304 ? Ss 16:14 0:00 postgres: 5432, walwriter
+gpadmin 4415 0.0 0.1 211544 12068 ? Ss 16:14 0:00 postgres: 5432, autovacuum launcher
+gpadmin 4416 0.0 0.0 45768 5672 ? Ss 16:14 0:00 postgres: 5432, stats collector
+gpadmin 4417 0.0 0.2 278596 22164 ? Ssl 16:14 0:00 postgres: 5432, dtx recovery process
+gpadmin 4418 0.0 0.2 278416 21624 ? Ssl 16:14 0:00 postgres: 5432, ftsprobe process
+gpadmin 4427 0.0 0.1 211492 11244 ? Ss 16:14 0:00 postgres: 5432, logical replication launcher
+gpadmin 4428 0.0 0.2 277792 17748 ? Ssl 16:14 0:00 postgres: 5432, pg_cron launcher
+gpadmin 4429 0.0 0.0 210124 5316 ? Ss 16:14 0:00 postgres: 5432, sweeper process
+gpadmin 4846 0.0 0.5 294692 48516 ? Ssl 16:25 0:00 postgres: 5432, gpadmin gpadmin [local] con46 cmd172 idle
+```
+
+#### primary processes:
+
+```
+[gpadmin@cdw ~]$ ps aux|grep 40000
+gpadmin 4373 0.0 0.5 212912 41404 ? Ss 16:14 0:00 /usr/local/cloudberry/bin/postgres -D /data0/database/primary/gpseg0 -p 40000 -c gp_role=execute
+gpadmin 4377 0.0 0.0 45540 5272 ? Ss 16:14 0:00 postgres: 40000, logger process
+gpadmin 4390 0.0 0.1 213212 9328 ? Ss 16:14 0:00 postgres: 40000, checkpointer
+gpadmin 4391 0.0 0.0 213076 7856 ? Ss 16:14 0:00 postgres: 40000, background writer
+gpadmin 4392 0.0 0.1 213076 10428 ? Ss 16:14 0:00 postgres: 40000, walwriter
+gpadmin 4393 0.0 0.1 213784 9948 ? Ss 16:14 0:00 postgres: 40000, autovacuum launcher
+gpadmin 4394 0.0 0.0 45768 5816 ? Ss 16:14 0:00 postgres: 40000, stats collector
+gpadmin 4395 0.0 0.0 213624 7984 ? Ss 16:14 0:00 postgres: 40000, logical replication launcher
+gpadmin 4396 0.0 0.0 212912 4480 ? Ss 16:14 0:00 postgres: 40000, sweeper process
+gpadmin 4400 0.0 0.1 214868 12432 ? Ss 16:14 0:00 postgres: 40000, walsender gpadmin 172.17.0.2(37278) streaming 0/100F9088
+```
+
+#### mirror processes:
+
+```
+[gpadmin@cdw ~]$ ps aux|grep 41000
+gpadmin 4375 0.0 0.5 212912 41196 ? Ss 16:14 0:00 /usr/local/cloudberry/bin/postgres -D /data0/database/mirror/gpseg0 -p 41000 -c gp_role=execute
+gpadmin 4379 0.0 0.0 45540 5160 ? Ss 16:14 0:00 postgres: 41000, logger process
+gpadmin 4383 0.0 0.1 213344 10908 ? Ss 16:14 0:00 postgres: 41000, startup recovering 000000010000000000000004
+gpadmin 4385 0.0 0.1 212912 8352 ? Ss 16:14 0:00 postgres: 41000, checkpointer
+gpadmin 4386 0.0 0.0 212912 6260 ? Ss 16:14 0:00 postgres: 41000, background writer
+gpadmin 4387 0.1 0.1 213788 9740 ? Ss 16:14 0:04 postgres: 41000, walreceiver streaming 0/100F9088
+```
+
+Exercise: Try to identify the processes for the instances in your cluster.
+
+## Lesson 16. Database log files
+
+Each instance has its own log files which are located in the `<data_directory>/log` directory.
+
+```shell
+[gpadmin@cdw log]$ pwd
+
+/data0/database/primary/gpseg0/log
+
+[gpadmin@cdw log]$ ls -ltr
+
+total 20
+-rw------- 1 gpadmin gpadmin 8842 Aug 23 16:14 gpdb-2023-08-23_155951.csv
+-rw------- 1 gpadmin gpadmin 468 Aug 23 16:14 startup.log
+-rw------- 1 gpadmin gpadmin 3269 Aug 23 16:17 gpdb-2023-08-23_161424.csv
+```
+
+The standard log file name is `gpdb_<date>-<time>.csv`.
+
+**Exercise**
+
+Look at the log file and do different things in the database (create table, run queries, etc.)
+
+## Lesson 17. Table types in Cloudberry: heap, AO, and AOCO
+
+**Heap tables**
+
+In Cloudberry, the standard table type is "heap". These tables store rows on pages, and one data file can contain multiple pages. They support common SQL tasks such as `SELECT`, `INSERT`, `UPDATE`, `DELETE`, and `TRUNCATE`. Each row in a heap table has a header. However, these tables do not support compression.
+
+**AO tables**
+
+Unlike heap tables, AO tables do not have a row header but they do allow compression. This feature makes AO tables a good fit for big fact tables.
+
+**AOCO (column-oriented) tables**
+
+For tasks that focus on single columns, like average (`AVG`) or sum, using row-oriented storage is not the ideal option. Instead, column-oriented tables, which store data by its column, are more efficient. This is because when you query one column, it does not get slowed down by the size or count of other columns.
+
+AOCO tables offer compression, and they compress even better than AO tables. This is due to the consistent type of data in one file, which means that all data in a single column is of the same kind.
+
+**Exercise**
+
+Make a heap table, an AO table, and an AOCO table. After creating them, use the `\d+ psql` command to view the results.
+
+## Lesson 18. External tables
+
+Cloudberry supports external tables. These tables are set up in the database, but they link to data outside of the database. Here is where the data for these tables can come from:
+
+- A file on your local filesystem.
+- A file on a remote host (you need to use the `gpfdist` server for this).
+- HDFS, which stands for the `gphdfs` type.
+- Data generated on the spot with a command.
+
+One good thing about external tables is that they make it easy to load data into Cloudberry. When you use a command like `INSERT INTO table SELECT * FROM ext_table`, data gets loaded all at once from different parts, instead of one piece at a time.
+
+**Exercise**
+
+Try making different types of external tables and play around with them to learn more.
+
+## Lesson 19. Workload management
+
+In Cloudberry, we have something called Resource Queues, or RQ for short. Think of an RQ as a group of sessions that need similar things and share resources. You can put any user into an RQ.
+
+Also, there is a feature called Priority. You can set a priority level for a whole RQ, which means every session in that RQ will have that priority. But if you want, you can set a priority for just one session using the `gp_adjust_priority()` function.
+
+**Exercise**
+
+Make a user, set up an RQ, put the user in the RQ, then run a query. Watch and see what happens to the RQ.
diff --git a/versioned_docs/version-2.x/tutorial/create-and-prepare-database.md b/versioned_docs/version-2.x/tutorial/create-and-prepare-database.md
new file mode 100644
index 000000000..8e5fa1c67
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/create-and-prepare-database.md
@@ -0,0 +1,200 @@
+---
+title: "Lesson 2: Create and Prepare Database"
+description: Let's create one new database in the Apache Cloudberry.
+---
+
+To create a new database in Apache Cloudberry, you can either use the `CREATE DATABASE` SQL command in the `psql` client, or use the `createdb` utility. The `createdb` utility is a wrapper around the `CREATE DATABASE` command.
+
+## Quick-start operations
+
+In the following operations, you will be guided to create a new database using the `createdb` utility, to create a schema, and to set search path for schemas. You will also learn how to create a user and grant privileges to the user.
+
+Before moving on to the operations, make sure that you have completed the previous tutorial `Lesson 1: Create Users and Roles`. You will connect to the tutorial database as the user `lily` with password set up in the previous tutorial.
+
+### Create database
+
+1. Log into Apache Cloudberry in Docker. Before creating the `tutorial` database, make sure that this database does not exist.
+
+ ```shell
+ [gpadmin@cdw ~]$ dropdb tutorial
+ ```
+
+ Output:
+
+ ```shell
+ dropdb: error: database removal failed: ERROR: database "tutorial" does not exist
+ ```
+
+2. Create the `tutorial` database using the `createdb` utility.
+
+ ```shell
+ [gpadmin@cdw ~]$ createdb tutorial
+ ```
+
+ ```shell
+ [gpadmin@cdw ~]$ psql -l # Verifies that this database has been created.
+ List of databases
+ Name | Owner | Encoding | Collate | Ctype | Access privileges
+ -----------+---------+----------+-------------+-------------+---------------------
+ gpadmin | gpadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
+ postgres | gpadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
+ template0 | gpadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/gpadmin +
+ | | | | | gpadmin=CTc/gpadmin
+ template1 | gpadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/gpadmin +
+ | | | | | gpadmin=CTc/gpadmin
+ tutorial | gpadmin | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
+ (5 rows)
+ ```
+
+ > **Info:**
+ >
+ > Unless you specify a different database, the newly created database is a copy of the `template1` database.
+
+3. Create an entry in the `pg_hba.conf` configuration file by appending `local tutorial lily md5` to `/data0/database/coordinator/gpseg-1/pg_hba.conf`.
+
+ ```shell
+ [gpadmin@cdw ~]$ echo "local tutorial lily md5" >> /data0/database/coordinator/gpseg-1/pg_hba.conf
+ ```
+
+ > **Info:**
+ >
+ > - `pg_hba.conf` is the configuration file for client access control in Apache Cloudberry.
+ > - `md5` is the authentication methods, which means that the user needs to enter the password to log in.
+
+
+4. Reload the configuration file to populate the change.
+
+ ```shell
+ [gpadmin@cdw ~]$ gpstop -u
+ ```
+
+ ```shell
+ 20230818:14:18:45:003733 gpstop:cdw:gpadmin-[INFO]:-Starting gpstop with args: -u
+ 20230818:14:18:45:003733 gpstop:cdw:gpadmin-[INFO]:-Gathering information and validating the environment...
+ 20230818:14:18:45:003733 gpstop:cdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
+ 20230818:14:18:45:003733 gpstop:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+ 20230818:14:18:45:003733 gpstop:cdw:gpadmin-[INFO]:-Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+ 20230818:14:18:45:003733 gpstop:cdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload
+ ```
+
+5. Connect to the `tutorial` database as the user `lily`. You need to enter the password set up in the [previous tutorial](./create-users-and-roles#create-a-user-using-the-create-user-command).
+
+ ```shell
+ [gpadmin@cdw ~]$ psql -U lily tutorial
+
+ Password for user lily: # changeme
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+ ```sql
+ tutorial=> \q -- Exits the database.
+ ```
+
+### Grant database privileges to users
+
+For database users to properly do their works, you need to grant them the minimum permissions required. For example, a user might need `SELECT` permissions on a table to view data, and need `UPDATE`, `INSERT`, or `DELETE` to modify the data.
+
+In the following operations, the database user `lily` will require permissions to create and manipulate objects in the `tutorial` database.
+
+1. Connect to the `tutorial` database as `gpadmin`.
+
+ ```shell
+ [gpadmin@cdw ~]$ psql -U gpadmin tutorial
+ ```
+
+ Output:
+
+ ```shell
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+2. Grant `lily` all privileges on the `tutorial` database.
+
+ ```sql
+ tutorial=# GRANT ALL PRIVILEGES ON DATABASE tutorial TO lily;
+ ```
+
+ Output:
+
+ ```sql
+ GRANT
+ ```
+
+ ```sql
+ tutorial=# \q -- Exits the database.
+ ```
+
+### Create schema and set search path
+
+In this section, you will be guided to create a `faa` schema and set the search path to make `faa` the default schema.
+
+> **Info:**
+>
+> Database schema is a named container for a set of database objects, including tables, data types, and functions. One database can have multiple schemas. Objects in the schema are referenced by prefixing the object name with the schema name, separated with a period. For example, the `person` table in the `employee schema` is written as `employee.person`.
+>
+> The schema provides a namespace for the objects it contains. If the database is used for multiple applications, each with its own schema, the same table name can be used in each schema. For example, `employee.person` is a different table than `customer.person`. Both tables can be accessed in the same query as long as they are with accordingly schema name.
+>
+> The database contains a schema search path including a list of schema names. The first schema in the search path is also the schema where new objects are created when no schema is specified. The default search path is user,public, so by default, each object you create belongs to a schema associated with your login name.
+
+1. Connect to the `tutorial` database as the user `lily`.
+
+ ```shell
+ [gpadmin@cdw ~]$ psql -U lily tutorial
+ ```
+
+ ```shell
+ Password for user lily: # changeme
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+2. Create the `faa` schema.
+
+ ```sql
+ tutorial=> DROP SCHEMA IF EXISTS faa CASCADE;
+ tutorial=> CREATE SCHEMA faa;
+ ```
+
+3. Set the search path to `faa`, `public`, `pg_catalog`, and `gp_toolkit` schemas.
+
+ ```sql
+ tutorial=> SET SEARCH_PATH TO faa, public, pg_catalog, gp_toolkit;
+ ```
+
+ Output:
+
+ ```sql
+ SET
+ ```
+
+4. Verify that the search path is set correctly.
+
+ ```sql
+ tutorial=> SHOW search_path;
+ ```
+
+ Output:
+
+ ```sql
+ search_path
+ -------------------------------------
+ faa, public, pg_catalog, gp_toolkit
+ (1 row)
+ ```
+
+5. Associate a search path with the user role `lily`.
+
+ The search path you have set in the previous step is not persistent. You need to set it each time you connect to the database. You can associate a search path with the user role by using the `ALTER ROLE` command, so that each time you connect to the database with that role, the search path is restored.
+
+ ```sql
+ tutorial=> ALTER ROLE lily SET search_path TO faa, public, pg_catalog, gp_toolkit;
+ ```
+
+ Output:
+
+ ```sql
+ ALTER ROLE
+ ```
diff --git a/versioned_docs/version-2.x/tutorial/create-tables.md b/versioned_docs/version-2.x/tutorial/create-tables.md
new file mode 100644
index 000000000..30a7ae371
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/create-tables.md
@@ -0,0 +1,148 @@
+---
+title: "Lesson 3: Create Tables"
+description: Learn how to create tables in the Apache Cloudberry.
+---
+
+After creating and preparing a database in [Lesson 2: Create and Prepare a Database](./create-and-prepare-database), you can start to create tables in the database.
+
+:::note
+
+To introduce Apache Cloudberry, we use a public data set, the Airline On-Time Statistics and Delay Causes data set, published by the United States Department of Transportation at http://www.transtats.bts.gov/. The On-Time Performance dataset records flights by date, airline, originating airport, destination airport, and many other flight details. The data is available for flights since 1987. The exercises in this guide use data for about a million flights in 2009 and 2010. You are encour [...]
+
+:::
+
+## Create tables using a SQL file in psql
+
+In Apache Cloudberry, you can use the `CREATE TABLE` SQL statement to create a table.
+
+In the following steps, you will be guided to run a SQL file `create_dim_tables.sql` that contains the `CREATE TABLE` statements needed to create `faa` databases.
+
+1. Log into Apache Cloudberry in Docker as `gpadmin`. Then enter the `faa` directory, in which the SQL file `create_dim_tables.sql` is located.
+
+ ```shell
+ [gpadmin@cdw tmp]$ cd /tmp
+ [gpadmin@cdw tmp]$ tar xzf faa.tar.gz
+ [gpadmin@cdw tmp]$ cd faa
+ ```
+
+2. Take a look at the `create_dim_tables.sql` file.
+
+ ```shell
+ [gpadmin@cdw faa]$ more create_dim_tables.sql
+ ```
+
+ The `create_dim_tables.sql` file contains the following `CREATE TABLE` statements:
+
+ ```sql
+ DROP TABLE IF EXISTS faa.d_airports; -- Drops the table if it exists to avoid name conflict.
+ CREATE TABLE faa.d_airports ( -- Creates the table.
+ AirportID INTEGER,
+ Name TEXT,
+ City TEXT,
+ Country TEXT,
+ airport_code TEXT,
+ ICOA_code TEXT,
+ Latitude FLOAT8,
+ Longitude FLOAT8,
+ Altitude FLOAT8,
+ TimeZoneOffset FLOAT,
+ DST_Flag TEXT,
+ TZ TEXT
+ )
+ DISTRIBUTED BY (airport_code); -- Specifies the distribution column airport_code.
+
+ DROP TABLE IF EXISTS faa.d_wac;
+ CREATE TABLE faa.d_wac (wac SMALLINT, area_desc TEXT)
+ DISTRIBUTED BY (wac);
+
+ DROP TABLE IF EXISTS faa.d_airlines;
+ CREATE TABLE faa.d_airlines (airlineid INTEGER, airline_desc TEXT)
+ DISTRIBUTED BY (airlineid);
+
+ DROP TABLE IF EXISTS faa.d_cancellation_codes;
+ CREATE TABLE faa.d_cancellation_codes (cancel_code TEXT, cancel_desc TEXT)
+ DISTRIBUTED BY (cancel_code);
+
+ DROP TABLE IF EXISTS faa.d_delay_groups;
+ CREATE TABLE faa.d_delay_groups (delay_group_code TEXT, delay_group_desc TEXT)
+ DISTRIBUTED BY (delay_group_code);
+
+ DROP TABLE IF EXISTS faa.d_distance_groups;
+ CREATE TABLE faa.d_distance_groups (distance_group_code TEXT, distance_group_desc TEXT)
+ DISTRIBUTED BY (distance_group_code);
+ ```
+
+3. Connect to the `tutorial` database as `lily` using the `psql`. You will run the SQL file as `lily`.
+
+ ```shell
+ [gpadmin@cdw faa]$ psql -U lily tutorial
+
+ Password for user lily: # changeme
+ ```
+
+ ```shell
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ tutorial=>
+ ```
+
+4. Run the `create_dim_tables.sql` file using the `psql` command `\i`.
+
+ ```sql
+ tutorial=> \i create_dim_tables.sql
+ ```
+
+ The following messages are displayed:
+
+ ```sql
+ psql:create_dim_tables.sql:1: NOTICE: table "d_airports" does not exist, skipping
+ DROP TABLE
+ CREATE TABLE
+ psql:create_dim_tables.sql:18: NOTICE: table "d_wac" does not exist, skipping
+ DROP TABLE
+ CREATE TABLE
+ psql:create_dim_tables.sql:22: NOTICE: table "d_airlines" does not exist, skipping
+ DROP TABLE
+ CREATE TABLE
+ psql:create_dim_tables.sql:26: NOTICE: table "d_cancellation_codes" does not exist, skipping
+ DROP TABLE
+ CREATE TABLE
+ psql:create_dim_tables.sql:30: NOTICE: table "d_delay_groups" does not exist, skipping
+ DROP TABLE
+ CREATE TABLE
+ psql:create_dim_tables.sql:34: NOTICE: table "d_distance_groups" does not exist, skipping
+ DROP TABLE
+ CREATE TABLE
+ ```
+
+5. Use the `\dt` command to display all tables in the `faa` schema. You can see that 6 tables have been created.
+
+ ```sql
+ tutorial=> \dt
+ List of relations
+ Schema | Name | Type | Owner | Storage
+ --------+----------------------+-------+-------+---------
+ faa | d_airlines | table | lily | heap
+ faa | d_airports | table | lily | heap
+ faa | d_cancellation_codes | table | lily | heap
+ faa | d_delay_groups | table | lily | heap
+ faa | d_distance_groups | table | lily | heap
+ faa | d_wac | table | lily | heap
+ (6 rows)
+ ```
+
+## About the distribution policy
+
+The definition of a table includes the distribution policy for the data, which is critical for query performance. The goals for the distribution policy are to:
+
+- Distribute the volume of data and query execution work evenly among segments.
+- Enable segments to accomplish complicated query processing steps locally.
+
+The distribution policy determines how data is distributed among segments. To get an effective distribution policy requires understanding of the data's characteristics, what kind of queries that would be executed on the data and what distribution strategy will best utilize the parallel execution capacity among segments.
+
+Use the `DISTRIBUTED` clause in `CREATE TABLE` statement to define the distribution policy for a table. Ideally, each segment possesses an equal volume of data and performs equal share of work when queries run. There are 2 kinds of distribution policy syntax in Apache Cloudberry:
+
+- `DISTRIBUTED BY (column, ...)` defines a distribution key from one or more columns. A hash function applied to the distribution key determines which segment stores the corresponding row. Rows that have same distribution key are stored on the same segment. If the distribution keys are unique, the hash function will ensure that data is distributed evenly. The default distribution policy is a hash on the primary key of the table or the first column of table if no primary key is specified.
+- `DISTRIBUTED RANDOMLY` distributes rows in round-robin fashion among segments.
+
+When different tables that have the same or similar columns as distribution key are about to be joined, join action might be accomplished on segments, which will be much faster than re-distributing rows across segments and then joining. The random distribution policy cannot make it happen, so it is definitely better to have a distribution key for a table.
diff --git a/versioned_docs/version-2.x/tutorial/create-users-and-roles.md b/versioned_docs/version-2.x/tutorial/create-users-and-roles.md
new file mode 100644
index 000000000..6ab18c368
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/create-users-and-roles.md
@@ -0,0 +1,219 @@
+---
+title: "Lesson 1: Create Users and Roles"
+description: Learn how to create users and roles in the Apache Cloudberry with this helpful introduction.
+---
+
+Apache Cloudberry manages database access using roles. Initially, there is one superuser role, the role associated with the OS user who initialized the database instance, usually `gpadmin`. This user owns all of the Apache Cloudberry files and OS processes, so it is important to reserve the `gpadmin` role for system tasks only.
+
+A role can be a user or a group. A user role can log into a database; that is, it has the `LOGIN` attribute. A user or group role can become a member of a group.
+
+Permissions can be granted to users or groups. Initially, only the `gpadmin` role is able to create roles. You can add roles using the `createuser` utility command, `CREATE ROLE` SQL command, or the `CREATE USER` SQL command. The `CREATE USER` command is the same as the `CREATE ROLE` command except that it automatically assigns the role the `LOGIN` attribute.
+
+## Quick-start operations
+
+You can follow the examples below to create users and roles.
+
+Before moving on to the operations, make sure that you have installed Apache Cloudberry by following [Install a Apache Cloudberry](https://github.com/apache/cloudberry-bootcamp/tree/main/000-cbdb-sandbox).
+
+### Create a user using the CREATE USER command
+
+1. Log into Apache Cloudberry in Docker. Connect to the database as the `gpadmin` user.
+
+ ```shell
+ [gpadmin@cdw ~]$ psql
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+ ```shell
+ gpadmin=#
+ ```
+
+2. Create a user named `lily` using the `CREATE USER` command, with a password `changeme`. After the creation, you need to enter the password to log in as the user `lily`.
+
+ ```sql
+ gpadmin=# CREATE USER lily WITH PASSWORD 'changeme';
+ ```
+
+ Output:
+
+ ```sql
+ NOTICE: resource queue required -- using default resource queue "pg_default"
+ CREATE ROLE
+ ```
+
+3. Verify that the user `lily` has been created.
+
+ ```sql
+ gpadmin=# \du
+ List of roles
+ Role name | Attributes | Member of
+ -----------+------------------------------------------------+-----------
+ gpadmin | Superuser, Create role, Create DB, Replication | {}
+ lily | | {}
+ ```
+
+### Create a user using the createuser utility command
+
+1. Create a user named `lucy` using the `createuser` utility command.
+
+ ```sql
+ gpadmin=# \q -- exit psql
+ ```
+
+ ```shell
+ [gpadmin@cdw ~]$ createuser --interactive lucy
+ ```
+
+ You will be asked to choose whether the new role should be a superuser. Enter `y` to create a superuser.
+
+ ```shell
+ Shall the new role be a superuser? (y/n)
+ ```
+
+2. Connect to the database as the `gpadmin` user.
+
+ ```shell
+ [gpadmin@cdw ~]$ psql
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+3. Verify that the user `lucy` has been created.
+
+ ```sql
+ gpadmin=# \du
+ List of roles
+ Role name | Attributes | Member of
+ -----------+------------------------------------------------+-----------
+ gpadmin | Superuser, Create role, Create DB, Replication | {}
+ lily | | {}
+ lucy | Superuser, Create role, Create DB | {}
+ ```
+
+ ```sql
+ gpadmin=# \q -- exit psql
+ ```
+
+### Create a users group and add the users to it
+
+1. Connect to the database as the `gpadmin` user.
+
+ ```shell
+ [gpadmin@cdw ~]$ psql
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+ Output:
+
+ ```shell
+ gpadmin=#
+ ```
+
+2. Create a group named `users` using the `CREATE ROLE` command.
+
+ ```sql
+ gpadmin=# CREATE ROLE users;
+ ```
+
+ Output:
+
+ ```sql
+ NOTICE: resource queue required -- using default resource queue "pg_default"
+ CREATE ROLE
+ ```
+
+3. Add the `lily` and `lucy` users to the `users` group.
+
+ ```sql
+ gpadmin=# GRANT users TO lily, lucy;
+ ```
+
+ Output:
+
+ ```sql
+ GRANT ROLE
+ ```
+
+4. Verify that the two users have been added to the `users` group.
+
+ ```sql
+ gpadmin=# \du
+ List of roles
+ Role name | Attributes | Member of
+ -----------+------------------------------------------------+-----------
+ gpadmin | Superuser, Create role, Create DB, Replication | {}
+ lily | | {users}
+ lucy | Superuser, Create role, Create DB | {users}
+ users | Cannot login | {}
+ ```
+
+However, after creating the `users` group, `lily` and `lucy` cannot log into Apache Cloudberry yet. See the following error messages.
+
+```shell
+[gpadmin@cdw ~]$ psql -U lily -d gpadmin
+
+psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "lily", database "gpadmin", no encryption
+```
+
+```shell
+[gpadmin@cdw ~]$ psql -U lucy -d gpadmin
+
+psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: no pg_hba.conf entry for host "[local]", user "lucy", database "gpadmin", no encryption
+```
+
+To make users (`lily` and `lucy`) able to log into the database, you need to adjust the `pg_hba.conf` configuration file on the coordinator node and use `gpstop` to populate the change.
+
+1. Append `local gpadmin lily md5` and `local gpadmin lucy trust` to the `pg_hba.conf` file on the coordinator node.
+
+ ```sql
+ gpadmin=# \q -- exit psql
+ ```
+
+ ```shell
+ [gpadmin@cdw ~]$ echo "local gpadmin lily md5" >> /data0/database/coordinator/gpseg-1/pg_hba.conf
+ [gpadmin@cdw ~]$ echo "local gpadmin lucy trust" >> /data0/database/coordinator/gpseg-1/pg_hba.conf
+ ```
+
+ > **Info:**
+ >
+ > - `pg_hba.conf` is a configuration file in Apache Cloudberry to control access permissions.
+ > - `md5` and `trust` are the authentication methods. `md5` means that the user needs to enter the password to log in. `trust` means that the user can log in without entering the password.
+
+2. Use `gpstop` to populate the change.
+
+ ```shell
+ [gpadmin@cdw ~]$ gpstop -u
+ ```
+
+ ```shell
+ 20230818:14:16:05:003653 gpstop:cdw:gpadmin-[INFO]:-Starting gpstop with args: -u
+ 20230818:14:16:05:003653 gpstop:cdw:gpadmin-[INFO]:-Gathering information and validating the environment...
+ 20230818:14:16:05:003653 gpstop:cdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
+ 20230818:14:16:05:003653 gpstop:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+ 20230818:14:16:05:003653 gpstop:cdw:gpadmin-[INFO]:-Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+ 20230818:14:16:05:003653 gpstop:cdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload
+ ```
+
+3. Verify that the two users can log into the database.
+
+ ```shell
+ [gpadmin@cdw ~]$ psql -U lily -d gpadmin
+ Password for user lily: # changeme
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+ ```shell
+ [gpadmin@cdw ~]$ psql -U lucy -d gpadmin
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+ ```
+
+ User `lily` and user `lucy` have had different privileges. You need to provide the password "changeme" for lily when login.
diff --git a/versioned_docs/version-2.x/tutorial/data-loading.md b/versioned_docs/version-2.x/tutorial/data-loading.md
new file mode 100644
index 000000000..7d77e3c87
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/data-loading.md
@@ -0,0 +1,382 @@
+---
+title: "Lesson 4: Data Loading"
+description: Load your data to the Apache Cloudberry.
+---
+
+This tutorial briefly introduces 3 methods to load the example data `FAA` into Apache Cloudberry tables you have created in the previous tutorial [Lesson 3: Create Tables](./create-tables). Before continuing, make sure you have completed the previous tutorial.
+
+- Method 1: Use the `INSERT` statement. This is the easiest way to load data. You can execute `INSERT` directly in psql, run scripts that have `INSERT` statements, or run a client-side application with database connection. It is not recommended to use `INSERT` to load a large amount of data, because the loading efficiency is low.
+
+- Method 2: Use the SQL statement `COPY` to load data into database. The `COPY` syntax allows you to define the format of the text file so that data can be parsed into rows and columns. This method is faster than the `INSERT` statement. But, like `INSERT` statement, `COPY` is not a parallel data loading process.
+
+ The `COPY` statement requires that external files be accessible to the host where the coordinator process is running. On a multi-node Apache Cloudberry system, data files might reside on a file system that is not accessible from coordinator node. In this case, you need to use the psql command `\copy meta-command` that streams data to Cloudberry coordinator node over `psql` connection. Some example scripts in this tutorial use the `\copy meta-command`.
+
+- Method 3: Use Apache Cloudberry utilities to load external data into tables. When you are working with a large-scale data warehouse, you might often face the challenge of loading large amounts of data in a short time. The utilities, `gpfdist` and `gpload`, are tailored for this purpose, enabling you to achieve rapid, parallel data transfers.
+
+ During your data loading process, if any rows run into issues, they will be noted. You can set an error threshold that fits your needs. If the number of problematic rows exceeds this limit, Apache Cloudberry will stop the loading process.
+
+ For optimal speed, combine the use of external tables with the parallel file server (`gpfdist`). This approach will help you maximize efficiency, making your data loading tasks smoother and more efficient.
+
+ 
+ _Figure 1. External Tables Using Parallel File Server (gpfdist)_
+
+ Another utility `gpload` is a batch job. When using this utility, you should specify a YAML-formatted control file, describe source data locations, format, transformations required, participating hosts, database destinations and other particulars in the file. `gpload` will parse the control file and use `gpfdsit` to execute the task. This allows you to describe a complex task and execute it in a controlled and repeatable way.
+
+## Quick-start operations
+
+In the following exercise, you will load sample data into the `tutorial` database using each of these above methods.
+
+### Load data using `INSERT`
+
+In [Lesson 3: Create Tables](./create-tables), you have created 6 tables in the `tutorial` database, one of which is `d_cancellation_codes` in the `faa` directory.
+
+The `faa.d_cancellation_codes` table is a simple 2-column look-up table. You will load data into the table using the `INSERT` statement.
+
+<!-- You change to directory faa, containing FAA data and scripts, take a look at table faa.d_cancellation_codes, insert data into table. -->
+
+1. Log into Apache Cloudberry in Docker as `gpadmin`, and change to the `faa` directory. This directory contains `faa` data and scripts.
+
+ ```shell
+ [gpadmin@cdw ~]$ cd /tmp/faa
+ ```
+
+2. Log into the `tutorial` database as `lily`.
+
+ ```shell
+ [gpadmin@cdw faa]$ psql -U lily -d tutorial
+ ```
+
+ ```shell
+ Password for user lily: # changeme
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+
+ tutorial=>
+ ```
+
+3. Check the `faa.d_cancellation_codes` table.
+
+ ```sql
+ tutorial=> \d d_cancellation_codes
+ ```
+
+ Output:
+
+ ```sql
+ Table "faa.d_cancellation_codes"
+ Column | Type | Collation | Nullable | Default
+ -------------+------+-----------+----------+---------
+ cancel_code | text | | |
+ cancel_desc | text | | |
+ Distributed by: (cancel_code)
+ ```
+
+4. Insert data into the `faa.d_cancellation_codes` table.
+
+ ```sql
+ tutorial=> INSERT INTO faa.d_cancellation_codes
+ tutorial-> VALUES ('A', 'Carrier'),
+ tutorial-> ('B', 'Weather'),
+ tutorial-> ('C', 'NAS'),
+ tutorial-> ('D', 'Security'),
+ tutorial-> ('', 'none');
+ ```
+
+ Output:
+
+ ```sql
+ INSERT 0 5
+ tutorial=>
+ ```
+
+### Load data using `COPY`
+
+The `COPY` statement moves data from the file system to database tables. Data for 5 of the `faa` tables is in the following CSV-formatted text files:
+
+1. In a text editor, open and check the following `.csv` data files.
+
+ - `L_AIRLINE_ID.csv`
+ - `L_AIRPORTS.csv`
+ - `L_DISTANCE_GROUP_250.csv`
+ - `L_ONTIME_DELAY_GROUPS.csv`
+ - `L_WORLD_AREA_CODES.csv`
+
+ Note that the first line of each file contains the column names, and that the last line of each file contains the characters `.`, which signals the end of the input data.
+
+2. In a text editor, open and check the following SQL scripts:
+
+ - `copy_into_airlines.sql`
+ - `copy_into_airports.sql`
+ - `copy_into_delay_groups.sql`
+ - `copy_into_distance_groups.sql`
+ - `copy_into_wac.sql`
+
+3. Log into the `tutorial` database as `lily`.
+
+ ```shell
+ [gpadmin@cdw faa]$ psql -U lily -d tutorial
+ ```
+
+ ```shell
+ Password for user lily: # changeme
+
+ psql (14.4, server 14.4)
+ Type "help" for help.
+
+ tutorial=>
+ ```
+
+4. Run the following psql `\i` commands to load data into the `faa` tables.
+
+ ```sql
+ tutorial=> \i copy_into_airlines.sql
+ tutorial=> \i copy_into_airports.sql
+ tutorial=> \i copy_into_delay_groups.sql
+ tutorial=> \i copy_into_distance_groups.sql
+ tutorial=> \i copy_into_wac.sql
+ ```
+
+ Output:
+
+ ```sql
+ COPY 1514
+ COPY 1697
+ COPY 15
+ COPY 11
+ COPY 342
+ tutorial=>
+ ```
+
+### Load data using the `gpdist` utility
+
+For the `faa` fact table, you will use an ETL (Extract, Transform, Load) process to load data from the source gzip files into a data table. For the best loading speed, use the `gpfdist` utility to distribute rows to segments.
+
+In production system, `gpfdist` runs on file servers that external data resides. However, for a single-node Apache Cloudberry instance, there is only one logical host, so you run `gpfdist` on it as well. Starting `gpfdist` is similar as a file server, no data movement will occur until SQL query request has been ended.
+
+> **Note:**
+>
+> This exercise loads data using `gpfdsit` to move data from external data files into Apache Cloudberry. Moving data between the database and external tables also needs security request. Therefore, only superusers are permitted to use `gpfdsit` and you will complete this exercise as `gpadmin` user.
+
+1. Start `gpfdist`:
+
+ ```shell
+ [gpadmin@cdw tmp]$ gpfdist -d /tmp/faa -p 8081 > /tmp/gpfdist.log 2>&1 &
+ ```
+
+ In this operation:
+
+ - `-d /tmp/faa`: Specifies the directory where the data files reside. The utility will serve the files from `/tmp/faa`.
+ - `-p 8081`: Sets the port on which `gpfdist` will listen, in this case, port `8081`.
+ - `> /tmp/gpfdist.log`: Redirects standard output to a log file at `/tmp/gpfdist.log`.
+ - `2>&1`: Ensures that both standard output and standard error are redirected to the same log file.
+ - `&`: Runs the process in the background.
+
+ Once executed, you see `6581`, which indicates the background job number `[1]` and its process ID `6581`.
+
+2. Check the running processes:
+
+ ```shell
+ [gpadmin@cdw tmp]$ ps -ef |grep gpfdist
+ ```
+
+ This command checks whether `gpfdist` is running:
+
+ ```shell
+ gpadmin 6581 6552 0 16:02 pts/8 00:00:00 gpfdist -d /tmp/faa -p 8081
+ gpadmin 6585 6552 0 16:02 pts/8 00:00:00 grep --color=auto gpfdist
+ ```
+
+3. View the log file:
+
+ ```shell
+ [gpadmin@cdw tmp]$ more /tmp/gpfdist.log
+ ```
+
+ This command allows you to view the contents of the `gpfdist.log` file. The log messages indicate the initialization steps of the `gpfdist` utility:
+
+ ```shell
+ 2023-07-25 16:02:41 6581 INFO Before opening listening sockets - following listening sockets are available:
+ 2023-07-25 16:02:41 6581 INFO IPV6 socket: [::]:8081
+ 2023-07-25 16:02:41 6581 INFO IPV4 socket: 0.0.0.0:8081
+ 2023-07-25 16:02:41 6581 INFO Trying to open listening socket:
+ 2023-07-25 16:02:41 6581 INFO IPV6 socket: [::]:8081
+ 2023-07-25 16:02:41 6581 INFO Opening listening socket succeeded
+ 2023-07-25 16:02:41 6581 INFO Trying to open listening socket:
+ 2023-07-25 16:02:41 6581 INFO IPV4 socket: 0.0.0.0:8081
+ 2023-07-25 16:02:41 6581 INFO Opening listening socket succeeded
+ Serving HTTP on port 8081, directory /tmp/faa
+ ```
+
+The following operations are performed in this section:
+
+1. Set up the necessary tables.
+
+ First, we'll create two tables:
+
+ - `faa_otp_load`: The table where data will be loaded.
+ - `faa_load_errors`: A table to log any load errors.
+
+ These operations are purely metadata-based; no actual data will be transferred at this point.
+
+ ```shell
+ [gpadmin@cdw tmp]$ cd faa
+ [gpadmin@cdw faa]$ psql -U gpadmin tutorial
+
+ tutorial=# \i create_load_tables.sql
+ ```
+
+ Note: If you receive an error that the `faa_load_errors` table already exists, you can safely ignore it.
+
+2. Load data from external files.
+
+ The `faa_otp_load` table is structured to match the `faa` Web site's input data format. The external table definition will refer to files in the `faa` directory with a pattern `otp*.gz`. For our example, we have two matching files, one for December 2009 and the other for January 2010.
+
+ Now, let's move the data:
+
+ ```shell
+ tutorial=# \i create_ext_table.sql
+ tutorial=# INSERT INTO faa.faa_otp_load SELECT * FROM faa.ext_load_otp;
+ ```
+
+ Note: Apache Cloudberry facilitates moving data from the gzip files into the database's load table. In a production setting, there might be several `gpfdist` processes running, either on separate hosts or multiple on one host, each using a different port.
+
+3. Examine load errors.
+
+ Errors during data loading are common. Let's examine these errors for a better understanding. For clarity, we'll display results in a format where each column data is shown on a new line:
+
+ ```sql
+ tutorial=# \x -- Changes the display of the results to one line per column, which is easier to read for some result sets.
+ tutorial=# select DISTINCT relname, errmsg, count(*) from gp_read_error_log('faa.ext_load_otp') GROUP BY 1,2;
+ ```
+
+ Once you have reviewed the errors, you can end your session:
+
+ ```sql
+ tutorial=# \q
+ [gpadmin@cdw faa]$
+ ```
+
+ Summary: By now, you should have set up your tables, loaded the data, and had a quick look at any loading errors. This ensures that you have a good understanding of the data quality and structure.
+
+
+### Load data using the `gpload` utility
+
+Apache Cloudberry provides a wrapper program for `gpfdist` called `gpload` that does much of the work to set up external table and data movement. In this exercise, you will reload the `faa_otp_load` table using the gpload utility.
+
+In this section, we walk through the process of loading data with `gpload`. The steps are:
+
+1. Ensure the environment is clean.
+
+ Before using `gpload`, ensure no `gpfdist` processes from previous tasks are running. Here is how you can check and kill them:
+
+ ```shell
+ [gpadmin@cdw faa]$ ps -ef | grep gpfdist
+ [gpadmin@cdw faa]$ pkill gpfdist
+ [gpadmin@cdw faa]$ ps -ef | grep gpfdist
+ ```
+
+2. Customize the `gpload` input file.
+
+ You will need to edit and tailor the `gpload.yaml` input file to your needs. Notably, make sure to set the correct path for the `faa` directory. In this guide, the `gpload.yaml` file has the `TRUNCATE: true` preload instruction. This ensures that any previously loaded data is cleared out before the current loading begins.
+
+ The following is what the `gpload.yaml` file might look like:
+
+ ```shell
+ [gpadmin@cdw faa]$ cat ./gpload.yaml
+
+ ---
+ VERSION: 1.0.0.1
+ # describe the Greenplum database parameters
+ DATABASE: tutorial
+ USER: gpadmin
+ HOST: cdw
+ PORT: 5432
+ # describe the location of the source files
+ # in this example, the database coordinator lives on the same host as the source files
+ GPLOAD:
+ INPUT:
+ - SOURCE:
+ LOCAL_HOSTNAME:
+ - cdw
+ PORT: 8081
+ FILE:
+ - /tmp/faa/otp*.gz
+ - FORMAT: csv
+ - QUOTE: '"'
+ - ERROR_LIMIT: 50000
+ - ERROR_TABLE: faa.faa_load_errors
+ OUTPUT:
+ - TABLE: faa.faa_otp_load
+ - MODE: INSERT
+ PRELOAD:
+ - TRUNCATE: true
+ ```
+
+3. Run the `gpload` command.
+
+ Finally, you can run the gpload command to start the data loading process. If you want a detailed view of the loading, include the -v flag.
+
+ ```shell
+ [gpadmin@cdw faa]$ gpload -f gpload.yaml -l gpload.log
+ ```
+
+ Summary: At the end of this guide, you would have successfully used gpload to load data into Apache Cloudberry. Make sure to check the logs for any warnings or errors to ensure data consistency and integrity.
+
+
+### Create and load fact tables
+
+The final step of the ELT process is to move data from the load table to the fact table. For the `FAA` example, you create 2 fact tables. The `faa.otp_r` table is a row-oriented table, which will be loaded with data from the `faa.faa_otp_load` table. The `faa.otp_c` table has the same structure as the `faa.otp_r` table, but is column-oriented and partitioned. You will load it with data from the `faa.otp_r` table. The 2 tables will contain identical data and allow you to experiment with a [...]
+
+```shell
+[gpadmin@cdw faa]$ psql -U gpadmin tutorial
+
+psql (14.4, server 14.4)
+Type "help" for help.
+```
+
+```sql
+tutorial=# \i create_fact_tables.sql
+CREATE TABLE
+CREATE TABLE
+
+tutorial=#
+tutorial=# \i load_into_fact_table.sql
+INSERT 0 1024552
+INSERT 0 1024552
+tutorial=#
+```
+
+## Data loading summary
+
+- Key Feature: rapid data loading
+
+ - Extract, load, and t ransform (ELT): This method takes advantage of the massive parallelism of Apache Cloudberry.
+ - Staging: Data can be staged using methods like external tables.
+ - Transformation: Data transformations occur within the Apache Cloudberry.
+ - Performance: Set-based operations are done in parallel to maximize efficiency.
+
+- Loading mechanisms
+
+ - `COPY`: Loads data via the coordinator in a single process, but doesn't harness Apache Cloudberry's parallel capabilities.
+ - External tables:
+ - Advantage: Takes advantage of the parallel processing power of segments.
+ - Flexibility: One `SELECT` statement can access multiple data sources.
+ - Data availability: Makes static data accessible within the database.
+ - Protocols: Defined using `file:// or gpfdist://`.
+ - `gpfdist`: A parallel-loading file server program.
+ - Static data: External tables can be re-scanned during a query as data remains unchanged.
+
+- External web tables
+
+ - Features:
+ - Protocols: Allows the `http://` protocol or an `EXECUTE` clause for OS command/script execution.
+ - Dynamic data: Assumes that data might change during query execution. Thus, rescanning is not permitted.
+ - Performance: Might be slower if data exceeds memory capacity, leading to I/O operations.
+ - Execution: Scripts or processes might run on every segment host.
+
+ - Duplication caution:
+ - There is a risk of data duplication, especially when extracting data from another database.
+ - Users need to be cautious and verify data when using Web tables.
+
+Understanding and using these features and mechanisms effectively can ensure optimal data loading and management within the Apache Cloudberry.
diff --git a/versioned_docs/version-2.x/tutorial/index.md b/versioned_docs/version-2.x/tutorial/index.md
new file mode 100644
index 000000000..d66a3a132
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/index.md
@@ -0,0 +1,11 @@
+---
+title: Cloudberry Tutorials
+---
+
+This part contains a series of tutorials for quickly trying out Cloudberry based on the Cloudberry Sandbox. Before starting to read the tutorials, you are expected to finish installing the single-node Apache Cloudberry by following the Cloudberry Sandbox.
+
+```mdx-code-block
+import DocCardList from '@theme/DocCardList';
+
+<DocCardList />
+```
diff --git a/versioned_docs/version-2.x/tutorial/introduction-to-cloudberry-in-database-analytics.md b/versioned_docs/version-2.x/tutorial/introduction-to-cloudberry-in-database-analytics.md
new file mode 100644
index 000000000..8ae4c799b
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/introduction-to-cloudberry-in-database-analytics.md
@@ -0,0 +1,213 @@
+---
+title: "[104-1] Introduction to Apache Cloudberry In-Database Analytics"
+description: Run analytics directly in the Apache Cloudberry by MADlib.
+---
+
+Running analytics directly in Apache Cloudberry, rather than exporting data to a separate analytics engine, allows greater agility when exploring large data sets and much better performance due to parallelizing the analytic processes across all the segments.
+
+A variety of power analytic tools is available for use with Apache Cloudberry:
+
+* MADlib, an open-source, MPP implementation of many analytic algorithms, available at [http://madlib.apache.org/](http://madlib.apache.org/)
+* R statistical language
+* SAS, in many forms, but especially with the SAS Accelerator for Cloudberry
+* PMML, Predictive Modeling Markup Language
+
+The exercises in this chapter introduce using MADlib with Apache Cloudberry, using the FAA on-time data example dataset. You will examine scenarios comparing airlines and airports to learn whether there are significant relationships to be found.
+
+In this lesson, you will use [Apache Zeppelin](https://zeppelin.apache.org/) to submit SQL statements to the Apache Cloudberry. Apache Zeppelin is a web-based notebook that enables interactive data analytics. A [PostgreSQL interpreter](https://issues.apache.org/jira/browse/ZEPPELIN-250) has been added to Zeppelin, so that it can now work directly with products such as Pivotal Apache Cloudberry and Pivotal HDB.
+
+## Prepare Apache Zeppelin
+
+1. Open a browser on your desktop and browse to `http://X.X.X.X:8080` using the same IP address that you used for the ssh step. You will see the Apache Zepplin Welcome page.
+
+ 
+
+2. Click Interpreter at the top of the Screen and scroll down to the psql section and press edit.
+
+3. Edit the _postgresql.url_ entry by adding tutorial to the end, so that it will connect to the <strong>tutorial</strong> database.
+4. Click Save and then Hit OK to restart the Interpreter
+5. Click on Create new note underneath the Notebook heading and type: `tutorial`
+
+ 
+
+6. Click "tutorial" to open the newly created notebook.
+
+7. You should now see the the open notebook with a "paragraph" ready for input. Click in the the empty white rectangle (called paragraph) and type:
+
+ ```sql
+ %psql.sql select count(*) from faa.otp.c;
+ ```
+
+ Then press the play button:
+
+ 
+
+ The result should look like the graphic below:
+
+ 
+
+## Run PostgreSQL built-in aggregates
+
+PostgreSQL has built-in aggregate functions to get standard statistics on database columns—minimum, maximum, average, and standard deviation, for example. The functions take advantage of the Apache Cloudberry MPP architecture, aggregating data on the segments and then assembling results on the coordinator.
+
+First, gather simple descriptive statistics on some of the data you will analyze with MADlib. The commands in this exercise are in the stats.sql script in the sample data directory.
+
+1. Get average delay, standard deviation, and number of flights for USAir and Delta airlines. Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT carrier, AVG(arrdelayminutes),STDDEV(arrdelayminutes),
+ COUNT(*) FROM faa.otp_c WHERE carrier = 'US' OR carrier = 'DL'
+ GROUP BY carrier;
+ ```
+
+ Then press the Play button to execute the query.
+
+2. Get average delay, standard deviation, and number of flights originating from Chicago O’Hare or Atlanta Hartsfield airports. Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT origin, AVG(arrdelayminutes),STDDEV(arrdelayminutes),
+ COUNT(*) FROM faa.otp_c WHERE origin = 'ORD' OR origin = 'ATL'
+ GROUP BY origin;
+ ```
+
+ Then press the Play button to execute the query.
+
+3. Get average delay, standard deviation, and number of flights originating from Chicago O’Hare or Atlanta Hartsfield airports. Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT origin, AVG(arrdelayminutes),STDDEV(arrdelayminutes),
+ COUNT(*) FROM faa.otp_c WHERE carrier = 'DL' AND origin IN ('ATL', 'MSP',
+ 'DTW') GROUP BY origin;
+ ```
+
+ Then press the Play button to execute the query.
+
+4. Get average delay, standard deviation, and number of flights for Delta and USAir flights originating from Atlanta Harsfield. Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT carrier, AVG(arrdelayminutes),STDDEV(arrdelayminutes),
+ COUNT(*) FROM faa.otp_c WHERE carrier IN ('DL', 'UA') AND origin = 'ATL'
+ GROUP BY carrier;
+ ```
+
+ Then press the Play button to execute the query.
+
+## Run Apache MADlib ANOVA
+
+ANOVA (Analysis of Variance) shows whether groups of samples are significantly different from each other. The MADlib ANOVA function uses an integer value to distinguish between the groups to compare and a column for the data. The groups we want to analyze in the FAA fact table are text in the data, so we use a PostgreSQL CASE statement to assign the samples to integer values based on the text values. The ANOVA module then divides the rows into groups and performs the test.
+
+ANOVA is a general linear model. To determine whether statistical data samples are significantly different from one another, you compare the total variability of the group by the variability between the groups. This is tempered by the number of observations, which is summarized by the degrees of freedom within the groups. The relevant statistic that measures the degree to which the difference between groups is significant is the ratio of the variance between groups divided by the varianc [...]
+
+From statistical theory you can determine the probability distribution of the F statistic if the groups were identical given sampling error. This is given by the p-value. A p-value close to zero indicates it is very likely that the groups are different. A p-value close to one indicates that it is very likely the groups are the same.
+
+1. Run an ANOVA analysis on the average delay minutes between USAir and Delta airlines. The CASE clause assigns USAir flights to group 1 and Delta flights to group 2.
+
+ Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT (MADlib.one_way_anova (
+ CASE WHEN carrier = 'US' THEN 1
+ WHEN carrier = 'DL' THEN 2
+ ELSE NULL
+ END,
+ arrdelayminutes
+ )).* FROM faa.otp_r;
+ ```
+
+ Then press the Play button to execute the query.
+
+2. Run an ANOVA analysis to determine if the average delays for flights from Chicago and Atlanta are statistically different.
+
+ Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT (MADlib.one_way_anova (
+ CASE WHEN origin = 'ORD' THEN 1
+ WHEN origin = 'ATL' THEN 2
+ ELSE NULL
+ END,
+ arrdelayminutes
+ )).* FROM faa.otp_r;
+ ```
+
+ Then press the Play button to execute the query.
+
+3. Run an ANOVA analysis to determine if the differences in average delay minutes from three Delta hubs are significant.
+
+ Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT (MADlib.one_way_anova (
+ CASE WHEN carrier = 'DL' AND origin = 'ATL' THEN 1
+ WHEN carrier = 'DL' AND origin = 'MSP' THEN 2
+ WHEN carrier = 'DL' AND origin = 'DTW' THEN 3
+ ELSE NULL
+ END,
+ arrdelayminutes
+ )).* FROM faa.otp_r;
+ ```
+
+ Then press the Play button to execute the query.
+
+4. Run an ANOVA analysis to determine if the differences in average delay minutes between Delta and USAir flights from Atlanta are significant.
+
+ Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT (MADlib.one_way_anova (
+ CASE WHEN carrier = 'DL' AND origin = 'ATL' THEN 1
+ WHEN carrier = 'UA' AND origin = 'ATL' THEN 2
+ ELSE NULL
+ END,
+ arrdelayminutes
+ )).* FROM faa.otp_r;
+ ```
+
+ Then press the Play button to execute the query.
+
+ From these ANOVA analyses we have learned the following:
+
+ * There is a fairly certain difference between delays for USAir and Delta, but the difference is not great.
+ * Delays from O’Hare seem to be significantly different than from Atlanta.
+ * There is a large difference between delays at the three Delta hubs.
+ * There is no significant difference in delays from Atlanta between United and Delta.
+
+## Perform Linear Regression
+
+Linear regression shows the relationship between variables. A classic example is the linear relationship between height and weight of adult males in a particular country or ethnic group. MADlib includes modules to perform linear regression with one or multiple independent variables.
+
+The r2 statistic measures the proportion of the total variability in the dependent variable that can be explained by the independent variable.
+
+1. Perform a linear regression to see if there is any relationship between distance and arrival delay. This tests the hypothesis that longer flights are more likely to be on time because the flight crew can make up delays by flying faster over longer periods of time. Test this by running a regression on arrival time as the dependent variable and distance as the independent variable.
+
+ Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT ( madlib.linregr(arrdelayminutes,
+ ARRAY[1,distance])).* FROM faa.otp_c;
+ ```
+
+ Then press the Play button to execute the query.
+
+ The regression shows that r2 is close to zero, which means that distance is not a good predictor for arrival delay time.
+
+2. Run a regression with departure delay time as the independent variable and arrival delay time as the dependent variable. This tests the hypothesis that if a flight departs late, it is unlikely that the crew can make up the time.
+
+ Click a new white rectangle and enter:
+
+ ```sql
+ %psql.sql SELECT ( madlib.linregr(arrdelayminutes,
+ ARRAY[1,depdelayminutes])).* FROM faa.otp_c;
+ ```
+
+ Then press the Play button to execute the query.
+
+ The r2 statistic is very high, especially with 1.5 million samples. The linear relationship can be written as
+
+ `Arrival_delay = 1.2502729312843388 + 0.96360804792526189 * departure_delay`
+
+ If you scroll over in the results, the condition_no result is a measure of the mathematical stability of the solution. In computer arithmetic, numbers do not have infinite precision, and round-off error in calculations can be significant, especially if there are a large number of independent variables and they are highly correlated. This is very common in econometric data and techniques have evolved to deal with it.
+
+## Learn more about Apache MADlib
+
+MADlib is an Apache open source project on GitHub. You can find source code for the latest release and information about participating in the project in the GitHub repository. Access the MADlib user documentation on the MADlib Web site at [http://madlib.apache.org](http://madlib.apache.org/).
diff --git a/versioned_docs/version-2.x/tutorial/introduction-to-database-and-cloudberry-architecture.md b/versioned_docs/version-2.x/tutorial/introduction-to-database-and-cloudberry-architecture.md
new file mode 100644
index 000000000..838d0b367
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/introduction-to-database-and-cloudberry-architecture.md
@@ -0,0 +1,64 @@
+---
+title: "Lesson 0: Introduction to Database and Apache Cloudberry Architecture"
+description: This page provides an introduction to the basic concepts of databases and explains the architecture of Apache Cloudberry.
+---
+
+## Background: Database Concepts
+
+Before starting this tutorials, spend some time to get familiar with how (single instance) databases work. If you already have some knowledge and experience with Oracle, MySQL or especially Postgres - this is great.
+
+Databases (relational databases) are pieces of software that are used to store and manage/process data. Usually these databases are built with the client/server concept - the database is implemented as a server and multiple clients can connect and read or update the data.
+
+The clients usually use SQL language to access the data (or some dialect of the SQL language specification). The clients can be different implementations - proprietary client libraries or ODBC/JDBC compliant.
+
+Database data is usually stored in objects called tables. Tables have predefined structure (columns) and have zero or multiple rows.
+
+Tables can be grouped in logical entities called 'schemas' (or namespaces).
+
+Tables/schemas are located in a 'database' entity. Some database software supports multiple databases per instance (MySQL, Postgres), others support one database per instance (Oracle).
+
+Along with tables there are supporting objects such as indexes, sequences, views, etc.
+
+The database system needs to maintain some metadata - called the database catalog. The database catalog contains information about the data objects and supporting objects as well as anything else that needs to be stored on system level (user authentication, etc.).
+
+SQL (Structured Query Language) is a descriptive language, not imperative language. Therefore it describes what the user needs, not how to get it. When the user describes what he needs, the database need to decide how to get it. This process is called query optimization. The end result from this process is a query plan, which is a step by step instruction how to get the result.
+
+## Introduction to the Apache Cloudberry Architecture
+
+Apache Cloudberry is a massively parallel processing (MPP) database server with an architecture specially designed to manage large-scale analytic data warehouses and business intelligence workloads.
+
+MPP (also known as a shared-nothing architecture) refers to systems with two or more processors that cooperate to carry out an operation, each processor with its own memory, operating system, and disks. Apache Cloudberry uses this high-performance system architecture to distribute the load of multi-terabyte data warehouses and all of a system's resources in parallel to process a query.
+
+Apache Cloudberry is based on the open-source PostgreSQL. It is essentially several PostgreSQL database instances working together as one cohesive database management system (DBMS). It is based on PostgreSQL 14.4 kernel and in most cases it is very similar to PostgreSQL. Database users interact with Apache Cloudberry as a regular PostgreSQL DBMS.
+
+In Apache Cloudberry, internals of PostgreSQL have been modified and optimized to support parallel structure of Apache Cloudberry. For instance, system catalog, optimizer, query executor and transaction manager components have been modified and enhanced to be able to execute queries simultaneously across the parallel PostgreSQL database instances. Apache Cloudberry interconnect (the networking layer) enables communication between distinct PostgreSQL instances and allows the system to beh [...]
+
+Apache Cloudberry also includes features designed to optimize PostgreSQL for business intelligence (BI) workloads. For example, Apache Cloudberry has added parallel data loading (external tables), resource management, query optimizations and storage enhancements,.
+
+_Figure 1. High-Level Apache Cloudberry Architecture_
+
+
+
+The following topics describe the components that make up a Apache Cloudberry system and how they work together.
+
+### Apache Cloudberry Coordinator
+
+The Apache Cloudberry coordinator is the entry to the Apache Cloudberry system. It accepts client connections, handles SQL queries, and then distributes workload to the segment instances.
+
+Apache Cloudberry end-users only interact with Apache Cloudberry through coordinator node as a typical PostgreSQL database. They connect to database using client such as `psql` or drivers like `JDBC` or `ODBC`.
+
+The coordinator stores global system catalog. Global system catalog is set of system tables that contain metadata for Apache Cloudberry itself. Coordinator node does not contain any user table data; user table data resides only on segments. Coordinator node would authenticate client connections, processe incoming SQL commands, distribute workloads among segments, collect the results returned by each segment and return the final results to the client.
+
+### Apache Cloudberry Segments
+
+Apache Cloudberry segment instances are independent PostgreSQL databases that each of them stores a portion of the data and performs the majority of query execution work.
+
+When a user connects to the database via the Cloudberry coordinator and issues queries, accordingly execution plan would be distributed to each segment instance.
+
+The server that has segments running on it is called segment host. A segment host usually has two to eight Cloudberry segments running on it, the number depending on serveral factors: CPU cores, memory, disk, network interfaces or workloads. To get better performance in Apache Cloudberry, it is suggested to distribute data and workloads evenly across segments so that execution plan can be finished across all segments and with no bottleneck.
+
+### Apache Cloudberry Interconnect
+
+The interconnect is the networking layer of the Apache Cloudberry architecture.
+
+The interconnect refers to the inter-process communication mechanism in-between segments. By default, interconnect uses User Datagram Protocol (UDP) to send/receive messages over the network. Interconnect provide datagram verification and retransmission mechanism. Reliability is equivalent to Transmission Control Protocol (TCP), performance and scalability exceeds TCP. If a user chooses TCP in interconnect, Cloudberry would have limit around 1000 segment instances. With UDP and interconn [...]
diff --git a/versioned_docs/version-2.x/tutorial/queries-and-performance-tuning.md b/versioned_docs/version-2.x/tutorial/queries-and-performance-tuning.md
new file mode 100644
index 000000000..5137b2ffe
--- /dev/null
+++ b/versioned_docs/version-2.x/tutorial/queries-and-performance-tuning.md
@@ -0,0 +1,666 @@
+---
+title: "Lesson 5: Queries and Performance Tuning"
+description: Understand the queries in the Apache Cloudberry.
+---
+
+This lesson provides an overview of how Apache Cloudberry processes queries. Understanding this process can be useful when you write and tune queries.
+
+## Concepts
+
+Users submit queries to Apache Cloudberry as they would to any database management system. They connect to the database instance on the Apache Cloudberry coordinator host using a client application such as psql and submit SQL statements.
+
+### Understand query planning and dispatch
+
+The coordinator host receives, parses, and optimizes the query. The resulting query plan is either parallel or targeted. The coordinator dispatches parallel query plans to all segments, as shown in Figure 1. Each segment is responsible for executing local database operations on its own set of data query plans.
+
+Most database operations such as table scan, join, aggregation and sort will be executed across all segments in parallel. Each operation is performed on one segment database independent of the data stored on other segment databases.
+
+_Figure 1. Dispatch the parallel query plan_
+
+
+
+### Understand query plans
+
+A query plan is a set of operations Apache Cloudberry will perform to produce the answer to a query. Each node or step in the plan represents a database operation such as a table scan, join, aggregation or sort. Plans are read and executed from bottom to top.
+
+In addition to common database operations such as tables scan and join, Apache Cloudberry has an additional operation type called "motion". A motion operation involves moving tuples between segments during query processing.
+
+To achieve maximum parallelism during query execution, Apache Cloudberry divides the work of a query plan into slices. A slice is a portion of the plan that segments can work on independently. A query plan is sliced wherever a motion operation occurs in the plan with one slice on each side of the motion.
+
+### Understand parallel query execution
+
+Apache Cloudberry creates a number of database processes to handle the work of a query. On the coordinator, the query worker process is called "query dispatcher" or "QD". QD is responsible for creating and dispatching query plan. It also accumulates and presents the final results. On segments, a query worker process is called "query executor" or "QE". QE is responsible for completing its portion of work and communicating its intermediate results to other worker processes.
+
+There is at least one worker process assigned to each slice of the query plan. A worker process works on its assigned portion of the query plan independently. During query execution, each segment will have a number of processes working on the query in parallel.
+
+Related processes that are working on the same slice of the query plan but on different segments are called "gangs". As a portion of work is completed, tuples flow up the query plan from one gang of processes to the next. This inter-process communication between segments is referred to as the interconnect component of Apache Cloudberry.
+
+The following section introduces some of the basic principles of query and performance tuning in a Apache Cloudberry.
+
+Some items to consider in performance tuning:
+
+- VACUUM and ANALYZE
+- Explain plans
+- Indexing
+- Column or row orientation
+- Set based vs. row based
+- Distribution and partitioning
+
+## Exercises
+
+After doing the following exercises, you are expected to finish the previous tutorial [Lesson 4: Data Loading](./101-4-data-loading).
+
+### Analyze the tables
+
+Apache Cloudberry uses Multi-version Concurrency Control (MVCC) to guarantee data isolation, one of the ACID properties of relational databases. MVCC allows multiple users of the database to obtain consistent results for a query, even if the data is changing as the query is being executed. There can be multiple versions of rows in the database, but a query sees a snapshot of the database at a single point in time, containing only the versions of rows that are valid at that point in time. [...]
+
+In a Apache Cloudberry, regular OLTP operations do not create the need for vacuuming out old rows, but loading data while tables are in use might create such a need. It is a best practice to `VACUUM` a table after a load. If the table is partitioned, and only a single partition is being altered, then a `VACUUM` on that partition might suffice.
+
+The `VACUUM FULL` command behaves much differently than `VACUUM`, and its use is not recommended in Apache Cloudberry. It can be expensive in CPU and I/O consumption, cause bloat in indexes, and lock data for long periods of time.
+
+The ANALYZE command generates statistics about the distribution of data in a table. In particular, it stores histograms about the values in each of the columns. The query optimizer depends on these statistics to select the best plan for executing a query. For example, the optimizer can use distribution data to decide on join orders. One of the optimizer's goals in a join is to minimize the volume of data that must be analyzed and potentially moved between segments by using the statistics [...]
+
+1. Connect to the database as `gpadmin` and run the `ANALYZE` command on each of the tables:
+
+ ```shell
+ $ psql -U gpadmin tutorial
+ ```
+
+ ```sql
+ tutorial=# ANALYZE faa.d_airports;
+ ANALYZE
+ tutorial=# ANALYZE faa.d_airlines;
+ ANALYZE
+ tutorial=# ANALYZE faa.d_wac;
+ ANALYZE
+ tutorial=# ANALYZE faa.d_cancellation_codes;
+ ANALYZE
+ tutorial=# ANALYZE faa.faa_otp_load;
+ ANALYZE
+ tutorial=# ANALYZE faa.otp_r;
+ ANALYZE
+ tutorial=# ANALYZE faa.otp_c;
+ ANALYZE
+ ```
+
+### View `EXPLAIN` plans
+
+An `EXPLAIN` plan explains the method the optimizer has chosen to produce a result set. Depending on the query, there can be a variety of methods to produce a result set. The optimizer calculates the cost for each method and chooses the one with the lowest cost. In large queries, cost is generally measured by the amount of I/O to be performed.
+
+An `EXPLAIN` plan does not do any actual query processing work. Explain plans use statistics generated by the `ANALYZE` command, so plans generated before and after running `ANALYZE` can be quite different. This is especially true for queries with multiple joins, because the order of the joins can have a great impact on performance.
+
+In the following exercise, you will generate some small tables that you can query and view some explain plans.
+
+1. Enable timing so that you can see the effects of different performance tuning measures.
+
+ ```sql
+ tutorial=# \timing on
+ ```
+
+2. View the `create_sample_table.sql` script, and then run it.
+
+ ```sql
+ tutorial=# \i create_sample_table.sql
+ ```
+
+ ```sql
+ psql:create_sample_table.sql:1: NOTICE: table "sample" does not exist, skipping
+ DROP TABLE
+ Time: 8.996 ms
+ SET
+ Time: 0.509 ms
+ CREATE TABLE
+ Time: 20.419 ms
+ INSERT 0 1000000
+ Time: 28598.022 ms (00:28.598)
+ UPDATE 1000000
+ Time: 5176.394 ms (00:05.176)
+ UPDATE 50000
+ Time: 408.038 ms
+ UPDATE 1000000
+ Time: 3148.945 ms (00:03.149)
+ ```
+
+3. Request the explain plan for the `COUNT()` aggregate.
+
+ ```sql
+ tutorial=# EXPLAIN SELECT COUNT(*) FROM sample WHERE id > 100;
+ ```
+
+ ```sql
+ QUERY PLAN
+ ------------------------------------------------------------------------------------
+ Aggregate (cost=0.00..431.00 rows=1 width=8)
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.00 rows=1 width=1)
+ -> Seq Scan on sample (cost=0.00..431.00 rows=1 width=1)
+ Filter: (id > 100)
+ Optimizer: Pivotal Optimizer (GPORCA)
+ (5 rows)
+
+ Time: 5.635 ms
+ ```
+
+ You are expected to read query plans from bottom to top. In this example, there are 4 steps. First, there is a sequential scan on each segment server to access the rows. Then there is an aggregation on each segment server to produce a count of the number of rows from that segment. Then there is a gathering of the count value to a single location. Finally, the counts from each segment are aggregated to produce the final result.
+
+ The cost number on each step has a start and stop value. For the sequential scan, this begins at time zero and goes until 431.00. This is a fictional number created by the optimizer. It is not a number of seconds or I/O operations.
+
+ The cost numbers are cumulative, so the cost for the second operation includes the cost for the first operation. Notice that nearly all the time to process this query is in the sequential scan.
+
+4. The `EXPLAIN ANALYZE` command actually runs the query (without returning the result set). The cost numbers reflect the actual timings. It also produces some memory and I/O statistics.
+
+ ```sql
+ tutorial=# EXPLAIN ANALYZE SELECT COUNT(*) FROM sample WHERE id > 100;
+ ```
+
+ ```sql
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate (cost=0.00..463.54 rows=1 width=8) (actual time=329.600..329.602 rows=1 loops=1)
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..463.54 rows=1 width=8) (actual time=325.897..329.586 rows=2 loops=1)
+ -> Partial Aggregate (cost=0.00..463.54 rows=1 width=8) (actual time=324.713..324.716 rows=1 loops=1)
+ -> Seq Scan on sample (cost=0.00..463.54 rows=499954 width=1) (actual time=30.992..296.384 rows=500184 loops=1)
+ Filter: (id > 100)
+ Rows Removed by Filter: 53
+ Planning Time: 5.192 ms
+ (slice0) Executor memory: 37K bytes.
+ (slice1) Executor memory: 122K bytes avg x 2 workers, 122K bytes max (seg0).
+ Memory used: 128000kB
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Execution Time: 338.004 ms
+ (12 rows)
+
+ Time: 343.866 ms
+ ```
+
+### Change optimizers
+
+By default, the sandbox instance disables the Pivotal Query Optimizer and you might see "legacy query optimizer" listed in the `EXPLAIN` output under "Optimizer status."
+
+1. Check whether the Pivotal Query Optimizer is enabled.
+
+ ```shell
+ $ gpconfig -s optimizer
+ ```
+
+ ```shell
+ Values on all segments are consistent
+ GUC : optimizer
+ Coordinator value: on
+ Segment value: on
+ ```
+
+2. Disable the Pivotal Query Optimizer.
+
+ ```shell
+ $ gpconfig -c optimizer -v off --coordinatoronly
+ ```
+
+ ```shell
+ 20230726:14:42:31:031343 gpconfig:cdw:gpadmin-[INFO]:-completed successfully with parameters '-c optimizer -v on --coordinatoronly'
+ ```
+
+3. Reload the configuration on coordinator and segment instances.
+
+ ```shell
+ $ gpstop -u
+ ```
+
+ ```shell
+ 20230726:14:42:49:031465 gpstop:cdw:gpadmin-[INFO]:-Starting gpstop with args: -u
+ 20230726:14:42:49:031465 gpstop:cdw:gpadmin-[INFO]:-Gathering information and validating the environment...
+ 20230726:14:42:49:031465 gpstop:cdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
+ 20230726:14:42:49:031465 gpstop:cdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
+ 20230726:14:42:49:031465 gpstop:cdw:gpadmin-[INFO]:-Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
+ 20230726:14:42:49:031465 gpstop:cdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload
+ ```
+
+### Indexes and performance
+
+Apache Cloudberry does not depend upon indexes to the same degree as traditional data warehouse systems. Because the segments execute table scans in parallel, each segment scanning a small part of the table, the traditional performance advantage from indexes is gone. Indexes consume large amounts of space and require considerable CPU time slot to compute during data loads. There are, however, times when indexes are useful, especially for highly selective queries. When a query looks up a [...]
+
+In this exercise, you work with the legacy optimizer to know how index can improve performance. You first run a single row lookup on the sample table without an index, then rerun the query after creating an index.
+
+```sql
+tutorial=# SELECT * FROM sample WHERE big = 12345;
+
+ id | big | wee | stuff
+-------+-------+-----+-------
+ 12345 | 12345 | 0 |
+(1 row)
+
+Time: 251.304 ms
+tutorial=#
+tutorial=# EXPLAIN SELECT * FROM sample WHERE big = 12345;
+
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..8552.02 rows=1 width=15)
+ -> Seq Scan on sample (cost=0.00..8552.00 rows=1 width=15)
+ Filter: (big = 12345)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+Time: 0.709 ms
+tutorial=#
+tutorial=#
+tutorial=# CREATE INDEX sample_big_index ON sample(big);
+
+CREATE INDEX
+Time: 1574.117 ms (00:01.574)
+tutorial=#
+tutorial=#
+tutorial=# SELECT * FROM sample WHERE big = 12345;
+ id | big | wee | stuff
+-------+-------+-----+-------
+ 12345 | 12345 | 0 |
+(1 row)
+
+Time: 2.774 ms
+tutorial=# EXPLAIN SELECT * FROM sample WHERE big = 12345;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Gather Motion 2:1 (slice1; segments: 2) (cost=0.17..8.21 rows=1 width=15)
+ -> Index Scan using sample_big_index on sample (cost=0.17..8.19 rows=1 width=15)
+ Index Cond: (big = 12345)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+Time: 0.627 ms
+```
+
+Notice the difference in timing between the single-row `SELECT` with and without the index. The difference would have been much greater for a larger table, because indexes improve performance for queries on large datasets. Note that even when an index exists, the optimizer might choose not to use it if another more efficient plan is available.
+
+View the following `EXPLAIN` plans to compare plans for some other common types of queries.
+
+```sql
+tutorial=# EXPLAIN SELECT * FROM sample WHERE big = 12345;
+tutorial=# EXPLAIN SELECT * FROM sample WHERE big > 12345;
+tutorial=# EXPLAIN SELECT * FROM sample WHERE big = 12345 OR big = 12355;
+tutorial=# DROP INDEX sample_big_index;
+tutorial=# EXPLAIN SELECT * FROM sample WHERE big = 12345 OR big = 12355;
+```
+
+### Row vs. column orientation
+
+Apache Cloudberry offers the ability to store a table in either row or column orientation. Both storage options have advantages, depending upon data compression characteristics, the kinds of queries executed, the row length, and the complexity, and the number of join columns.
+
+As a general rule, very wide tables are better stored in row orientation, especially if there are joins on many columns. Column orientation works well to save space with compression and to reduce I/O when there is much duplicated data in columns.
+
+In this exercise, you will create a column-oriented version of the fact table and compare it with the row-oriented version.
+
+1. Create a column-oriented version of the FAA On Time Performance fact table and insert the data from the row-oriented version.
+
+ ```sql
+ tutorial=# CREATE TABLE FAA.OTP_C (LIKE faa.otp_r) WITH (appendonly=true,
+ orientation=column)
+ DISTRIBUTED BY (UniqueCarrier, FlightNum) PARTITION BY RANGE(FlightDate)
+ ( PARTITION mth START('2009-06-01'::date) END ('2010-10-31'::date)
+ EVERY ('1 mon'::interval));
+ CREATE TABLE
+ tutorial=#
+ ```
+
+ ```sql
+ tutorial=# INSERT INTO faa.otp_c SELECT * FROM faa.otp_r;
+ INSERT 0 1024552
+ ```
+
+2. Compare the definitions of the row and the column versions of the table.
+
+ ```sql
+ tutorial=# \d faa.otp_r
+
+ Table "faa.otp_r"
+ Column | Type | Collation | Nullable | Default
+ ----------------------+------------------+-----------+----------+---------
+ flt_year | smallint | | |
+ flt_quarter | smallint | | |
+ flt_month | smallint | | |
+ flt_dayofmonth | smallint | | |
+ flt_dayofweek | smallint | | |
+ flightdate | date | | |
+ uniquecarrier | text | | |
+ airlineid | integer | | |
+ carrier | text | | |
+ flightnum | text | | |
+ origin | text | | |
+ origincityname | text | | |
+ originstate | text | | |
+ originstatename | text | | |
+ dest | text | | |
+ destcityname | text | | |
+ deststate | text | | |
+ deststatename | text | | |
+ crsdeptime | text | | |
+ deptime | integer | | |
+ depdelay | double precision | | |
+ depdelayminutes | double precision | | |
+ departuredelaygroups | smallint | | |
+ taxiout | smallint | | |
+ wheelsoff | text | | |
+ wheelson | text | | |
+ taxiin | smallint | | |
+ crsarrtime | text | | |
+ arrtime | text | | |
+ arrdelay | double precision | | |
+ arrdelayminutes | double precision | | |
+ arrivaldelaygroups | smallint | | |
+ cancelled | smallint | | |
+ cancellationcode | text | | |
+ diverted | smallint | | |
+ crselapsedtime | integer | | |
+ actualelapsedtime | double precision | | |
+ airtime | double precision | | |
+ flights | smallint | | |
+ distance | double precision | | |
+ distancegroup | smallint | | |
+ carrierdelay | smallint | | |
+ weatherdelay | smallint | | |
+ nasdelay | smallint | | |
+ securitydelay | smallint | | |
+ lateaircraftdelay | smallint | | |
+ Distributed by: (uniquecarrier, flightnum)
+ ```
+
+ Notice that the column-oriented version is append-only and partitioned. It has seventeen child files for the partitions, one for each month from June 2009 through October 2010.
+
+ ```sql
+ tutorial=# \d+ faa.otp_c
+ ```
+
+ ```sql
+ Partitioned table "faa.otp_c"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+ ----------------------+------------------+-----------+----------+---------+----------+-------------+--------------+-------------
+ flt_year | smallint | | | | plain | | |
+ flt_quarter | smallint | | | | plain | | |
+ flt_month | smallint | | | | plain | | |
+ flt_dayofmonth | smallint | | | | plain | | |
+ flt_dayofweek | smallint | | | | plain | | |
+ flightdate | date | | | | plain | | |
+ uniquecarrier | text | | | | extended | | |
+ airlineid | integer | | | | plain | | |
+ carrier | text | | | | extended | | |
+ flightnum | text | | | | extended | | |
+ origin | text | | | | extended | | |
+ origincityname | text | | | | extended | | |
+ originstate | text | | | | extended | | |
+ originstatename | text | | | | extended | | |
+ dest | text | | | | extended | | |
+ destcityname | text | | | | extended | | |
+ deststate | text | | | | extended | | |
+ deststatename | text | | | | extended | | |
+ crsdeptime | text | | | | extended | | |
+ deptime | integer | | | | plain | | |
+ depdelay | double precision | | | | plain | | |
+ depdelayminutes | double precision | | | | plain | | |
+ departuredelaygroups | smallint | | | | plain | | |
+ taxiout | smallint | | | | plain | | |
+ wheelsoff | text | | | | extended | | |
+ wheelson | text | | | | extended | | |
+ taxiin | smallint | | | | plain | | |
+ crsarrtime | text | | | | extended | | |
+ arrtime | text | | | | extended | | |
+ arrdelay | double precision | | | | plain | | |
+ arrdelayminutes | double precision | | | | plain | | |
+ arrivaldelaygroups | smallint | | | | plain | | |
+ cancelled | smallint | | | | plain | | |
+ cancellationcode | text | | | | extended | | |
+ diverted | smallint | | | | plain | | |
+ crselapsedtime | integer | | | | plain | | |
+ actualelapsedtime | double precision | | | | plain | | |
+ airtime | double precision | | | | plain | | |
+ flights | smallint | | | | plain | | |
+ distance | double precision | | | | plain | | |
+ distancegroup | smallint | | | | plain | | |
+ carrierdelay | smallint | | | | plain | | |
+ weatherdelay | smallint | | | | plain | | |
+ nasdelay | smallint | | | | plain | | |
+ securitydelay | smallint | | | | plain | | |
+ lateaircraftdelay | smallint | | | | plain | | |
+ Partition key: RANGE (flightdate)
+ Partitions: otp_c_1_prt_mth_1 FOR VALUES FROM ('2009-06-01') TO ('2009-07-01'),
+ otp_c_1_prt_mth_10 FOR VALUES FROM ('2010-03-01') TO ('2010-04-01'),
+ otp_c_1_prt_mth_11 FOR VALUES FROM ('2010-04-01') TO ('2010-05-01'),
+ otp_c_1_prt_mth_12 FOR VALUES FROM ('2010-05-01') TO ('2010-06-01'),
+ otp_c_1_prt_mth_13 FOR VALUES FROM ('2010-06-01') TO ('2010-07-01'),
+ otp_c_1_prt_mth_14 FOR VALUES FROM ('2010-07-01') TO ('2010-08-01'),
+ otp_c_1_prt_mth_15 FOR VALUES FROM ('2010-08-01') TO ('2010-09-01'),
+ otp_c_1_prt_mth_16 FOR VALUES FROM ('2010-09-01') TO ('2010-10-01'),
+ otp_c_1_prt_mth_17 FOR VALUES FROM ('2010-10-01') TO ('2010-10-31'),
+ otp_c_1_prt_mth_2 FOR VALUES FROM ('2009-07-01') TO ('2009-08-01'),
+ otp_c_1_prt_mth_3 FOR VALUES FROM ('2009-08-01') TO ('2009-09-01'),
+ otp_c_1_prt_mth_4 FOR VALUES FROM ('2009-09-01') TO ('2009-10-01'),
+ otp_c_1_prt_mth_5 FOR VALUES FROM ('2009-10-01') TO ('2009-11-01'),
+ otp_c_1_prt_mth_6 FOR VALUES FROM ('2009-11-01') TO ('2009-12-01'),
+ otp_c_1_prt_mth_7 FOR VALUES FROM ('2009-12-01') TO ('2010-01-01'),
+ otp_c_1_prt_mth_8 FOR VALUES FROM ('2010-01-01') TO ('2010-02-01'),
+ otp_c_1_prt_mth_9 FOR VALUES FROM ('2010-02-01') TO ('2010-03-01')
+ Distributed by: (uniquecarrier, flightnum)
+ ```
+
+ ```sql
+ tutorial=# \d+ otp_c_1_prt_mth_1
+ ```
+
+ ```sql
+ Table "faa.otp_c_1_prt_mth_1"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Compression Type | Compression Level | Block Size | Description
+ ----------------------+------------------+-----------+----------+---------+----------+-------------+--------------+------------------+-------------------+------------+-------------
+ flt_year | smallint | | | | plain | | | none | 0 | 32768 |
+ flt_quarter | smallint | | | | plain | | | none | 0 | 32768 |
+ flt_month | smallint | | | | plain | | | none | 0 | 32768 |
+ flt_dayofmonth | smallint | | | | plain | | | none | 0 | 32768 |
+ flt_dayofweek | smallint | | | | plain | | | none | 0 | 32768 |
+ flightdate | date | | | | plain | | | none | 0 | 32768 |
+ uniquecarrier | text | | | | extended | | | none | 0 | 32768 |
+ airlineid | integer | | | | plain | | | none | 0 | 32768 |
+ carrier | text | | | | extended | | | none | 0 | 32768 |
+ flightnum | text | | | | extended | | | none | 0 | 32768 |
+ origin | text | | | | extended | | | none | 0 | 32768 |
+ origincityname | text | | | | extended | | | none | 0 | 32768 |
+ originstate | text | | | | extended | | | none | 0 | 32768 |
+ originstatename | text | | | | extended | | | none | 0 | 32768 |
+ dest | text | | | | extended | | | none | 0 | 32768 |
+ destcityname | text | | | | extended | | | none | 0 | 32768 |
+ deststate | text | | | | extended | | | none | 0 | 32768 |
+ deststatename | text | | | | extended | | | none | 0 | 32768 |
+ crsdeptime | text | | | | extended | | | none | 0 | 32768 |
+ deptime | integer | | | | plain | | | none | 0 | 32768 |
+ depdelay | double precision | | | | plain | | | none | 0 | 32768 |
+ depdelayminutes | double precision | | | | plain | | | none | 0 | 32768 |
+ departuredelaygroups | smallint | | | | plain | | | none | 0 | 32768 |
+ taxiout | smallint | | | | plain | | | none | 0 | 32768 |
+ wheelsoff | text | | | | extended | | | none | 0 | 32768 |
+ wheelson | text | | | | extended | | | none | 0 | 32768 |
+ taxiin | smallint | | | | plain | | | none | 0 | 32768 |
+ crsarrtime | text | | | | extended | | | none | 0 | 32768 |
+ arrtime | text | | | | extended | | | none | 0 | 32768 |
+ arrdelay | double precision | | | | plain | | | none | 0 | 32768 |
+ arrdelayminutes | double precision | | | | plain | | | none | 0 | 32768 |
+ arrivaldelaygroups | smallint | | | | plain | | | none | 0 | 32768 |
+ cancelled | smallint | | | | plain | | | none | 0 | 32768 |
+ cancellationcode | text | | | | extended | | | none | 0 | 32768 |
+ diverted | smallint | | | | plain | | | none | 0 | 32768 |
+ crselapsedtime | integer | | | | plain | | | none | 0 | 32768 |
+ actualelapsedtime | double precision | | | | plain | | | none | 0 | 32768 |
+ airtime | double precision | | | | plain | | | none | 0 | 32768 |
+ flights | smallint | | | | plain | | | none | 0 | 32768 |
+ distance | double precision | | | | plain | | | none | 0 | 32768 |
+ distancegroup | smallint | | | | plain | | | none | 0 | 32768 |
+ carrierdelay | smallint | | | | plain | | | none | 0 | 32768 |
+ weatherdelay | smallint | | | | plain | | | none | 0 | 32768 |
+ nasdelay | smallint | | | | plain | | | none | 0 | 32768 |
+ securitydelay | smallint | | | | plain | | | none | 0 | 32768 |
+ lateaircraftdelay | smallint | | | | plain | | | none | 0 | 32768 |
+ Partition of: otp_c FOR VALUES FROM ('2009-06-01') TO ('2009-07-01')
+ Partition constraint: ((flightdate IS NOT NULL) AND (flightdate >= '2009-06-01'::date) AND (flightdate < '2009-07-01'::date))
+ Checksum: t
+ Distributed by: (uniquecarrier, flightnum)
+ Access method: ao_column
+ ```
+
+3. Compare the sizes of the tables using the `pg_relation_size()` and `pg_total_relation_size()` functions. The `pg_size_pretty()` function converts the size in bytes to human-readable units.
+
+ ```sql
+ tutorial=# SELECT pg_size_pretty(pg_relation_size('faa.otp_r'));
+ ```
+
+ ```sql
+ pg_size_pretty
+ ----------------
+ 256 MB
+ (1 row)
+ ```
+
+ ```sql
+ tutorial=# SELECT pg_size_pretty(pg_total_relation_size('faa.otp_r'));
+ ```
+
+ ```sql
+ pg_size_pretty
+ ----------------
+ 256 MB
+ (1 row)
+ ```
+
+ ```sql
+ tutorial=# SELECT pg_size_pretty(sum(pg_relation_size(inhrelid))) FROM pg_inherits WHERE inhparent = 'faa.otp_c'::regclass;
+ ```
+
+ ```sql
+ pg_size_pretty
+ ----------------
+ 403 MB
+ (1 row)
+ ```
+
+ ```sql
+ tutorial=# SELECT pg_size_pretty(sum(pg_total_relation_size(inhrelid))) FROM pg_inherits WHERE inhparent = 'faa.otp_c'::regclass;
+ ```
+
+ ```sql
+ pg_size_pretty
+ ----------------
+ 405 MB
+ (1 row)
+ ```
+
+### Check for even data distribution on segments
+
+The `faa.otp_r` and `faa.otp_c` tables use a hash distribution on the UniqueCarrier and FlightNum columns. These columns ensure an even data distribution across segments and, due to frequent joins involving them, optimize query execution by minimizing data movement between segments. When there is no benefit in co-locating data from various tables on the segments, using a unique column guarantees balanced distribution. However, using a low-cardinality column like Diverted, having only two [...]
+
+A primary goal of distribution is to have a balanced amount of data in each segment. The subsequent query demonstrates this. Given that both column-oriented and row-oriented tables share the same distribution columns, their counts should match.
+
+```sql
+tutorial=# SELECT gp_segment_id, COUNT(*) FROM faa.otp_c GROUP BY
+gp_segment_id ORDER BY gp_segment_id;
+```
+
+```sql
+ gp_segment_id | count
+---------------+--------
+ 0 | 513746
+ 1 | 510806
+(2 rows)
+```
+
+### About partitioning
+
+Partitioning a table can improve query performance and simplify data administration. The table is divided into smaller child files using a range or a list value, such as a date range or a country code.
+
+Partitions can improve query performance dramatically. When a query predicate filters on the same criteria used to define partitions, the optimizer can avoid searching partitions that do not contain relevant data.
+
+A common application for partitioning is to maintain a rolling window of data based on date, for example, a fact table containing the most recent 12 months of data. Using the `ALTER TABLE` statement, an existing partition can be dropped by removing its child file. This is much more efficient than scanning the entire table and removing rows with a `DELETE` statement.
+
+Partitions might also be sub-partitioned. For example, a table can be partitioned by month, and the month partitions can be sub-partitioned by week. Apache Cloudberry creates child files for the months and weeks. The actual data, however, is stored in the child files created for the week subpartitions. Only child files at the leaf level hold data.
+
+When a new partition is added, you can run `ANALYZE` on just the data in that partition. `ANALYZE` can run on the root partition (the name of the table in the `CREATE TABLE` statement) or on a child file created for a leaf partition. If `ANALYZE` has already run on the other partitions and the data is static, it is not necessary to run it again on those partitions.
+
+Apache Cloudberry supports:
+
+- Range partitioning: division of data based on a numerical range, such as date or price.
+- List partitioning: division of data based on a list of values, such as sales territory or product line.
+- A combination of both types.
+
+
+
+The following exercise compares `SELECT` statements with `WHERE` clauses that do and do not use a partitioned column.
+
+The column-oriented version of the fact table you created is partitioned by date. First, execute a query that filters on a non-partitioned column and note the execution time.
+
+```sql
+tutorial=# \timing on
+
+Timing is on.
+
+tutorial=# EXPLAIN SELECT MAX(depdelay) FROM faa.otp_c WHERE UniqueCarrier = 'UA';
+
+NOTICE: One or more columns in the following table(s) do not have statistics: otp_c
+HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics.
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
+ Finalize Aggregate (cost=0.00..531.33 rows=1 width=8)
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..531.33 rows=1 width=8)
+ -> Partial Aggregate (cost=0.00..531.33 rows=1 width=8)
+ -> Append (cost=0.00..531.15 rows=204908 width=16)
+ -> Seq Scan on otp_c_1_prt_mth_1 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_2 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_3 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_4 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_5 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_6 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_7 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_8 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_9 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_10 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_11 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_12 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_13 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_14 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_15 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_16 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ -> Seq Scan on otp_c_1_prt_mth_17 (cost=0.00..531.15 rows=204908 width=16)
+ Filter: (uniquecarrier = 'UA'::text)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(39 rows)
+Time: 16.113 ms
+tutorial=#
+tutorial=#
+
+tutorial=# EXPLAIN SELECT MAX(depdelay) FROM faa.otp_c WHERE flightdate ='2009-11-01';
+
+NOTICE: One or more columns in the following table(s) do not have statistics: otp_c
+HINT: For non-partitioned tables, run analyze <table_name>(<column_list>). For partitioned tables, run analyze rootpartition <table_name>(<column_list>). See log for columns missing statistics.
+
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Finalize Aggregate (cost=0.00..470.52 rows=1 width=8)
+ -> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..470.52 rows=1 width=8)
+ -> Partial Aggregate (cost=0.00..470.52 rows=1 width=8)
+ -> Append (cost=0.00..470.45 rows=81963 width=12)
+ -> Seq Scan on otp_c_1_prt_mth_6 (cost=0.00..470.45 rows=81963 width=12)
+ Filter: (flightdate = '2009-11-01'::date)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+Time: 7.434 ms
+```
+
+The query on the partitioned column takes much less time to execute. If you compare the explain plans for the queries in this exercise, you will see that the first query scans each of the seventeen child files, while the second scans just one child file. The reduction in I/O and CPU time explains the improved execution time.
diff --git a/versioned_sidebars/version-2.x-sidebars.json b/versioned_sidebars/version-2.x-sidebars.json
index 4a534955b..2868afadb 100644
--- a/versioned_sidebars/version-2.x-sidebars.json
+++ b/versioned_sidebars/version-2.x-sidebars.json
@@ -34,7 +34,8 @@
"cbdb-op-deploy-guide",
"deploy-cbdb-with-single-node"
]
- }
+ },
+ "deployment/sandbox"
]
},
{
@@ -561,7 +562,7 @@
]
},
- "sys-catalogs/gp_toolkit"
+ "sys-catalogs/gp_toolkit"
]
}
]
@@ -572,6 +573,30 @@
"items": [
"releases/index"
]
+ },
+ {
+ "type": "category",
+ "label": "Tutorials",
+ "link": {
+ "type": "doc",
+ "id": "tutorial/index"
+ },
+ "items": [
+ "tutorial/introduction-to-database-and-cloudberry-architecture",
+ "tutorial/create-users-and-roles",
+ "tutorial/create-and-prepare-database",
+ "tutorial/create-tables",
+ "tutorial/data-loading",
+ "tutorial/queries-and-performance-tuning",
+ "tutorial/backup-and-recovery-operations"
+ ]
+ },
+ {
+ "type": "category",
+ "label": "Crash Course",
+ "items": [
+ "tutorial/crash-course"
+ ]
}
]
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org