-
Working with bitstrings in PostgreSQL
When working with short, fixed width binary data in PostgreSQL (for example: MD5/SHA1 hashes), an option to consider is the bitstring type. It has several advantages: Smaller data storage requirements (also – smaller index sizes). Storing an MD5 hash as characters representing hexadecimal numbers for example takes up 32 bytes, while storing it as bit…
-
Finding unused indexes in PostgreSQL
First some quick DB/index design tips: When you have a 1-1 relationship, you might consider putting the data in one table, instead of several tables and linking them with foreign keys. This will speed up data retrieval considerably. This needs to be balanced with the number of cases when the given values are missing /…
-
Know your transactions and know them well
In the context of databases transactions are usually thought of as a mechanism to make ensure that different batches of work can be executed in parallel, but result be the same as if they would have been executed in series. This is however only the 10,000 foot overview, and as always, the devil is in…
-
Why isn’t my GIN index being used in Postgres after I install the _int contrib package?
While working with some GIN indexes (to speed up the && and <@ / @> operators) I was very surprised to see that after the installation of the intarray (_int) contrib module the query planner stopped using the index. After some poking around it came to me: The contrib module is defining its own operators…
-
Postgres Rocks!
It really does!
-
Kernel 2.6.24 + PostgreSQL != love?
It seems so. The sad thing is that at the moment Ubuntu Server 8.04 (LTS) comes with 2.6.24… Ouch. Hopefully the patches will trickle down quickly. Also, note to myself: newer is not always better. Update: back to the future – 2.6.24. Thank you Marcin for pointing it out. Also corrected the tags.
-
Converting rows (records) to and from arrays in Postgres
Arrays are one of those more special features in PostgreSQL. Like any more esoteric features, you have people both in favor and opposed to them. On the pro side you have the fact that you can have an arbitrary number of elements without wasting space and/or having cumbersome table structure. On the con side you…
-
A (not so new) technique for breaking databases
There is joke which goes something like: those who know how to do it, do it. Those who don’t, teach it. Those who don’t even know how to teach it, supervise it. Sadly this is true for many tech journalists who make up sensationalized titles both because of lack of comprehension and because they have…
-
Avoiding the dogpile effect
When using caching to speed up webpages (or other request-response oriented protocols), it is very common to tie the update of the cache to a new request, meaning that every request checks if the cache is too old first. If not, it returns the value from the cache. If it is, it tries to recompute…
-
Trees in PostgreSQL
depesz has written an other of his great articles. There isn’t really much I can add to it, other than it’s very nice and doesn’t use any PostgreSQL specific elements (like arrays), so it can easily be ported to other DB systems which support triggers. Personally I only had to implement tree structures once, and…