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.

pg_restore -j

Systems administrators will rejoice in the new ‘-j’ option for pg_restore. Now you can specify the number of jobs that pg_restore will start in parallel for a restore. This is a brand new feature, so the authors are still working on figuring out what level of parallelization makes the most sense for a given workload. The rule of thumb for now is one job per processor.

One trick with a large database is to remove the constraints on tables before you restore, and then reapply them after, so that restore jobs don’t block against each other while the constraints are being checked. Remember to test this before you actually need to perform a production restore!


Another useful new feature is the ability to specify a location for the statistics file. Previously, moving this file required a patch to the PostgreSQL source code and a re-compile. Now, there is a configuration parameter: stats_temp_directory. This allows you to relocate where statistics are written to a swap or memory filesystem, thereby reducing the disk I/O required for statistics gathering.

Determining that your statistics should be relocated could be determined through an application like atop, which can show per-process I/O utilization. Taking a sample of your I/O utilization during busy times could show you that your statistics writer is consuming more I/O than you’d like.

Statistics writer enhancement

Another statistics-related patch enables writing of statistics information only when a Postgres backend requests information from statistics tables. For example: through an interactive query of a statistics table or maybe an AUTOVACUUM. Previous versions of Postgres write this file every 500 ms. On a large, and very busy server, this overhead can be significant. No configuration is required to take advantage of the new behavior.

default_statistics_target default increased

A small change is that the default_statistics_target parameter was increased to 100 by default. Previously, this parameter was set to 10. ANALYZE uses this parameter to generate estimates of the selectivity of various WHERE clauses. Increasing this parameter too high can make ANALYZE very expensive, but testing and feedback from the PostgreSQL community convinced the development team to make this change.

The benefit is that your database will automatically be generating more detailed statistics for the query planner, and likely will make the query planner more informed and hopefully better at choosing the most efficient plan when executing your queries.

This was only a small slice of the new performance features in 8.4. There are more! Plus there are lots of new SQL and developer features like Common Table Expressions (recursive queries) and Windowing Functions.

More Information

Looking for more info on the latest version of PostgreSQL? Here are a few presentations about new features in 8.4 you might be interested in:

Magnus Hagander – “What’s new in 8.4
Robert Treat – “Intro to Postgres 8.4 Tutorial

Download the beta and try it out today!

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