Tag: postgresql

(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.

Continue reading (postgre)sql gotchas

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 🙂

Continue reading how many transactions per second?

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.

Continue reading better results paging in postgresql 8.2

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 🙂

Continue reading effective finding queries to optimize

rownum anyone? cumulative sum in one query?

one of the nice (for some things) features of oracle is rownum() function.

i assume you know what it does – if you don't – i think a quick google will show it.

today i will show how to make rownum in postgresql. if you're not interested in rownum itself – please continue reading anyway, as some functions shown here have other uses as well 🙂

Continue reading rownum anyone? cumulative sum in one query?

“hacking” with postgresql

< french translation is now available >

very recently i got a task which involved finding out what happened (what sql queries were called) on remote machine.

the problem was that i didn't have any kind of shell access to server – i could connect only to postgresql. the good side was that i had a superuser-level access.

so the task was quite simple – how to find and read a file from psql. on remote machine. with no access to ssh, ftp, nor anything like this.

this is when i got idea that i'll write more about it. some might say that you shouldn't write this kind of information. my view is that i'm not disclosing any secrets – i will be using only basic (remote) psql with superuser access. all things that i will describe in here are in documentation – you just have to know where to look for them.

Continue reading “hacking" with postgresql