Category Archives: Databases

PostgreSQL: using indices on json fields

It’s obviously a really bad idea. But if you really need it, that’s what you can do.

Postgresql: show all locked queries

WITH RECURSIVE l AS (
 SELECT pid, locktype, mode, granted,
ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj
 FROM pg_locks
), pairs AS (
 SELECT w.pid waiter, l.pid locker, l.obj, l.mode
 FROM l w
 JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted
 WHERE NOT w.granted
), tree AS (
 SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids
 FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
 UNION ALL
 SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
 FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )
)
SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,
      replace(a.state, 'idle in transaction', 'idletx') state,
      (clock_timestamp() - state_change)::interval(3) AS change_age,
      a.datname,tree.pid,a.usename,a.client_addr,lvl,
      (SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,
      repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query
FROM tree
JOIN pg_stat_activity a USING (pid)
ORDER BY path;

WITH RECURSIVE l AS (

SELECT pid, locktype, mode, granted,

ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj

FROM pg_locks

), pairs AS (

SELECT w.pid waiter, l.pid locker, l.obj, l.mode

FROM l w

JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted

WHERE NOT w.granted

), tree AS (

SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids

FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l

UNION ALL

SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()

FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )

)

SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,

replace(a.state, 'idle in transaction', 'idletx') state,

(clock_timestamp() - state_change)::interval(3) AS change_age,

a.datname,tree.pid,a.usename,a.client_addr,lvl,

(SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,

repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query

FROM tree

JOIN pg_stat_activity a USING (pid)

ORDER BY path;

docker-compose example for ceph, kafka, postgres cluster

That is how it can be done:

Книги по PostgreSQL

Хорошая бесплатная книга на русском языке про оптимизацию — Работа с PostgreSQLнастройка и масштабирование, А. Ю. Васильев. Про разработку и синтаксис — PostgreSQL Development Essentials. Manpreet Kaur, Baji Shaik: на google books можно ознакомиться и скачать на scribd

SQL: выборка разреженных данных (не больше раза в час)

Допустим, мы сохраняем данные в базу раз в 10 минут. Нужно достать данные из базы, начинающиеся с определённого времени и не чаще одного раза за час. В качестве базы используется sqlite3, но идея реализуется так же для других SQL.

SQL. Как переписать correlation subquery в JOIN to derived table

Допустим есть такая таблица

mysqldump

mysqldump -h ←host→ --skip-lock-tables --skip-add-locks --no-data -u ←user→ -p ←db name→ > dump.sql

# inside docker container
mysqldump --skip-lock-tables --skip-add-locks --no-data -u root --password=root ←db name→ > dump.sql

# this one somehow does not work on a host machine
docker-compose -f docker-compose.yml exec mysql /usr/bin/mysqldump --no-data --skip-lock-tables --skip-add-locks -u root --password=root ←db name→ > dump.sql

mysqldump -h ←host→ --skip-lock-tables --skip-add-locks --no-data -u ←user→ -p ←db name→ > dump.sql

# inside docker container

mysqldump --skip-lock-tables --skip-add-locks --no-data -u root --password=root ←db name→ > dump.sql

# this one somehow does not work on a host machine

docker-compose -f docker-compose.yml exec mysql /usr/bin/mysqldump --no-data --skip-lock-tables --skip-add-locks -u root --password=root ←db name→ > dump.sql

on k8s pod you can run this:

apt-get install default-mysql-client
mysqldump --skip-lock-tables --skip-add-locks -h [some host] -u [user] --password=[pass] [db_name] > dump.sql

1 2	apt-get install default-mysql-client mysqldump --skip-lock-tables --skip-add-locks -h [some host] -u [user] --password=[pass] [db_name] > dump.sql

Про нормализацию данных в базе

Набрёл на перевод про третью нормальную форму (ссылка в конце). Перевод вроде бы неплохой. Далее цитата. Нормализация базы данных Попала в руки одна замечательная книжка — PHP 6 and MySQL 5 for Dynamic Web Sites , за авторством Larry Ulman. В целом, книга расчитана на новичков — середнячков, но затрагиваются и довольно серьёзные вещи, при …

Подробное объяснение EXPLAIN в PostgreSQL

understanding_explain

HeidiSQL — бесплатная программа для работы с MySQL и MsSQL Server

http://www.heidisql.com/screenshots.php — бесплатная и opensource. При этом быстрая. Одно плохо — только под Windows. Но под wine летает.