### Setup TopN Extension and Aggregation

Source: https://docs.citusdata.com/en/v13.0/develop/reference_sql.html

Installs the TopN extension, creates a table for JSON data, and applies the topn_add_agg function.

```sql
-- run below command from coordinator, it will be propagated to the worker nodes as well
CREATE EXTENSION topn;

-- a table to materialize the daily aggregate
CREATE TABLE reviews_by_day
(
  review_date date unique,
  agg_data jsonb
);

SELECT create_reference_table('reviews_by_day');

-- materialize how many reviews each product got per day per customer
INSERT INTO reviews_by_day
  SELECT review_date, topn_add_agg(product_id)
  FROM customer_reviews
  GROUP BY review_date;
```

--------------------------------

### Install and Run Microservices

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_microservices.html

Change into each microservice directory (user, time, ping), install dependencies, activate the virtual environment, and run the application.

```bash
cd user
pipenv install
pipenv shell
python app.py
```

--------------------------------

### Example Data Insertion and Query

Source: https://docs.citusdata.com/en/v13.0/develop/api_metadata.html

This example demonstrates inserting data into a distributed table and then querying it, which will be reflected in the tenant-level statistics if tracking is enabled.

```sql
INSERT INTO dist_table(tenant_id) VALUES (1);
INSERT INTO dist_table(tenant_id) VALUES (1);
INSERT INTO dist_table(tenant_id) VALUES (2);

SELECT count(*) FROM dist_table WHERE tenant_id = 1;
```

--------------------------------

### Download Example Dataset

Source: https://docs.citusdata.com/en/v13.0/develop/reference_dml.html

Use wget to download the example github_events dataset and gzip to decompress it. Ensure your database uses UTF8 encoding for this data.

```bash
wget http://examples.citusdata.com/github_archive/github_events-2015-01-01-{0..5}.csv.gz
gzip -d github_events-2015-01-01-*.gz

```

--------------------------------

### Install PostgreSQL and Citus, Initialize Database

Source: https://docs.citusdata.com/en/v13.0/installation/multi_node_rhel.html

Installs PostgreSQL with the Citus extension, initializes the system database, and configures PostgreSQL to preload the Citus extension.

```bash
sudo yum install -y citus130_17
# initialize system database
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
# preload citus extension
echo "shared_preload_libraries = 'citus'" | sudo tee -a /var/lib/pgsql/17/data/postgresql.conf
```

--------------------------------

### Clone Example Microservices Repository

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_microservices.html

Clone the public GitHub repository containing example microservices for ping, time, and user functionalities.

```bash
git clone https://github.com/citusdata/citus-example-microservices.git
```

--------------------------------

### Start Citus Database Server

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_debian.html

Starts the PostgreSQL server instance for the new Citus directory on a specified port and logs activity to a file.

```bash
pg_ctl -D citus -o "-p 9700" -l citus_logfile start
```

--------------------------------

### Create Reference Table

Source: https://docs.citusdata.com/en/v13.0/develop/reference_ddl.html

Example of creating a reference table and distributing it. Reference tables are fully replicated on all nodes.

```sql
-- we're using the "text" column type here, but a real application
-- might use "citext" which is available in a postgres contrib module

CREATE TABLE users ( email text PRIMARY KEY );
SELECT create_reference_table('users');

```

--------------------------------

### Start Kafka and Zookeeper Services

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

Shell commands to start Zookeeper and Kafka server instances. Ensure Kafka version compatibility (0.9 or earlier for kafka-sink-pg-json).

```shell
# save some typing
export C=confluent-2.0.0

# start zookeeper
$C/bin/zookeeper-server-start \
  $C/etc/kafka/zookeeper.properties

# start kafka server
$C/bin/kafka-server-start \
  $C/etc/kafka/server.properties
```

--------------------------------

### Add Citus Repository and Install Citus Extension

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_rhel.html

Installs the Citus repository for your package manager and then installs the Citus extension package for PostgreSQL 17. Ensure you have curl installed.

```bash
# Add Citus repository for package manager
curl https://install.citusdata.com/community/rpm.sh | sudo bash

# install Citus extension
sudo yum install -y citus130_17
```

--------------------------------

### Install pgbench on Debian-based systems

Source: https://docs.citusdata.com/en/v13.0/extra/write_throughput_benchmark.html

Installs pgbench version 10 on Debian-based systems using PostgreSQL's APT repository. Ensure you have wget and ca-certificates installed.

```bash
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-10
```

--------------------------------

### Install SaasKit.Multitenancy Package

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Use the dotnet CLI to add the SaasKit.Multitenancy NuGet package to your project. This package provides the core functionality for multi-tenancy.

```bash
dotnet add package SaasKit.Multitenancy

```

--------------------------------

### Example Citus Connection String

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Provides an example of a Citus connection string for PostgreSQL. It includes server, port, database, user, password, and SSL mode settings. Consider using Secret Manager for sensitive credentials.

```text
Server=myformation.db.citusdata.com;Port=5432;Database=citus;Userid=citus;Password=mypassword;SslMode=Require;Trust Server Certificate=true;

```

--------------------------------

### Create Partitioned Table and Partitions

Source: https://docs.citusdata.com/en/v13.0/use_cases/timeseries.html

Create a partitioned table with a structure similar to an existing table and generate time-based partitions. This setup is for demonstrating columnar storage.

```sql
-- our new table, same structure as the example in
-- the previous section

CREATE TABLE github_columnar_events ( LIKE github_events )
PARTITION BY RANGE (created_at);

-- create partitions to hold two hours of data each

SELECT create_time_partitions(
  table_name         := 'github_columnar_events',
  partition_interval := '2 hours',
  start_from         := '2015-01-01 00:00:00',
  end_at             := '2015-01-01 08:00:00'
);

-- fill with sample data
-- (note that this data requires the database to have UTF8 encoding)

\COPY github_columnar_events FROM 'github_events.csv' WITH (format CSV)

-- list the partitions, and confirm they're
-- using row-based storage (heap access method)

SELECT partition, access_method
  FROM time_partitions
 WHERE parent_table = 'github_columnar_events'::regclass;

```

--------------------------------

### Shard Placement Result

Source: https://docs.citusdata.com/en/v13.0/get_started/concepts.html

Example output showing the node name and port where shard 102027 is located.

```text
┌─────────┬───────────┬──────────┐
│ shardid │ nodename  │ nodeport │
├─────────┼───────────┼──────────┤
│  102027 │ localhost │     5433 │
└─────────┴───────────┴──────────┘

```

--------------------------------

### Install Kafka Sink and Create Table SQL

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

SQL commands to set up the Kafka sink metadata tables and create/distribute the target ingestion table in PostgreSQL.

```sql
-- create metadata tables for kafka-sink-pg-json
\i sink/install-justone-kafka-sink-pg-1.0.sql

-- create and distribute target ingestion table
create table kafka_test ( a int, b int );
select create_distributed_table('kafka_test', 'a');
```

--------------------------------

### Example Rows from Events Table

Source: https://docs.citusdata.com/en/v13.0/articles/metrics_dashboard.html

Illustrates sample data entries from the 'events' table, showcasing the format of the 'id', 'name', 'created_at', and 'data' fields.

```sql
citus=> select * from events order by created_at desc limit 2;
-[ RECORD 1 ]-
id         | 9a3dfdbd-c395-40bb-8d25-45ee7c913662
name       | Timeout::Error
created_at | 2016-07-28 13:18:47.289917-07
data       | {"id": "5747a999-9768-429c-b13c-c7c0947dd950", "class": "Server", "message": "execution expired"}
-[ RECORD 2 ]-
id         | ba9d6a13-0832-47fb-a849-02f1362c9019
name       | Sequel::DatabaseConnectionError
created_at | 2016-07-28 12:58:40.506267-07
data       | {"id": "232835ec-31a1-44d0-ae5b-edafb2cf6978", "class": "Timeline", "message": "PG::ConnectionBad: could not connect to server: Connection refused\n\tIs the server running on host \"ec2-52-207-18-20.compute-1.amazonaws.com\" (52.207.18.20) and accepting\n\tTCP/IP connections on port 5432?\n"}


```

--------------------------------

### Citus Lock Waits Record Example

Source: https://docs.citusdata.com/en/v13.0/develop/api_metadata.html

An example output from `citus_lock_waits` showing a blocked statement, the blocking statement, and the node IDs involved in the lock wait.

```text
SELECT * FROM citus_lock_waits;

-[ RECORD 1 ]--------------------------------------
waiting_gpid                          | 10000011981
blocking_gpid                         | 10000011979
blocked_statement                     | UPDATE numbers SET j = 3 WHERE i = 1;
current_statement_in_blocking_process | UPDATE numbers SET j = 2 WHERE i = 1;
waiting_nodeid                        | 1
blocking_nodeid                       | 1


```

--------------------------------

### Insert Sample Tenant and Question Data

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Populates the 'tenants' and 'questions' tables with sample data, including UUIDs, domain names, and question details. This data is used to test the multi-tenant setup.

```sql
INSERT INTO tenants VALUES (
    'c620f7ec-6b49-41e0-9913-08cfe81199af',
    'bufferoverflow.local',
    'Buffer Overflow',
    'Ask anything code-related!',
    now(),
    now());

INSERT INTO tenants VALUES (
    'b8a83a82-bb41-4bb3-bfaa-e923faab2ca4',
    'dboverflow.local',
    'Database Questions',
    'Figure out why your connection string is broken.',
    now(),
    now());

INSERT INTO questions VALUES (
    '347b7041-b421-4dc9-9e10-c64b8847fedf',
    'c620f7ec-6b49-41e0-9913-08cfe81199af',
    'How do you build apps in ASP.NET Core?',
    1,
    now(),
    now());

INSERT INTO questions VALUES (
    'a47ffcd2-635a-496e-8c65-c1cab53702a7',
    'b8a83a82-bb41-4bb3-bfaa-e923faab2ca4',
    'Using postgresql for multitenant data?',
    2,
    now(),
    now());

```

--------------------------------

### Set up Docker Compose for Citus

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

This command starts the necessary Docker containers for Citus. It may require sudo privileges depending on your Docker daemon configuration.

```bash
docker-compose up
```

--------------------------------

### Start Single-Node Citus Docker Container

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_docker.html

Use this command to start a Citus Docker container. It maps port 5432 and sets a PostgreSQL password. The image is for development/testing only.

```bash
# start the image
docker run -d --name citus -p 5432:5432 -e POSTGRES_PASSWORD=mypass \
           citusdata/citus:13.0
```

--------------------------------

### Create and Distribute a Function

Source: https://docs.citusdata.com/en/v13.0/develop/api_metadata.html

This example demonstrates creating a PostgreSQL function and then distributing it across worker nodes using `create_distributed_function`. The `citus.pg_dist_object` table will then contain entries for both the type and the function.

```sql
CREATE TYPE stoplight AS enum ('green', 'yellow', 'red');

CREATE OR REPLACE FUNCTION intersection()
RETURNS stoplight AS $$
DECLARE
        color stoplight;
BEGIN
        SELECT * 
          FROM unnest(enum_range(NULL::stoplight)) INTO color
         ORDER BY random() LIMIT 1;
        RETURN color;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT create_distributed_function('intersection()');

-- will have two rows, one for the TYPE and one for the FUNCTION
TABLE citus.pg_dist_object;
```

--------------------------------

### Verify Citus Installation via psql

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_docker.html

Connect to the running Citus Docker container using psql to verify that Citus is installed and running correctly by querying the citus_version() function.

```bash
# verify it's running, and that Citus is installed:
psql -U postgres -h localhost -d postgres -c "SELECT * FROM citus_version();"
```

--------------------------------

### Citus Schemas View Example

Source: https://docs.citusdata.com/en/v13.0/develop/api_metadata.html

Displays distributed schemas in the Citus system. Local schemas are not included.

```sql
 schema_name  | colocation_id | schema_size | schema_owner
--------------+---------------+-------------+--------------
 user_service |             1 | 0 bytes     | user_service
 time_service |             2 | 0 bytes     | time_service
 ping_service |             3 | 632 kB      | ping_service

```

--------------------------------

### Finish PostgreSQL Upgrade

Source: https://docs.citusdata.com/en/v13.0/admin_guide/upgrading_citus.html

Run this command on both coordinator and worker nodes after starting the new server and before running any other queries to finalize the Citus metadata restoration.

```sql
SELECT citus_finish_pg_upgrade();
```

--------------------------------

### Download Docker Spark Compose File

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

Downloads the docker-compose.yml file to set up a local Spark cluster using Docker. This is the recommended method for starting the cluster.

```shell
wget https://raw.githubusercontent.com/gettyimages/docker-spark/master/docker-compose.yml
```

--------------------------------

### Inner Join Example

Source: https://docs.citusdata.com/en/v13.0/articles/outer_joins.html

Demonstrates a standard inner join between the customer and purchase tables, returning only rows with matching customer IDs in both tables.

```sql
SELECT customer.name, purchase.comment
FROM customer JOIN purchase ON customer.customer_id = purchase.customer_id
ORDER BY purchase.comment;
```

```sql
SELECT customer.name, purchase.comment
FROM customer INNER JOIN purchase ON customer.customer_id = purchase.customer_id
ORDER BY purchase.comment;
```

--------------------------------

### Bulk Copy Data from STDIN

Source: https://docs.citusdata.com/en/v13.0/performance/performance_tuning.html

Utilize the COPY command for high-throughput bulk ingestion into distributed tables. This example shows loading data directly from standard input, suitable for applications.

```sql
COPY pgbench_history FROM STDIN WITH (FORMAT CSV);

```

--------------------------------

### Full Outer Join Example

Source: https://docs.citusdata.com/en/v13.0/articles/outer_joins.html

Demonstrates a full outer join, which returns all rows from both the customer and purchase tables. Rows with no match in the other table will have NULL values for the columns of that table.

```sql
SELECT customer.name, purchase.comment
FROM customer FULL JOIN purchase ON customer.customer_id = purchase.customer_id
ORDER BY purchase.comment;
```

--------------------------------

### Join query for running campaigns

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_multi_tenant.html

Perform join queries across multiple tables to analyze complex relationships. This example identifies running campaigns that have received the most clicks and impressions.

```sql
SELECT campaigns.id, campaigns.name, campaigns.monthly_budget,
       sum(impressions_count) as total_impressions, sum(clicks_count) as total_clicks
FROM ads, campaigns
WHERE ads.company_id = campaigns.company_id
AND ads.campaign_id = campaigns.id
AND campaigns.company_id = 5
AND campaigns.state = 'running'
GROUP BY campaigns.id, campaigns.name, campaigns.monthly_budget
ORDER BY total_impressions, total_clicks;
```

--------------------------------

### CircleCI Configuration for Citus Cluster

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_ror.html

Set up CircleCI to run tests against a Citus cluster using Docker. This involves installing Docker Compose, checking out the code, and starting the Citus cluster via Docker Compose.

```bash
steps:
  - setup_remote_docker:
      docker_layer_caching: true
  - run:
      name: Install Docker Compose
      command: |
        curl -L https://github.com/docker/compose/releases/download/1.19.0/docker-compose-`uname -s`-`uname -m` > ~/docker-compose
        chmod +x ~/docker-compose
        mv ~/docker-compose /usr/local/bin/docker-compose

  - checkout

  - run:
      name: Starting Citus Cluster
      command: docker-compose -f citus-docker-compose.yml up -d
```

--------------------------------

### Efficiently Join Shared and Tenant Data

Source: https://docs.citusdata.com/en/v13.0/use_cases/multi_tenant.html

Query shared reference tables alongside tenant-specific data. This example joins click data with geographical information, benefiting from the co-location of the `geo_ips` reference table.

```sql
SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;
```

--------------------------------

### Create and Distribute Offer Table

Source: https://docs.citusdata.com/en/v13.0/articles/faceted_search.html

Defines a distributed 'offer' table and distributes it by 'product_id' to enable co-located joins with the 'product' table. This setup is crucial for searching products with associated offers.

```sql
CREATE TABLE offer (
  product_id int not null,
  offer_id int not null,
  seller_id int,
  price decimal(12,2),
  new bool,
  primary key(product_id, offer_id)
);
SELECT create_distributed_table('offer', 'product_id');

```

--------------------------------

### Verify Citus Installation

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_debian.html

Checks if the Citus extension is successfully installed and active by querying the Citus version.

```bash
psql -p 9700 -c "select citus_version();"
```

--------------------------------

### Install PostgreSQL and Citus

Source: https://docs.citusdata.com/en/v13.0/installation/multi_node_debian.html

Installs PostgreSQL version 17 with the Citus extension and preloads the Citus shared library.

```bash
sudo apt-get -y install postgresql-17-citus-13.0
```

```bash
sudo pg_conftool 17 main set shared_preload_libraries citus
```

--------------------------------

### Initialize and Distribute pgbench Tables

Source: https://docs.citusdata.com/en/v13.0/extra/write_throughput_benchmark.html

Initializes the pgbench benchmarking environment by creating test tables and then distributes a specific table to the Citus cluster. Replace 'connection_string_to_coordinator' with your actual connection string.

```bash
pgbench -i connection_string_to_coordinator

psql connection_string_to_coordinator -c "SELECT create_distributed_table('pgbench_history', 'aid');"
```

--------------------------------

### Initialize pgbench and Distribute Table

Source: https://docs.citusdata.com/en/v13.0/extra/write_throughput_benchmark.html

Initialize the pgbench environment and distribute a table for benchmarking. Ensure the 'pgbench_accounts' table is distributed on the 'aid' column.

```bash
pgbench -i connection_string_to_coordinator

```

```sql
SELECT create_distributed_table('pgbench_accounts', 'aid');

```

--------------------------------

### Function create_distributed_table Does Not Exist

Source: https://docs.citusdata.com/en/v13.0/reference/common_errors.html

This error indicates that the Citus extension is not properly installed or enabled. Ensure the Citus extension is installed in your PostgreSQL database.

```sql
SELECT create_distributed_table('foo', 'id');
/*
ERROR:  function create_distributed_table(unknown, unknown) does not exist
LINE 1: SELECT create_distributed_table('foo', 'id');
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
*/

```

--------------------------------

### Create Partial Index for Tenant-Specific Queries

Source: https://docs.citusdata.com/en/v13.0/use_cases/multi_tenant.html

Improve query performance for individual tenants by creating partial indexes on JSONB data. This example indexes mobile device information for a specific company.

```sql
CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;
```

--------------------------------

### Define Tenant and Question Tables

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Sets up the initial database schema for tenants and questions, including primary keys. Ensure tenant_id is included in the questions table for data colocation.

```sql
CREATE TABLE tenants (
    id uuid NOT NULL,
    domain text NOT NULL,
    name text NOT NULL,
    description text NOT NULL,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL
);

CREATE TABLE questions (
    id uuid NOT NULL,
    tenant_id uuid NOT NULL,
    title text NOT NULL,
    votes int NOT NULL,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL
);

ALTER TABLE tenants ADD PRIMARY KEY (id);
ALTER TABLE questions ADD PRIMARY KEY (id, tenant_id);

```

--------------------------------

### Create New Database, Enable Citus, and Add Worker Nodes on Coordinator

Source: https://docs.citusdata.com/en/v13.0/admin_guide/cluster_management.html

On the coordinator node, create the new database, enable the Citus extension, and then register the worker nodes.

```sql
CREATE DATABASE newbie;
\c newbie
CREATE EXTENSION citus;

SELECT * from citus_add_node('node-name', 5432);
SELECT * from citus_add_node('node-name2', 5432);
-- ... for all of them

```

--------------------------------

### Get Current Time via API

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_microservices.html

Use curl to send a GET request to the time service API to retrieve the current server time.

```bash
curl http://localhost:5001/current_time
```

--------------------------------

### Create ASP.NET Core MVC Project

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Initializes a new ASP.NET Core MVC project using the .NET CLI. This command creates the project structure and necessary files for an MVC application.

```bash
dotnet new mvc -o QuestionExchange
cd QuestionExchange

```

--------------------------------

### Set up Distributed Table and Measure INSERT Latency

Source: https://docs.citusdata.com/en/v13.0/performance/performance_tuning.html

Demonstrates setting up a distributed table and measuring the latency of transactional INSERT statements. The first INSERT includes connection setup time, while subsequent INSERTs are faster due to persistent connections.

```sql
CREATE TABLE pgbench_history (tid int, bid int, aid int, delta int, mtime timestamp);
SELECT create_distributed_table('pgbench_history', 'aid');

\timing on

INSERT INTO pgbench_history VALUES (10, 1, 10000, -5000, CURRENT_TIMESTAMP); -- Time: 10.314 ms
INSERT INTO pgbench_history VALUES (10, 1, 22000, 5000, CURRENT_TIMESTAMP); -- Time: 3.132 ms
```

--------------------------------

### Add Citus Repository and Install PostgreSQL with Citus

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_debian.html

Adds the Citus repository to your system's package manager and installs PostgreSQL version 17 with the Citus extension.

```bash
# Add Citus repository for package manager
curl https://install.citusdata.com/community/deb.sh | sudo bash

# install the server and initialize db
sudo apt-get -y install postgresql-17-citus-13.0
```

--------------------------------

### EXPLAIN Output with Citus Adaptive Scan Details

Source: https://docs.citusdata.com/en/v13.0/performance/performance_tuning.html

This is an example of the output from an EXPLAIN query when citus.explain_all_tasks is enabled. It shows the overall plan and details for each distributed task, including node information and scan details.

```text
Sort  (cost=0.00..0.00 rows=0 width=0)
   Sort Key: remote_scan.minute
   ->  HashAggregate  (cost=0.00..0.00 rows=0 width=0)
     Group Key: remote_scan.minute
     ->  Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0)
       Task Count: 32
       Tasks Shown: All
       ->  Task
         Node: host=localhost port=5433 dbname=postgres
         ->  HashAggregate  (cost=93.42..98.36 rows=395 width=16)
           Group Key: date_trunc('minute'::text, created_at)
           ->  Seq Scan on github_events_102042 github_events  (cost=0.00..88.20 rows=418 width=503)
             Filter: (event_type = 'PushEvent'::text)
       ->  Task
         Node: host=localhost port=5434 dbname=postgres
         ->  HashAggregate  (cost=103.21..108.57 rows=429 width=16)
           Group Key: date_trunc('minute'::text, created_at)
           ->  Seq Scan on github_events_102043 github_events  (cost=0.00..97.47 rows=459 width=492)
             Filter: (event_type = 'PushEvent'::text)
       --
       -- ... repeats for all 32 tasks
       --     alternating between workers one and two
       --     (running in this case locally on ports 5433, 5434)
       --

(199 rows) 
```

--------------------------------

### Create Logical Replication Slots on All Nodes

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

Run this command on the coordinator to create logical replication slots in 'pgoutput' format on all nodes.

```sql
-- create replication slots on all nodes, and choose the pgoutput format

SELECT * FROM run_command_on_all_nodes($$
  SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput', false)
$$);
```

--------------------------------

### Simulate Blocking Lock

Source: https://docs.citusdata.com/en/v13.0/develop/api_metadata.html

Demonstrates a scenario where two sessions attempt to update the same row on a distributed table, causing one to block. Session 1 starts a transaction and updates a row. Session 2 then starts a transaction and attempts to update the same row, which will block.

```sql
-- session 1                           -- session 2
-------------------------------------  -------------------------------------
BEGIN;
UPDATE numbers SET j = 2 WHERE i = 1;
                                       BEGIN;
                                       UPDATE numbers SET j = 3 WHERE i = 1;
                                       -- (this blocks)

```

--------------------------------

### Download Sample Data

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_realtime_analytics.html

Download the sample CSV files for users and events data.

```bash
curl https://examples.citusdata.com/tutorial/users.csv > users.csv
curl https://examples.citusdata.com/tutorial/events.csv > events.csv
```

--------------------------------

### Temporary Table Workaround Result

Source: https://docs.citusdata.com/en/v13.0/develop/reference_workarounds.html

This is an example output after applying the temporary table workaround for grouping sets.

```text
. 
 repo_id |    event_type     | event_public | grouping |         min
---------+-------------------+--------------+----------+---------------------
    8514 | PullRequestEvent  | t            |        0 | 2016-12-01 05:32:54
    8514 | IssueCommentEvent | t            |        0 | 2016-12-01 05:32:57
   19438 | IssueCommentEvent | t            |        0 | 2016-12-01 05:48:56
   21692 | WatchEvent        | t            |        0 | 2016-12-01 06:01:23
   15435 | WatchEvent        | t            |        0 | 2016-12-01 05:40:24
   21692 | WatchEvent        |              |        1 | 2016-12-01 06:01:23
   15435 | WatchEvent        |              |        1 | 2016-12-01 05:40:24
    8514 | PullRequestEvent  |              |        1 | 2016-12-01 05:32:54
    8514 | IssueCommentEvent |              |        1 | 2016-12-01 05:32:57
   19438 | IssueCommentEvent |              |        1 | 2016-12-01 05:48:56
   15435 |                   |              |        3 | 2016-12-01 05:40:24
   21692 |                   |              |        3 | 2016-12-01 06:01:23
   19438 |                   |              |        3 | 2016-12-01 05:48:56
    8514 |                   |              |        3 | 2016-12-01 05:32:54

```

--------------------------------

### Set citus.multi_task_query_log_level

Source: https://docs.citusdata.com/en/v13.0/develop/api_guc.html

Example of how to update a Citus configuration setting using the ALTER DATABASE SET command.

```sql
ALTER DATABASE citus SET citus.multi_task_query_log_level = 'log';
```

--------------------------------

### Initialize HomeController with DbContext and Tenant in ASP.NET Core

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Add this constructor to your HomeController to inject the `AppDbContext` and `Tenant` dependencies. These are required for data access and tenant identification.

```csharp
public class HomeController : Controller
{
    private readonly AppDbContext _context;
    private readonly Tenant _currentTenant;

    public HomeController(AppDbContext context, Tenant tenant)
    {
        _context = context;
        _currentTenant = tenant;
    }

    // Existing code...

}
```

--------------------------------

### Get Distribution Column Name

Source: https://docs.citusdata.com/en/v13.0/develop/api_udf.html

Retrieves the distribution column name for a given distributed table from the pg_dist_partition table.

```sql
SELECT column_to_column_name(logicalrelid, partkey) AS dist_col_name
  FROM pg_dist_partition
 WHERE logicalrelid='products'::regclass;
```

--------------------------------

### Add Citus Repository

Source: https://docs.citusdata.com/en/v13.0/installation/multi_node_rhel.html

Adds the Citus community repository to the system's package manager for installing Citus packages.

```bash
curl https://install.citusdata.com/community/rpm.sh | sudo bash
```

--------------------------------

### Enable HLL Extension in PostgreSQL

Source: https://docs.citusdata.com/en/v13.0/articles/hll_count_distinct.html

Install the HLL extension on the Citus coordinator node. This is a prerequisite for using HLL functionalities.

```sql
CREATE EXTENSION hll;
```

--------------------------------

### Enable Write-Only Mode for Initial Setup

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_ror.html

Use this initializer to enable write-only mode, which sets the tenant ID for new records without filtering existing queries. Remove this line once tenancy is fully enforced.

```ruby
MultiTenant.enable_write_only_mode
```

--------------------------------

### Create Kafka Topic

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

Command to create a Kafka topic named 'test' with one partition and one replica. This topic will be used for data ingestion.

```shell
# create the topic we'll be reading/writing
$C/bin/kafka-topics --create --zookeeper localhost:2181 \
                    --replication-factor 1 --partitions 1 \
                    --topic test
```

--------------------------------

### Get Distributed Table Size

Source: https://docs.citusdata.com/en/v13.0/develop/api_udf.html

Calculates the disk space used by all shards of a distributed table, excluding index sizes.

```sql
SELECT pg_size_pretty(citus_relation_size('github_events'));
```

--------------------------------

### Restart PostgreSQL and Enable Auto-start

Source: https://docs.citusdata.com/en/v13.0/installation/multi_node_debian.html

Restarts the PostgreSQL service to apply configuration changes and enables it to start automatically on system boot.

```bash
# start the db server
sudo service postgresql restart
# and make it start automatically when computer does
sudo update-rc.d postgresql enable
```

--------------------------------

### Download and Unpack Sample Data

Source: https://docs.citusdata.com/en/v13.0/articles/parallel_indexing.html

Use these commands to download and decompress the sample CSV files for the GitHub events dataset.

```bash
wget http://examples.citusdata.com/github_archive/github_events-2015-01-01-{0..24}.csv.gz
gzip -d github_events-*.gz
```

--------------------------------

### Restart PostgreSQL and Enable Auto-Start

Source: https://docs.citusdata.com/en/v13.0/installation/multi_node_rhel.html

Restarts the PostgreSQL service to apply configuration changes and configures it to start automatically on system boot.

```bash
# start the db server
sudo service postgresql-17 restart
# and make it start automatically when computer does
sudo chkconfig postgresql-17 on
```

--------------------------------

### Create New Database and Enable Citus on Worker Nodes

Source: https://docs.citusdata.com/en/v13.0/admin_guide/cluster_management.html

Run these commands on each worker node to create a new database and enable the Citus extension.

```sql
-- on every worker node

CREATE DATABASE newbie;
\c newbie
CREATE EXTENSION citus;

```

--------------------------------

### Create SQL File for UPDATE Benchmarks

Source: https://docs.citusdata.com/en/v13.0/extra/write_throughput_benchmark.html

Define SQL commands for UPDATE benchmarks in a file named update.sql. This script sets variables for the number of accounts, a random account ID, and a random balance delta for updates.

```sql
\set naccounts 100000 * :scale
\set aid random(1, :naccounts)
\set delta random(-5000, 5000)
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

```

--------------------------------

### Schedule Partition Creation with pg_cron

Source: https://docs.citusdata.com/en/v13.0/use_cases/timeseries.html

Schedule a monthly cron job to automatically create new partitions for the next 12 months using `create_time_partitions`. This ensures continuous partition availability.

```sql
-- set two monthly cron jobs:

-- 1. ensure we have partitions for the next 12 months

SELECT cron.schedule('create-partitions', '0 0 1 * *', $$
  SELECT create_time_partitions(
      table_name         := 'github_events',
      partition_interval := '1 month',
      end_at             := now() + '12 months'
  )
$$);
```

--------------------------------

### Schedule Rollup Function with Crontab

Source: https://docs.citusdata.com/en/v13.0/use_cases/realtime_analytics.html

Example of how to schedule the `rollup_http_request()` function to run every minute using a crontab entry on the coordinator node.

```bash
* * * * * psql -c 'SELECT rollup_http_request();'
```

--------------------------------

### Initialize Citus Cluster Directory

Source: https://docs.citusdata.com/en/v13.0/installation/single_node_rhel.html

Creates a new database directory for Citus and sets up the PostgreSQL configuration. It's recommended to run these commands as the 'postgres' user for socket access.

```bash
# this user has access to sockets in /var/run/postgresql
sudo su - postgres

# include path to postgres binaries
export PATH=$PATH:/usr/pgsql-17/bin

cd ~
mkdir citus
initdb -D citus
```

--------------------------------

### Update campaign budgets

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_multi_tenant.html

Modify existing data using standard UPDATE statements. This example doubles the budget for all campaigns of a specific company.

```sql
UPDATE campaigns
SET monthly_budget = monthly_budget*2
WHERE company_id = 5;
```

--------------------------------

### Create Product Table

Source: https://docs.citusdata.com/en/v13.0/articles/faceted_search.html

Defines the schema for the product table, including columns for ID, name, description, price, and JSONB attributes.

```sql
CREATE TABLE product (
  product_id int primary key,
  name text not null,
  description text not null,
  price decimal(12,2),
  attributes jsonb
);

```

--------------------------------

### Configure Local Hosts File for Multi-Tenant Testing

Source: https://docs.citusdata.com/en/v13.0/develop/migration_mt_asp.html

Map the specified local IP address to your application's domain names to test multi-tenancy locally. This allows the application to resolve different tenants based on the domain.

```text
127.0.0.1 bufferoverflow.local
127.0.0.1 dboverflow.local


```

--------------------------------

### Get Rebalance Progress

Source: https://docs.citusdata.com/en/v13.0/develop/api_udf.html

Lists the progress of every shard involved in a rebalance operation. Use this to monitor moves planned and executed by citus_rebalance_start().

```sql
SELECT * FROM get_rebalance_progress();

```

--------------------------------

### Create Distributed Table and Insert Data

Source: https://docs.citusdata.com/en/v13.0/admin_guide/cluster_management.html

Sets up a distributed 'events' table and inserts initial data. Assumes tenant roles will be created separately.

```sql
CREATE TABLE events(
  tenant_id int,
  id int,
  type text
);

SELECT create_distributed_table('events','tenant_id');

INSERT INTO events VALUES (1,1,'foo'), (2,2,'bar');

-- assumes that roles tenant_1 and tenant_2 exist
GRANT select, update, insert, delete
  ON events TO tenant_1, tenant_2;
```

--------------------------------

### List Users via API

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_microservices.html

Use curl to send a GET request to the user service API to retrieve a list of all created users.

```bash
curl http://localhost:5000/users
```

--------------------------------

### Load Data using COPY Command

Source: https://docs.citusdata.com/en/v13.0/articles/parallel_indexing.html

Demonstrates loading data using the COPY command. This operation benefits from parallelization when used on a distributed table, especially with an index present.

```sql
\COPY github_events FROM PROGRAM 'cat github_events-*.csv' WITH (FORMAT CSV)
```

--------------------------------

### Connect to Citus Coordinator using psql

Source: https://docs.citusdata.com/en/v13.0/get_started/tutorial_microservices.html

Connect to the Citus coordinator node using psql. Use port 9700 for native PostgreSQL installations.

```bash
psql -p 9700
```

--------------------------------

### Delete Rows from Distributed Table

Source: https://docs.citusdata.com/en/v13.0/develop/reference_dml.html

Use the standard DELETE command to remove rows from a distributed table. This example targets specific `repo_id` values.

```sql
DELETE FROM github_events
WHERE repo_id IN (24509048, 24509049);
```

--------------------------------

### Download and Extract kafka-sink-pg-json

Source: https://docs.citusdata.com/en/v13.0/develop/integrations.html

Download and extract the kafka-sink-pg-json tool for ingesting JSON messages from Kafka into a PostgreSQL table. This example uses Confluent 2.0.

```bash
# we're using Confluent 2.0 for kafka-sink-pg-json support
curl -L http://packages.confluent.io/archive/2.0/confluent-2.0.0-2.11.7.tar.gz 
  | tar zx

# Now get the jar and conf files for kafka-sink-pg-json
mkdir sink
curl -L https://github.com/justonedb/kafka-sink-pg-json/releases/download/v1.0.2/justone-jafka-sink-pg-json-1.0.zip -o sink.zip
unzip -d sink $_ && rm $_
```

--------------------------------

### Prepare for PostgreSQL Upgrade

Source: https://docs.citusdata.com/en/v13.0/admin_guide/upgrading_citus.html

Run this command on both coordinator and worker nodes to prepare Citus metadata for the pg_upgrade process.

```sql
SELECT citus_prepare_pg_upgrade();
```

--------------------------------

### Start Draining Multiple Nodes

Source: https://docs.citusdata.com/en/v13.0/develop/api_udf.html

Initiates a rebalancing process specifically for draining multiple nodes. Use this after setting individual node properties to false.

```sql
SELECT * FROM citus_rebalance_start(drain_only := true);
```

--------------------------------

### EXPLAIN Output for Push-Pull Execution

Source: https://docs.citusdata.com/en/v13.0/develop/reference_processing.html

This is a partial EXPLAIN output illustrating Citus's adaptive scan and distributed subplan execution for a query involving a subquery with a LIMIT clause.

```text
GroupAggregate  (cost=0.00..0.00 rows=0 width=0)
  Group Key: remote_scan.page_id
  ->  Sort  (cost=0.00..0.00 rows=0 width=0)
    Sort Key: remote_scan.page_id
    ->  Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0)
      ->  Distributed Subplan 6_1
        ->  Limit  (cost=0.00..0.00 rows=0 width=0)
          ->  Sort  (cost=0.00..0.00 rows=0 width=0)
            Sort Key: COALESCE((pg_catalog.sum((COALESCE((pg_catalog.sum(remote_scan.worker_column_2))::bigint, '0'::bigint))))::bigint, '0'::bigint) DESC
            ->  HashAggregate  (cost=0.00..0.00 rows=0 width=0)
              Group Key: remote_scan.page_id
              ->  Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0)
                Task Count: 32
                Tasks Shown: One of 32
                ->  Task
                  Node: host=localhost port=9701 dbname=postgres
                  ->  HashAggregate  (cost=54.70..56.70 rows=200 width=12)
                    Group Key: page_id
                    ->  Seq Scan on page_views_102008 page_views  (cost=0.00..43.47 rows=2247 width=4)
      Task Count: 32
      Tasks Shown: One of 32
      ->  Task
        Node: host=localhost port=9701 dbname=postgres
        ->  HashAggregate  (cost=84.50..86.75 rows=225 width=36)
          Group Key: page_views.page_id, page_views.host_ip
          ->  Hash Join  (cost=17.00..78.88 rows=1124 width=36)
            Hash Cond: (page_views.page_id = intermediate_result.page_id)
            ->  Seq Scan on page_views_102008 page_views  (cost=0.00..43.47 rows=2247 width=36)
            ->  Hash  (cost=14.50..14.50 rows=200 width=4)
              ->  HashAggregate  (cost=12.50..14.50 rows=200 width=4)
                Group Key: intermediate_result.page_id
                ->  Function Scan on read_intermediate_result intermediate_result  (cost=0.00..10.00 rows=1000 width=4)


```

--------------------------------

### Get Total Distributed Table Size

Source: https://docs.citusdata.com/en/v13.0/develop/api_udf.html

Calculates the total disk space used by all shards of a distributed table, including all indexes and TOAST data.

```sql
SELECT pg_size_pretty(citus_total_relation_size('github_events'));
```

--------------------------------

### EXPLAIN Output: Subquery Fragment Execution

Source: https://docs.citusdata.com/en/v13.0/develop/reference_processing.html

This snippet shows the execution plan for the subquery fragment, including Limit, Sort, HashAggregate, and a sequential scan on a partitioned table.

```text
        ->  Limit  (cost=0.00..0.00 rows=0 width=0)
          ->  Sort  (cost=0.00..0.00 rows=0 width=0)
            Sort Key: COALESCE((pg_catalog.sum((COALESCE((pg_catalog.sum(remote_scan.worker_column_2))::bigint, '0'::bigint))))::bigint, '0'::bigint) DESC
            ->  HashAggregate  (cost=0.00..0.00 rows=0 width=0)
              Group Key: remote_scan.page_id
              ->  Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=0 width=0)
                Task Count: 32
                Tasks Shown: One of 32
                ->  Task
                  Node: host=localhost port=9701 dbname=postgres
                  ->  HashAggregate  (cost=54.70..56.70 rows=200 width=12)
                    Group Key: page_id
                    ->  Seq Scan on page_views_102008 page_views  (cost=0.00..43.47 rows=2247 width=4)

```