Open Enterprise: The PostgreSQL Open Source Database Blog from EnterpriseDB

Virtual Private Database (VPD) in PPAS 9.1

Vibhor Kumar — Fri, 10 Feb 2012 15:18:18 +0000

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1 Virtual Private Database (VPD) is a feature which enables Administrator to create …

Virtualizing Postgres

Bruce Momjian — Thu, 09 Feb 2012 13:15:01 +0000

Postgres is an ideal database to run in a virtual environment or public/private cloud — one reason is that Postgres relies heavily on the operating system, rather than using features like raw devices. Second, its license is obviously very flexible for virtual deployments.

I am often asked about running Postgres in virtual environments, and I usually answer that it runs just fine — and it does. However, I am starting to realize that I am not answering the more complex questions of which visualization technology to choose, and what is the performance and reliability impact of virtualization.

My Patches Are Breeding

Robert Haas — Wed, 08 Feb 2012 14:28:00 +0000

One of the great things about being a long-term contributor to an open source project like PostgreSQL is that you get to see other people take the stuff you’ve done and use it as a stepping stone to bigger and better things. One of my early PostgreSQL hacking projects was a patch to extend the syntax of EXPLAIN. Prior to 9.0, the grammar looked like this:

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

Now, there’s nothing particularly wrong with that grammar from a usability perspective, but it turns out to be pretty terrible for extensibility. Let’s suppose we want to add a new EXPLAIN option that does something new and different – say, omit the costing information from the output. Then we have to change the grammar to something like this:

EXPLAIN [ ANALYZE ] [ VERBOSE ] [ NOCOSTS ] statement

There are a couple of problems with this. One is that, as the number of options increases, it gets hard to remember the order in which they must be specified. You might think that it would be easy enough to recode the grammar to look like this:

EXPLAIN [ ANALYZE | VERBOSE | NOCOSTS ]... statement

…and it might be, but it’s surprisingly easy, when using bison, to create situations that bison finds ambiguous, even though a human being might not. Another problem is that NOCOSTS has to become what’s called a keyword, which has a very small but nonzero distributed cost across our entire grammar. Rightly or wrongly, a number of patches that proposed to enhance EXPLAIN in various ways got shot down because of these issues.

Configure PEM Server on Postgres Plus Cloud

Vibhor Kumar — Sun, 05 Feb 2012 19:24:04 +0000

Postgres Plus Cloud is now available for users, so I thought to write a short tutorial on How to create Instance on Postgres Plus Cloud and How to configure PEM Server on Postgres Plus Cloud. Creating and Installing on Postgres … Continue reading →

Let’s See work_mem

Bruce Momjian — Thu, 02 Feb 2012 14:45:01 +0000

Having shown memory allocation (and deallocation) in my previous blog post, I would like to show work_mem in action.

First, I ran the following database session using a ~6k RPM drive:

Postgres Memory Surprises

Bruce Momjian — Wed, 01 Feb 2012 14:15:01 +0000

In my previous blog entry, I analyzed how various tools (ps and smem) report memory usage. In summary:

ps columns TRS, DRS, and VSZ report virtual address space allocated, not actual RAM allocated.
smem’s USS reports a process’s private (unshared) memory allocated.
smem’s PSS is a sum of process’s private memory allocated and a proportional amount of shared memory (both System V shared memory, like Postgres’s shared_buffers, and shared libraries).
RSS shows actual RAM allocated, private and shared.

With these issues understood, let’s look at a running Postgres cluster:

Revisiting Memory Reporting

Bruce Momjian — Mon, 30 Jan 2012 16:00:00 +0000

Memory is very important to databases — much more so than for typical applications (presentation). Unfortunately, because memory allocation is so
complex, it is often hard to figure out how physical RAM is being used. There are several reasons for the complexity:

Virtual Memory: CPUs in virtual memory mode don’t access RAM directly, but rather through page tables.
Segmentation: Memory is allocated in specific segments:
text(code), data, and stack.
Sharing: physical RAM is often shared by multiple processes, either in read-only mode (program instructions), shared mode (read/write of share memory), or copy-on-write (create a new copy on write; used by fork).

Robert Haas’s excellent blog post highlighted much uncertainty about how to analyze memory usage for specific processes, especially Postgres. I commented on his blog, as did others, and now have a much clearer idea of how to study memory usage. A blog post by Chris Siebenmann directly addresses some of my and Robert’s questions, and suggests smem as a way to analyze memory, especially the sharing of memory. It was interesting to learn that smem was designed specifically to address the problems Robert outlined (2007, 2009).

NoSQL Databases as the New Scripting Language

Bruce Momjian — Fri, 27 Jan 2012 16:00:00 +0000

This video presentation by Dwight Merriman (MongoDB) at OSCON Data 2011 explores the role of NoSQL databases. He makes some very interesting points:

NoSQL was created to allow horizontal, read-write scaling
NoSQL uses a dynamic schema
NoSQL is similar to dynamically-typed languages, e.g. Perl, PHP
NoSQL has serious limitations compared to relational data storage, e.g. reporting

What I found most interesting about his presentation was the comparison to dynamically-typed languages. When dynamically-typed scripting languages started to become popular years ago, there was a sense that they were only for trivial applications, compared to compiled language that were for serious work. As CPUs became faster, scripting languages were increasingly used for production applications, particularly web applications that are modified frequently. There might be a similar pattern now in the (improper?) dismissal of NoSQL for serious applications.

Scalability What?

Bruce Momjian — Thu, 26 Jan 2012 17:00:01 +0000

The term “scalability” gets injected into database conversations often, but scalability discussions often end without a clear consensus. I believe this is because “scalability”, like “replication”, actually means different things to different people.

There are two dimensions to database scalability; the first dimension specifies if additional servers are involved:

vertical: The hardware capabilities of a single database server are increased to increase throughput

horizontal: The number of a database servers is increased to increase throughput

Cloud Computing Takes Another Step

Sean Doherty — Wed, 25 Jan 2012 21:05:30 +0000

As enterprises embark down the path of cloud computing we?re realizing that with many new advancements in technology, the promise of the technology is often more grand than the reality. It usually takes more time than we think it will …