This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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", |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 >' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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, |
NewerOlder