cabecada

many times there are tables with 10s of references in other tables. when we try to delete data cascade, sometimes deletes are very slow.
we have an index on the column of the primary table and some of the foreign tables, but we dont know why the delete is slow.
here we try to make use of auto_explain with gucs to track plans of foreign tables to figure out slow deletes and how we fix them



postgres@ubuntu:/tmp$ cat db1/postgresql.auto.conf | grep -v '^#'
port=5432
session_preload_libraries = auto_explain
auto_explain.log_min_duration = 0

cabecada

postgres@ubuntu:/tmp$ psql -p 5432
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# create database demo;
CREATE DATABASE
postgres=# \c demo
You are now connected to database "demo" as user "postgres".
demo=# select * from pg_database;
demo=# select oid,datname from pg_database;

cabecada

postgres=# create table t(col1 int primary key, col2 int, col3 int);
CREATE TABLE
postgres=# create table r(col4 int primary key, col1 int references t(col1) on update cascade on delete cascade);
CREATE TABLE
postgres=# insert into t select x,x,x from generate_series(1, 1000000) x;
insert into r select col1, 1+ col1 % 10 from t;
INSERT 0 1000000
INSERT 0 1000000
postgres=# create table to_be_deleted as select col1, null::int as col2 from t where col2 % 9 = 3;
SELECT 111111

cabecada

This recipe is a work in progress and has never been run as-is.

• timeouts are in ms
• lock timeout: in postgres, when a statement that wants a restrictive lock waits on another lock, other statements that want locks can't jump the queue. so even though the statement that is waiting might only take a very short amount of time, when it starts running, while it is waiting no other statements can begin. So we set the lock timeout pretty low and retry if we don't get it.
• statement timeout: we set a short statement timeout before statements which do lock and which we expect to take a short amount of time, just in case something about our assumptions/understanding is wrong and the statement ends up taking a long time. if this happens the statement will bail early without causing harm, and we can investigate what is wrong with

cabecada

https://github.com/pgbouncer/pgbouncer/issues/982
https://www.pgbouncer.org/config.html


client -> pgbouncer(fe postgres server crt on 172.x.x.x) -> pgbouncer (be pgbouncer client crt) -> postgres (on 127.x.x.x)

  519  openssl ecparam -name prime256v1 -genkey -noout -out ca.key
  520  openssl req -new -x509 -sha256 -key ca.key -out ca.crt -subj "/CN=pg.mshome.net"
  522  openssl ecparam -name prime256v1 -genkey -noout -out server.key
  

cabecada

https://blog.dalibo.com/2022/09/19/psycopg-pipeline-mode.html

https://www.psycopg.org/psycopg3/docs/api/pq.html

conn.pgconn.trace(sys.stderr.fileno())
conn.pgconn.set_trace_flags(pq.Trace.SUPPRESS_TIMESTAMPS | pq.Trace.REGRESS_MODE)
conn.execute("select now()")
F       13      Parse    "" "BEGIN" 0
F       14      Bind     "" "" 0 0 1 0
F       6       Describe         P ""

cabecada

 ./configure --prefix /opt/17/usr/local --enable-tap-tests --with-perl --with-python --with-openssl --enable-dtrace --enable-debug --enable-cassert CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -DWAL_DEBUG"
 make 
 make install
 
 export PATH=/opt/17/usr/local/bin:$PATH
 
 select name,setting from pg_settings where name ~ 'trace.*locks';
      name       | setting
-----------------+---------
 trace_locks     | on

cabecada

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:region:account-id:dbuser:dbi-resource-id/database-user-name"

cabecada

Accomplished System Architect with over 13 years of hands-on experience managing large-scale databases, including those of 10s of 60TB scale. Proficient in designing and implementing robust monitoring and logging solutions to ensure optimal performance and reliability. Demonstrated expertise in building highly scalable architectures capable of handling substantial data volumes while maintaining seamless operations. Solid experience in facilitating ease of migration, upgrades, and incident management processes, ensuring minimal disruption and downtime. Adept at generating insightful reports to provide stakeholders with comprehensive visibility into system health and performance metrics. Proven track record of optimizing database environments for maximum efficiency and resilience in dynamic business landscapes.

Skills:

Systems Architecture               (Generl architecture meeting and discussions, making POCs in testlabs)  
Reliability Engineering            (uptime, fault injection and tolerance, upgrades, 

cabecada

postgres@pg:~/poc/patroni$ git status
On branch master
Your branch is up to date with 'origin/master'.

Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git restore <file>..." to discard changes in working directory)
        modified:   Dockerfile.citus
        modified:   docker-compose-citus.yml
        modified:   postgres0.yml