Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

Oracle to Azure PostgreSQL Lessons learnt

Oracle to Azure PostgreSQL Lessons learnt

AwdotiaRomanowna

December 14, 2021
Tweet

More Decks by AwdotiaRomanowna

Other Decks in Technology

Transcript

  1. CREATE EXTENSION pg_partman WITH SCHEMA partman; SELECT partman.create_parent('github.events', 'created_at', 'native',

    'hourly'); UPDATE partman.part_config SET retention_keep_table = false, retention = '1 month' WHERE parent_table = 'github.events';
  2. Not only plpgsql CREATE FUNCTION foo(out f2 text) AS $$

    SELECT 'BAR' $$ LANGUAGE SQL; https://www.postgresql.org/docs/current/sql-createfunction.html
  3. Data migration # PERFORMANCES SECTION (Control export/import performances) JOBS 1

    ORACLE_COPIES1 PARALLEL_TABLES 1 DEFAULT_PARALLELISM_DEGREE 0 PARALLEL_MIN_ROWS 100000 # DATA SECTION (Control data export behaviors) DATA_LIMIT 10000
  4. Configuration log_min_duration_statement = 0 log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h

    ' logging_collector = on log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_temp_files = 0 log_autovacuum_min_duration = 0 log_error_verbosity = default Rotate the log: SELECT pg_rotate_logfile();
  5. Easier than clicking az postgres server-logs list -g [resource_group] -s

    [server-name] --query '[].{Name:name}' -o tsv |xargs -I {} az postgres server-logs download -g [resource_group] -s [server-name] -n {}
  6. Database Only tuning • Storage tuning (indexes, fillfactor, statistics etc.)

    • GUC (Grand Unified Configuration) Tuning using GUC-es is NOT the best way of tuning and should not be the chosen method. It was used because of time constraints and no-code changes. Allows to showcase that expected performance level can be achieved (PoC). *https://www.postgresql.eu/events/pgconfeu2017/sessions/sessio n/1617/slides/9/FromMinutesToMilliseconds.pdf
  7. Example Query 01 Changing a GUC join_collapse_limit to 1 with

    the following command: SET JOIN_COLLAPSE_LIMIT TO 1; Reduced the time of query execution from 682715.083 ms to 1217.904 ms (43% of initial value).
  8. Example Query 02 Adding the following index: CREATE INDEX ON

    table(column1, column2 DESC); Reduced the time of query execution from 2 373 ms to 905 ms (38% of initial value).