What Databases Should Do For Me

When I was a kid Christmas was the most exciting day of the year. I’d rip open the packages to see if I finally owned what I’d spent months hoping for.  In technology we don’t have a scheduled yearly Christmas, but occasionally we get exciting new technology.  And I can dream of the great things I’d like to see.  Here’s my wish list of what I’d like to see databases do:

Automatic Transactions
Many times when testing or troubleshooting I need to see the history of record changes. I wish the following SQL were valid:

    SELECT * FROM item_master
    WHERE item_master.item_id = 475
    FROM SYSDATE – 1 TO SYSDATE

Some of you are going to argue, “What’s wrong with creating a trigger and writing changes to a transactional table?”.  There isn’t anything wrong with that approach, but it’s a time waster: create the trigger, create the table, maintain the table when fields are added/dropped, create reports on the transactional table, blah, blah.  Millions of databases around the world face the same problem and apply the same generic solutions.  Generic problems should be solved in one place, not millions of places.

Automatic Indexing
In my opinion discussions about which database is fastest is usually a moot point. In the practical world the fastest database is the one with the best indexing. (And sufficient IO capacity) Most databases are severely under indexed. Above a certain cost threshold databases should keep statistics on queries and automatically create indexes for costly or common queries. (Probably a combination of the two factors: a very common query with low cost deserves and index as much as a uncommon query with high cost)  The RDBMS would also need to track the cost savings of the new indexes and drop them if they no longer provide sufficient savings.  There would need to be a cap on index creation if a table has too many indexes (i.e. if the indexes are interfering with updates and inserts)  Index creation would need to be load aware so it wouldn’t kick off during the busiest time of the day. . .  as you can see this would not be trivial.

Automatic indexing would not replace manually defined (i.e. permanent) indexes, but it would be awesome.  Especially for purchased applictaions.  Purchased applications are chronically mal-indexed (one reason is that no one customer uses the application in exactly the same way) and no one at the customer sites knows what goes on under the hood to fix it.  Indexing problems in purchased applications are rarely resolved in a timely manner (or at all).

Appeal to MySQL Developers
I wish these features were part of Oracle or SQL Server, but other than natural product evolution I don’t think the giants have much innovation left in them. If we’re going to see innovation in databases it has to come from somewhere else.


Jeff’s Book Recommendations:

The Mythical Man-Month is the software engineering classic. This book should be mandatory reading for the professional programmer.

Advertisements

8 Responses to “What Databases Should Do For Me”

  1. Top Posts « WordPress.com Says:

    […] What Databases Should Do For Me When I was a kid Christmas was the most exciting day of the year. I’d rip open the packages to see if I […] […]

  2. Brian Hart Says:

    Oracle has had these features for sometime (3+ years). Google ‘Flashback Query’ and Oracle. The syntax is almost identical to what you ‘wished’ for:
    SELECT * FROM EMPLOYEE AS OF TIMESTAMP (’13-SEP-04 8:50:58′,’DD-MON-YY HH24: MI: SS’

  3. Bobby Says:

    In the 1990s, one database I used looked at the query and created the missing useful indexes before it ran the query. OK, often there was a big pause before any results appeared. The column(s) to index, seemed a trivial task, any column on the ORDER BY, any column in the WHERE (including joins). I think a new snapshot table was created and indexed as it was generated from the base tables.

  4. jeffspost Says:

    Thanks Brian! That’s an awesome feature. 🙂 I’m surprised I hadn’t heard about it through the grapevine. I’ll have to see if our DBAs are willing to try it out. Thanks again.

  5. Bron Gondwana Says:

    Ahh, yeah – query at a point in the past. I wrote one of those once – it was weird but nice enough to use when you got used to it. Bit odd if you spoke SQL natively since what you did was inserted a (digitally signed) change request to the audit log and a background process made changes to the read-only (to you) reporting tables which actually contained normalised data. Change requests could even include schema changes to the reporting tables, but the main thing was that everything was indexed and repeatable.

    Along with atomic replacement of data rows with entirely new data rows, it made historical queries look something like:

    SELECT {expression you want} FROM (SELECT * FROM raw_table WHERE exists_after = $SEQ))

    Yeah, a little more complex, sure (though the “active” record set is just “valid_before IS NULL”) and very wasteful of space (every revision takes up an entire row. Handy for things where auditability and repeatable reports is the key though.

  6. Bron Gondwana Says:

    My signs got eaten by the HTML gremlins, bah.

    SELECT * FROM raw_table WHERE exists_after <= $SEQ AND (valid_before IS NULL OR valid_before >= $SEQ)

  7. Tom Hume Says:

    PostgreSQL had a feature called “Time Travel” which does exactly what you’re describing; I remember using it err about 8-10 years back..

  8. Links of the Week « I’m just a simple DBA on a complex production system Says:

    […] business and technology blog posted What Databases should do for me. A post with significant number of factual mistakes but with an interesting idea about automatic […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: