Entries tagged with postgresql

Entry tags:

Сравнение требуемого места для различных вариантов хранения флагов в PostgreSQL

Давненько я ничего про PostgreSQL не писал.
Вот исправляюсь.

Как известно таблицы с узкими строками и большим количеством строк в PostgreSQL могут быть неожиданно дорогими по месту на диске.
Так как в PostgreSQL есть 24 байт заголовка + 4 байт в ItemData на строку см: http://www.postgresql.org/docs/9.3/interactive/storage-page-layout.html .

Поэтому (хоть это и нарушение разнообразных нормальных форм) зачастую полезно (если есть возможность) группировать данные в одну строку (с использованием массивов и других структур).

( Read more... )

Entry tags:

Большие offset в запросе, 9.2 PostgreSQL и IOS (index-only scans)

Давно я не писал ничего интересного по работе и PostgreSQL.

Но вот недавно нашел случайно весьма забавную вещь.
Как известно большие offset в запросах к PostgreSQL это зло... но была надежда что в 9.2 с появлением IOS на запросах по проиндексированному полю эта проблема станет несколько легче/проще.
( Read more... )

Entry tags:

Рабочее - задумчиво о пути дао в исполнении pgbouncer

Вот недавно при перезапуске pgbouncer получил ошибку с милейшей записью в лог файл:

[root@db-1 pgadmin]# tail -f /var/log/pgbouncer.log
...
2013-05-14 03:50:32.126 15709 FATAL Cannot open logfile: '/var/log/pgbouncer.log': Permission denied

Вот как это можно было получить интересно...

Entry tags:

Читать всем кто занимается разработкой WEB сайтов

Вот просто очень очень очень жизненно и все именно так и происходит на практике:

http://www.slideshare.net/OReillyOSCON/unbreaking-your-django-application

(от http://hydrobiont.livejournal.com/ )

Entry tags:

Оптимизация работы запросов Postgresql с длиными списками значений в IN ()

Я давно знал что PostgreSQL не любит длинные списки в IN в условиях запроса.
Но вот недавно я окончательно достался от этой проблемы и решил посмотреть нет ли более эффективных workaround пригодных хотябы в части ситуаций (так как вижу эту проблему в куче мест у моих клиентов и у знакомых тоже).

( Read more... )

Выводы:
Если IN список в запросе это primary key поле - ничего с ним сделать не получится (там и так производительность оптимальная).
Если есть подходящий под IN поле составной индекс - ситуация неоднозначная но на списках длиннее 1000 JOIN с VALUES начинает выигрывать и чем дальше чем больше.
Если же у вас IN список не попадает ни под какой индекс и есть другие селективные условия с использованием имеющихся индексов - вариант с JOIN VALUES оказывается быстрее фактически всегда.
Надо учитывать что рассматривается ситуация когда база или в памяти или на ssd (т.е. нет необходимости учитывать время поиска данных на hdd).
Так что как всегда прежде чем применять ту или иную оптимизацию - обязательно делать EXPLAIN ANALYZE на реальных данных и по возможности на реальных запросах.

Entry tags:

Ограничение возможности менять 1 поле в таблице в PostgreSQL

Недавно столкнулся с следующей задачей:

Есть старая база, куча кода написанного к ней, местами ORM и так далее (и никакого API из хранимок естественно).
И в какой то момент для древней таблицы account надо запретить изменять поле balance иначе чем через вызов специальной хранимки balance_tranfer (не хочется чтобы из за сбоя в коде где то баланс взял и обнулился без audit-trail и чтобы нельзя было просто себе написать миллион на счету).

( Read more... )
Таким образом обеспечивается и работоспособность старого кода и новые бизнес-требования.

Entry tags:

Не прошло и года с начала работ

Как наконец запустился мой сайт-визитка:
http://www.postgresql-consulting.ru/
и (доконца не вычитанная) английская версия
http://www.postgresql-consulting.com/

Самое веселое было ловить синтаксические ошибки в заголовках главных страниц.

Вообще год на создание и запуск сайта-визитки это какой то ужас и признание моей полной неспособности как организатора :(.

Теперь осталось взять рекламу в поисковиках и посмотреть что из всего этого безобразия получится.

Временно под замком.

Entry tags:

Поедание слона по частям или разбор запроса из предыдущего поста.

Разбор WITH RECURSIVE запроса из предыдущего поста.
( Read more... )

Entry tags:

И опять SQL

По итогам анализа всех вышеобсужденных методов получить M свежих постов от K пользователей они все мне не понравились. И вот к чему я пришел:
( Read more... )

Entry tags:

Разбор запроса из предыдущего поста

Поскольку после публикации http://astarsan.livejournal.com/5751.html поступило 4 жалобы на взорванный мозг я решил усугубить проблему и опубликовать подробный разбор запроса и что собственно там происходит (иллюстрируя для наглядности какие то куски кодом на Perl).

( Read more... )

Current Location: Australia, Sydney

Entry tags:

Очередная SQL задачка (опять из жизни)

Итого дано:

таблица комментариев вида:

id integer primary key,
blog_id integer not null,
ctime timestamp not null,
message text
( Кому интересно читайте дальше... )

Current Mood: angry

Entry tags:

9.1 psql shell и кодировки

Я вчера весь день убил на вот эту шутку которую в 9.1 ввели:

=======================================================

> Description:        Is ALTER ROLE set client_encoding broken in 9.1?

No, but I think psql prefers to set its encoding according to its locale
enviroment these days.

   regards, tom lane
=======================================================

На практике я пытался протестировать стандартный путь организации работы legacy application 
с UTF8 базой через установку client_encoding для пользователя.

Ну и естественно проверял все в psql где теперь:
=====================================================
If at least one of standard input or standard output are a terminal, 
then psql sets the client encoding to auto, 
which will detect the appropriate client encoding from the
locale settings (LC_CTYPE environment variable on Unix systems).
=====================================================

В итоге это умничание завело меня в полный тупик.

Ну и заодно как это выглядит на практике работа с этим слишком умным софтом:

postgres@db13:~$ psql -U slony -d test -c 'show client_encoding'
client_encoding
-----------------
UTF8
(1 row)

postgres@db13:~$ psql -U slony -d test -c 'show client_encoding' | head -10
client_encoding
-----------------
KOI8R
(1 row)

Уууу.... кругом враги и вредители!!!!

Current Location: Australia, Sydney

Entry tags:

postgresql,
sql

Быстрый подсчет distinct values по индексированным полям

Читая форумы я натолкнулся на факт что Mysql умеет использовать индексы для поиска уникальных значений.
И в принципе это логично если уникальных значений не много.

PostgreSQL так не умеет.
Подсчет уникальных значений в большой таблице достаточно частый головняк для Postgresql DBA так как любит тормозить.
Пришлось обьяснять.
( Read more... )

Entry tags:

Давно ожидавшиеся ответы на sql задачи из http://astarsan.livejournal.com/2016.html

Итого давно заждавшиеся ответы на задачи из http://astarsan.livejournal.com/2016.html

( Ответы с подробным разбором решения )

Current Location: New Zealand, Auckland

Entry tags:

Почти неблокирующая реализация vacuum full

По мотивам http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/
Я за недельку написал и отладил в 100 где то раз более быструю реализацию той же идеи с нормальным управлением и не насилующую триггера ON UPDATE (если таковые есть на таблице).

брать здесь:
svn checkout http://compacttable.googlecode.com/svn/trunk/ compacttable-read-only

На тестовом примере созданном следующим образом:

DROP TABLE IF EXISTS __test;
CREATE TABLE __test as select id,random() as f1,random() as
f2,random()::text as f3,now() as mtime,(random()>1)::boolean as flag
FROM generate_series(1,10000000) as t(id);
DELETE FROM __test where id%5<>0;
ALTER TABLE __test add primary key (id);
CREATE INDEX __test_f1_key ON __test(f1);
CREATE INDEX __test_f2_key ON __test(f2);
CREATE INDEX __test_f3_key ON __test(f3);
VACUUM __test;
CREATE OR REPLACE FUNCTION __set_mtime() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.mtime = clock_timestamp();
return NEW;
END;
$$;
CREATE TRIGGER set_mtime
BEFORE UPDATE ON __test
FOR EACH ROW
EXECUTE PROCEDURE __set_mtime();

SELECT sum(pg_relation_size('public.'

||indexname))::bigint/current_setting('block_size')::bigint
FROM pg_indexes WHERE schemaname='public' AND tablename='__test';
SELECT pg_relation_size('__test')/current_setting('block_size')::bigint;

По итогам сравнительного тестирования получилось у меня:

1)VACUUM FULL __test;
Table size (pages) 113574 -> 22714
Index size (pages) 26364 -> 51616 (index bloat 95%)
Time: 211873,227 ms (3.5 minutes)
(3.5 минуты таблица недоступна вообще ни для чего ну и индексы в двое распухли)

vs

2)time ./vacuum_table.pl --table=__test
Table size (pages) 113574 -> 23594
Index size (pages) 26364 -> 32242 (index bloat: 22%)
real 12m10.300s
(в 4 раза медленее зато никакой блокировки таблицы и индексы не пухнут).

Уже было использовано в боевых условиях для ужатия таблицы распухшей из-за идиотов програмистов до 200Gb (ужалось до 20Gb) (потребовалось всего около 18 часов при этом таблица все это время оставалась доступной для работы что было критично в данном случае).

В общем получилась вполне рабочая тулза (8.4+ only) к сожалению которая если бы была под рукой последние 2 года сэкономила бы мне кучу времени и мороки.

Из того что надо отметить из тонкостей:
1)Postgres 8.4 и выше
2)pl/pgsql установленный в базе
3)superuser доступ
4)не работает если есть 'always' или 'replica' триггеры (я за свою жизнь ни одного не видел в реальной задаче)
5)не может ужать распухший TOAST (увы)
6)слегка индексы пухнут но именно слегка а не в 2 раза как от VF
7)на активной таблице мождет deadlock давать иногда но я пока в реальности не видел
8)может давать серьезный index bloat если запустить паралельно с очень долгой транзакцией
9)игнорит fillfactor у таблтицы (увы)
10)чтобы в конце процесса отрезать чистые страницы в конце таблицы всеравно понадобится короткий exclusive lock
11)всякие баги невыловленные заранее (тестировал как мог)

Последние обновления:
1)Добавлен ключ --all для обработки всех таблиц в базе за 1 вызов
2)Добавлена эвристика которая оценивает насколько таблица распухла (и не мучает ее почем зря если там нечего ужимать (меньше 30% свободного места). Отключается через --force
3)Добавлена возможность проводить конкурентный reindex через --perform-reindex
4)поддержка работы через DBD::Pg (может ускорять до 2х раз процесс)
5)полностью переделана структура программы так чтобы ее можно было дальше развивать (теперь это не скрипт сваяный на коленке за 1 день а что то похожее на нормальный код)
6)добавлено много уровней verbosibility (--verobse-level=) (by default =1 наиболее удобный 2 но он говорливый)
7)исправлена куча мелких багов вылезающих на всяких нестандартных таблицах и базах

ToDo:
1)обработка всех баз оптом в пределах одного кластера (по аналогии с vacuumdb -a)
пока можно эту штуку эмулировать через:
psql -t -c 'SELECT datname from pg_database where datallowconn is true order by pg_database_size(datname)' | perl -ne 'chomp; if (/^\s(\S+)/) {my $cmd="time ./vacuum_table.pl --host=127.0.0.1 --all --force --perform-reindex --verbose-level=2 --dbname=$1 > $1.log"; print "executing cmd: $cmd\n"; system $cmd;}'

2)попробовать еще ускорить (есть пара идей) но уже сейчас raid10 из 4х дисков при --delay-ratio=0 оно на 100% может нагрузить

3)добавить побольше отладочной иформации при --verbose-level > 2

4)сделать наконец нормальную документацию на английском и описание логики работы

5)(сложно весьма) - сделать возможность при упаковке таблицы задать индекс по которому будет произведена автоматическая кластеризация (задача более чем нетривиальная на самом деле но у меня есть идеи как к ней подобраться)

(Reply) (Thread)

Entry tags:

Задачка (-и) по SQL на подумать

Задача из практики. Уже решенная для postgres. Решать на чистом SQL (т.е. без хранимок).
Хорошее решение не должно иметь N^2 или еще худшего поведения от длинны интервала.

Итого есть таблица:

user_id integer, session_start timestamp, session_end timestamp
(для простоты по всем полям есть индексы).
(фактически сессии пользоваталей в online)

Для заданного интервала времени (интервал задается начальным и конечным timestamp) подсчитать:

1)среднее количество пользователей online в этом интервале (просто если придумать как)

2)максимальное и минимальное значение пользователей в online в заданном интервале (это уже будет посложнее)

На всякий случай во избежание заведомо неверных решений сессии могут начинатся до начала рабочего интервала и одновременно заканчиватся после начала рабочего интервала (и это надо учитывать).

Have a fun.