The EDB Blog

PERCENT_RANK Vs. CUMM_DIST

11/14/2017
During research for my Postgres Window Magic talk, I studied the unusual behavior of percent_rank and cumm_dist ( cumulative distribution ). The Postgres documentation wasn't helpful. I finally came up with this paragraph to add to the Postgres 10 docs: cume_dist computes the fraction of partition...

Amazon RDS and Aurora: The Golden Arches of Postgres?

11/9/2017
Would Beyoncé still be Beyoncé if she couldn’t sing? She would of course technically be the same person, but think of how different her life would be based on whatever career choice her other skills might define. The same is true for Postgres. If you take away its most powerful and unique...

Storing the Original Time Zone

11/7/2017
In a previous blog entry I suggested storing the original time zone offset in a separate column if clients need to know the stored time in the original time zone. There is some more complexity to this issue that I would like to cover. First, when I suggested using select extract(timezone from...

Materialized Views and Foreign Data Wrappers

10/31/2017
You might know that Postgres supports materialized views and foreign data wrappers (fdw). Briefly, materialized views allow for queries to be materialized and refreshed on demand. Foreign data wrappers allow data to be pulled from foreign data sources, like Nosql stores and other Postgres servers...

Containers: Impossible to Ignore and Just Might Change Everything

10/26/2017
Virtual machines are by far the most prevalent deployment model for data centers – close to 99%. What was once a revolutionary idea has transformed into the best practice standard for data center deployments. Virtualization has evolved into a very well established and understood technology that is...

Session State Failover

10/24/2017
On the server side, high availability means having the ability to quickly failover to standby hardware, hopefully with no data loss. Failover behavior on the client side is more nuanced. For example, when failover happens, what happens to connected clients? If no connection pooler is being used,...

Road Trip! Your Oracle Migration Journey Just Got Simplified

10/19/2017
Licensing complexity, rising costs, and audit risk are motivating Oracle customers to jump start their migration efforts to alternative database platforms. Even the largest of enterprises and the most restrictive of government agencies are actively evaluating open source database alternatives...

Playing with IPv6

10/17/2017
Now that everyone is using IPv6 ( ) it might be time to start playing with it. Postgres has had full IPv6 support for years, so Postgres is a good place to start, particularly with IPv6-aware data types. Since IPv6 addresses are 128-bits instead of IPv4's 32-bits, they can be quite long, e.g. 2001:...

Odd Month Arithmetic

10/10/2017
You might be aware of the interval data type, which allows mathematical operations on date, time, and timestamp values. This can lead to odd behavior, but this email posting showed me a new oddity when dealing with months containing a different number of days. First, let's summarize how many days...

Power Your Digital Transformation with Application Innovation in the Cloud

10/5/2017
Digital transformation has officially become the key to meeting modern customer expectations. Only two years ago, Don Butler, Executive Director of Connected Vehicle and Services at Ford, famously said, " We need to adopt more and more of a mindset of a software or services company. ” And today,...

Use with Time Zone

10/3/2017
If you often use the timestamp data type, you might not be making full use of it. In these queries: CREATE TABLE tztest (x TIMESTAMP ); INSERT INTO tztest VALUES (CURRENT_TIMESTAMP); SELECT * FROM tztest; x ---------------------------- 2016-10-25 18 :49:20.220891 SHOW timezone; TimeZone...

PostgreSQL's Hash Indexes Are Now Cool

9/26/2017
Since I just committed the last pending patch to improve hash indexes to PostgreSQL 11, and since most of the improvements to hash indexes were committed to PostgreSQL 10 which is expected to be released next week, it seems like a good time for a brief review of all the work that has been done over...

PL/Java Adoption

9/19/2017
PL/Java has been around since 2005, but it has regularly struggled to gain users. Unfortunately, a lot of these problems are specific to the Java language and hamper its adoption. First, there are a limited number of people who know both Java and the Postgres backend code. Fortunately Chapman Flack...

Are You Confident You’re Handling Your Postgres Project the Right Way?

9/14/2017
IT professionals have many new responsibilities around data management and strategy. The pace of change is very rapid in modern organizations. Database administrators and developers are joining forces. DevOps teams are now working together to rapidly develop new applications and replatform legacy...

When to Use Server-Side Logic

09/12/2017
The use of server-side logic, particularly stored procedures, has been a highly contentious topic among database professionals for decades. The question has always been what amount of logic should be encoded in the database vs. in client applications or application servers. Beyond using the...