👽

Stéphanie sbaltus

👽

sbaltus / idempotence_and_commutativity_devoxxfr_demo.sql

Last active June 21, 2022 07:24

	docker run -t -e POSTGRES_PASSWORD=WHATEVER -e POSTGRES_DB=devoxx_demo -p 5555:5432 --name pg postgres
	psql -U postgres -p 5555 -h localhost devoxx_demo

	CREATE TABLE IF NOT EXISTS users_stats (user_id int, email varchar(128), nb_dog_ads int, nb_castle_ads int, version timestamp);
	CREATE TABLE IF NOT EXISTS dataset_a (user_id int, email varchar(128), nb_dog_ads int, nb_castle_ads int, version timestamp);
	CREATE TABLE IF NOT EXISTS dataset_b (user_id int, email varchar(128), nb_dog_ads int, nb_castle_ads int, version timestamp);

	TRUNCATE users_stats;
	TRUNCATE dataset_a;
	TRUNCATE dataset_b;

sbaltus / refresh_concurrently.sql

Created January 27, 2018 19:00

Refresh mat view

	CREATE UNIQUE INDEX IF NOT EXISTS fomv_uq_jo_id ON job_offer_views_mv (job_offer_id) ;

	REFRESH MATERIALIZED VIEW CONCURRENTLY job_offer_views_mv;

sbaltus / create_mat_view.sql

Created January 27, 2018 18:57

Create Materialized view

	CREATE MATERIALIZED VIEW IF NOT EXISTS job_offer_views_mv AS
	SELECT job_offer_id
	, COUNT(*) AS view_count
	, COUNT(distinct session_id) AS unique_view_count
	FROM views
	GROUP BY job_offer_id;

sbaltus / TroubleShooting PG

Last active January 18, 2018 15:59

Useful administration queries

	-- Lock management
	https://wiki.postgresql.org/wiki/Lock_Monitoring

	SELECT relname,
	mode,
	relation,
	database,
	pid,
	query
	FROM pg_locks

sbaltus / creating_a_date_dimension_table_in_postgresql.sql

Last active January 12, 2018 16:16 — forked from duffn/creating_a_date_dimension_table_in_postgresql.sql

Creating a date dimension table in PostgreSQL

	CREATE SCHEMA IF NOT EXISTS common;
	CREATE TABLE IF NOT EXISTS common.d_date
	(
	date_dim_id INT NOT NULL,
	date_actual DATE NOT NULL,
	epoch BIGINT NOT NULL,
	day_suffix VARCHAR(4) NOT NULL,
	day_name VARCHAR(9) NOT NULL,
	day_of_week INT NOT NULL,
	day_of_month INT NOT NULL,