benoittgt

#!/bin/bash
set -e

# Use this script with
# Add space before the command to not save it into you shell history
# ./update_postgresql.sh -h pg-xxx.region.rds.amazonaws.com -p password

# Define default host and password
host="localhost"
user="admin"

benoittgt

class ThreadPool
  def initialize(size)
    @size = size
    @jobs = Queue.new
    @pool = Array.new(@size) do |i|
      Thread.new do
        Thread.current[:id] = i
        catch(:exit) do
          loop do
            job, args = @jobs.pop

benoittgt

-- Create a table with a UUID column
CREATE TABLE your_table (
    id serial PRIMARY KEY,
    uuid_column uuid
);

-- Insert 400 rows with UUIDs
INSERT INTO your_table (uuid_column)
SELECT gen_random_uuid() FROM generate_series(1, 400);

benoittgt

DROP TABLE IF EXISTS notifications;
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

INSERT INTO
  notifications (created_at)
SELECT
  (LOCALTIMESTAMP - interval '1 month' * random())::timestamptz

benoittgt

require 'benchmark/ips'

DataS = Struct.new(:encoder, :values) do # :nodoc:
  def hash
    [encoder, values].hash
  end
end

DataD = Data.define(:encoder, :values) do # :nodoc:
  def hash

benoittgt

-- based on https://www.crunchydata.com/blog/understanding-postgres-iops

SELECT
    interval '1 millisecond' * total_exec_time AS "Total Exec. Time",
    to_char (
        (total_exec_time / sum(total_exec_time) OVER ()) * 100,
        'FM90D0'
    ) || '%' AS "Proportional Exec. Time",
    to_char (calls, 'FM999G999G999G990') AS "Calls",
    interval '1 millisecond' * (blk_read_time + blk_write_time) AS "Time Spent on IO",

benoittgt

bind_param = Arel::Nodes::BindParam.new(ActiveRecord::Relation::QueryAttribute.new('search_text', "%Nate%", ActiveRecord::Type::String.new))
Customer.where(Customer.arel_table["search_text"].matches(
  Arel::Nodes::NamedFunction.new('unaccent', [Arel::Nodes::build_quoted(bind_param)]),
  nil,
  true # case insensitive
))

benoittgt

sql = <<-SQL
SELECT
  s.indexrelname AS indexname,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  s.idx_scan AS number_of_scans
FROM
  pg_catalog.pg_stat_user_indexes s
  JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE
  0 <> ALL (i.indkey) -- no index column is an expression

benoittgt

You well get query that greater than 5000ms with the next line that as often query parameter if you use prepared statements. This commande requires ripgrep.
rg --pcre2 '[6-9][0-9][0-9][0-9]\.[0-9][0-9][0-9] ms.*' -A=1

Get queries that timeout, exclude the one with a COUNT but with a filter on `rented_at`
rg --pcre2 'STATEMENT:  SELECT(?:(?!COUNT).)*rented_at >'

benoittgt

-- Carefull setup script is slow
-- SETUP SCRIPT :: START --
DROP TABLE IF EXISTS docs;
CREATE TABLE docs (
  id SERIAL PRIMARY KEY,
  type varchar(40) DEFAULT 'pdf' NOT NULL,
  status varchar(40) NOT NULL,
  sender_reference varchar(40) NOT NULL,
  sent_at TIMESTAMPTZ,