The EDB Blog

Column Storage Intervals

4/25/2017
Postgres uses native cpu alignment to store values in a row. This allows blocks to be copied unchanged from disk into shared buffers and accessed as local variables, as outlined in this presentation. This can be illustrated by pg_column_size(). First, an empty row size: SELECT pg_column_size(row...

Creating Descending Indexes

4/18/2017
You might have noticed that create index allows you to specify asc or desc for each column being indexed, and you might have wondered why. Asc is the default, so there is no need to use it. Desc actually does have a use, but it isn't obvious. Postgres can access indexes forward or backward, so...

Index Order Does Matter

4/12/2017
Postgres has supported multi-column indexes since 1997, e.g. create index i_test on test (a, b, c). It can easily use an index if the supplied columns are all at the front of the index, e.g. a and b in the previous index, but it can also use the index if some of the indexed column values are not...

New Features Coming in PostgreSQL 10

4/10/2017
The list of new features coming in PostgreSQL 10 is extremely impressive. I've been involved in the PostgreSQL project since the 8.4 release cycle (2008-2009), and I've never seen anything like this. Many people have already blogged about these features elsewhere; my purpose here is just to bring...

Expression Index Statistics and Joins

4/5/2017
In my previous blog post, I showed how statistics generated on expression indexes can be used to produce more accurate row counts, and potentially better plans. While I did show more accurate row counts via explain, I did not show changed query plans. I plan to do so in this blog post. First, the...

Still No Query Hints?

03/28/2017
So, it is 2017 and Postgres still doesn't support query hints like other relational databases? Yep, it's true, and probably will be forever since "'Oracle-style' optimizer hints" is listed in the "Features We Do Not Want" section of the Postgres todo list. A wiki page outlines the reasons for this...

Hash Indexes are Faster than Btree Indexes?

3/24/2016
PostgreSQL have supported Hash Index for a long time, but they are not much used in production mainly because they are not durable. Now, with the next version of PostgreSQL, they will be durable. The immediate question is how do they perform as compared to Btree indexes. There is a lot of work...

Statistics on Expression Indexes

3/22/2017
Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where clauses. However, there is another benefit to expression indexes, and that is optimizer statistics. Not only do expression indexes allow rapid lookups...

Parallel Query v2

3/16/2017
A recent Twitter poll asked What is your favorite upcoming feature of PostgreSQL V10? In this admittedly unscientific survey, "better parallelism" (37%) beat out "logical replication" (32%) and "native partitioning" (31%). I think it's fruitless to argue about which of those features is actually...

Getting the Most of out Application_Name

3/15/2017
Application_name might be one of those Postgres settings that you have seen in various places but never understood its purpose. It is true that setting application_name doesn't change the behavior of Postgres (with one small exception ), but it is very useful for monitoring. Its value appears in...

Combining Partitioning and FDWs for Real Time Analytics

3/14/2017
Since v9.1 PostgreSQL has been steadily improving its Foreign Data Wrapper (FDW) capabilities. FDWs are a way to access and manipulate data external to PostgreSQL from a PostgreSQL server. The technology is based on the SQL/MED standard, which represents foreign data in the form of relational...

Wait Event Reporting

3/13/2017
Postgres often lacks the sophisticated reporting of more established enterprise relational database systems. Sometimes that is because Postgres doesn't require as much monitoring and tuning, but there are legitimate monitoring needs which Postgres doesn't support. Thanks to the wait_event_type and...

Benchmarks Reveal Major Increase in EDB Replication Performance

3/10/2017
A fundamental question database administrators ask before deploying replication services is what they can expect in terms of performance. As described below, if a sufficient rate of data replication cannot be maintained, replication latency will grow to a level that makes replication suboptimal for...