How much slower are numerics?
The general knowledge is that numerics are slower than integers/float, but offer precision and ranges that are better. While I understand what is slow, I don’t really know how much slower numerics are. So let’s test it.
Search Results for: bloat
How to make backups of PostgreSQL?
Recently someone was looking for help with script from Pg wiki. I never really checked it, but when talking with the guy needing help, I looked at it. And didn’t really like it.
How I Learned to Stop Worrying and Love the Triggers
Some people are afraid of triggers. Reasons for this are not really understandable for me, but I guess it stems from the fact that these are usually application developers, and not database admins. Or they encountered some kind of problem with triggers, and now they tend to think that triggers are inherently evil. But they … Continue reading “How I Learned to Stop Worrying and Love the Triggers”
Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index
On 25th of January, Tom Lane committed patch: Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX. This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an already-existing UNIQUE INDEX. While the CONSTRAINT isn’t very functionally different from the bare index, it’s nice TO be able TO do that FOR documentation purposes. … Continue reading “Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index”
Understanding postgresql.conf : checkpoint_completion_target
Starting new blog series – explanation of various configuration parameters. I will of course follow no schedule or order – if I’d had to – it would be my job, and in this way – it’s fun. First configuration parameter to write about is checkpoint_completion_target.
To rule or not to rule – that is the question
Every now and then there is someone on IRC, mailing lists, or private contact which asks about rules. My answer virtually always is: don’t use rules. If you think that they solve your problem, think again. Why?
speeding up like ‘%xxx%’
as most of you know postgresql can easily speedup searches using: field like ‘something%’ and (less easily): field like ‘%something’ but how about: field like ‘%something%’ general idea is to use some kind of full text search/indexing – tsearch, lucene, sphinx, you name it. but sometimes you can’t install fts/fti, or it doesn’t really solve … Continue reading “speeding up like ‘%xxx%’”
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 … Continue reading “avoiding “empty” updates”