Migrating from MySQL? Try Pg (PostgreSQL)

[selena & jeff talks about Pg vs Mysql]

• introductions and why your interested
• performance
• 8.3 starts to look to the 'web-app' way of thinking (faster write/ smoother performance)
• basically just use 8.3
• vacuum is the pg update/cleanup process
• now more integrated vs external cron
• MVCC
• non-overwrite storage
• like ZFS
• idea is that an insert is atomic so that readers and writers do not conflict
• allows alter table in transaction and roll back if needed (ONLY IN TRANSACTION)
• thus isolated and atomic
• minimizes wait and restart of application
• wide selection of data types
• xml
• networking
• come with functions at the type level that provide specific actions
• stored procedures in other languages
• perl
• php
• python
• ruby
• lolcode
• pgSql
• allows you to use existing code (perl and pgSql run in safemode)
• clustering
• third-party support currently
• community is selective in what is integrated in to core
• sloney : look in to as the 'go-to' solution for replication/clustering
• warm standby
• export transaction logs to 'replicate' actions...
• basically keeps an almost up-to-date backup
• pgPool2
• also something to look to?
• jPug uses
• another version of the remote transaction log system
• pgcripto
• it's separate only for export restrictions
• query caching
• what options for tuning the query cache
• pgPool2 : also query caches
• memcached : also a possible solution
• seems that the existing method is deal with caching at the application level
• there is some underlying blocks so that the second call is likely to come back quickly but there is no guarantee
• seems more like a fs cache
• what does the mysql-user need to move pg?
• mysql-dump translator
• DB mirror thingie (possibly a transaction-log translator, or even just a query translator)
• dummy/training mode for pg cli client
• a things to watch out tutorial
• a one to one doc on 'show tables' => '/whatever'
• have some abstraction layer for other languages
• convert queries from mysql-mode
• warnings when you do non-standard SQL in pg
• its in the docs but it could be cool in the cli
• have a code scanner that looks at queryies that will flag mysql
• specific queries
• there's some "LAMP" stack in one click on windows... pg could be in on that
• there's PGadmin3
• less hate on mysql
• or more relevant discussion
• admin/login
• naggios already has a plugin
• ptop (like innotop)
• there are others but it could be better
• why should some one starting out now pick pg
• pg is more standard (or tries to adhere to SQL standard better)
• current language drivers
• it's more like oracle
• thus its more knowledge that is transferable
• there's a good community
• devs are available
• the mailing list is active
• responsive and accurate
• the BSD license is a plus for PG
• you can re
• ship PG
• GIS extensions for PG
• therethere'ss a guy in Eugene. if you want
• there's some guy that is using the GIS stuff to model sound to find the location of an artist based on a recording... crazy
• tSearch
• full-text searching plug-in
• it's now in 8.3
• major upgrades
• yes you have to do a full dump but it's for your own good
• the steps are well documented
• its more about commitment on maintained code base
• there's a lot of work on this but there's been a lot of this but it's not done yet
• it's something that a distro might try and do for you
• but it's more likely that you have to do by hand
• there might also be syntax issues with stored procedures
• it's not a hard process its just tedious
• mostly rebuilding indexes
• more data = more wait
• there's a PSU prof that is setting up an entire curriculum that he is building around PG
• he comes to the pdxPG meetings
• selena's switcher plug
• if you want in contact her