new commit info from cvs:
Log Message: ----------- Stamp releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20.
it doesn't mean that we will see them today, but it means that we should be getting them really soon now 🙂 (at least that's what i hope)
Tag: postgresql
avoiding “empty” updates
just recently i saw a very interesting situation.
one table in customer database (with about 1.5 milion rows) was *very often* updated. using series of simple:
update table set field = .., where id = ...
updates always updated 1 record, search was using primary key, so it was quite fast.
what was strange was the fact that the table get about 20 times more updates then the next most updated table.
after careful checking i found out that a lot (about 60-70%) of the updates actually didn't change anything!
they were simply setting values that were already there.
so, i started to think about hwo to avoid this kind of situation.
and this is what i found out.
(postgre)sql gotchas
i would like to write about a things that are not really errors by themselves – they are simply situations when database acts in undesired way. a way that's perfectly logical, but where the logic is not always clearly seen “at first sight".
so, don't expect any postgresql-bashing. if you want some, indicate so in comments, i'll find some things to bash postgresql for.
but in here i'd rather bash us – people – for making mistakes. the ones that are not really easy to see as mistakes for database.
indexable “field like ‘%something'” – update
thomas reiss just published his follow up to my post about indexed searches in “like ‘%something'" cases.
i don't understand french so i can't tell what exactly he wrote, but i understand c code and graphs 🙂
basically – he wrote c function that does reverse() of strings, and benchmarked it against pl/pgsql and pl/perl versions. of course c version is the fastest, but just take a look at how much faster it is. and how simple it is.
how many transactions per second?
i wanted to know how many transactions per second is my machine processing.
how to do so? a simple select to pg_stat_database will do the job (actually 2 selects 🙂
but since i have to write it anyway, perhaps i can/should make it so it will print the current value continuously?
and, while i'm at it, some kind of graph wouldn't be bad 🙂
postgresql tips & tricks
faber4 on irc asked about how to get ascii-based sorting, while his postgresql was initdb'ed with utf-8 based locale (en_US.UTF-8 to be exact).
what can we do about it?
better results paging in postgresql 8.2
some time ago merlin moncure wrote about one of new features of postgresql 8.2 – row-wise comparison.
i also read about it, but at first didn't find anything very useful about it – after all it doesn't give you any new functionality. any kind “(a, b, c) > (…)" can be written with standard column-based operators. so it's basically just a syntactic-sugar.
that's true. but merlin pointed me to not-so-obvious benefit – basically it allows much better paging of results. how? let's see.
postgresql tips & tricks
luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?
effective finding queries to optimize
let's imagine simple situation – you have postgresql server. configuration was fine-tuned, hardware is ok. yet the system is not really as fast as it should.
most common problem – slow queries.
second most common problem – fast queries, but too many of them. for example – i once saw a system which did something like this:
- select id from table;
- for every id do:
- select * from table where id = ?
reason? very “interesting" orm.
now i'll show you how i deal with these kind of situations 🙂
securing your postgresql database
now, some time ago i wrote about how to “hack" system with postgresql. today i'll write more about how to make pg database as safe as possible.
i'll go through several steps, tell you what's possible, what's easy, and what is not so easy 🙂
so, let's start the tutorial.
Continue reading securing your postgresql database