Speed up your fast text search queries with RUM index
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.
Видео
Другие доклады
-
45 мин
Simon Riggs Enterprise DBPostgreSQL and the SQL Standard
PostgreSQL is one of the most standards-compliant databases available. Talk discusses the impact that PostgreSQL has made on the SQL Standard and the features PostgreSQL has implemented from the SQL Standard, as well as upcoming features in PG14 and beyond.
-
22 мин
Andrey Lepikhov Postgres ProfessionalHow query execution history can help with re-execution
Postgres is able to build optimal query plans for most practical cases. However, sometimes, for objective reasons, for complex queries or because of open issues in the planner itself, it can make mistakes and produce a suboptimal plan. Because of this, the execution time of such a request can increase tenfold. If the query is executed frequently, then from time to time this query takes longer than it could, and the DBMS as a whole produces a lower TPS. If the planner is able to record his mistakes and take them into account in the subsequent planning of the same query, then this will improve the characteristics of the DBMS during its operation. We present the results of the development of a PostgreSQL DBMS extension that stores the query execution history and implements the planner recommendation mechanism. We show how knowledge about previously executed queries can improve the performance of subsequent ones.
-
45 мин
Anton Doroshkevich ИнфоСофтPostgres Pro Data Compression (CFS) for 1C ERP platform data
Postgres Pro Enterprise has a great compression engine. The year 2020 was devoted to the study of this mechanism in the real work of 1C. We have accumulated some statistical data and of course the subtleties of the use and behavior of 1C compared to other popular DBMS, which I want to share.
-
45 мин
Ibrar Ahmed Percona LLCAll about PostgreSQL Security
PostgreSQL provides different levels of security. This talk will cover all the available security techniques used in PostgreSQL 13. We’ll look at client-side security (LibPq, JDBC) through to server-side security. It will cover all supported authentication methods and the pros and cons of all these methods. Some of the key features of the talk are:
- Introduction to Cryptography
- SSL, TLS, GSSAPI, and OpenSSL
- Client-Side Encryption
- Securing Authentication
- Securing Data on the disk
- Securing Backup & Basebackup
- Securing Replication
- Database Roles and Privileges
It’s important to be familiar with all the security levels such as (1)network-level security (2) on-disk security (3) row-level, (4), and column level security. The talk will cover all the aspects with some real-life use cases and examples.