For various reasons, and in various cases, bloat happens. Theoretically autovacuum protects us all, but sometimes it doesn't. Sometimes someone disables it, or mis-configures, or bad planet alignment happens, and we end up in deep bloat.
What to do then? Vacuum? Vacuum Full? Cluster? No. pg_reorg!
For todays post in Understanding postgresql.conf series, I chose work_mem parameter.
On 30th of June, Alvaro Herrera committed patch:
Enable CHECK constraints to be declared NOT VALID This means that they can initially be added to a large existing table without checking its initial contents, but new tuples must comply to them; a separate pass invoked by ALTER TABLE / VALIDATE can verify existing data and ensure it complies with the constraint, at which point it is marked validated and becomes a normal part of the table ecosystem. An non-validated CHECK constraint is ignored in the planner for constraint_exclusion purposes; when validated, cached plans are recomputed so that partitioning starts working right away. This patch also enables domains to have unvalidated CHECK constraints attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT VALID, which can later be validated with ALTER DOMAIN / VALIDATE CONSTRAINT. Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various reviews, and Robert Hass for documentation wording improvement suggestions. This patch was sponsored by Enova Financial.
Every so often someone asks why sorting behaves irrational. Like here:
$ SELECT string FROM test ORDER BY string; string ---------- dean deer de luca depesz de vil dyslexia (6 ROWS)
Why aren't “de luca" and “de vil" together?
Continue reading Why is “depesz" between “de luca" and “de vil"?
Some (quite long) time ago, someone, somewhere (my memory is pretty fragile) asked a question. I don't have it exact, but the gist was: is it possible to give some users rights to create new users, without making them superusers, and forcing new users to have access only to one particular database.
After some discussion it was clear that the scenario was shared hosting with PostgreSQL, so the situation could look like this:
you are administrator of shared hosting service. One of services is PostgreSQL. You have client, named “depesz", and you want him to be able to create new users, but these users shouldn't be able to connect to any other database than depesz's db.
Is it doable?
And so, it happened. After todays refresh of my Pg, I got:
$ SELECT version(); version ------------------------------------------------------------------------------------------------------------------------ PostgreSQL 9.2devel ON x86_64-unknown-linux-gnu, compiled BY gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit (1 ROW)
Yes. We're in 9.2 development now. Looks like we got another really cool release coming soon.
Thanks a lot to all developers.
First of all, as of today, I added new feature, called anonymization.
It is for all of the people who are afraid that their plans contain information that they don't want to (or can't) share on the internet.
It works like this:
Continue reading explain.depesz.com – new feature and some new stats
Couple of important points:
The fix is important – in some cases, making backups off slave on 9.0 will not work.
By not work I mean that backups would be still done, but Pg wouldn't start from it. If started as slave, it would work OK, but you wouldn't be able to promote it to standalone.
New version of OmniPITR fixes that, as long as you're using -cm switch (and possibly some others like -h, -P, -U or -d – details in docs.
Version information is actually not really simple to get, it requires:
perl -I/opt/omnipitr/lib -le 'use OmniPITR::Program; print $OmniPITR::Program::VERSION'
But it works. It will be changed to normal –version option in near future.
Also, as you can perhaps see, we migrated to github.
Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn't get page count, and easily jump to page number N.
I did some thinking on the subject, and I think I found a way to make it all work. Quite fast. And with not big overhead. Let me show you.
After loooong pause, adding next (well, second) post to the “series“. This time, I'd like to describe how logging works. And I don't mean binary logging (WAL), but the log for us, humans, to read.
Before I will go to the postgresql.conf options, let me first describe what happens when PostgreSQL wants to log something.