benoittgt

At the time of writing this article, PostgreSQL 17 is nearly out. On September 5th, the first release candidate was published. The final release is expected on September 26th, but we can already explain why we’ve been eagerly awaiting this release since 1 year.

At Lifen, we’ve loved Rails from the beginning. We have several Rails applications, each with different scopes and teams, all using PostgreSQL as the main database. Some of these applications handle a significant amount of traffic, and their databases need to be properly monitored. This is done by the infrastructure team and the developers themselves using PgAnalyze, Grafana and sometimes AWS console with "Performance Insight".

More than a year ago, we started monitoring the 95th and 99th percentile response times (p95, p99) on an endpoint that was experiencing timeouts. These p99 times were important because they involved a client with significantly mo

benoittgt

# frozen_string_literal: true
require "bundler/inline"
gemfile(true) do
  source "https://rubygems.org"
  gem "rails"
  gem "pg"
end
require "active_record"
require "minitest/autorun"
require "logger"

benoittgt

require 'socket'

@queue = Ractor.new do
  loop do
    Ractor.yield(Ractor.receive, move: true)
  end
end

listener = Ractor.new(@queue) do |queue|
  socket = TCPServer.new(PORT)

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
))