В публикациях на хабре, чаще всего самое интересное в коментариях. Вот и в моем прошлом посте "Сгенерировать web интерфейс из БД или объектной модели не стало проще даже 10 лет спустя" я собрал джекпот из SharePoint, Vaadin и нескольких библиотек либо устаревших, либо поддерживаемых "одним автором".
Что я пытался объяснить читателям, что на этапе проверки идей не нужна дополнительная сложность в виде фреймворков, серверной инфраструктуры только ради этой самой инфраструктуры и нового языка программирования/платформы. Именно поэтому мне не нужны сейчас Yii, Symfony и Laravel, не подойдет ADF. На любое обучение тратится драгоценное время и поэтому надо понимать зачем тратить время на что-то, что потом возможно навсегда придется забыть через неделю-другую игры с прототипом.
В своих эксперементах с помощью лапшекода на JavaScript я быстро разработал вполне функциональный (для проверки идеи) но при этом не дружелюбный к пользователю интерфейс. Как пример, один из моих экранов:
При этом все это время я не программировал backend совсем. Все что есть в приложении - это набор статических файлов на http сервере jetty, и PostgREST для превращения базы данных PostgreSQL в сервис Open API и несколько новых хранимых функций в базе данных для интерфейса.
И jetty по хорошему здесь был бы лишний, если бы не...
Если бы PostgREST позволял отдавать статические HTML и картинки. Ведь когда я пытался запустить страничку из локальной папки, взаимодействие с PostgREST не работат из-за ограничений безопасности в современных браузерах.
Чем я могу поделиться и что будет полезно вам
Что из моего опыта разработки "на коленке" может быть полезно бэкэндерам, кто хочет бытро показать табличные данные из PostgreSQL?
Пересмотрев несколько библиотек на GitHub для отображения таблиц я остановился для себя на Tabulator которая из коробки умеет отображать таблицы с сортировкой, фильтрацией и разбивкой на страницы, автоматически распознает типы колонок, с отличной документацией, доступной для понимания не фронтэндером. А также позволяет быстро делать прототип, разрешая настраивать типы отображения поля даже в режиме автоопределения.
Попытка программировать на Java 21
За несколько минут( не учитывая время чтения документации) я набросал прототип который позволяет просмотреть все таблицы в базе данных. И сделал это я на Java еще не выпущенной версии 21 и потом еще минут десять пытался его запустить, разыскивая где в новой для меня версии IntelliJ Idea CE и в старой версии maven место куда же добавить эксперементальный параметр JDK enablePreview.
Честное слово, это привычка на автоматизме разрабатывать с заделом на будущее, чтобы когда-нибудь разбирать метаданные PostgreSQL и комментарии к объектам БД, все то что не экспортирует PostgREST. Пошел заварить чай и подумал доберусь ли я до этих метаданных постгреса и когда, что все это тоже лишнее, не нужен тут overengineering и надо переписать на HTML+JavaScript.
Ощущения что с Servlet API 5.0 разработка стала проще и без web.xml, а String Templates из JDK 21 сделают код более читаемым и в простых случаях избавляют от надобности подключения внешнего движка шаблонизации Apache Velocity:
Для любопытных под спойлером новый Java сервлет который больше не понадобится:
package com.github.com.github.isuhorukov.postgrest.crud;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import static java.lang.StringTemplate.STR;
@WebServlet("/table")
public class TableServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
String endpoint = request.getParameter("endpoint");
if(endpoint==null || endpoint.isBlank()){
List<String> postgRestAvailablePaths = getPostgRestAvailablePaths();
String options = postgRestAvailablePaths.stream().map(path ->
STR. """
<option value="\{path}">\{path}</option>
""").collect(Collectors.joining("\n"));
response.getWriter().append(STR."""
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
</head>
<body>
<form method="GET" action="/table">
<select name="endpoint">\{options}</select>
<input type="submit" value="Show">
</form>
</body>
</html>
""");
response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
return;
}
response.getWriter().append(STR."""
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
<link href="https://unpkg.com/tabulator-tables@5.5.2/dist/css/tabulator.min.css" rel="stylesheet">
<script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.5.2/dist/js/tabulator.min.js"></script>
</head>
<body>
<div id="table-result"></div>
<script>
var table = new Tabulator("#table-result", {
ajaxURL: '\{endpoint}',
pagination:true,
paginationMode:"remote",
dataSendParams:{
"size" : "limit" //rename page request parameter to "limit"
} ,
ajaxURLGenerator:function(url, config, params){
let sortParam = '';
if (params.sort && params.sort.length > 0) {
sortParam = '&order='+params.sort.map(sorter => `${sorter.field}.${sorter.dir}`).join(',');
}
return url + (url.includes("?")?"&":"?") + "limit=" + params.limit+"&offset=" + params.limit*(params.page-1) + sortParam;
},
ajaxResponse: function (url, params, response) {
document.querySelector(".tabulator-footer .tabulator-page[data-page='last']").style.display = "none";
return {
last_page: 10000000000,
data: response
};
},
autoColumns:true,
paginationSize: 35,
sortMode: 'remote',
filterMode:'remote',
ajaxSorting: true,
ajaxFiltering: true
});
</script>
</body>
</html>""");
}
List<String> getPostgRestAvailablePaths() {
String postgRestUrl = System.getenv("postgrest_url");
if(postgRestUrl==null || postgRestUrl.isBlank()){
throw new IllegalArgumentException("Please provide PostgREST / endpoint in environment variable: postgrest_url");
}
List<String> paths = new ArrayList<>();
try (HttpClient httpClient = HttpClient.newHttpClient()){
HttpRequest request = HttpRequest.newBuilder().uri(URI.create(postgRestUrl)).build();
HttpResponse<String> postgRestResponse = httpClient.send(request, HttpResponse.BodyHandlers.ofString());
if (postgRestResponse.statusCode() == 200) {
try {
HttpResponse<String> response = httpClient.send(request, HttpResponse.BodyHandlers.ofString());
ObjectMapper objectMapper = new ObjectMapper();
if (response.statusCode() == 200) {
String responseBody = response.body();
JsonNode openApiSpec = objectMapper.readTree(responseBody);
JsonNode pathsNode = openApiSpec.get("paths");
if (pathsNode != null && pathsNode.isObject()) {
for (Iterator<String> it = pathsNode.fieldNames(); it.hasNext(); ) {
String path = it.next();
if (!"/".equals(path) && !path.startsWith("/rpc")) {
paths.add(postgRestUrl + path);
}
}
} else {
throw new RuntimeException("No table paths found.");
}
} else {
throw new RuntimeException("PostgREST failed with status code: " + response.statusCode());
}
} catch (IOException | InterruptedException e) {
throw new RuntimeException(e);
}
} else {
throw new RuntimeException("PostgREST request failed with status code: " + postgRestResponse.statusCode());
}
} catch (IOException | InterruptedException e) {
throw new RuntimeException(e);
}
Collections.sort(paths);
return paths;
}
}
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.github.igor-suhorukov</groupId>
<artifactId>postgrest-crud</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.eclipse.jetty.toolchain</groupId>
<artifactId>jetty-jakarta-servlet-api</artifactId>
<version>5.0.2</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.15.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<enablePreview>true</enablePreview>
</configuration>
</plugin>
<plugin>
<groupId>org.eclipse.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>11.0.15</version>
<configuration>
<scanIntervalSeconds>10</scanIntervalSeconds>
<connectors>
<connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
<port>8080</port>
<maxIdleTime>60000</maxIdleTime>
</connector>
</connectors>
</configuration>
</plugin>
</plugins>
</build>
</project>Итак, я сделал все то что не хотел, добавил лишний компонент между PostgREST и браузером. Смело выбрасываем этот код/подход и движимся дальше!
Делаем все то же, но без сервера и только в браузере
Tabulator сделает всю магию за нас. Он отлично работает на ноутбуке и в мобильном браузере, и его нужно лишь правильно сконфигурировать для работы с PostgREST API.
Первой попыткой было создание задачи на адаптацию его для PostgREST с помощью комьюнити проекта, но безрезультатно.
Ну что ж, сделаем сами. Его параметр ajaxURL, который подгружает данные с сервера не сработает с PostgREST потому что в ответе он ожидает JSON объект c last_page - номером последней страницы и массивом data, который содержит сами данные.
Наш же API возвращает данные в виде массива сразу и не возвращает сколько страниц. Но нам это и не нужно, в первом приближении, так как эта та "кроличья нора" провалившись куда никогда не вернусь к основной задаче.
К слову, API умеет возвращать сколько строк в результате по нескольким алгоритмам. Нужно лишь установить HTTP Header:
Prefer: count=exact
Prefer: count=planned
Prefer: count=estimated
Результат можно извлечь из Content-Range в Response после "/". Я же поступил гораздо проще - убрал кнопку перехода на последнюю страницу.
Продолжаю разбирать проблемы: Tabulator передает в параметры номер страницы, а мне это нужно перевести их в limit/offset для API. Делаю это в функции ajaxURLGenerator и с помощью конфигурации dataSendParams.
Преобразование в нужный для виджета формат данных с сервера делаю на клиенте в функции для ajaxResponse.
Итого, у моей страницы есть параметры:
postgrest_url указывает на PostgREST API. Если не указать, то по умолчанию будет "стучаться" на localhost:3000
endpoint - таблица или вьюшка в постгресе. Если ее не указать, то код запросит все пути endpoint из PostgREST, отфильтрует функции и метаданные, и выдаст вам форму с выбором endpoint. Укажите в поле select какую таблицу хотите посмотреть, после "Нажми на кнопку и получишь результат".
Для работы нужена ваша база данных, сервер для API PostgREST который "смотрит" на эту базу, любой веб сервер куда вы можете положить эту страничку ( запускаю с помощьюmvn jetty:run). Если бы браузеры не боролись с cross site scripting и безопасностью ваших локальных данных, как это было раньше, то и веб сервер не был бы нужен для раздачи статики.
Код PostgREST-Tabulator
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
<link href="https://unpkg.com/tabulator-tables@5.5.2/dist/css/tabulator.min.css" rel="stylesheet">
<script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.5.2/dist/js/tabulator.min.js"></script>
</head>
<body>
<form id="endpoint_selector" method="GET" action="tables.html" style="display:none">
<select id="endpoint" name="endpoint"></select>
<input id="postgrest_url" name="postgrest_url" type="hidden" value="">
<input type="submit" value="Show">
</form>
<div id="table-result"></div>
<script>
const params = new URLSearchParams(window.location.search);
const postgRestUrl = params.has('postgrest_url')?params.get('postgrest_url') :'http://localhost:3000';
async function fetchAndExtractPaths(postgRestUrl) {
try {
const response = await fetch(postgRestUrl);
if (!response.ok) {
throw new Error(`Failed to fetch data from ${postgRestUrl}`);
}
const data = await response.json();
const paths = [];
if (data && typeof data.paths==='object') {
paths.push(...Object.keys(data.paths).filter(key => !key.startsWith('/rpc') && key!='/' ));
}
return paths;
} catch (error) {
alert('Error:', error);
return [];
}
}
if (params.has('endpoint')) {
const endpoint = params.get('endpoint');
var table = new Tabulator("#table-result", {
ajaxURL: postgRestUrl+endpoint,
pagination:true,
paginationMode:"remote",
dataSendParams:{
"size" : "limit"
} ,
ajaxURLGenerator:function(url, config, params){
let sortParam = '';
if (params.sort && params.sort.length > 0) {
sortParam = '&order='+params.sort.map(sorter => `${sorter.field}.${sorter.dir}`).join(',');
}
return url + (url.includes("?")?"&":"?") + "limit=" + params.limit+"&offset=" + params.limit*(params.page-1) + sortParam;
},
ajaxResponse: function (url, params, response) {
document.querySelector(".tabulator-footer .tabulator-page[data-page='last']").style.display = "none";
return {
last_page: 10000000000,
data: response
};
},
autoColumns:true,
paginationSize: 35,
sortMode: 'remote',
filterMode:'remote',
ajaxSorting: true,
ajaxFiltering: true
});
} else {
fetchAndExtractPaths(postgRestUrl)
.then(paths => {
const container = document.getElementById('endpoint');
paths.forEach(path => {
const optionElement = document.createElement('option');
optionElement.setAttribute('option',path);
optionElement.textContent = path;
container.appendChild(optionElement);
});
document.getElementById("endpoint_selector").style.display = 'block';
document.getElementById("postgrest_url").value = postgRestUrl;
});
}
</script>
</body>
</html>PostgREST я запускаю локально в Docker:
docker run --name postgrest --net=host -e PGRST_DB_URI="postgres://USER:PASSWORD@127.0.0.1:5432/DATABASE" -e PGRST_DB_ANON_ROLE="YOUR_ROLE" postgrest/postgrest:v11.2.0PostgreSQL с данными у меня тоже запущен в Docker образе, как я уже рассказывал на хабре.
Что еще можно сделать, пока не вышло универсально - в зависимости от типов данных менять отображение в колонках.
Результат
Получается отображать данные таблиц на сотни гигабайт с помощью постраничной загрузки с возможностью сортировки в базе данных.
И самое главное, что в обсуждении о производительности мы фокусируемся на самих бизнес данных и методах доступа базы к ним, а не на тюнинге GC, оптимизации сериализации в приложении, не о кластеризации серверов и даже не о мониторинге кешей Hazelcast, Ingnite, Coherence, оркестрации микросервисов и много еще чего. То есть мы ближе к решению задачи, чем к зоопарку технологий в MVP. На начальном этапе достаточно грамотной работы с базой данных. И откладываем самые важные архитектурные решения и трудозатраты на разработку на потом, когда проверим что в решении задачи есть ценность.
У меня нет планов на создание или на развитие этого как low code/no code платформы. Свою задачу я решаю быстро. Возможно кому-нибудь еще, кто использует PostgreSQL и не фронтэнд разработчик, этот опыт поможет для проверки идеи или создания "админки" к базе для пользователей внутри компании.
Комментарии (12)
pin2t
30.08.2023 23:45Я думал будет про то как ChatGPT генерирует таблицы за вас. Как-то не в тренде слвсем
igor_suhorukov Автор
30.08.2023 23:45- Внучек возьми вот скриптов домой.
- Деда, да нас этих скриптов дома, как #$*на.
- Так это все нейросети сгенерировали, а дед сам писал.
Попробовал чтобы ChatGPT написал детекторы и настраивал форматтеры таблицы для ссылок и изображений. Уже 10 минут закидываю промпты и поправляю результаты...
не осилила нейросетка поправить готовый компонент
poxvuibr
А как вам это удалось? Ведь судя по джаваскрипту используется конструкция limit ... offset . Неужели работает? Или там одна строка занимает мегабайты? Но на скриншоте строки маленькие.
Интересно, узнать объём данных поточнее. Сколько там сотен гигабайт получилось? И сколько это строк. И на каком диске лежат данные?
Индексы, наверное, по всем колонкам, да?
igor_suhorukov Автор
За счет того что клиент забирает только необходимые данные из PostgreSQL базы, а не пытается загрузить весь датасет в память.
В простом случае да. Если хочется быстрее, то конечно надо переходить на
ctid BETWEEN '($,0)'::tid AND '($,0)'::tidРаботает!
Диск NVME:
Общий объем базы данных 587GB. Число строк в самой большой таблице:
Распределение объема данных по партициям
Подробнее о схеме данных тут и тут.
Первичный ключ и гео столбцы.
javalin
Немного не понятно, как работает сортировка и партицирование тут..
Если я правильно понимаю, когда у нас таблица разбита на партиции, то сортировка происходит в рамках партиции а не всей таблицы. Или я ошибаюсь?
Если не ошибаюсь, то отсортировав, как у Вас в примере по рейтингу, мы не получим тех строк, которые в других партициях, и в результате выборка будет не верной?
igor_suhorukov Автор
Все зависит от плана запроса, который в свою очередь от наличия индексов для столбцов в запросе, схемы партиционирования и статистики по данным. В любом случае секционированые таблицы выгоднее и для построения индексов и для из перестройки и для обслуживания партиций. Даже seqscan скорее всего будет параллельным, что плюс на многопроцессорных системах.
Пример с удачным планом для запроса:
select * from ways order by scale desc limit 35 offset 350;Time: 51,948 ms
Его план использует обратный скан индексов
Этот же план с человечным форматированием
PostgreSQL не выдаст неверный результат, а вот "супер пупер производительная СУБД убийца PostgreSQL" вполне может поступить как вы описали. Или упасть в процессе выполнения с OOM/segfault или сказать что ваш запрос не верный "мы так не умеем как вы хотите", докупите еще GPGPU / RAM / блейдов / Infiniband и попробуйте еще раз.
PostgreSQL в случае недостатка памяти будет использовать диск для промежуточных результатов, merge sort и т.п.
poxvuibr
Это ж будет баг в реализации sql. Я не слышал про СУБД с таким поведением. Они правда есть или это предположение?
В смысле пользы от разбиения на секции раз не очень удачный пример. Если бы секций не было, сканирование по индексу прошло бы быстрее.
igor_suhorukov Автор
Это наблюдение из опыта, не буду показывать пальцем на них. Конечно фиксят ошибки, кто-то сам находит. В других проектах SQLancer вылавливает логические ошибки в SQL.
При условии что индекс умещается в памяти и нет вставок в базу. В любом случае лучше десяток небольших индексов обслуживать и перестраивать, чем один монструозный
poxvuibr
Судя по коду у вас как раз такой случай. И я вас так понял, что всё работает с приемлемой скоростью. Или я неправильно понял?
А можно использовать ctid вместе с сортировкой по произвольной колонке?
На скриншоте можно сортировать по любой колонке. Скорость получается приемлемой несмотря на отсутствие индексов?
И ещё на скриншоте оффсеты совсем маленькие. 352, например. И они меняются на единицу каждый раз. Такое впечатление, что каждая строка загружается отдельным запросом с limit... offset . Страницы из середины или конца больших таблиц при этом загружаются быстро?
igor_suhorukov Автор
Укажите где я говорил, что произвольные сортировки с миллионными офсетами работают с приемлемой скоростью?
Есть и страница 1011, под спойлером "Работает!" в ответе вам. С таймингами в отладчике firefox.
Это же открытый код. Вдруг есть идея как сделать так, чтобы выдавало за 1-2мс на произвольной сортировке и базе, то ваш вклад в код приветствуется!
poxvuibr
Ну вы же написали, что получается отображать данные таблиц на сотни гигабайт с помощью постраничной загрузки с возможностью сортировки в базе данных. Наверное вы бы не стали уточнять, что речь идёт о таблицах на сотни гигабайт, если бы скорость работы с ними была неприемлемой. Наверное вы не стали бы говорить про возможность сортировки, если бы с ней были какие-то проблемы.
Да, странно, что номер страницы 1011, а offset - 352 . Поэтому я и уточняю. Наверное отладчик firefox показывает загрузку какой-то другой страницы? Но вот то, что строки по одной загружаются я правильно понял?
Я чего-то ссылки в статье не нахожу. Плохо ищу?
Если сделать индексы по каждой колонке, сортировать только по одной колонке, не накладывать фильтры и убрать limit ... offset , то можно . А так - я не знаю как. Вот прочитал что вы написали про большие таблицы, посмотрел на возможность сортировки в интерфейсах и подумал - вдруг вы знаете.
igor_suhorukov Автор
Похоже дело в ваших догадках, "чтении" моих мыслей, да прочтении между строчек! На статье не указан хаб "Высокая производительность", теги "low latency" и уж тем более за время выполнения произвольных запросов с сортировкой по произвольному столбцу я не отвечаю. Датасет легко воспроизводим каждым: дампы планеты OSM доступны, как и утилита загрузки.
У меня проблем с производительностью и не было. Объемы своих данных показал, мои задачи Tabulator успешно решает, таблицы из этой базы данных успешно открывает.
Если долистаете до стотысячных страниц в таблице - записывайте скринкаст, порадуюсь за вашу упорность в достижении поставленных задач!
Добавил заголовок "Код PostgREST-Tabulator", теперь точно найдете. Исходники не по ссылке, а прямо в этой статье.
Дерзайте! Исходники доступны.
Видимо колонка в отладчике с адресом не до конца развернута.
Основная тема публикации о создании интерфейса в браузере, а не оптимизации произвольного запроса в PostgreSQL.