nonplus

Using Google's Closure Compiler in Phing

2011-09-19T21:46:00+00:00

Phing is a build tool for automating and managing php deployments. One great feature is the ability to minify your .js files during the build process. Unfortunately Phing uses jsmin to accomplish this instead of the newer, more efficient compilers like Google’s Closure Compiler.

To add minifying via Closure I created this Phing task. The API is very similar to that of jsmin, e.g.

<taskdef name="jsCompile" classname="phing.tasks.my.JsCompileTask" />
<jsCompile compilerPath="docroot/bin/compiler.jar" targetPath="docroot/script/minified" failOnError="false">
    <fileset dir="docroot/script">
        <include name="**/*.js"/>
    </fileset>
</jsCompile>

Happy Phinging!

Using bits to store tags, or perhaps not

2011-09-15T16:35:00+00:00

In a previous post I looked at using PostgreSQL arrays w/ GiN indexes to store tags for quick subset and overlap matching. I noticed soon after implementing this on filmlust (for the film genres) that some queries were taking longer than they should have. Namely, queries with a genre filter in conjunction with a highly selective filter, such as a high minimum vote count.

So, I had a look at a query plan (note: I map the genres to integers for a smaller footprint, so this is actually looking for all of “action”, “adventure”, “classic”):

filmlust=> explain analyze select id from title WHERE imdb_count >= 5000 AND genre @> '{6,7,19}';
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on title  (cost=101.26..120.59 rows=5 width=4) (actual time=4.533..4.593 rows=55 loops=1)
   Recheck Cond: ((genre @> '{6,7,19}'::smallint[]) AND (imdb_count >= 5000))
   ->  BitmapAnd  (cost=101.26..101.26 rows=5 width=0) (actual time=4.522..4.522 rows=0 loops=1)
         ->  Bitmap Index Scan on ix_title_genre  (cost=0.00..8.82 rows=175 width=0) (actual time=3.922..3.922 rows=372 loops=1)
               Index Cond: (genre @> '{6,7,19}'::smallint[])
         ->  Bitmap Index Scan on ix_title_imdb_count  (cost=0.00..92.19 rows=5305 width=0) (actual time=0.563..0.563 rows=5296 loops=1)
               Index Cond: (imdb_count >= 5000)
 Total runtime: 4.620 ms

And there’s the crux of the issue. In a perfect world it would perform the one scan on the imdb_count index, which is faster than the genre GiN index, and then scan the few remaining rows (5296 here) for genre matches. I could make this happen with a multicolumn index, but because of the large variety of filters I have it’s not a viable option as there would be too many permutations. Thus, Postgres is forced to do two separate index scans on queries like this (see “combining multiple indexes”).

Drop the index? Surely you jest.

One way to get around this is to drop the genre index, so Postgres would first do the index scan on the fast, selective, imdb_count condition, and then filter those rows.

filmlust=> drop index ix_title_genre;
DROP INDEX
filmlust=> explain analyze select id from title WHERE imdb_count >= 5000 AND genre @> '{6,7,19}';
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on title  (cost=92.19..2169.77 rows=5 width=4) (actual time=0.838..5.229 rows=55 loops=1)
   Recheck Cond: (imdb_count >= 5000)
   Filter: (genre @> '{6,7,19}'::smallint[])
   ->  Bitmap Index Scan on ix_title_imdb_count  (cost=0.00..92.19 rows=5305 width=0) (actual time=0.569..0.569 rows=5296 loops=1)
         Index Cond: (imdb_count >= 5000)
 Total runtime: 5.253 ms

Unfortunately that wasn’t any faster because the comparisons, as few as there may be, are too expensive. Perhaps there’s another way of storing genres to speed up the “Filter” step in the above query process.

Enter the bits

If we pack the genres into a bit string we can compare them very quickly with bitwise operations while still maintaining the small memory size I crave. Adding a mere bit for each of the 24 genres to every row. To implement this technique we’ll first need to map all the genres to a position in the bit string. The one I use for filmlust is at the bottom of this file (coincidentally this is how I do the integer mapping I noted above). So the bit at the 4th least significant position (2³⁾ would be on if the film contained the “foreign” genre.

Querying for all genres

Here’s the results for returning the same rows as the two examples above:

filmlust=> explain analyze select id from title_bit WHERE imdb_count >= 5000 AND genre & B'000010000000000011000000' = B'000010000000000011000000';
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on title_bit  (cost=96.97..1794.30 rows=28 width=4) (actual time=0.771..4.572 rows=55 loops=1)
   Recheck Cond: (imdb_count >= 5000)
   Filter: ((genre & B'000010000000000011000000'::"bit") = B'000010000000000011000000'::"bit")
   ->  Bitmap Index Scan on ix_title_bit_imdb_count  (cost=0.00..96.96 rows=5562 width=0) (actual time=0.554..0.554 rows=5296 loops=1)
         Index Cond: (imdb_count >= 5000)
 Total runtime: 4.602 ms

Huzzah! It cut the time by a whole .018ms. Well, that wasn’t the huge gain I was looking for, but I figured it warranted further investigation.

Benchmarks

The following benchmarks were run with my production code, so it includes the limits for pages and a second query to obtain the row count. The first column describes the conditions: small cull = a not very selective condition (leaves about 1/3), large cull = a selective condition (the imdb_count condition above, leaves only 1/30 of the rows). The second and third columns are the time it took to execute using the array and bit string types, respectively. The fourth column is the difference (array – bits).

                              Arrays      Bits        Diff
all 1 genre                   0.01839     0.06916     -0.05077
all 3 genres                  0.00949     0.06374     -0.05425
any 3 genres                  0.02175     0.06315     -0.04140
all 1 genre, small cull       0.00969     0.04161     -0.03192
all 3 genres, small cull      0.00982     0.04101     -0.03119
any 3 genres, small cull      0.01587     0.02574     -0.00987
all 1 genre, large cull       0.01072     0.00770     0.00303
all 3 genres, large cull      0.01053     0.00713     0.00339
any 3 genres, large cull      0.01759     0.00604     0.01155

There’s a definite performance advantage to using the bit strings with the more selective conditions, about 1.5x for matching any genre and 3x for matching all, but they’re a bit slower when flying solo or combined with a condition that doesn’t cut the data down very much.

Matching any genre

For posterity here’s how to match an overlap of genres (i.e. the film contains any of “action”, “adventure”, “classic”):

filmlust=> explain analyze select id from title_bit WHERE imdb_count >= 5000 AND genre & B'000010000000000011000000' != B'000000000000000000000000';
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on title_bit  (cost=98.34..1795.68 rows=5534 width=4) (actual time=0.758..4.767 rows=2228 loops=1)
   Recheck Cond: (imdb_count >= 5000)
   Filter: ((genre & B'000010000000000011000000'::"bit") <> B'000000000000000000000000'::"bit")
   ->  Bitmap Index Scan on ix_title_bit_imdb_count  (cost=0.00..96.96 rows=5562 width=0) (actual time=0.548..0.548 rows=5296 loops=1)
         Index Cond: (imdb_count >= 5000)
 Total runtime: 4.903 ms

Conclusion

You can eke out a little more performance using bit strings for tags/genres depending on the other conditions in your typical query. If they’re highly selective, use bit strings, otherwise you’ll probably be better off with the array data type. I’m sticking with the arrays for now because they are somewhat easier to work with and the queries are still a bit faster in many of my use cases. I’ve started logging user interaction to get a handle on which filters are most popular so I may switch over to bit strings in the future if necessary.

One caveat: a major potential shortcoming of the bit string method is the size. With my data I only have 24 possible genres yielding 24 bits per row. There is an average of 4 genres per film which would yield an array size of ~64 bits (4 smallints) per row. So the bit strings are nearly a third of the size. However, if you implemented this with 1000 possible genres the arrays would probably be the lighter option because you would need that massive 1000 bit column to hold the bit string for each row.

Thus, as usual, there’s no “one size fits all” solution, so pick your poison carefully.

Aggressively caching CSS media with Sass and Tornado

2011-09-02T20:22:00+00:00

Tornado comes with a nice static url feature for including static files in your templates. It appends hashes to those urls and turns on really aggressive file caching, since the hash bearing url of the requested static file will change whenever the file itself changes, thus busting any browser caching. Unfortunately there’s no inherent means of dealing with the urls in the css (or scss as the case may be) files (e.g. png icons).

To solve this there are a few (probably more) options that I thought of: regex replacements before deployment, making the css files into a tornado template and processing them, adding a hook to sass, or adding the same static_url method to Sass’s arsenal using custom functions. I opted for the last method and added the following to config.rb in order to extend Sass and add a new static_url function for .scss files.

The perfect French press cold brew

2011-08-25T15:35:00+00:00

Ah, the cold brew. Nothing quite hits the spot on a hot summer morning. I (over?) indulged in it frequently over the past few months and at this point I’d say unequivocally that I prefer it to hot coffee, regardless of the season. Most of the people online were using regular drippers to home brew the stuff so I figured I’d write this guide for using a French press, a most magical apparatus for both hot and cold alike. Though, it’s so simple and intuitive as to render a tutorial nearly superfluous.

In any event, after a bit of experimentation I’ve settled on the process and measurements for extracting the optimal cold brew cup from a French press. I haven’t found the ideal beans yet, as I prefer to try something new every time, but I generally opt for something dark that’s heavy on the chocolate notes. I’ve noticed the chocolate flavor, for which I have a particular affinity, really gets amplified with the cold brew process. Recently I rather enjoyed the Stinky Bkln blend (ultimately from D'Amico) and a Guatemalan roast from Gorilla.

Without further ado…

Ingredients

0.5 cups fresh coarsely ground coffee (the usual for a French press)
3.5 cups water
A jug of sorts. I use a glass (no BPA!) quart bottle. Previous tenant: milk.
Cold storage area (e.g. a refrigerator)

Process

Pour the coffee into the French press.
Add the water and stir vigorously with a spoon.
Put the uncovered press into cold storage for 12-24 hours.
Put the jug in next to the press (pouring the cold brew into a warm container sucks).
After the time has elapsed, take the brewed coffee out
Break the coffee cake on the top and lightly stir.
Scrape off the “fines” that remain hovering (demonstration).
Cover and press!
Let it sit for a minute or so.
Unload the cold brew into the cool container (or straight into a mug) and partake.

The length of the brewing process is up for debate; some swear by 24 hours and others 12. I always do the latter mostly because I don’t want to go sans French press for a whole day and it works out nicely to do the preparation after dinner, let it sit overnight, and then have it waiting in the morning.

One of the great things about cold brewing is it doesn’t go bad within the hour like hot coffee. You can brew a large quantity and store it in the fridge for up to a week without tarnishing the flavor.

Note: I’m sorely lacking in the precision measurement department at the moment, but I’ll hopefully acquire a scale soon and write an update with more accurate numbers.

Managing virtualenv apps with Supervisor

2011-08-18T16:57:00+00:00

I’ve been using supervisor a lot to manage my server processes and I’ve grown quite fond of it but it can make things a bit tricky when running apps contained in a virtualenv sandbox. There are a couple workarounds: setting all the environment variables that virtualenv exports in its bin/activate script or using a bash script to launch the app from within the virtual environment. I chose the latter route because it’s pretty simple and it can be convenient to have the script around for other tasks. The script, which I place in a my_app/script folder, follows.

And in the supervisor conf file:

[program:my_app_0]
user=my_user
autostart=true
autorestart=true
command=/home/my_user/my_env/my_app/script/env_run.sh python main.py -p 8880

[program:my_app_1]
user=my_user
autostart=true
autorestart=true
command=/home/my_user/my_env/my_app/script/env_run.sh python main.py -p 8881

In the last line of the script using exec to start the program is somewhat important because it replaces the bash script’s process with the python process, thus giving supervisor control of the app itself instead of the bash script. You can check the process ids to verify. Here’s a quick test I ran on my local box.

# Running without exec (don't actually daemonize (the "&") with supervisor)
scott@c ~ $ /home/scott/webdev/my_env/my_app/script/env_run.sh python main.py -p 8888 &
[1] 5152
scott@c ~ $ ps -ef | grep main.py
scott     5152  4642  0 12:30 pts/19   00:00:00 /bin/bash /home/scott/webdev/my_env/my_app/script/env_run.sh python main.py -p 8888
scott     5161  5152 15 12:30 pts/19   00:00:00 python main.py -p 8888
#
#
# Running with exec...
scott@c ~ $ /home/scott/webdev/my_env/my_app/script/env_run.sh python main.py -p 8888 &
[1] 3740
scott@c ~ $ ps -ef | grep main.py
scott     3740 27015  7 12:24 pts/9    00:00:00 python main.py -p 8888

No more bash middleman!

Note: the above examples were done for a tornado deployment.

Launching chromium from xmonad prompt shortcuts

2011-08-17T01:50:00+00:00

For some unknown reason chromium seems only to accept UTF-8 encoded urls for its cli arguments. This conflicts with xmonad which pipes ISO-8859 text when it spawns processes via shortcuts (e.g. the XMonad.Actions.Search module). To get around this you can point the default browser (xmonad uses the $BROWSER env variable in bash) to a script which uses the unix iconv program to sanitize xmonad’s text for chromium input and then launches chromium with the purified url. It also logs all the xmonad-spawned searches to a file for my future amusement.

In .bashrc:

export BROWSER="${HOME}/.chromium_opener.sh"

And .chromium_opener.sh…

Voila!

Using PostgreSQL arrays with GiN indexes for fast tag filtering

2011-08-16T19:06:00+00:00

The setup

A major feature on sites like yelp and filmlust is the ability to quickly drill down search results by filtering in/out unwanted tags. In a typical normalized relational db the tags would have a many-to-many relationship with the main objects and they would be stored in a separate table with an association table joining the two. This leads to painfully slow filtering queries. A better method is to add a “tags” column using PostgreSQL’s array type to the main table and store the tags as text in there. For example…

Create a new table

CREATE TABLE posts (post_id INTEGER, title TEXT, content TEXT, tags TEXT[]);
INSERT INTO posts (post_id, title, content, tags) VALUES (1, 'el posto numero uno', 'witticisms here', '{"bacon", "chunky"}');
INSERT INTO posts (post_id, title, content, tags) VALUES (2, 'el posto numero dos', 'mas witticisms here', '{"chunky", "fauna"}');
INSERT INTO posts (post_id, title, content, tags) VALUES (3, 'el posto numero tres', 'plus witticisms here', '{"bacon", "fauna"}');

Alter an existing table

ALTER TABLE posts ADD COLUMN tags TEXT[]; #add the array of text values
UPDATE posts SET tags='{"bacon", "chunky"}' WHERE post_id=;

Querying

So the table is all set to go and the tags column is populated for all your items. Another advantage of using arrays over normalization is more intuitive queries, particularly for some of the more complex queries. To search for all posts tagged with “bacon” with the traditional many-to-many setup…

SELECT p.* FROM posts AS p JOIN posts_tags_assoc AS pta ON p.post_id=pta.post_id JOIN tags t ON pta.tag_id=t.tag_id WHERE t.name = 'bacon';

vs. the new array columns…

SELECT posts.* FROM posts WHERE 'bacon' = ANY (tags);

This is certainly more elegant, but the array type really shines when using the array operators to do overlaps and subsets. To find all posts which are tagged by “chunky” or “bacon” (an overlap) or to find the posts tagged by both “chunky” and “bacon” (a subset, or “is contained by” in postgres verbiage) in the normalized tables:

SELECT DISTINCT(p.*) FROM posts AS p JOIN posts_tags_assoc AS pta ON p.post_id=pta.post_id JOIN tags t ON pta.tag_id=t.tag_id WHERE t.name IN ('bacon', 'chunky'); # overlap
SELECT p.post_id, p.title, p.content FROM posts AS p JOIN posts_tags_assoc AS pta ON p.post_id=pta.post_id JOIN tags t ON pta.tag_id=t.tag_id WHERE t.name IN ('bacon', 'chunky') GROUP BY p.post_id, p.name, p.content HAVING COUNT(pta.post_id) = 2; # subset

vs. the new array columns…

SELECT posts.* FROM posts WHERE ARRAY['chunky', 'bacon'] && tags; # overlap
SELECT posts.* FROM posts WHERE ARRAY['chunky', 'bacon'] <@ tags; # subset

Not only is this easier on the developer but it’s going to be a bit faster since you’re not joining three tables together.

Optimizing with GiN indexes

At this point there’s a fully functioning table which you can query and it’s probably a little bit quicker than original many-to-many method. However, we can make it much faster by indexing the new tags array. The standard postgres index types (bitmap, hash, etc.) don’t work with arrays but fortunately there’s the oft overlooked GiN index which can make these queries really fly. Before adding the index let’s take a look at what’s going on behind the scenes when we run one of the above queries. I’m using the filtering table from filmlust for this since postgres won’t use the indexes without a significant amount of data in there.

mydb=> explain analyze select id from films_test WHERE 'action' = ANY(tag);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on films_test  (cost=0.00..6666.27 rows=8507 width=4) (actual time=0.034..61.595 rows=14767 loops=1)
   Filter: ('action'::text = ANY (tag))
 Total runtime: 62.396 ms
(3 rows)

mydb=> explain analyze select id from films_test WHERE ARRAY['action','adventure'] <@ tag;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on films_test  (cost=0.00..4926.15 rows=174 width=4) (actual time=0.123..75.030 rows=3340 loops=1)
   Filter: ('{action,adventure}'::text[] <@ tag)
 Total runtime: 75.269 ms
(3 rows)

So it’s doing a normal sequential scan and doing row-by-row comparisons. Now, to add the GiN index:

CREATE INDEX ix_posts_tags ON "posts" USING GIN ("tags");

And running the queries again we get:

mydb=> explain analyze select id from films_test WHERE 'action' = ANY(tag);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on films_test  (cost=0.00..6666.27 rows=8507 width=4) (actual time=0.035..61.593 rows=14767 loops=1)
   Filter: ('action'::text = ANY (tag))
 Total runtime: 62.343 ms
(3 rows)

So that first query remained the same because the index isn’t used when using the ANY function. The GiN index only kicks in with the operators shown here. Trying it again with the overlap operator:

mydb=> explain analyze select id from films_test WHERE ARRAY['action'] && tag;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on films_test  (cost=4.83..557.34 rows=174 width=4) (actual time=2.926..8.755 rows=14767 loops=1)
   Recheck Cond: ('{action}'::text[] <@ tag)
   ->  Bitmap Index Scan on ix_films_test  (cost=0.00..4.78 rows=174 width=0) (actual time=2.533..2.533 rows=14767 loops=1)
         Index Cond: ('{action}'::text[] <@ tag)
 Total runtime: 9.461 ms
(5 rows)

mydb=> explain analyze select id from films_test WHERE ARRAY['action','adventure'] <@ tag;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on films_test  (cost=4.83..557.34 rows=174 width=4) (actual time=2.761..5.229 rows=3340 loops=1)
   Recheck Cond: ('{action,adventure}'::text[] <@ tag)
   ->  Bitmap Index Scan on ix_films_test  (cost=0.00..4.78 rows=174 width=0) (actual time=2.517..2.517 rows=3340 loops=1)
         Index Cond: ('{action,adventure}'::text[] <@ tag)
 Total runtime: 5.427 ms
(5 rows)

Now we’re cooking with grease! A solid order of magnitude performance increase when using the proper index and corresponding operators.

Notes

Disadvantages

One downside is the management of the tags themselves. It can become more tenuous to do some operations such as changing the name of the tag. Whereas in a normalized schema you just change one column in one row of the tags table you now need to search through the entire posts table and update all the tags arrays. There also could be some issues maintaining consistency since you don’t really have one list of tags anymore. It’s not to difficult with data like mine on filmlust, where all the tags are known beforehand, but it becomes hairy once you have users inserting/updating all sorts of things on something like delicious. One possibility for amelioration here would be to still carry that separate tags table but cut out the middleman and put the tag_ids directly into the tags array in the posts table.

Size

Usually when denormalizing a schema you face the prospect of growing the physical size of your db exponentially due to all the newly redundant data. For something as small as a tag this isn’t really the case. Before there was an association table which had a post_id and a tag_id for every tag/post combo so we’re only adding the difference of the average tag length (~10 chars = 10 bytes) and the two id integers (~2 * 4 = 8 bytes) (ignoring the table overhead and the entire separate tags table). A paltry 2 bytes, so I don’t think this is much of an issue here since the content itself is going to be much much larger.

Pragmatic language experimentation

2011-04-11T02:37:00+00:00

I purchased the book Seven Languages in Seven Weeks as a Christmas present for myself in order to dabble in several languages and hopefully choose one or two of my favorites to pursue further. I’m three languages deep at the moment and so far the book has fulfilled this expectation.

It’s a good resource for quickly grokking a new language as it’s meant to be used as opposed to merely translating the ubiquitous C/Java/etc. approaches to solving problems. I especially like that the book is targeted towards experienced programmers who have already picked up multiple languages. Not much space is devoted to the tedious particulars of each language’s syntax, since these can be found readily via google or the main documentation. Instead the focus seems to be on the aspects of the language which are unique and the problem domains for which it is particular apt. For example, there is a section for prolog, which is an exceptional language for quickly finding solutions to constraint based problems. A huge chunk of this chapter walks through writing a program for solving sudoku puzzles, a problem well suited for prolog. This exercise isn’t used for any of the other languages, since, well, none of the other languages are quite as appropriate for the task.

You can definitely tell it’s a work by “The Pragmatic Programmers” as one of the goals throughout seems to be exposure to as many tools (i.e. languages) as possible so that you can pick the right one for your next project. Thus providing the programmer with a screwdriver to drive that screw instead of the more familiar sledgehammer.

I’ve put all my code up in a github repo (including the exercises) and I plan on sharing my thoughts on each of the languages in the days to come.

Thank you, Glaucon

2011-02-01T03:22:41+00:00

In reading Plato’s The Republic I’ve been entirely befuddled by Socrates on more than one occasion. The beauty of the piece is that it’s a dialog and the various interlocutors are often as nonplussed as myself. I’ve found that usually during these times of bewilderment one only has to read on for Glaucon, Adeimantus, etc. to request and subsequently receive an explanation from Socrates himself. Sure beats trying to make sense of some Socrates’ verbal soup myself.

“However,” I said, “of all things that are such as to be related to something, those that are of a certain kind are related to a thing of a certain kind, as it seems to me, while those that are severally themselves are related only to a thing that is itself.”

“I don’t understand,” [Glaucon] said.

“Don’t you understand,” I said, “that the greater is such as to be greater than something?”

…

Dynamically adding methods to a Rails model

2011-01-24T18:24:36+00:00

I’ve been using CanCan for implementing role-based access in my rails app and it’s been great so far. I had created a separate convenience method in the User model for testing each possible user role, e.g.

def admin?
  self.role == 'admin'
end

This was great and allowed me to do a simple user.admin? to test for role membership. After changing my roles around a few times I wanted a way to avoid the tedium of creating/editing those methods. I began experimenting with Ruby’s powerful class manipulation abilities and came up with this:

It dynamically adds all those methods based on a simple list of possible roles. I’ve only been using Ruby for a short while and the ease with which I was able to accomplish such a feat was pretty impressive. Score one for Ruby.

Creating a new enum type in sqlalchemy with the type decorator

2011-01-19T18:17:00+00:00

I wasn’t satisfied with the built-in EnumType that SQLAlchemy offers for column definitions because it’s limited to using the native sql enum type or a large varchar. The native sql enum isn’t a bad choice if you’re using MySQL, but I’ve found the postgres (which is what I’m using) implementation to be too rigid since you can’t easily alter the list of possible values. The varchar is overkill and takes up too much storage space.

I settled on storage using a small integer due to its small footprint while still allowing a large bucket of values. I was initially planning on using the integers in the code and trying to remember what each integer represented but my feeble mind wasn’t up for the challenge. So I found the SQLAlchemy TypeDecorator class which makes it dead simple to create a custom column type:

Using the new type is very similar to the generic EnumType:

#in your table declaration
animal_table = sa.Table("title", meta,
    sa.Column("name", sa.types.Unicode(31), nullable=False),
    sa.Column("type", EnumIntType(("dog", "cat", "narwhal")), nullable=False),
    )

#in a query
session.query(Animal).filter(Animal.type == "cat").all()