Вставлять строку каждый раз, когда обновляется предыдущая строка (Postgresql, PHP)
У меня есть несколько устройств (одиннадцать, если быть точным), которые отправляют информацию каждую секунду. Эта информация поступает на сервер apache, анализируется PHP-скриптом, сохраняется в базе данных и, наконец, отображается в графическом интерфейсе.
Сейчас я проверяю, существует ли строка для текущего дня, если она не создаст новую, в противном случае обновите ее.
Причина, по которой я так поступаю, заключается в том, что мне нужно опросить информацию из базы данных и отобразить ее в приложении c ++, чтобы она выглядела в реальном времени; Если бы мне приходилось создавать строки каждый раз, когда устройство отправляло бы информацию, обработка и чтение данных требовали бы значительного количества времени, а также системных ресурсов (памяти, процессора и т. Д.), Что делало бы отображение данных не совсем реальным. время.
Я написал инструмент для создания отчетов, который получает информацию за каждый день (с 00:00:00 до 23:59:59) и помещает ее в таблицу Excel.
Мои вопросы в основном:
-
Можно ли выполнить вставку / обновление непосредственно на сервере базы данных или мне нужно выполнить логику в сценарии php?
-
Есть ли лучший (более эффективный) способ хранения информации без снижения производительности в устройстве отображения?
-
Что касается генерации отчета, то если я хочу выбрать интервалы, скажем, начиная со вчерашнего дня в 15:50:00 и заканчивая сегодня в 12:45:00, это не может быть сделано с моей текущей структурой данных, так что я должен рассмотреть в порядке создать структуру данных, которая позволила бы мне создавать такие запросы.
Компоненты, которые я использую:
— Apache 2.4.4
— PostgreSQL 9.2.3-2
— PHP 5.4.13
Решение
Мои рекомендации — просто храните всю информацию, которую отправляют ваши устройства. С правильными индексами и запросами вы можете обрабатывать и извлекать информацию из БД очень быстро.
По вашим вопросам:
Да, внутри Postgres DB можно построить любую желаемую логику, используя SQL, PL / pgSQL, PL / PHP, PL / Java, PL / Py и многие другие языки, встроенные в Postgres.
Как я уже говорил, правильное индексирование может творить чудеса.
Если вы не можете получить желаемую скорость запроса с полной таблицей — вы можете создать небольшую таблицу с 1 строкой для каждого устройства. И сохраните в этой таблице последние известные значения, чтобы показать их в sort of real-time,
Другие решения
1) Техника называется upsert. В PG 9.1+ это можно сделать с помощью wCTE (http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/)
2) Если вы действительно хотите, чтобы это было в режиме реального времени, вы должны отправлять данные непосредственно в приложение, хранение их в памяти или в виде открытого текста также будет быстрее, если вы заботитесь только о последних нескольких значениях. Но у PG есть каналы Listen / notify, так что, вероятно, ваше отставание составит всего лишь 100-200 миль, и это не должно быть слишком большим, вы только отображаете его.
Я думаю, что вы переоцениваете системные требования к памяти, учитывая описанный вами процесс. Добавление ряда данных каждую секунду (или 11 в секунду) — это не куча ресурсов. На самом деле это скорее займет больше времени, чтобы ОБНОВИТЬ против ДОБАВИТЬ новую строку. Кроме того, если вы добавляете TIMESTAMP к вашей таблице, операции сортировки происходят молниеносно. Просто добавьте некоторую обработку для сбора мусора как задание CRON (удаление старых данных) один раз в день или около того, и вы — золотой.
Однако, чтобы ответить на ваши вопросы:
Можно ли выполнить вставку / обновление непосредственно на сервере базы данных или я должен выполнить логику в сценарии php?
Писать логику с помощью движка базы данных обычно не очень просто. Для простоты придерживайтесь логики в скрипте php. UPDATE (или) INSERT INTO table SET var1 = ‘assignment1’, var2 = ‘assignment2’ (WHERE id = ‘checkedID’)
Есть ли лучший (более эффективный) способ хранения информации без снижения производительности в устройстве отображения?
Трудно ответить, потому что вы не описали подключение устройства отображения. Есть более эффективные способы сделать процесс, однако ни один из них не имеет механизмов блокировки, необходимых для такого частого обновления.
Что касается генерации отчета, то если я хочу выбрать интервалы, скажем, начиная со> вчера в 15:50:00 и заканчивая сегодня в 12:45:00, это невозможно сделать с моей текущей структурой data>, так что мне нужно учитывать чтобы создать структуру данных, которая позволила бы мне создавать такие запросы.
Вы можете использовать тип переменной TIMESTAMP. Это будет включать ДАТУ и ВРЕМЯ операции ОБНОВЛЕНИЯ. Тогда это просто предложение WHERE, использующее функции DATE в запросе к базе данных.