Сетевой фильтр PostgreSQL для EnvoyProxy

How do you monitor Postgres? What information can you get out of it, and to what degree does this information help to troubleshoot operational issues? What if you want/need to log all the queries? That may bring heavy trafficked databases down.

At OnGres we’re obsessed with improving PostgreSQL’s observability. So we worked together with Tetrate folks on an Envoy’s Network Filter extension for PostgreSQL, to provide and extend observability of the traffic inout a cluster infrastructure. This extension is public and open source. You can use it anywhere you use Envoy. It allows you to capture automated metrics and to debug network traffic. This talk will be a technical deep-dive into PostgreSQL’s protocol decoding, Envoy proxy filters and will cover all the capabilities of the tool and its usage and deployment in any environment.

Resources:

• https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/other_protocols/postgres#arch-overview-postgres
• https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new-postgres-extension-with-monitoring-support/

Как обфусцировать базу в Postgres для задач нагрузочного тестирования веб-приложений

Postgres is a well-known database for high load web applications. Such apps require stress/load testing itself to run properly in production. Besides obvious difficulties in preparation a test environment identical to production, generating proper traffic there is another one issue - database preparation for the test environment. And it seems it is not good to use the database from production in the testing environment in the era of personal data protection (GDPR, HIPAA). Data obfuscation is the rescue.

There are few instruments for data obfuscation in Postgres. During this session, I will tell you which of them we've selected and why what type of issues we faced, and if our solution was successful. You will know if it is possible to get an identical response on the test database without real data from production, we will observe some restriction on obfuscation, I'll present our utility which simplify things.

Constraints или о том, как попытаться спокойно жить

There's a common delusion that constraints should never be used as they affect performance in a negative way, interfere with regular work, and are, all in all, useless. The database is commonly perceived as just a storage without any logic. I'll explain why it isn't so and what this careless approach may lead to.

Ускорение быстрого текстового поиска с помощью индекса RUM

Fast text search queries can be made even faster with indexing on lexemes inside compound records of tsvector format. RUM index is an open-source PostgreSQL extension. It represents a big improvement of GIN index and it can index lexemes with additional information e.g. tsvector lexeme weight-mark. So it can support tsvector capabilities more.

Until recently it was needed to recheck results of weight-containing queries by table. My modification (2020) is to make the processing of this kind of queries index-only and therefore much faster.

Also, I will describe and provide benchmarks for different usage cases for fast text search. We'll see how RUM index can improve the performance and compare it with PostgreSQL internal GIN index.