Роман Теличко

Заметки веб-разработчика/админа

Тег postgresql

Партицирование (partitioning) больших таблиц PostgreSQL

// Февраль 17, 2013 | 14912 просмотров | 4 комментария

Сегодня я хочу вам рассказать о партиционировании больших таблиц в моей любимой PostgreSQL.

Итак, начнём с определения:
Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям. Партиционированные или секционированные таблицы призваны улучшить производительность и управляемость базами данных.

Вроде понятно. Теперь идём дальше. Как же разбить таблицу на партиции или секции?
В PostgreSQL эта процедура потребует небольших усилий, но результатом вы будете довольны 🙂
Читать далее…

PostgreSQL. Список всех таблиц

// Январь 31, 2013 | 2183 просмотров | комментариев (1)

Вывести список всех таблиц при помощи SQL довольно просто:

SELECT
        n.nspname               AS "schema",
        c.relname               AS "table"
FROM
        pg_catalog.pg_class AS c 
LEFT JOIN 
        pg_catalog.pg_namespace AS n 
        ON n.oid = c.relnamespace
WHERE
        n.nspname NOT IN ('pg_catalog', 'pg_toast')
        AND
        c.reltablespace > 0
        AND
        c.relkind = 'r'
ORDER BY
        c.relname ASC

В результате получим набор схема-таблица.

PostgreSQL 9.2+ в Ubuntu 12.04+

// Сентябрь 26, 2012 | 286 просмотров | комментариев (0)

PPA с последней версией PostgreSQL устанавливается просто:

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update

И потом всё просто:

sudo apt-get install postgresql-9.2

PostgreSQL. Использование индексов

// Сентябрь 16, 2012 | 1779 просмотров | комментариев (0)

Запрос отображает использование индексов. Что позволяет увидеть наиболее часто использованные индексы, а также и наиболее редко (у которых будет index_scans_count = 0).

Учитываются только пользовательские индексы и не учитываются уникальные, т.к. они используются как ограничения (как часть логики хранения данных).

В начале отображаются наиболее часто используемые индексы (отсортированы по колонке index_scans_count).

SELECT
    idstat.relname    				        AS table_name,                  -- имя таблицы
    indexrelname    				        AS index_name,                  -- индекс
    idstat.idx_scan    			                AS index_scans_count,           -- число сканирований по этому индексу
    pg_size_pretty(pg_relation_size(indexrelid))        AS index_size,                  -- размер индекса
    tabstat.idx_scan    			        AS table_reads_index_count,     -- индексных чтений по таблице
    tabstat.seq_scan    			        AS table_reads_seq_count,       -- последовательных чтений по таблице
    tabstat.seq_scan + tabstat.idx_scan    	        AS table_reads_count,           -- чтений по таблице
    n_tup_upd + n_tup_ins + n_tup_del    	        AS table_writes_count,          -- операций записи
    pg_size_pretty(pg_relation_size(idstat.relid))      AS table_size                   -- размер таблицы
FROM
    pg_stat_user_indexes    			        AS idstat
JOIN
    pg_indexes
    ON
    indexrelname = indexname
    AND
    idstat.schemaname = pg_indexes.schemaname
JOIN
    pg_stat_user_tables    			        AS tabstat
    ON
    idstat.relid = tabstat.relid
WHERE
    indexdef !~* 'unique'
ORDER BY
    idstat.idx_scan DESC,
    pg_relation_size(indexrelid) DESC

pgAdmin III v1.16 в Ubuntu 12.04+

// Сентябрь 14, 2012 | 1227 просмотров | комментариев (0)

Установить последний pgAdmin3 в Ubuntu (или в моём случаи, в Xubuntu) достаточно просто.
Нужно просто знать адрес правильный адрес PPA-репозитория 🙂

Делается так:

sudo apt-add-repository ppa:voronov84/andreyv
sudo apt-get update && sudo apt-get upgrade

И ставим, если ещё до этого не был установлен:

sudo apt-get install pgadmin3

PostgreSQL. Список всех индексов

// Июль 17, 2012 | 3340 просмотров | комментариев (0)

Получить в виде списка все индексы довольно просто.
Достаточно выполнить следующее:

SELECT 
        n.nspname               AS "schema",
        c.relname               AS "index"
FROM 
        pg_catalog.pg_class AS c 
LEFT JOIN 
        pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE
        c.relkind = 'i'
        AND 
        n.nspname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY
        c.relname ASC

И всё!

Кстати, а вот так можно получить список всех неиспользуемых индексов.

PostgreSQL. Список всех дат между двумя датами

// Апрель 6, 2012 | 363 просмотров | комментариев (0)

Узнаём timestamp от интересуещей даты

SELECT
    extract( 'epoch' from '2012-02-01'::timestamp without time zone  )::integer

(это будет 1325455200)

Используем generate_series()

SELECT
    *
FROM
    (
        SELECT
            date_trunc( 
                'day', 
                    to_timestamp( 
                        generate_series( 1325455200, 1333691315, 80000 ) 
                        ) 
                    )::date AS d
    ) AS s
GROUP BY 
    s.d
ORDER BY
    s.d ASC

В результате получим что-то такое:

2012-01-02
2012-01-03
2012-01-04
2012-01-05
...

PostgreSQL. InitDB в русской/украинской локали

// Февраль 20, 2012 | 2510 просмотров | комментариев (0)

Для украинской локали

initdb --locale=uk_UA.UTF-8 --lc-collate=uk_UA.UTF-8 \
--lc-ctype=uk_UA.UTF-8 --encoding=UTF8 -D /db/postgresql

Для русской локали

initdb --locale=ru_RU.UTF-8 --lc-collate=ru_RU.UTF-8 \
--lc-ctype=ru_RU.UTF-8 --encoding=UTF8 -D /db/postgresql

Перепост: FreeBSD + PostgreSQL: тюнинг сервера БД

// Июнь 8, 2011 | 820 просмотров | комментариев (0)

Наверное, моя статья будет не интересна матерым сисадминам и покажется копипастом. Но я адресую ее тем, кто, как и я, будучи только разработчиком, впервые столкнулся с необходимостью еще и администрировать сервер, при этом решая задачи высоконагруженной БД. И чтобы гугл вас не проклял, постараюсь собрать в одном месте основные приемы для разгона сервера БД, которые мне успешно удалось реализовать.
Читать далее…

Перепост: Сервер на стероидах: FreeBSD, nginx, MySQL, PostgreSQL, PHP и многое другое

// Сентябрь 20, 2009 | 684 просмотров | комментариев (0)

Введение

С момента написания мной предыдущей статьи по оптимизации этой связки прошло довольно много времени. Тот многострадальный Pentium 4 c 512Мб памяти, обслуживающий одновременно до тысячи человек на форуме и до 150,000 пиров на трекере уже давно покоится на какой-нить немецкой, свалке, а клуб сменил уже не один сервер. Всё сказанное в ней всё ещё остаётся актуальным, однако есть вещи которые стоит добавить.

Читать далее…