Sunday, September 30, 2007

Tablespaces in MySQL, Oracle and Postgres

If you are not familiar with tablespaces you may be wondering what the big deal about them is. Tablespaces are a logical addition to a database that helps maintenance, and potentially, can improve performance.

In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).

The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files. Postgres support of tablespaces is minimal. In MySQL and Oracle, performance can be improved by a more granular spread of data across disks. Ease of maintenance is maintained due to the logical grouping of tablespaces.

Oracle syntax for creating a tablespace is much the same as MySQL but with many more options. Oracle also allows a single tablespace to be made up of many data files.

Below is a very simple example of creating and using a tablespace in MySQL:

mysql> create tablespace testts
-> add datafile 'myfirstfile'
-> engine = falcon;
Query OK, 0 rows affected (0.48 sec)
mysql> create table testmyts (
-> abc integer )
-> tablespace testts;
Query OK, 0 rows affected (0.11 sec)

The first command creates the tablespace (naming it testts) and assigning it a file name of myfirstfile. Of course, we are using the falcon engine. If you look in your MySQL data directory after running this command, you should a new file named myfirstfile.

The second command creates a table using our new tablespace. If you look at the tablespace now, it should be bigger. If not, insert a bunch of rows and watch it grow.

Oracle syntax, in its simplest form, is very close to MySQL syntax. Here is the same example in Oracle.

SQL> create tablespace testts
2 datafile 'myfirstfile'
3 size 10M;
Tablespace created.
SQL> create table testmyts (
2 abc integer )
3 tablespace testts;
Table created.

In Oracle, we leave off the engine keyword and we need to declare a size. We can create a small data file and use autoextend if we want, but we MUST declare an initial size.

In Oracle, it is possible to create each partition of a partitioned table in its own tablespace. I'm not sure if that's possible with MySQL, but I plan to try it out!


Magnus Hagander said...

"Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows)."

Postgres supports tablespaces fine on Windows, assuming you don't use a FAT drive. And if you're using FAT, you deserve whatever you get ;-)

LewisC said...

Does it? Is that new with 8.2? I tried with 8.1 and it didn't support it. I will go back and refer to the docs.

Thanks for the update!


LewisC said...

Maybe it's a documentation issue. The doc says:


Tablespaces are only supported on systems that support symbolic links.

How does that work on windows, even an NTFS?