Archive for the ‘Database’ Category
PostgreSQL foreign keys and indexes
![[PostgreSQL]](https://scottmoonen.com/wp-content/uploads/2008/12/postgres.gif?w=95&h=51)
If you’re a frequent user of MySQL, you may be familiar with the fact that all MySQL table constraints automatically create indexes for you. This is true of the InnoDB foreign key constraints, for which “an index is created on the referencing table automatically if it does not exist.”
If you’re switching or considering a switch to PostgreSQL, you should be aware that not all PostgreSQL table constraints will automatically create indexes for for you. In PostgreSQL, a UNIQUE or PRIMARY KEY constraint on one or more fields will implicitly create an index for you. However, in PostgreSQL a FOREIGN KEY constraint will not automatically create an index for you.
For each of your foreign key constraints, you should evaluate whether you want to create an index. You may want to do this for optimizing your own queries, but be aware that it can also help to speed up DELETE queries on the referenced table and UPDATE queries on the referenced field. This is because any foreign key reference must be located to enforce whatever ON DELETE and ON UPDATE behavior is in effect for the constraint.
A performance comparison of AF_UNIX with loopback on Linux
On various Linux hosts I use either MySQL or PostgreSQL for my back-end database. Typically the database is running on the local host, and this raises the question of how to connect to the database. For both MySQL and PostgreSQL it is possible to connect using a local AF_UNIX connection, or using an AF_INET connection over the loopback socket (address 127.0.0.1).
I had thought that it might actually be more efficient to connect over loopback; for my day job I work on the TCP/IP stack for the z/OS operating system, and I know firsthand that our TCP/IP implementation is heavily optimized, including specific optimizations for local traffic.
I decided to test this hypothesis by comparing both the throughput and latency of AF_UNIX and AF_INET connections. I was running on a Celeron Dual-core system running Debian 4.0 with kernel version 2.6.18-4-686. The results disproved my hypothesis, at least for Linux:
- AF_UNIX:
- 100GB transferred in 80.575200 s
- 100 million 8-byte messages:
- Average latency 14.463910 μs
- Standard deviation 0.003376 μs
- AF_INET, loopback address
- 100GB transferred in 226.717520 s
- 100 million 8-byte messages:
- Average latency 1133.444424 μs
- Standard deviation 0.067419 μs
So, for both throughput and latency, on the Linux platform AF_UNIX is a superior choice to AF_INET. For local MySQL and PostgreSQL connections you should use a local socket rather than the loopback socket.
I gotta have my orange juice. 