The EDB Blog

Postgres Keywords

By Bruce Momjian
You might be aware that the sql standard reserves certain identifiers that cannot be used for naming user objects. Postgres follows that standard, with slight modifications. For example, you cannot create a table called all: CREATE TABLE all (x INTEGER); ERROR: syntax error at or near "all" LINE 1...

Cheat Sheet: Configuring Streaming Synchronous Replication in PostgreSQL

By Thomas Munro
(This blog was co-written by Sunil Narain.) Streaming replication in PostgreSQL can be asynchronous or synchronous. The synchronous option provides greater data protection in case of disaster, or if a server or data center goes down. Synchronous replication does come with a performance penalty,...

Transitioning Synchronous Standby to Asynchronous in EDB Postgres

By Vibhor Kumar
In the world of solution architecture, where we as solution architects are developing configurations to meet specific customer needs, we often consider a great deal of interesting scenarios. One of such scenario my team at EnterpriseDB ® (EDB™) encountered recently bears exploring further with this...

Who Contributes to PostgreSQL Development?

By Robert Haas
In a talk which I gave at PGCONF.IN and, in a shorter version, at PGCONF.US, I had a few slides on who contributes to PostgreSQL development. Here, I'd like to present a slightly expanded version of the information which was in the talk. The information in this post considers calendar year 2016 and...

Column Storage Intervals

By Bruce Momjian
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

By Bruce Momjian
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

By Bruce Momjian
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...

Customizing Postgres Code: New Feature for Selectively Recording Error Messages

By Rajkumar Raghuwanshi
EnterpriseDB ® (EDB™) works closely with its customers to develop new features as they encounter needs unique to their environments. Global organizations are deploying the EDB Postgres Platform across a broader spectrum of workloads as they discover greater value in the enterprise-class performance...

New Features Coming in PostgreSQL 10

By Robert Haas
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

By Bruce Momjian
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...

Pgpool II Keeps Pace With Demands of Today’s Architectures and Workloads

By ahsan hadi
Middleware can have a significant impact on the operation and performance of the database system. Pgpool II is the middleware product that sits between the PostgreSQL server and database clients and is developed and maintained by a committed open source Postgres community that includes EnterpriseDB...

Still No Query Hints?

By Bruce Momjian
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?

By Amit Kapila
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

By Bruce Momjian
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

By Robert Haas
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...