Harder, Better, Faster, Stronger: New Features in Postgres 8.4

Looking for a mature, open source database that's not owned by Oracle? Whether you're already a fan or are looking for alternatives, now might be a good time to take a look PostgreSQL. The latest release of Postgres has a slew of built-in features that allow you to improve both performance and systems administration. Learn how you take advantage of them.

The roller coaster announcements coming from Oracle and Sun this week have a number of people questioning the fate of MySQL. While the GPL’d database probably isn’t going anyplace soon, now might be a good time to remind yourself that alternatives to MySQL do exist. And that some, in the case of PostgreSQL, are incredibly feature-rich.

With the beta version of PostgreSQL 8.4 just announced, testers are already downloading and trying out some of the newest features. As with every new release of PostgreSQL, there are performance improvements that you should be aware of, as well as new and useful configuration parameters available that will make your system administrator’s life easier. Let’s dive in.

If you’ve never used PostgreSQL before, now is a great time to try it out. PostgreSQL is a powerful, open source object-relational database system with more than than 15 years of active development. PostgreSQL is also known as ‘Postgres’, and will be referred to by that nickname for the rest of the article.

New Free Space Map and Visibility Map

The Free Space Map (FSM) improvements and the new Visibility Map will both significantly improve VACUUM performance. The FSM was re-implemented, and now does not require configuration, as in releases past. This data structure keeps track of the available free space inside of a table due to UPDATEs and DELETEs on your tables. You can throw out your pre-8.4 max_fsm_pages and max_fsm_relations settings, and relax while Postgres keeps track of this information for you!

The new Visibility Map provides information about which tuples were actually modified since the last time VACUUM was run. Now VACUUM can skip over tuples the Visibility Map knows have not changed. For infrequently updated tables, this results in fewer I/O operations per VACUUM, and much faster VACUUMs overall.

Heikki Linnakangas presented a talk about this feature at FOSDEM 2009 and his slides are available on the Postgres wiki.

How often you VACUUM can have a huge impact on the performance of your database. One important distinction that beginning administrators struggle with is the difference between a VACUUM FULL and a plain VACUUM. For regular maintenance, the autovacuum daemon should be used (enabled by default since version 8.2). Autovacuum will run regular VACUUMs on all your tables that take advantage of the FSM improvements.

A related tool is autoanalyze (also enabled by default), which keeps statistics used by Postgres to craft the best possible plans for your database queries. When these statistics get out of date, the planner has trouble choosing the best way of executing a query. On busy systems, increasing the frequency of autoanalyze by adjusting the autovacuum_vacuum_scale_factor down to ‘.1′ (default is ‘.2′) will keep your table statistics more up-to-date. Other adjustments are possible, including setting parameters for individual tables.

You only need VACUUM FULL when you determine that you have “bloat” in a table. This can happen because of a high number of updates, inserts and deletes that a typical schedule of VACUUMs can’t take care of. Most Postgres databases do not need this treatment regularly. In particular, scheduling daily VACUUM FULLs is not only not necessary, it can have a terrible performance impact on a busy system.


Dipping a toe slightly into the developer pool, a built-in function was added called suppress_redundant_updates_trigger(). When used with an UPDATE trigger, this function will prevent an update from occurring if what is being written is exactly the same as what is already there.

If the write occurs, a value was just updated unnecessarily and Postgres keeps (essentially) a record of that change until the next VACUUM.

You can use this like so:

CREATE TRIGGER your_trigger BEFORE UPDATE on your_table
EXECUTE procedure suppress_redundant_updates_trigger();'

You may see this kind of application behavior from an object relational mapping (ORM) tool.

One final note on bloat: there is a very useful tool called check_postgres that has a built-in bloat check. It works on its own, or as part of an overall monitoring or graphing system (such as Nagios or MRTG). There are many other checks included in that tool worth checking out related to performance monitoring and tuning.

Next: System Administration Features

Comments on "Harder, Better, Faster, Stronger: New Features in Postgres 8.4"

Here is my web page: regal assets reviews; Desmond,

Also visit my page reviews of regal assets (Juliana)

Most people would certainly attribute this to the use of severe pressure by the resident, yet not
all secrets are broken due to the fact that their proprietors use stress.

Also visit my web page – residential locksmith (Shanon)

Ask about exactly how our smart lock and also crucial administration services could aid contribute to your benefit and simplicity of accessibility.

Here is my site :: home locksmith; Margareta,

People typically forget the value of keys to their
safety and security and also, hence, forget
the value of crucial substitute.

My web-site; commercial locksmith, Sharon,

The inherent features of this lock make it safeguard,
simply by having a completely different indoor system than the classic pin-tumbler lock.

Take a look at my web page :: car locksmith, Shawn,

The work for taking the shut out and also placing it back
was truly just only enjoyable 5 minutes doing it. Many thanks you very much
for the wonderful $95 saver post.

Check out my page; residential locksmith (Madison)

If you were to insert a type in most any type of pin tumbler lock and transform it,
the key might not be gotten rid of till the lock cyndrical tube is back in the position received Number D.

my web-site car locksmith (Erica)

Well, not once more, since Brian Jones is right here
to provide you locksmith safe (Alberto) companies anywhere and also whenever you require it.

United Locksmith Services has many years of experience in protecting residential residences.

Have a look at my website; New lock

In a lot of these situations, not only is the crucial damaged as well as broken off within the lock, yet the
lock itself is harmed.

Visit my web site :: automotive locksmith; Malinda,

There isn’t anything Safe Residence Lock and also Trick can refrain when it involves locks,
safes, or tricks whether industrial, domestic, or auto locksmith services.

Not always pick resistance – in Twenty Years, I have yet
ahead across the consequences of a theft where the bad
guy really picked the lock – that’s hollywood things, not
real life.

Check out my web page – auto locksmith (Kaylee)

I bought a deal for $19 for a emergency locksmith –
Earle – solution call plus
Thirty Minutes of labor.

Aid do your component to shield the setting by supplying our service
technicians your old locks or bring your waste steel to SOS
emergency locksmith (Felica) professional and we will gladly discard
the steel in a Eco-friendly and also environment-friendly way, all earnings will be donated to charitable company.

When your lock has to be totally replaced, our 1 Day service division has accessibility to excellent quality name
brand name hardware that is able to withstand all kinds of abuse.

Here is my blog post; home locksmith – Stephanie,

Securing your type in the auto or locking on your own from
your home or business is an aggravating hold-up.

my site commercial locksmith (Lorri)

Get in touch with us today at -LRB-404-RRB- 845-7876 regarding mounting a
master key system, having your locks re-keyed, setting up high safety locks, changing
or transforming your existing lock hardware, fixing locks, replacing lost tricks or opening your workplace.

my blog … 24 hour locksmith (Maybell)

He is the owner of Altic new lock (Rex) Service in Indianapolis, Indiana and also is a functioning locksmith.

Locksmith Golden has professional state accredited as well as expert
locksmith functioning night and day in order to assist you for
any kind of locksmith professional emergency locksmith – Ute,
situation you could be experience.

Deadbolts have single or dual steel screws that lock into location inside a
strike plate in the door jamb.

my blog … 24 hour locksmith (Adelaida)

Our mobile auto locksmiths in Cincinnati believe qualities such as patience and also
confidence play an essential function in obtaining you back when traveling once more.

Here is my homepage … 24 hour emergency locksmith [Maybelle]

It’s possible that I could minimize the number of
tricks that you should continue your chain by making the same vital
open all your doors.

Also visit my web blog: locksmith safe [Joe]

It’s awesome designed for me to have a web site, which is beneficial designed for my knowledge.thanks adminHere is my web blog: miami e key west turisti per caso

Hey! This is my 1st comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading through your blog posts.Can you suggest any other blogs/websites/forums that deal with the same topics?Many thanks!my blog post; miami to key west airport

Hello everyone, it’s my first go to see at this web site, and post is really fruitful in support of me, keep up posting these articles or reviews.Feel free to surf to my blog :: miami to key west places to stop

Great post. I was checking constantly this blog and I am impressed! Extremely useful info specially the last part :) I care for such info a lot. I was looking for this certain information for a very long time. Thank you and best of luck.Feel free to visit my web site – points of interest miami to key west

Feel free to surf to my weblog … regal assets reviews (Claire)

Leave a Reply