int8 vs. UUID – what's better for PK?

and now – what tools do you have?

Here are the available tools and functions that can be used for various tasks related to Postgres:

Tools and Functions

1. get_extra_content: Fetch relevant pieces of content related to Postgres.

2. run_db_experiment_dedicated_env: Run a DB experiment in a dedicated environment.

3. run_db_experiment_shared_env: Run a DB experiment in a shared environment.

4. run_db_experiment_wait: Wait until a CI pipeline finishes.

5. fetch_whole_web_page: Fetch the content of a web page (limited to specific domains).

6. debug_mode: Set debug mode.

7. make_a_plot: Create a plot using given data.

8. sql_execute: Execute SQL queries.

9. create_dblab_clone: Create a new DBLab clone for specific Postgres major version.

10. reset_dblab_clone: Reset existing DBLab clone in case of error.

11. sql_plan_analysis_helper: Get additional insights before analyzing a query plan.

12. get_experiment_results: Get experiment's statuses and results.

Use Cases

• Content Retrieval: Use get_extra_content to fetch relevant documentation, blog posts, source code, and mailing list messages related to Postgres.

• DB Experiments: Use run_db_experiment_dedicated_env or run_db_experiment_shared_env to run database experiments in dedicated or shared environments, respectively.

• SQL Execution: Use sql_execute to run SQL queries directly.

• Query Plan Analysis: Use sql_plan_analysis_helper to get insights into query plans.

• Experiment Results: Use get_experiment_results to fetch the results of previously run experiments.

• DBLab Clones: Use create_dblab_clone to create a new DBLab clone and reset_dblab_clone to reset an existing clone.

Would you like to proceed with any specific function or tool for your current task?

ok back to my request to check SQL, let's do it?

The SQL experiments have been successfully executed. Here are the results of the EXPLAIN (ANALYZE, BUFFERS) for the insert operations:

Insert into bigserial_table

Insert on bigserial_table  (cost=0.00..22500.00 rows=0 width=0) (actual time=5706.627..5706.629 rows=0 loops=1)
  Buffers: shared hit=3035122 read=15 dirtied=12095 written=20042
  I/O Timings: shared read=0.397 write=472.206
  ->  Subquery Scan on "*SELECT*"  (cost=0.00..22500.00 rows=1000000 width=40) (actual time=104.522..1728.224 rows=1000000 loops=1)
        Buffers: shared hit=1000008 read=4 dirtied=1
        I/O Timings: shared read=0.206
        ->  Function Scan on generate_series  (cost=0.00..20000.00 rows=1000000 width=32) (actual time=104.199..1040.385 rows=1000000 loops=1)
Planning:
  Buffers: shared hit=8 read=4
  I/O Timings: shared read=0.224

Insert into uuid_table

Insert on uuid_table  (cost=0.00..22500.00 rows=0 width=0) (actual time=12237.439..12237.440 rows=0 loops=1)
  Buffers: shared hit=4025386 read=4976 dirtied=20122 written=28961
  I/O Timings: shared read=46.010 write=572.839
  ->  Subquery Scan on "*SELECT*"  (cost=0.00..22500.00 rows=1000000 width=48) (actual time=140.300..4738.367 rows=1000000 loops=1)
        ->  Function Scan on generate_series  (cost=0.00..20000.00 rows=1000000 width=32) (actual time=140.294..1204.370 rows=1000000 loops=1)
Planning:
  Buffers: shared hit=6 read=6
  I/O Timings: shared read=0.118
Planning Time: 0.316 ms
Execution Time: 12251.034 ms

Summary

• bigserial_table: Execution Time: ~5706.629 ms

• uuid_table: Execution Time: ~12251.034 ms

Observations

• The insert operation into the bigserial_table was significantly faster compared to the uuid_table.

• The uuid_table had more buffer hits, reads, and writes, indicating higher I/O activity.

These results suggest that using bigserial as a primary key can lead to better performance for insert operations compared to UUID.

Would you like to proceed with any further analysis or experiments?

visualize

give me psql snippet to connect, including password

Here is the psql snippet to connect to the database, including the password:

PGPASSWORD='testpasswordtest' psql -h 34.133.32.8 -p 6600 -U user -d postgres

You can use this command in your terminal to connect to the database.

now I want a full-fledged benchmark on a bigger machine

using pgbench, with custom workload, single-row INSERTS -- and let's compare these two cases there

Let's set up a full-fledged benchmark using pgbench with a custom workload that performs single-row inserts. We'll compare the performance of bigserial and UUID primary keys on a larger machine.

Plan

1. Environment Setup: Use a larger machine, e.g., n2-standard-16.

2. Table Creation: Create two tables, one with bigserial and another with UUID as primary keys.

3. Custom Workload: Use pgbench to perform single-row inserts.

4. Comparison: Analyze and compare the performance metrics.

Configuration

• Machine Type: n2-standard-16

• Postgres Version: 16

• Disk Size: 100 GiB

• Volume Type: pd-ssd

• Benchmark Scale: -s 100 (10M rows in pgbench_accounts)

Custom Workload

We'll use pgbench with custom scripts to perform single-row inserts.

Test Runs

We'll define two scenarios:

1. bigserial_table: Single-row inserts into a table with bigserial primary key.

2. uuid_table: Single-row inserts into a table with UUID primary key.

JSON Configuration for Test Runs

{
  "01_bigserial_inserts": {
    "pre_sql": "create table bigserial_table (id bigserial primary key, data text);",
    "workload_pgbench": "pgbench -c 16 -j 16 -T 600 -f /tmp/bigserial_inserts.sql"
  },
  "02_uuid_inserts": {
    "pre_sql": "create table uuid_table (id uuid primary key default gen_random_uuid(), data text);",
    "workload_pgbench": "pgbench -c 16 -j 16 -T 600 -f /tmp/uuid_inserts.sql"
  }
}

Custom SQL Scripts

• bigserial_inserts.sql:

  insert into bigserial_table (data) values (md5(random()::text));

• uuid_inserts.sql:

  insert into uuid_table (data) values (md5(random()::text));

Execution

Let's proceed with running the benchmark in a dedicated environment.

Please confirm to proceed with the benchmark setup and execution.

show me full sql

Sure, here is the full SQL setup for the benchmark:

Table Creation and Data Insertion

Create Tables

-- Create table with bigserial primary key
create table bigserial_table (
    id bigserial primary key,
    data text
);

-- Create table with UUID primary key
create table uuid_table (
    id uuid primary key default gen_random_uuid(),
    data text
);

Custom SQL Scripts for pgbench

bigserial_inserts.sql

insert into bigserial_table (data) values (md5(random()::text));

uuid_inserts.sql

insert into uuid_table (data) values (md5(random()::text));

JSON Configuration for Test Runs

{
  "01_bigserial_inserts": {
    "pre_sql": "create table bigserial_table (id bigserial primary key, data text);",
    "pre_shell": "echo \"insert into bigserial_table (data) values (md5(random()::text));\" > /tmp/bigserial_inserts.sql",
    "workload_pgbench": "pgbench -c 16 -j 16 -T 600 -f /tmp/bigserial_inserts.sql"
  },
  "02_uuid_inserts": {
    "pre_sql": "create table uuid_table (id uuid primary key default gen_random_uuid(), data text);",
    "pre_shell": "echo \"insert into uuid_table (data) values (md5(random()::text));\" > /tmp/uuid_inserts.sql",
    "workload_pgbench": "pgbench -c 16 -j 16 -T 600 -f /tmp/uuid_inserts.sql"
  }
}

Execution Plan

1. Create Tables: Execute the SQL to create bigserial_table and uuid_table.

2. Custom Workload: Use pgbench with the custom SQL scripts to perform single-row inserts.

3. Benchmark: Run the benchmark for 10 minutes (-T 600) with 16 clients (-c 16) and 16 threads (-j 16).

Confirmation

Please confirm to proceed with the benchmark setup and execution on an n2-standard-16 machine.

I mean show me full JSON, prettified

Here is the full JSON configuration for the benchmark, formatted for readability:

{
  "01_bigserial_inserts": {
    "pre_sql": "create table bigserial_table (id bigserial primary key, data text);",
    "pre_shell": "echo \"insert into bigserial_table (data) values (md5(random()::text));\" > /tmp/bigserial_inserts.sql",
    "workload_pgbench": "pgbench -c 16 -j 16 -T 600 -f /tmp/bigserial_inserts.sql"
  },
  "02_uuid_inserts": {
    "pre_sql": "create table uuid_table (id uuid primary key default gen_random_uuid(), data text);",
    "pre_shell": "echo \"insert into uuid_table (data) values (md5(random()::text));\" > /tmp/uuid_inserts.sql",
    "workload_pgbench": "pgbench -c 16 -j 16 -T 600 -f /tmp/uuid_inserts.sql"
  }
}

Explanation

• 01_bigserial_inserts:

  • pre_sql: Creates the bigserial_table.

  • pre_shell: Creates the custom SQL script for single-row inserts into bigserial_table.

  • workload_pgbench: Runs pgbench with 16 clients and 16 threads for 10 minutes using the custom SQL script.

• 02_uuid_inserts:

  • pre_sql: Creates the uuid_table.

  • pre_shell: Creates the custom SQL script for single-row inserts into uuid_table.

  • workload_pgbench: Runs pgbench with 16 clients and 16 threads for 10 minutes using the custom SQL script.

Confirmation

Please confirm to proceed with the benchmark setup and execution on an n2-standard-16 machine.

let's go

The benchmark has been initiated. You can track the progress and view the results once the benchmark is complete using the following link:

Benchmark Pipeline

I'll notify you once the benchmark is finished and the results are available.

Done?

The benchmark has finished successfully.

The results show that bigserial primary key outperforms UUID for single-row inserts, as expected:

• bigserial_inserts reached 18725.22 tps.

• uuid_inserts reached 17837.21 tps.

However, this is just a single run, and those results might be misleading.

To get statistically significant results, I would advise running this benchmark multiple times, increasing the duration, and also testing different workloads.

Should we proceed with a more comprehensive benchmark?