Long story short, since PostgreSQL 14 to_tsquery('pg_class') becomes 'pg' <-> 'class' instead of 'pg' & 'class' ( commit 0c4f355c6a ). That is for instance, in PostgreSQL 13 and earlier to_tsquery('pg_class') matches to_tsvector('a class of pg') . But since PostgreSQL 14 it doesn’t match, but still matches to_tsvector('pg_class') and to_tsvector('pg*class') . This is incompatible change, which…
It seems a good idea to change grey psql output to a lovely rainbow in honor of IDAHOT day. Thankfully there is lolcat utility, which is very easy to install on Linux and Mac OS. Linux 1 $ sudo snap install lolcat Mac OS 1 $ brew install lolcat Having lolcat installed, you can set it up as a psql pager and get lovely rainbow psql output! 1 2 \ pset pager always \ setenv PAGER 'lolcat -f | less…
PostgreSQL has an extension to jsonpath: ** operator, which explores arbitrary depth finding your values everywhere. At the same time, there is a lax mode, defined by the standard, providing a “relaxed” way for working with json. In the lax mode, accessors automatically unwrap arrays; missing keys don’t trigger errors; etc. In short, it appears that the ** operator and lax mode aren’t designed to…
The world changes. ARM architecture breaks into new areas of computing. An only decade ago, only your mobile, router, or another specialized device could be ARM-based, while your desktop and server were typically x86-based. Nowadays, your new MacBook is ARM-based, and your EC2 instance could be ARM as well. In the mid-2020, Amazon made graviton2 instances publically available. The maximum number…
Long story short, using PostgreSQL 11 with RUM index you can do both TOP-N query and COUNT(*) for non-selective FTS queries without fetching all the results from heap (that means much faster). Are you bored yet? If not, please read the detailed description below. At November 1st 2017, Tome Lane committed a patch enabling bitmap scans to behave like index-only scan when possible. In particular,…
It’s not very widely known, but PostgreSQL is gathering statistics for indexed expressions. See following example. 1 2 3 4 5 6 7 8 9 CREATE TABLE test AS ( SELECT random () x , random () y FROM generate_series ( 1 , 1000000 )); ANALYZE test ; EXPLAIN ANALYZE SELECT * FROM test WHERE x + y < 0 . 01 ; QUERY PLAN…
Today I gave a talk “Our answer to Uber” at United Dev Conf, Minsk. Slides could be found at slideshare . In my talk I attempted to make a review of Uber’s notes and summarize community efforts to overcome highlighted shortcomings. United Dev Conf is quite big IT conference with more than 700 attendees. I’d like to notice that interest in PostgreSQL is quire high. The room was almost full during…
Faceted search is very popular buzzword nowadays. In short, faceted search specialty is that its results are organized per category. Popular search engines are receiving special support of faceted search. Let’s see what PostgreSQL can do in this field. At first, let’s formalize our task. For each category which have matching documents we want to obtain: Total number of matching documents; TOP N…
Dealing with partitioned tables we can’t always select relevant partitions during query planning. Naturally, during query planning you can’t know values which come from subquery or outer part of nested loop join. Nevertheless, it would be ridiculous to scan all the partitions in such cases. This is why my Postgres Professional colleague Dmitry Ivanov developed a new custom executor node for…
For people who are actively working with psql, it frequently happens that you want to draw graph for the table you’re currently seeing. Typically, it means a cycle of actions including: exporting data, importing it into graph drawing tool and drawing graph itself. It appears that this process could be automated: graph could be drawn by typing a single command directly in psql. See an example on…
PostgreSQL scalability on multicore and multisocket machines became a subject of optimization long time ago once such machines became widely used. This blog post shows brief history of vertical scalability improvements between versions 8.0 and 8.4. PostgreSQL 9.2 had very noticeable scalability improvement. Thanks to fast path locking and other optimizations it becomes possible to achieve more…
PostgreSQL 9.6 receives suitable support of extensible index access methods. And that’s good news because Postgres was initially designed to support it. “It is imperative that a user be able to construct new access methods to provide efficient access to instances of nontraditional base types” Michael Stonebraker, Jeff Anton, Michael Hirohama. Extendability in POSTGRES , IEEE Data Eng. Bull. 10 (2)…
Recently Robert Haas has committed a patch which allows seeing some more detailed information about current wait event of the process. In particular, user will be able to see if process is waiting for heavyweight lock, lightweight lock (either individual or tranche) or buffer pin. The full list of wait events is available in the documentation . Hopefully, it will be more wait events in further…
Recently pg_pathman receives support of UPDATE and DELETE queries. Because of some specialties of PostgreSQL query planner hooks, UPDATE and DELETE planning is accelerated only when only one partition is touched by query. Other way, regular slow inheritance query planning is used. However, case when UPDATE and DELETE touches only one partition seems to be most common and most needing optimization.…
In my previous post I’ve introduced pg_pathman as an extension which accelerate query planning over partitioned tables. In this post I would like to covert another aspect of pg_pathman: it not only produce plans faster, but also produce better plans. Thanks to it query execution with pg_pathman becomes much faster in some cases. When you search partitioned table with some filter conditions,…
Partitioning in PostgreSQL is traditionally implemented using table inheritance . Table inheritance allow planner to include into plan only those child tables (partitions) which are compatible with query. Simultaneously a lot of work on partitions management remains on users: create inherited tables, writing trigger which selects appropriate partition for row inserting etc. In order to automate…
Introduction Users of jsonb datatype frequently complaint that it lucks of statistics. Naturally, today jsonb statistics is just default scalar statistics, which is suitable for < , <= , = , >= , > operators selectivity estimation. But people search jsonb documents using @> operator, expressions with -> operator, jsquery etc. This is why selectivity estimation, which people typically get in their…
While hacking PostgreSQL it’s very useful to know pid of the backend you are working with. You need to know pid of the process to attach debugger, profiler etc. Luckily, .psqlrc provides us an elegant way to define the shortcuts for psql. Using config line below one can find out backend pid just by typing :pid . .psqlrc 1 \ set pid 'SELECT pg_backend_pid();' 1 2 3 4 5 =# : pid pg_backend_pid…