Partitioning may be used as a quite elegant solution of a wide variety of problems - distributing the table to multiple drives (if you don't want to use a traditional RAID), effective removal of archive data (without DELETE), etc. It may be automatically realized using triggers or rules. Lets see a puzzler related to partitioning realized with rules.
If you have ever engaged in performance tuning in one of the newer Oracle versions, maybe you have met the legendary statspack (see Oracle Statspack Survival Guide), or its successor Automatic Workload Repository (AWR), whose main principle lies in creating "snapshots" of important internal values (number of queries, transactions, disk reads, cache reads, etc) and consequent analysis of differences between snapshots. It is not widely known that there is a similar tool for PostgreSQL called pgstatspack, which references to Statspack even by its name - it is based on the very same principle (analysis of snapshot differences), and although it is more simple, it may provide invaluable services when tuning performance.
About a year ago I've bought an Eee PC 1000H netbook made by Asus - right from the beginning I've been using it with Gentoo which is my favourite distribution, with a KDE 3.5. There were some issues but most of the time it ran just fine. But as I'm a notorious breaker and I ignore most of the messages produced by portage, the netbook reached a point of no return - there was no other way than a fresh install - this time with a KDE 4.3 environment.
I forwarn you this is not an installation guide - after all we have a great Gentoo Handbook for that. It's rather a collection of useful and interesting information specific for the 1000H (and maybe other netbooks too), eg. kernel configuration, use flags, issues I've ran into, etc.
When developing applications, every now and then it's necessary to test application's behaviour in case the database server suddenly "dies." In the PostgreSQL mailing list there was an interesting thread, describing several interesting ways how to simulate this (from very elegant ones to quite brutal ones). Let's see various ways of killing PostgreSQL ...
It is said that "A road to hell is paved with good intentions," and it's true even for SQL queries. A lot of problems is caused by developers who strive to optimize the database, frequently without necessary knowledge of the internals.
Let's check another source of problems - structure of a table. Again, this is a very broad topic, so I'll present just one very common example, I guess you'll be able to come up with many variants.
Last week I've posted a first puzzler related to generating random numbers or rather selecting random rows in PostgreSQL, believing that it's first and maybe the last puzzler for a long time. Well, today I've found a similar feature in Oracle - so lets begin!
And now let's check some problems with the SQL queries and indexes. There are two basic problems related to indexes - using an index when the index should not be used (false positive) and not using an index when it should be used (false negative). If you don't know what indexes are or if you're not sure how they work, read something about it - for example my article basic principles of database indexes describes basics of indexes.
First (and so far last) SQL puzzler, related to some basic concepts from relational databases, as for example primary keys. Note - this puzzler is quite closely adherent to PostgreSQL database (it uses some PL/pgSQL functions to generate example data, but that's not all).
It may be surprising, but many of the performance problems with SQL statements are originally caused by mistakes of the administrators. Some of these problems are closely related to the particalar database system. Thera are infinitely many ways to misconfigure a system or a database - let's check at least two quite frequent problems.
