Skip to content

Instantly share code, notes, and snippets.

View FranckPachot's full-sized avatar

Franck Pachot (YB) FranckPachot

72 followers · 9 following
View GitHub Profile
@FranckPachot
FranckPachot / IndexingWorkshop-DSS2024.sql
Created November 12, 2024 16:50
IndexingWorkshop-DSS2024.sql
# start a YugabyteDB node
docker run -d --name yb yugabytedb/yugabyte:2.23.0.0-b710 \
yugabyted start --enable_pg_parity_tech_preview --background=false
# start PostgreSQL client
docker run --rm -it --link yb:yb \
-e PGHOST=yb -e PGPORT=5433 -e PGUSER=yugabyte postgres psql
select version();
@FranckPachot
FranckPachot / yb-ash.sql
Last active June 18, 2024 17:44
create extension if not exists postgres_fdw;
select format('
create server if not exists "gv$%1$s"
foreign data wrapper postgres_fdw
options (host %2$L, port %3$L, dbname %4$L)
', host, host, port, current_database()) from yb_servers();
\gexec
select format('
drop user mapping if exists for admin
server "gv$%1$s"
@FranckPachot
FranckPachot / gist:50a6a491b85b0ddb3da6399d54653085
Last active January 6, 2024 12:50
1brc in SQL
-- SQL implementation of https://github.com/gunnarmorling/1brc
--
-- get the basic feed data as insert statements in weatherstations.sql
--
/* run in shell to build the 400 lines feed data that was hardcoded in CreateMeasurements.java :
curl -s https://raw.githubusercontent.com/gunnarmorling/1brc/main/src/main/java/dev/morling/onebrc/CreateMeasurements.java | awk '
# Oracle Database # BEGIN{print "begin"} END{ print "end;" ; print "/" }
@FranckPachot
FranckPachot / isolation-levels.html
Created December 22, 2023 08:19
https://dev.to/franckpachot/isolation-levels-part-ii-characteristics-and-use-case-21e0
<table>
<tbody><tr>
<td>
<strong>MVCC Isolation level</strong>
</td>
<td>
<strong>Serializable Snapshot Isolation</strong>
</td>
<td>
<strong>Snapshot Isolation</strong>
@FranckPachot
FranckPachot / joins_scale_on_yuagybte.sql
Last active April 24, 2023 11:32
For the next time you hear that "Joins Don't Scale": Joining to a 7M rows table to return 20K rows with text search filters in 50 milliseconds on @yugabyte​DB and that scales as the plan doesn't depend on the table size😎 SQL databases rocks 🚀
-- load names
\! wget -cO/var/tmp/baby-names.csv "https://github.com/hadley/data-baby-names/blob/master/baby-names.csv?raw=true"
drop table if exists baby_names;
create table baby_names ( year int , name text, percent float, sex text, primary key (name,year, sex));
\copy baby_names from '/var/tmp/baby-names.csv' with ( skip 1, format csv );
-- joins scale
drop table if exists users,messages cascade;
create table users (
primary key (user_id)
@FranckPachot
FranckPachot / oracle_home_unwrap.sql
Created April 6, 2023 13:01
Unwrap Oracle Home package bodies
export PATH=$PATH:$ORACLE_HOME/python/bin
pip install html2text
for f in $(grep -l ' wrapped *$' $(find $ORACLE_HOME -name "*.plb"))
do
curl -s -F "file=@$f" -F "ShowLineNumbers=false" -F "UnwrapFile=Unwrap File" https://www.codecrete.net/UnwrapIt/ |
html2text | awk '
/Unwrap More Code/{p="Y";next}
/Bleichenbacher/{print "--",$0;next}
p=="Y"{gsub(/ʼ/,qq);print}
' qq="'" > ${f}.unwrapped && wc ${f}.unwrapped
@FranckPachot
FranckPachot / yb_emp_dept.sql
Last active March 21, 2023 08:49
The EMP and DEPT tables used in the cloud.yugabyte.com tutorial
/* Load from psql:
\! curl -s https://gist.githubusercontent.com/FranckPachot/ec2b586d18965a4571a544c2b862e6a7/raw/fef5faa450ab25949b6bd35f7af9dbc48e89c068/yb_emp_dept.sql > yb_emp_dept.sql
\i yb_emp_dept.sql
*/
drop table if exists emp;
drop table if exists dept cascade;
CREATE TABLE IF NOT EXISTS dept (
@FranckPachot
FranckPachot / Dockerfile
Last active March 14, 2023 11:40
Dockerfile reversed engineered from yugabytedb/yugabyte for almalinux:8
# take the following from the public image: /home/yugabyte /etc/yum.repos.d/CentOS.repo /etc/yum.repos.d/almalinux8.repo /usr/bin/NOTICE.txt /usr/bin/azcopy /usr/local/gsutil
FROM yugabytedb/yugabyte:2.17.1.0-b439 as public_image
# remove those that are re-created later
RUN rm -rf /home/yugabyte/{master,tserver}
FROM almalinux:8
ENV RELEASE=4
ENV USER=root
@FranckPachot
FranckPachot / tombstone-grafana.json
Created November 8, 2022 13:30
Grafana dashboard fo rblog post https://dev.to/yugabyte/lsm-tree-tombstones-and-yugabytedb-31cc
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
@FranckPachot
FranckPachot / gist:fcd11b5a63b7512cfe3404ed61a3fa53
Created July 7, 2022 18:14
Testing PostgreSQL text in Hibernate
/*
cd /var/tmp
wget -c https://netix.dl.sourceforge.net/project/hibernate/hibernate-orm/5.6.0.Final/hibernate-release-5.6.0.Final.zip
unzip -qo hibernate-release-5.6.0.Final.zip
for i in hibernate-release-5.6.0.Final/lib/required/*.jar
do
CLASSPATH="${CLASSPATH}:$PWD/$i"
done
wget -c https://jdbc.postgresql.org/download/postgresql-42.3.0.jar
export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc8.jar:$PWD/postgresql-42.3.0.jar:$CLASSPATH