How available do you want your services? A lot? Downtime is never desirable, so you want zero downtime, right? OK, that’s impossible,
so what is reasonable, and how much are you willing to pay for it?

Uptime is often measured in 9’s, meaning how many nines in a percentage of uptime is acceptable? 99%, 99.9%, 99.99%, or even 99.95% if
you want to be more precise. Of course, this is the desirable uptime — there will be years you are 100% available, then years you
will fall far short of your goal.

Let’s look at some uptime percentages:

Continue Reading »

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link: Functions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a …

Continue reading »

Having just explained the value of using swap space, I would like to comment on memory overcommit. Last month, Tom Lane posted a very clear explanation of how copy-on-write and fork interact to require either excess swap space or unrecoverable process failure during high memory usage. This article explains historically how operating systems have dealt with this trade-off. (The Postgres documentation explains how to control the Linux out-of-memory (OOM) killer.)

In summary, this is another reason to have swap space, and to monitor it, because it supplies early warning that memory is in high demand. If you have no swap space, you can’t monitor its usage, and you lose valuable information about the health of your system.

I have occasionally heard students in my training classes mention that they configure their servers with no swap space. The students have two reasons for this:

• I have enough memory so I shouldn’t be swapping
• I don’t want to swap because it is slow

Both of these reasons are inaccurate. Let’s look at the first one, “I have enough memory so I shouldn’t be swapping”. While it is generally true that large memory systems never fully use their RAM, it isn’t optimal to avoid swap. Applications often have initialization data that can be written to swap so the memory can be used for other purposes. Kernel kernel hacker Andrew Morton once stated:

My point is that decreasing the tendency of the kernel to swap stuff out is wrong. You really don’t want hundreds of megabytes of
BloatyApp’s untouched memory floating about in the machine. Get it out on the disk, use the memory for something useful.

Continue Reading »

Ever need to generate random data? You can easily do it in client applications and server-side functions, but it is possible to generate random data in SQL. The following query generates five lines of 40-character-length lowercase alphabetic strings:

SELECT
(
        SELECT string_agg(x, '')
        FROM (
                SELECT chr(ascii('a') + floor(random() * 26)::integer)
                FROM generate_series(1, 40 + b * 0)
        ) AS y(x)
)
FROM generate_series(1,5) as a(b);

Continue Reading »

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information: Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by …

Continue reading »

Most people know Heroku as a Platform as a Service (PaaS) provider — they supply a git server, website for your application, and an optional Postgres database (details). What is less well known is that Heroku also has a pure-database offering, more like Database as a Service (DBaaS). This is similar to creating your own Postgres cluster on Amazon Web Services (AWS), except Heroku manages the database, and shares the costs.
What is really interesting is that if you create an account, you can create free databases (of limited size) that will be always available (screenshot). (FYI, you can easily get stuck in the PaaS section of the Heroku website trying to return to the DBaaS section.)

In contrast, EnterpriseDB (my employer) also offers a cloud database product, but it is closer to a database cluster as a service (rather than just a database) because you control the entire database cluster, including cluster configuration, scaling options, and have full SSH access to the operating system (details). Unfortunately, their free trial lasts only 24 hours, probably because of the cost associated with Amazon instances.

Once you have created a free Heroku database, you can click on the database name to see database details, including instructions on how to connect to the database, like from psql (screenshot, screenshot; database subsequently destroyed). If you do a psql \l, you can see databases owned by other users:

Continue Reading »

I just finished attending OSCON this week, and Matthew Soldo of Heroku gave a talk titled, PostgreSQL’s place in the New World Order. (What’s not to like about that title? )

The talk was interesting in its description of Postgres in the new world order of NoSQL and Cloud. He felt that Postgres’s features like hstore and JSON (coming in Postgres 9.2) make it uniquely positioned to handle many NoSQL workloads. He felt Postgres’s open source nature allowed more confident use of Postgres in public clouds without vendor lock-in.

Overall, it was an inspiring talk that highlighted Postgres’s ability to adapt to new environments.

I have received three phone calls in the last three days from job recruiters looking for Postgres employees. I usually get one call a month, which I think means the Postgres job market is heating up.

To help employers find Postgres talent, I have written a wiki page explaining how to post to the Postgres jobs group email list. If you are contacted by a recruiter, please mention this web page to them. I have added a suggestion to the bottom of my résumé page — hopefully that helps. If you are looking for Postgres employment, please subscribe to that email list.

Suppose a manufacturer produces a product and makes a profit, yet manufacturing generates greater social harm than the value of the product. How does the manufacturer stay in business? Because the manufacturing costs are distributed among many people and are not all paid by the company. This is called an external cost or externality and is commonly seen with pollution costs or natural resource depletion. It can also apply to database index creation.

Huh? Database index creation? Yes. Consider this case: you are troubleshooting a query; it is slow. It is slow because there is no index matching the query qualification, so you add one. The query is now much faster, so you are done. But are you? Your query is faster, but what about other queries? What about insert and some update operations?  They now have to update that index you created — that has to take some time. How much? It is hard to measure, because it is distributed among many queries, and is probably small compared to the speed-up you saw in the query that prompted the index creation. But a lot of small slowdowns can easily surpass the speed-up you saw in that one query.

How can your measure all those small slowdowns? I don’t know, but do know they exist, so be careful adding an index that has limited use — you might find that externalities make your system slower.