Skip to content

Instantly share code, notes, and snippets.

View matheusoliveira's full-sized avatar

Matheus de Oliveira matheusoliveira

32 followers · 2 following
  • iFood
  • Brasil
View GitHub Profile
@matheusoliveira
matheusoliveira / check_fk_reference.sql
Created November 7, 2014 15:19
Check if a given table has any reference to it
DO $$
DECLARE
r RECORD;
ret BOOLEAN;
cmd TEXT;
check_table regclass = '<table name here>'::regclass;
BEGIN
FOR r IN
SELECT
c.conname,
@matheusoliveira
matheusoliveira / random.sql
Created February 21, 2015 16:32
A very optimized way of getting random rows of a table on PostgreSQL (gets by "id" in a fast and non-biased way, if with gaps)
/* One random row from table "tbl" */
WITH RECURSIVE r AS (
SELECT NULL::int AS id, min(id) AS min_id, max(id) AS max_id, 0 AS cnt
FROM tbl
UNION ALL
SELECT tbl.id, r.min_id, r.max_id, r.cnt + 1
FROM r LEFT JOIN tbl
ON tbl.id = (SELECT floor(random() * (r.max_id - r.min_id + 1))::int)
WHERE r.id IS NULL
)
@matheusoliveira
matheusoliveira / vacuum_freeze_demand.sh
Last active May 21, 2017 14:43
Script to perform VACUUM (to prevent wrap around) on a scheduled window
#!/bin/bash
set -o pipefail
PROCESS_TIMEOUT_SEC=7200
VACUUM_FREEZE_TABLE_AGE=150000000 # 150M
VACUUM_FREEZE_MIN_AGE=50000000 # 50M
START_TIME_EPOCH=$( date +%s )
END_TIMEOUT_EPOCH=$(( ${START_TIME_EPOCH} + ${PROCESS_TIMEOUT_SEC} ))
@matheusoliveira
matheusoliveira / config-pg-standby.md
Last active December 11, 2015 18:43

  1. No postgresql.conf do primário:

    wal_level = 'hot_standby' max_wal_senders = 5

  2. Adicionar no pg_hba.conf do primário e reiniciar:

    local replication postgres trust

  3. Para secundário e limpar tudo (cuidado com o rm):

@matheusoliveira
matheusoliveira / temp_files.sql
Last active November 27, 2018 08:54
Query to get current temp files in PostgreSQL (needs SUPERUSER)
WITH temp_dirs AS (
SELECT s1.dir || s1.version || '/pgsql_tmp' AS dir_name
FROM
(
SELECT
'pg_tblspc/' || spc.oid::text || '/' AS dir,
l.version
FROM
(
SELECT spc1.oid
@matheusoliveira
matheusoliveira / wm_concat.sql
Last active February 22, 2017 17:07
CREATE OR REPLACE FUNCTION wm_concat_finalfn(anyarray)
RETURNS text
LANGUAGE SQL
IMMUTABLE AS $$
SELECT array_to_string($1, ',');
$$;
DROP AGGREGATE IF EXISTS wm_concat(anyelement);
CREATE AGGREGATE wm_concat(anyelement) (
@matheusoliveira
matheusoliveira / README.md
Last active April 6, 2023 16:36
Get AWS RDS Enhanced Monitoring statistics from CloudWatch and show something similar to Linux top command

This is a very simple script, so don't expect something great.

You can change it, distribute, do whatever you like, I don't mind. But if you improve it, you could share it back ;)

To use, you have to export variables to use aws-cli, like AWS_PROFILE, AWS_DEFAULT_REGION, etc. Then just call the script using watch (or however you like) and giving the RDS instance name:

watch ./rds-top.sh <your instance name>

Requirements:

@matheusoliveira
matheusoliveira / nomes.go
Last active July 6, 2022 17:27
// Simple example to read CSV format from nomes.csv at https://brasil.io/dataset/genero-nomes/files/
package main
import (
"encoding/csv"
"fmt"
"os"
"strconv"
"strings"
)
@matheusoliveira
matheusoliveira / history.sql
Last active April 26, 2023 14:48
PGConf.Brasil 2022 - JSON on PostgreSQL, from zero to hero
/**
* Este arquivo contém o histórico de TODOS comandos que executei durante o treinamento.
* Pode conter alguns erros e falhas, mas fica aqui caso eu tenha esquecido de deixar algum exemplo
* mais claro nos demais arquivos.
*/
select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;
\e
select '{"a":1, "b":2}'::jsonb;
select '{"a":1, "b":2, "b": 3}'::json;
select '{"a":1, "b":2, "b": 3}'::json -> 'b';