PostgreSQL is being used for mission critical applications in enterprises and FOSS projects. As part of a SAAS (Software As a Service) solution, database uptime is even more critical. However, databases need just as much maintainance as application code, and if the database isn't designed correctly, even more.
With large databases, refactoring the database tends to cause long downtimes while data is moved around or table structure changes wait for lock availability. Some database refactorings need exclusive locks which block reads as well.
We shall speak about techniques for refactoring the database while maintaining service availability and uptime.
We shall demonstrate how to use PostgreSQL rules and functions to mimic exclusive lock behaviour while not blocking SELECT queries.
Database events generate logging. We will see how to use partitioning
within the database for event log management, including log rotation and archival.
Application programmers often miss out on PostgreSQL's rich native datatype environment and use varchar(n) or integer fields instead. This often leads to invalid data in the database due to missing checks, and makes querying harder than necessary. We will show examples of how to rename columns to more sensible names, and change datatypes using updatable views without any application code changes.
This talk is aimed at programmers and DBAs who realise they have data problems, but cannot really afford much downtime for the database. Attendees with a grasp of basic relational theory and SQL will be able to gain the most out of this talk.
Devdas is a system/network admin, DBA and occasional programmer of Perl. He is currently playing a DBA role at Directi, refactoring a badly designed production database. He is also actively involved in the antispam/anti-malware community, and is usually better known for that work. He has given talks on a variety of topics, ranging from system administration (LCA 2009 sysadmin miniconf), DNS (SANOG XI, LCA 2009 sysadmin miniconf), Mail systems and spam (SANOG IX, SANOG VII, SANOG V, APRICOT 2004, SANOG III, SANOG I, FOSS.IN/2005). When not playing with computers, he spends time trekking, photographing or reading.