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
We created table with two columns
y whose values are independently and uniformly distributed from 0 to 1. Despite we analyze that table, PostgreSQL optimizer estimates selectivity of
x + y < 0.01 qual as 1/3. You can see that this estimation is not even close to reality: we actually selected 56 rows instead of 333333 rows estimated. This estimation comes from a rough assumption that
< operator selects 1/3 of rows unless something more precise is known. Of course, it could be possible for planner to do something better in this case. For instance, it could try to calculate histogram for
x + y from the separate histograms for
y. However, PostgreSQL optimizer doesn’t perform such costly and complex computations for now.
Situation changes once we define an index on
x + y.
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 my talk. Also, after the talk I was continuously giving answers to surroundings in about 1 hour.
I think that Minsk is very attractive place for IT events. There are everything required for it: lovely places for events, good and not expensive hotels, developed infrastructure. Additionally Belarus introduces 5 days visa-free travel for 80 countries, and that made conference attendance much easier for many people. It would be nice to have PGDay.Minsk one day.
New major release of PostgreSQL is approaching. PostgreSQL 9.6 is expected to be released later today. This is a great release which provides to users set of outstanding new features. I’m especially happy that Postgres Professional did substantial contribution to this release. In particular, full-text search for phrases and scalability improvements are listed as major enhancements of this new PostgreSQL release.
The full list of Postgres Professional constributions including:
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 matching documents.
For sure, it’s possible to query such data using multiple per category SQL queries. But we’ll make it in a single SQL query. That also would be faster in majority of cases. The query below implements faceted search over PostgreSQL mailing lists archives using window functions and CTE. Usage of window function is essential while CTE was used for better query readability.
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 pg_pathman: RuntimeAppend. This node behaves like regular Append node: it contains set of children Nodes which should be appended. However, RuntimeAppend have one distinction: each run it selects only relevant children to append basing on parameter values.
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 the screenshot below.
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 than 350 000 TPS in select-only pgbench test. The latest stable release PostgreSQL 9.5 also contain significant scalability advancements including LWLock improvement which allows achieving about 400 000 TPS in select-only pgbench test.
Postgres Professional company also became involved into scalability optimization. In partnership with IBM we researched PostgreSQL scalability on modern Power8 servers. The results of this research was published in popular Russian blog habrahabr (Google translated version). As brief result of this research we identify two ways to improve PostgreSQL scalability on Power8:
- Implement Pin/UnpinBuffer() using CAS operations instead of buffer header spinlock;
- Optimize LWLockAttemptLock() in assembly to make fewer loops for changing lwlock state.
The optimization #1 appears to give huge benefit on big Intel servers as well, while optimization #2 is Power-specific. After long rounds of optimization, cleaning and testing #1 was finally committed by Andres Freund.
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) pp.16-23, 1987
That was a huge work which consists of multiple steps.
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 releases.
It’s nice to see current wait event of the process, but just one snapshot is not very descriptive and definitely not enough to do any conclusion. But we can use sampling for collecting suitable statistics. This is why I’d like to present pg_wait_sampling which automates gathering sampling statistics of wait events. pg_wait_sampling enables you to gather statistics for graphs like the one below.
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.
Also, I’d like to share some benchmark. This benchmark consists of operations on journal table with about 1 M records for year partitioned by day. For sure, this is kind of toy example, because nobody will split so small amount of data into so many partitions. But it is still good to see partitioning overhead. Performance of following operations was compared:
- Select single row by its timestamp,
- Select data for whole day (whole one partition),
- Insert one row with random timestamp,
- Update one row with random timestamp.
The following partitioning methods were compared:
- Single table, no partitioning,
- pg_partman extension,
- pg_pathman extension.
Benchmarks were done on 2 x Intel Xeon CPU X5675 @ 3.07GHz, 24 GB of memory server with fsync = off in 10 threads. See the results below.
|Test name||single table, TPS||pg_partman, TPS||pg_pathman, TPS|
|Select one row||47973||1084||41775|
|Select whole one partition||2302||704||2556|
|Insert one row||34401||7969||25859|
|Update one row||32769||202||29289|