#postgresql #sql-update #geometry #postgis
#postgresql #sql-обновление #геометрия #postgis
Вопрос:
У меня есть большая таблица PostgreSQL из 29 миллионов строк. Размер (согласно вкладке статистика в pgAdmin) составляет почти 9 ГБ.) В таблице включена поддержка post-gis с пустым столбцом геометрии.
Я хочу ОБНОВИТЬ столбец геометрии, используя ST_GeomFromText, считывая данные из столбцов координат X и Y (SRID: 27700), хранящихся в той же таблице. Однако выполнение этого запроса ко всей таблице сразу приводит к ошибкам «не хватает места на диске» и «соединение с сервером потеряно»… последнее происходит реже.
Чтобы преодолеть это, должен ли я ОБНОВЛЯТЬ 29 миллионов строк пакетно / поэтапно? Как я могу выполнить 1 миллион строк (ПЕРВЫЙ 1 миллион), затем выполнить следующий 1 миллион строк, пока не достигну 29 миллионов?
Или есть другие, более эффективные способы обновления больших таблиц, подобных этому?
Я должен добавить, что таблица размещена в AWS.
Мой запрос на ОБНОВЛЕНИЕ:
UPDATE schema.table
SET geom = ST_GeomFromText('POINT(' || eastingcolumn || ' ' || northingcolumn || ')',27700);
Комментарии:
1. попробуйте выполнить обновление для предельного набора строк .. если у вас есть в id для каждой строки первые 100.000, сначала попробуйте для наборов по 1 млн.
2. Не могу помочь с проблемой дискового пространства, но st_point работает быстрее, чем st_geomfromtext gis.stackexchange.com/questions/122247 /…
3. @mlinth вы имеете в виду ST_MakePoint или ST_PointFromText? Или на самом деле ST_Point? Извините, просто уточняю.
4. ST_SetSRID(ST_Point(eastingcolumn,northingcolumn),27700); Ваши столбцы должны быть с плавающей запятой (или преобразованы в значения с плавающей запятой, если они текстовые, например, eastingcolumn::numeric).
5. Да — должно сработать, и приведение будет неявным. Больше информации о приведениях здесь: postgresql.org/docs/current/typeconv.html
Ответ №1:
Вы не указали никаких спецификаций сервера, запись 9 ГБ может быть довольно быстрой на новейшем оборудовании.
Вас должно устраивать одно длительное обновление — если у вас нет одновременных операций записи в эту таблицу.
Распространенный прием для преодоления этой проблемы (очень длинная транзакция, блокирующая запись в таблицу) заключается в разделении ОБНОВЛЕНИЯ на диапазоны на основе первичного ключа, выполняемого в отдельных транзакциях.
/* Use PK or any attribute with a known distribution pattern */
UPDATE schema.table SET ... WHERE id BETWEEN 0 AND 1000000;
UPDATE schema.table SET ... WHERE id BETWEEN 1000001 AND 2000000;
Для высокого уровня одновременной записи люди используют более тонкие приемы (такие как: ВЫБОР ДЛЯ ОБНОВЛЕНИЯ / NOWAIT, облегченные блокировки, логика повторных попыток и т.д.).
Комментарии:
1. спасибо за ответ, однако в моей таблице нет столбца id, который увеличивает 1, 2, 3, …. 29000000 и т.д. Мой столбец первичного ключа — это uprn (уникальный ссылочный номер длиной от 1 до 12 цифр). Короче говоря, я не знаю, какова схема распространения.
2. РЕДАКТИРОВАТЬ: я добавил столбец ID, используя- ALTER TABLE schema.tablename ДОБАВИТЬ идентификатор СТОЛБЦА SERIAL;
3. взяв тестовую таблицу из 1 миллиона строк, обновление одного столбца для всей таблицы сразу заняло 1 минуту. ОБНОВЛЕНИЕ одного и того же столбца в этой таблице из 4 частей, ГДЕ идентификатор ОТ 0 До 250000 и т.д. и т.п., занимает 47 секунд только для одной из 4 частей. таким образом, в общей сложности мы можем предположить, что для всей таблицы потребуется около 3 минут…
4. @TheoF спасибо за добавление некоторых деталей 🙂 это доказывает, что, возможно, разбиение обновления на пакеты не стоит дополнительных сложностей, и выбор «лучшего» решения зависит от среды.
Ответ №2:
Из моего первоначального вопроса:
Однако выполнение этого запроса ко всей таблице сразу приводит к ошибкам «не хватает места на диске» и «соединение с сервером потеряно»… последнее происходит реже.
Оказывается, в нашей базе данных экземпляров Amazon AWS не хватало места, что не позволило завершить мой исходный запрос ST_GeomFromText. Освобождение пространства исправило это.
На важном примечании, как предложил @mlinth, ST_Point выполнил мой запрос намного быстрее, чем ST_GeomFromText (24 минуты против 2 часов).
Мой последний запрос:
UPDATE schema.tablename
SET geom = ST_SetSRID(ST_Point(eastingcolumn,northingcolumn),27700);