Poker Tracker on PostgreSQL

[Twitch1977]

I posted this at the poker tracker forums, but I thought it might be of intrest here as well.

With a little farting around I have moved my PT database to a postgres server and have PT connecting and fully functional.

Now my duct tape solution is far from optimal, I was able to get this functioning by recreating the database structure in a postgres database, then creating a 'shell' PT database in MS Access and importing the postgres tables as linked tables in Access. Then I would load this shell database in poker tracker, so when PT would try to access the tables, the external links would automatically refer it to the postgres database.

Now here's what I've observed so far. Using a small database, so far the speeds seem comparable to a normal PT database, the initial load time is a bit less, but it's not that bad (using a small database). Once I ran a vacuum and analyze on the postgres database it made it considerably faster. The different tabs in PT for each player can be accessed relatively quickly, with the exception of the Position Stats tabs which takes slightly longer then a normal database.

But here's what this accomplishes:
- No limit to the number of hands that can be stored in the database.
- As the number of hands in the database increases I would expect the postgres database to eventually become faster then the MS Access style database.
- Database sharing, since postgres databases are designed to have several people accessing it at once, it should be possible for several people to use a single PT database concurrently. I haven't tested this yet.
- It would be possible with some modification to have external programs like GT+ and PlayerView make direct calls to the postgres database which should prove faster then using the Access database.

Here's some drawbacks:
- I have only done testing using a small database thus far, so a lot of the positive effects are theoretical. I don't have a large database to test with.
- It's not the easiest thing in the world to set up, but if there's intrest I could do up some templates to make things easier.
- Importing a large amount of hands at a time seems to take a while, but a few hands at a time (auto-importing) shouldn't be bad.
- MS Jet Engine which handles the ODBC database calls is garbage, it is likely what will end up limiting how fast this can work.


Anyways this is just some quick info on some of the junk I've been working from, if people express intrest I'll be happy to help people get this going. I don't have a large database to test with, so if you have a bit database and a working knowledge of PostgreSQL I'd be interested in talking to you. If you know how to optomize PostgreSQL and it's databases I could really use a hand with that.

Cheers,
T

[krishanleong]

Go download my database from www.pokerhandhistories.com if you want to test using a large database. (300K Hands) ~1.4GB

Krishan