[Postgres] Create partitionned tables and rotate

Script to create partitionned tables and rotate it on one year

PostgreSQL performance Turing Indexes

https://www.timescale.com/learn/postgresql-performance-tuning-optimizing-database-indexes

[Postgres] Fonction pour compter les lignes

Pratique lors d'une migration pour vérifier que toutes les lignes sont bien là !

[Postgres] Mesure the bloat

intéressant mon cher watson

[Postgres] Spot a missing postgreSQL index

--tuto tuning psql
--Spot a missing PostgreSQL index

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

--To find unused index in a db

SELECT
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan =0 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;

--returns the tables which have been hit by sequential scans the most and tells us how many rows a sequential scan has hit on average.
SELECT schemaname, 
              relname, 
              seq_scan, 
              seq_tup_read, 
              idx_scan, 
              seq_tup_read / seq_scan AS avg
 FROM         pg_stat_user_tables 
 WHERE        seq_scan > 0 
 ORDER BY     seq_tup_read DESC;

--Finding slow queries to be executed on pgadmin
SELECT query, 
              total_exec_time, 
              calls, 
              mean_exec_time 
       FROM   pg_stat_statements 
       ORDER BY total_exec_time DESC;

Migration assessment

[Postgres] Découverte d'une DB

Découverte d'une base postgres

PostgreSQL in IBM Cloud.

Création d'une base postgresql avec role hvault en mode batch dans le cloud IBM..

Migration of Synonyms from Oracle to PostgreSQL

Migration of Synonyms from Oracle to PostgreSQL - MigOps

Migration from Oracle Legacy to postgreSQL dMZR

Migration from Oracle Legacy to postgreSQL dMZR