Индексы существуют для ускорения доступа к данным. Они позволяют PostgreSQL избегать полного просмотра таблицы, значительно сокращая время выполнения запросов для рабочих нагрузок с интенсивным чтением.
Из реального производственного опыта мы наблюдали, что хорошо спроектированные, целевые индексы могут улучшить производительность запросов в 5 и более раз, особенно на больших транзакционных таблицах.
Однако индексы не являются бесплатными.
И в этой статье мы обсудим, какие проблемы могут вызывать неиспользуемые индексы и как удалить их из производственных систем с планом отката, безопасно.
Усечение таблицы может быть замечательно быстрым - и чрезвычайно опасным при неосмотрительном использовании. Если вы хотите иметь скорость и не разочароваться, тут дается практическое, готовое для интервью руководство по реальным подводным камням TRUNCATE TABLE в SQL Server и то, как избежать их.
Справка
TRUNCATE TABLE является операцией DDL, которая освобождает страницы (эффективно журнализированные) и сбрасывает IDENTITY к начальному значению. При этом триггеры DELETE не срабатывают. Возможен откат при выполнении внутри транзакции.
Завершается неудачно, если на таблицу ссылается внешний ключ (даже если дочерняя таблица пуста), используется в индексированных представлениях, является системно-версионной (временной), опубликованной для репликации или включена для CDC, или на нее ссылается ограничение EDGE графа. Существует специальная возможность для самоссылающихся внешних ключей.
Начиная с SQL Server 2016, вы можете усекать конкретные секции: TRUNCATE TABLE dbo.Fact WITH (PARTITIONS (4 TO 6)); (индексы должны быть выровнены).
Работа над RegreSQL заставила меня уделить много внимания буферам. Если вы иногда работаете с PostgreSQL, то наверняка слышали о настройке shared_buffers и следовали старому доброму совету выставить его на уровне 1/4 от доступной оперативной памяти. Но после того как мы немного слишком увлеклись этой темой в недавнем выпуске Postgres FM, меня спросили, что к чему.
Буферы — одна из тех тем, которую легко забыть. И хотя они являются фундаментальным блоком архитектуры производительности PostgreSQL, большинство из нас воспринимает их как чёрный ящик. Эта статья попытается это изменить.
Секционированные таблицы — это базовая возможность PostgreSQL, но один аспект по-прежнему регулярно вызывает путаницу — даже у опытных пользователей:
Как именно ведёт себя команда ALTER TABLE, когда задействованы секции?
Распространяется ли операция на секции? Влияет ли она на будущие секции? Действительно ли ключевое слово ONLY делает то, что заявлено? Почему некоторые команды работают на родительской таблице, но не на секциях — или наоборот?
Сегодня документация PostgreSQL хорошо описывает отдельные подкоманды ALTER TABLE, но редко объясняет их взаимодействие с секционированными таблицами в целом. В результате пользователи часто узнают о реальном поведении только методом проб и ошибок.
Эта статья обобщает систематическое исследование поведения ALTER TABLE для секционированных таблиц, превращая разрозненные правила в последовательную классификационную модель.
Раздувание таблиц в PostgreSQL — это явление, при котором «мёртвые записи» (dead tuples), образовавшиеся в результате операций UPDATE или DELETE, не собираются процессом VACUUM, что приводит к неоправданному увеличению размеров файлов данных.
Чтобы VACUUM мог освободить мёртвые записи, должна быть гарантия, что эти записи «точно не могут быть использованы какими-либо активными на данный момент транзакциями». Если старые транзакции сохраняются по какой-либо причине, сборка мусора процессом VACUUM останавливается на этой точке.
В этой статье объясняются следующие четыре причины раздувания таблиц: как каждая проявляется, как определить коренную причину и как её устранить.
Длительные транзакции
Незавершённые подготовленные транзакции
Запросы на серверах-репликах с включённым параметром hot_standby_feedback
Когда дело доходит до оптимизации баз данных, разработчики часто хватаются за одни и те же старые инструменты: немного переписать запрос, навесить индекс на столбец, денормализовать, проанализировать, очистить, перестроить кластер и т.д. Традиционные методы эффективны, но иногда творческий подход может принести реальную пользу!
В этой статье я представляю нестандартные методы оптимизации в PostgreSQL.
Когда специалисты по SQL Server начинают серьёзно работать с PostgreSQL, большая часть обучения кажется комфортной. Таблицы ведут себя ожидаемо, транзакции знакомы, а B-деревья выглядят обнадёживающе похожими на то, что вы использовали годами в SQL Server. Затем вы сталкиваетесь с индексами BRIN.
На первый взгляд они кажутся почти безрассудными: нет указателей на строки, нет точной навигации и явное принятие ложных срабатываний. И всё же на очень больших таблицах индексы BRIN часто обеспечивают прирост производительности, для достижения которого в SQL Server потребовались бы кластерные индексы, секционирование или даже columnstore-индексы. Чтобы понять почему, нужно посмотреть не только на то, что делает BRIN, но и на то, как он работает внутри.
Молодые программисты выросли с ASCII и Unicode как единственными способами представления символьных данных в компьютере. Но в тёмные века, до того как мы изобрели грязь и вошли в каменный век, были и другие претенденты.
Большинство руководств по массовой вставке SQL и SQL Server openrowset игнорируют проблемы доступа к файлам, которые могут прервать импорт. И оператор bulk insert, и функция openrowset выполняются под аккаунтом службы SQL Server при чтении файла источника. Аккаунт службы SQL Server должен иметь разрешение на чтение файла или папки, где файл находится. Также удобно иметь разрешения на чтение и выполнение, а также вывод содержимого папки. Кроме того, нестандартные места размещения файла источника (например, C:\Users\Public\Downloads) могут не предоставлять доступ на чтение по умолчанию аккаунту службы SQL Server - всегда проверяйте это перед использованием.
Планировщик запросов PostgreSQL - это сложный инженерный механизм, обычно принимающий блестящие решения относительно того, как выполнять ваши запросы. Однако в сложных сценариях или при необычных распределениях данных вы можете знать лучший способ. Именно в таких ситуациях на помощь приходит pg_hint_plan - мощное расширение, которое позволяет вам руководить, или "советовать", планировщиком для выбора специфичного пути выполнения.
Предложения GROUP BY и HAVING в SQL существуют с самого начала языка, в своей простейшей форме. Изначальная идея заключалась в том, что вы создаёте рабочую таблицу с помощью предложений SELECT ... FROM ... WHERE, а затем разбиваете этот результирующий набор на группы. Группа определяется как набор строк с одинаковыми значениями для столбцов группировки. Негруппируемые столбцы должны были быть свойствами группы (обычно агрегатными функциями), формулами или вычислениями, выполненными на них, либо константами (что технически также является свойством группы).
Затем каждая группа сводится к одной строке. Важное понятие здесь — исходная таблица больше не существует. Однако если вы достаточно взрослый, чтобы работать с самыми ранними версиями SQL Server, синтаксис Sybase не избавлялся от исходной таблицы. Это приводило к некоторым проблемам. Он предоставлял доступ к исходным данным строк вместе с агрегированными данными.
Исходное предложение HAVING затем применялось к каждой из строк в новой сгруппированной таблице. Это означало, что оно выполнялось после предложения WHERE, которое строило исходную таблицу.
Позже мы добавили оконные функции, расширения для GROUP BY в форме ROLLUP, CUBE и GROUPING SETS. Но с исходными простыми конструкциями GROUP BY – HAVING всё ещё можно сделать довольно много. Их преимущество в том, что они выполняют свою работу за один просмотр таблицы, в то время как более новые конструкции могут требовать временного хранения или нескольких проходов по данным. Исходные версии переносимы и предсказуемы. Спустя все эти десятилетия они теперь хорошо оптимизированы.
