[Summary view] [Print] [Text view]

   1  This document describes the state of Postgres support in MediaWiki.
   2  
   3  
   4  == Overview ==
   5  
   6  Support for PostgreSQL has been available since version 1.7
   7  of MediaWiki, and is fairly well maintained. The main code
   8  is very well integrated, while extensions are very hit and miss.
   9  Still, it is probably the most supported database after MySQL.
  10  Much of the work in making MediaWiki database-agnostic came
  11  about through the work of creating Postgres support.
  12  
  13  
  14  == Required versions ==
  15  
  16  The current minimum version of PostgreSQL for MediaWiki is 8.1.
  17  It is expected that this will be raised to 8.3 at some point,
  18  as 8.1 and 8.2 are nearing end of life.
  19  
  20  
  21  
  22  == Database schema ==
  23  
  24  Postgres has its own schema file at maintenance/postgres/tables.sql.
  25  
  26  The goal is to keep this file as close as possible to the canonical
  27  schema at maintenance/tables.sql, but without copying over
  28  all the usage comments. General notes on the conversion:
  29  
  30  * The use of a true TIMESTAMP rather than the text string that
  31  MySQL uses is highly encouraged. There are still places in the
  32  code (especially extensions) which make assumptions about the
  33  textual nature of timestamp fields, but these can almost always
  34  be programmed around.
  35  
  36  * Although Postgres has a true BOOLEAN type, boolean columns
  37  are always mapped to SMALLINT, as the code does not always treat
  38  the column as a boolean (which is limited to accepting true,
  39  false, 0, 1, t, or f)
  40  
  41  * The default data type for all VARCHAR, CHAR, and VARBINARY
  42  columns should simply be TEXT. The only exception is
  43  when VARBINARY is used to store true binary data, such as
  44  the math_inputhash column, in which case BYTEA should be used.
  45  
  46  * All integer variants should generally be mapped to INTEGER.
  47  There is small-to-no advantage in using SMALLINT versus
  48  INTEGER in Postgres, and the possibility of running out of
  49  room outweighs such concerns. The columns that are BIGINT
  50  in other schemas should be INTEGER as well, as none of them
  51  so far are even remotely likely to reach the 32 billion
  52  limit of an INTEGER.
  53  
  54  * Blobs (blob, tinyblog, mediumblob) should be mapped to TEXT
  55  whenever possible, and to BYTEA if they are known to contain
  56  binary data.
  57  
  58  * All length modifiers on data types should be removed. If
  59  they are on an INTEGER, it's probably an error, and if on
  60  any text-based field, simply using TEXT is preferred.
  61  
  62  * Sequences should be explicitly named rather than using
  63  SERIAL, as the code can depend on having a specific name.
  64  
  65  * Foreign keys should be used when possible. This makes things
  66  both easier and harder in the code, but most of the major
  67  problems have now been overcome. Always add an explicit ON DELETE
  68  clause, and consider carefully what choice to use (all things
  69  considered, prefer CASCADE).
  70  
  71  * The use of CIDR should be done very carefully, because the code
  72  will sometimes want to store things such as an empty string or
  73  other non-IP value in the column. When in doubt, use TEXT.
  74  
  75  * Indexes should be created using the original MySQL tables.sql
  76  as a guide, but keeping in mind the ability of Postgres to use
  77  partial indexes, functional indexes, and bitmaps. The index names
  78  should be logical but are not too important, as they are never
  79  referenced directly by the code (unlike sequence names). Most of
  80  the indexes in the file as of this writing are there due to production
  81  testing of expensive queries on a busy wiki.
  82  
  83  
  84  == Keeping in sync with tables.sql ==
  85  
  86  The script maintenance/postgres/compare_schemas.pl should be
  87  periodically run. It will parse both "tables.sql" files and
  88  produce any differences found. Such differences should be fixed
  89  or exceptions specifically carved out by editing the script
  90  itself. This script has also been very useful in finding problems
  91  in maintenance/tables.sql itself, as it is very strict in the
  92  format it expects things to be in. :)
  93  
  94  
  95  == MySQL differences ==
  96  
  97  The major differences between MySQL and Postgres are represented as 
  98  methods in the Database class. For example, implicitGroupby() is 
  99  true for MySQL and false for Postgres. This means that in those 
 100  places where the code does not add all the non-aggregate items 
 101  from the SELECT clause to the GROUP BY, we can add them in, but in 
 102  a conditional manner with the above method, as simply adding them 
 103  all in to the main query may cause performance problems with 
 104  MySQL.
 105  
 106  
 107  == Getting help ==
 108  
 109  In addition to the normal venues (MediaWiki mailing lists
 110  and IRC channels), the #postgresql channel on irc.freenode.net
 111  is a friendly and expert resource if you should encounter a
 112  problem with your Postgres-enabled MediaWiki.