Enrico Stahn

11 Jun 2010

PostgreSQL - Add primary key to an existing Table

The next months are full of hard work because we have to master a huge migration task. We migrate all SQL-Statements to an ORM Layer. The application is already a bit long in the tooth and has approximately 8000 queries. The database itself has some strange design issues and some of them are incompatible with the ORM layer. One of this issues is the lack of a primary key at some tables. In PostgreSQL we can solve this with the following steps:

Add a column with type integer to your table
Create a sequence
Update the column table with sequence values
Set the necessary column properties (e.g. default, not null, etc.

Example for table “foo” and column “id”:

ALTER TABLE "public"."foo"   ADD COLUMN "id" INTEGER;
CREATE SEQUENCE "public"."foo_id_seq";
UPDATE foo SET id = nextval('"public"."foo_id_seq"');
ALTER TABLE "public"."foo"
  ALTER COLUMN "id" SET DEFAULT nextval('"public"."foo_id_seq"');
ALTER TABLE "public"."foo"
  ALTER COLUMN "id" SET NOT NULL;
ALTER TABLE "public"."foo" ADD UNIQUE ("id");
ALTER TABLE "public"."foo" DROP CONSTRAINT "foo_id_key" RESTRICT;
ALTER TABLE "public"."foo" ADD PRIMARY KEY ("id");

27 Jun 2009

PHP's pg_connect via pgpool-II vs pgBouncer vs native

In the last few weeks one of our customer had some performance issues with our web application. We discovered a lot of opportunities in our infrastructure that would improve the performance. I’m currently working on an article series about those experiences.

Whatever, if you’re running a high-traffic PHP application with a PostgreSQL database as backend you might be running into problems with slow repsonse times of your pages caused by pg_connect. This was one of the things happens to our application.

To solve this issue you can use a connection pooling software like pgpool-II or pgBouncer. I’m not going to explain what this makes or show how this works in theory. I just give you some statistics fresh from one of our servers to think about it. The webserver is running on Apache 2 + fcgid + PHP (CGI). This explains why the first benchmark results are low and increasing by and by.

Overview

1) PHP file without a database connection

/etc/init.d/apache2 restart ; \ 
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ..
Requests per second:     55.04 [#/sec] (mean)
Requests per second:    117.04 [#/sec] (mean)
Requests per second:    108.67 [#/sec] (mean)
Requests per second:     99.30 [#/sec] (mean)
Requests per second:    114.79 [#/sec] (mean)
Requests per second:    155.76 [#/sec] (mean)
Requests per second:    152.06 [#/sec] (mean)
Requests per second:    142.65 [#/sec] (mean)
Requests per second:    154.77 [#/sec] (mean)
Requests per second:    149.60 [#/sec] (mean)

2) PHP file with pg_connect to PostgreSQL

/etc/init.d/apache2 restart ; \
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo2.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ..
Requests per second:     8.50 [#/sec] (mean)
Requests per second:    12.47 [#/sec] (mean)
Requests per second:    12.14 [#/sec] (mean)
Requests per second:    12.50 [#/sec] (mean)
Requests per second:    12.50 [#/sec] (mean)
Requests per second:    12.39 [#/sec] (mean)
Requests per second:    12.38 [#/sec] (mean)
Requests per second:    12.34 [#/sec] (mean)
Requests per second:    12.01 [#/sec] (mean)
Requests per second:    11.91 [#/sec] (mean)

3) PHP file with pg_connect via pgpool-II

/etc/init.d/apache2 restart ; \
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo3.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ...
Requests per second:     41.43 [#/sec] (mean)
Requests per second:     94.64 [#/sec] (mean)
Requests per second:     80.83 [#/sec] (mean)
Requests per second:     85.47 [#/sec] (mean)
Requests per second:     73.03 [#/sec] (mean)
Requests per second:     87.65 [#/sec] (mean)
Requests per second:     84.18 [#/sec] (mean)
Requests per second:    103.66 [#/sec] (mean)
Requests per second:    105.99 [#/sec] (mean)
Requests per second:    104.72 [#/sec] (mean)

4) PHP file with pg_connect via pgBouncer

/etc/init.d/apache2 restart ; \
  for c in {1..10}; \
  do ab -n 1000 -c 100 http://localhost/phpinfo4.php 2>&1 | \
    grep "Requests per second"; \
  done
Restarting web server: apache2 ... waiting ...
Requests per second:     47.53 [#/sec] (mean)
Requests per second:    106.67 [#/sec] (mean)
Requests per second:     90.49 [#/sec] (mean)
Requests per second:     66.29 [#/sec] (mean)
Requests per second:    102.98 [#/sec] (mean)
Requests per second:    129.06 [#/sec] (mean)
Requests per second:    115.56 [#/sec] (mean)
Requests per second:    132.95 [#/sec] (mean)
Requests per second:    127.06 [#/sec] (mean)
Requests per second:    126.64 [#/sec] (mean)

What are your experiences and which one do you prefer? Why do you prefer pgpool-II over pgBouncer or pgBouncer over pgpool-II?

PostgreSQL - Add primary key to an existing Table

PHP's pg_connect via pgpool-II vs pgBouncer vs native

1) PHP file without a database connection

2) PHP file with pg_connect to PostgreSQL

3) PHP file with pg_connect via pgpool-II

4) PHP file with pg_connect via pgBouncer

Enrico Stahn's Space

GitHub

Social Networks

Search

Archive