SQLite is a fairly straightforward engine, but it feels far less evolved than PostgreSQL, or even MySQL (which itself feels less evolved than PostgreSQL). It provides a set of "pragma" commands to make it easy to query the current database schema (perform introspection).

The thing about these attempts to make it easier to do the introspection is that you wind up with a fairly inflexible set. If you want to do more than is described by the "pragma" commands you either have to switch methodology mid-stream, or just sit S.O.L. and unable to finish. For instance, getting the list of tables in the database requires switching paradigms to doing a raw select with a union across two tables. If you want to know whether an index is unique or primary (cleanly), well... I'm still trying to figure that one out.

What PostgreSQL does is expose the system catalogs directly. There's 36 catalogs, and they require a great deal of documentation to figure things out, but the thing is, once you know what's going on, you have the full power of SQL available for querying the tables. You're also using the same tables that the engine is using, so you aren't having to peer through an abstraction layer where, for instance, someone renames constants into "friendly", but no-longer readily joined values. You can even query the data-types the engine knows about in order to adapt to newer versions of the engine. Oh, and you can run psql with a flag that tells it to spit out the queries it's running when you use it's introspection facilities so you don't generally need to do the work of figuring out the queries yourself.

MySQL takes the same general approach as SQLite, but it's had more people pounding on it to get the implementation further along. It provides a fairly complete set of introspection queries (given the limitations of the engine). They aren't exposing the system catalogs themselves, just synthetic "useful" views on them. In actual pratice I've never had to step beyond the set they provide when writing PyTable, so I don't know how bad it gets when you need just that little bit more.

PostgreSQL's approach reminds me of Python, or even PyPy in some ways. You use a few common constructs everywhere to create a highly generalised and reliable system, then optimise those common constructs to within an inch of insanity. Python does something very similar with dictionaries, it uses them in all sorts of places that a more traditional language implementation would be using array offsets.