Thursday, June 21

Testing new PostgreSQL versions without messing up your existing install

Craig's PlanetPostgreSQL
People are often hesitant to test out a new PostgreSQL release because they're concerned it'll break their current working installation. This is a perfectly valid concern, but it's easily resolved with a few simple protective measures: Build PostgreSQL from source as an unprivileged user Install your PostgreSQL build within that user's home directory Run PostgreSQL as that user, not postgres Run on a non-default port by setting the PGPORT env var If you take these steps the install its self cannot interfere at all. Starting and running the new PostgreSQL can only interfere by using too many resources (shared memory, file descriptors, RAM, CPU, etc) and you can just stop the new version if it causes any issues. The shared memory improvements in 9.3 make the shared memory (more…)

Choosing a PostgreSQL text search method

Craig's PlanetPostgreSQL
(This article is written with reference to PostgreSQL 9.3. If you're using a newer version please check to make sure any limitations described remain in place.) PostgreSQL offers several tools for searching and pattern matching text. The challenge is choosing which to use for a job. There's: LIKE and ILIKE SQL pattern matching; ~ and ~* operators for mostly-perl-compatible regular expressions; full text search with @@, to_tsvector and to_tsquery Use of an external search provider like Apache Lucene / Solr. There's also SIMILAR TO, but we don't speak of that in polite company, and PostgreSQL turns it into a regular expression anyway. Each of the built-in searching options comes with multiple choices of index: b-tree indexes with the text_pattern_ops opclass for left (more…)

VACUUM FREEZE y el misterio de relfrozenxid

El Planeta PostgreSQL de Jaime
En noviembre del 2012 asistí al PgDay en Buenos Aires. Ahí presente la charla "Autovacuum, ¿bendición o maldición? Afrontando el reto". En uno de los puntos de la charla explique, o al menos eso trate, que es necesario tratar de prevenir que el autovacuum ejecute VACUUM FREEZE. El problema con VACUUM FREEZE ejecutado por el autovacuum es que leerá y escribirá toda la tabla y las probabilidades son que el autovacuum decida hacer esto en un momento de alta actividad. Durante la charla entonces explique que podemos tratar de adelantarnos al autovacuum chequeando el valor del campo relfrozenxid en la tabla de sistema pg_class, ahora agregare que la consulta es similar a: select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; En todo caso, como era de (more…)
The first Australian PostgreSQL Day

The first Australian PostgreSQL Day

Gabriele's PlanetPostgreSQL
The first Australian PostgreSQL conference has come to an end and I am extremely happy that I was part of it. The event took place in Melbourne, at the offices of Experian Hitwise in St Kilda Rd. I must confess that, going back to the same building where I had worked in 2005/2006, was a strange but positive feeling. Hitwise is a long time Postgres user and it would appear that their cluster of PostgreSQL databases has grown to become one of the largest in the world. So, a double-special occasion for me! (more…)

Help us make a better PostgreSQL 9.3!

Craig's PlanetPostgreSQL
As interest in PostgreSQL grows, so does the rate at which new patches are proposed. To maintain the high level of quality in PostgreSQL it is important that all patches be checked and reviewed, so that what gets added to the codebase is good quality. Some of this evaluation requires a lot of expertise in the PostgreSQL core code, but most of it requires little development experience at all. The more initial checking and review gets done before patches get evaluated by the experts, the less work those experts have to do. So: please step up and review a patch. There are patch review guidelines on the wiki, and it's quite an accessible process. Step right up if you want to contribute a little back to the software you use and rely on, or if there's a particular enhancement you want to make (more…)