(disclaimer: all the data and examples in here are on Linux – the same data can be probably obtained on other systems too, it's just that I work on Linux and don't know other systems well).
This question pops occasionally in various places – PostgreSQL is using too much memory, why is that, and how can it be mitigated?
Before we can go to “optimizing", we should understand the problem. But do we? Both standard tools – ps and top – lie. How/why? Let's see.
Yesterday I wrote about selects on int4 vs. texts.
One of the comments that caught my attention was question about index creation time. So, let's see…
There is this idea that normal form in databases require you to use integer, auto incrementing, primary keys.
The idea was discussed by many people, I will just point you to series of three blog posts on the subject by Josh Berkus ( part 1, 2 and 3, and reprise).
One of the points that proponents of surrogate keys (i.e. those based on integer and sequences) raise is that comparing integers is faster than comparing texts. So,
select * from users where id = 123
is faster than
select * from users where username = 'depesz'
Is it?
Just released new version of OmniPITR.
This version has one important new feature: when you're calling omnipitr-backup-slave, it will make backups only of required xlog files, and not, as previously, of all in walarchive directory.
This is important, especially in case you have multiple slaves, or you keep shared long-term walarchive. Previously – backups would get all files from walarchive (-s option to omnipitr-backup-slave), but now, it picks just the ones that are needed.
On somehow related note – I will be working now, finally, to get omnipitr-monitor functionality working.
Just released new version, 0.6.0 (it should be visible on pgxn soon) of OmniPITR set of tools.
New version has one new feature – parallelism.
This works in omnipitr-archive and omnipitr-backup-* programs, and allows for parallel delivery to remote destinations (multiple -dr switches).
Also – if you're using compresses wal archive and omnipitr-backup-slave reading from it – all the wal files have to be decompressed before making backup – and this decompression can be parallelized too.
All parallelization is controled using -PJ option (–parallel-jobs), so you can add “-PJ 10" to get up to 10 decompressions at the same time or up to 10 deliveries at the same time.
On 6th of April, Simon Riggs committed patch:
Add DROP INDEX CONCURRENTLY [IF EXISTS], uses ShareUpdateExclusiveLock
Important disclaimer: the module that I'm writing about was written by my colleague Phil Sorber.
We all have been in, or heard about, situation like this:
$ update users set password = '...'; where id = 123;
(hint: first ; is before where).
Of course you should have backups, and you can protect yourself from it. But what if backup is too old, and you didn't protect yourself?
Three interesting patches:
New GUC, track_iotiming, to track I/O timings. Currently, the only way to see the numbers this gathers is via EXPLAIN (ANALYZE, BUFFERS), but the plan is to add visibility through the stats collector and pg_stat_statements in subsequent patches. Ants Aasma, reviewed by Greg Smith, with some further changes by me.
Expose track_iotiming information via pg_stat_statements. Ants Aasma, reviewed by Greg Smith, with very minor tweaks by me.
Improve contrib/pg_stat_statements to lump "similar" queries together. pg_stat_statements now hashes selected fields of the analyzed parse tree to assign a "fingerprint" to each query, and groups all queries with the same fingerprint into a single entry in the pg_stat_statements view. In practice it is expected that queries with the same fingerprint will be equivalent except for values of literal constants. To make the display more useful, such constants are replaced by "?" in the displayed query strings. This mechanism currently supports only optimizable queries (SELECT, INSERT, UPDATE, DELETE). Utility commands are still matched on the basis of their literal query strings. There remain some open questions about how to deal with utility statements that contain optimizable queries (such as EXPLAIN and SELECT INTO) and how to deal with expiring speculative hashtable entries that are made to save the normalized form of a query string. However, fixing these issues should require only localized changes, and since there are other open patches involving contrib/pg_stat_statements, it seems best to go ahead and commit what we've got. Peter Geoghegan, reviewed by Daniel Farina
Continue reading Waiting for 9.2 – pg_stat_statements improvements
Today, I released new version of OmniPITR – 0.5.0.
This new version has one important new feature – which is so called “direct destination" for backups.
What it means? What it does? How it helps? Let's see…
Three patches for you today, all committed by Robert Hass:
Add a transform function for numeric typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds when a column is changed to an unconstrained numeric, or when the scale is unchanged and the precision does not decrease. Noah Misch, with a few stylistic changes and a fix for an OID collision by me.
Add a transform function for varbit typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds when the new type is unconstraint varbit, or when the allowable number of bits is not decreasing. Noah Misch, with review and a fix for an OID collision by me.
Add transform functions for various temporal typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds in some cases. Noah Misch, with trivial changes by me.
Continue reading Waiting for 9.2 – More rewrite-less ALTER TABLE ALTER TYPEs