Skip to content

Instantly share code, notes, and snippets.

View benoittgt's full-sized avatar
🏴

Benoit Tigeot benoittgt

🏴
187 followers · 180 following
View GitHub Profile
@benoittgt
benoittgt / benchmark_define.rb
Last active November 3, 2023 10:45
Struct.new VS Data.define
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
benoittgt / pg_stat_statements.sql
Created October 4, 2023 17:33
Quickly look at pg_stat_statements
-- 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
benoittgt / unaccent_bind.rb
Created September 8, 2023 10:23
Force bind parameter and prepared statement with unaccent query with ActiveRecord and Arel
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
benoittgt / index_usage.rb
Created August 10, 2023 14:49
Report index usage (save every few hours and see difference to spot unused indexes)
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
benoittgt / commands.txt
Created August 7, 2023 09:28
How to fetch slower queries in postgresql logs (from RDS)
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
benoittgt / Issue_with_multiple_any.sql
Last active March 28, 2025 02:37
-- 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,
@benoittgt
benoittgt / psql_table_like_rails_console.rb
Last active June 26, 2023 16:22
PSQL table in Rails console
results = ActiveRecord::Base.connection.execute("SELECT * FROM pg_statio_user_tables;").to_a
@columns = results.first.keys.each_with_object({}) do |col,h|
h[col] = { col: col, width: [results.map { |g| g[col]&.size || 2 }.max, col.size].max }
end
def write_line(h)
str = h.keys.map { |k| h[k].to_s.ljust(@columns[k][:width]) }.join(" | ")
puts "| #{str} |"
end
@benoittgt
benoittgt / redis_key_expiration.rb
Created April 27, 2023 11:22
Check expiration date and cover range with Redis (RedisCacheStore) in Rails
p Rails.cache.delete("key2")
p Rails.cache.write("key2", "value2", expires_in: 2.hours)
p expiration_time = Rails.cache.redis.call("EXPIRETIME", "key2")
raise "No expiration time" if expiration_time == -1
raise "Key not found" if expiration_time == -2
p expirate_date_time = Time.at(expiration_time)
# Propably better way to check if expiration time is correct
@benoittgt
benoittgt / thread_pooling_octocat.rb
Last active March 15, 2023 11:34
require 'gvl-tracing'
require 'net/http'
class ThreadPool
def initialize(size)
@size = size
@jobs = Queue.new # FIFO https://rubyapi.org/3.2/o/thread/queue
@pool = Array.new(@size) do |i|
Thread.new do
catch(:exit) do
@benoittgt
benoittgt / outlets.html
Last active February 17, 2023 16:02
Playing with outlets from Stimulus with names with dash
<!DOCTYPE html>
<html lang="en">
<head>
<title>Outlets doc</title>
<script type="module">
import { Application, Controller } from 'https://esm.sh/@hotwired/stimulus'
const app = Application.start()