PostgreSQL large table SELECT/INSERT performance?

Question

I am in the process of designing a database layout to run on PostgreSQL. In this database i need to handle a lot of addresses, so I naturally want to use an address table and then have my other data just point at the primary key.

I want addresses to be unique, so i plan on using a simple primary key and then a unique index on all of the data columns.

Something along the lines of this:

CREATE TABLE address(id serial NOT NULL, name VARCHAR(32), address1 VARCHAR(32), address2 VARCHAR(32), postal VARCHAR(12), city VARCHAR(64), CONSTRAINT pkey PRIMARY KEY (id), CONSTRAINT unq UNIQUE (name,address1,address2,postal,city));

I anticipate that there will be well over 2 million entries in this table from day one.

The table will be hit by a lot of simultaneous SELECTS and also a few INSERTS. But for data integrity purposes the table will never see UPDATES or DELETES.

What sort of performance can i expect from this kind of table? Is there any performance penalty to using large tables rather than copying the data to where it is needed?

It simple to test. Use your favorite scripting language to generate 2 million sets of random strings, make sure they're unique (either by careful scripting or by sort -u) and load them into the database. For testing, it doesn't matter whether the address looks like an address. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jan 17, 2012 at 17:26

Cjxcz Odjcayrwl · Accepted Answer · 2012-01-17 20:16:52Z

1

It depends how many this queries will be, how strong is your machine, is there a good index that can be used for each query... Generally 2 millions rows for PostgreSQL it isn't a big number, when the query results are good restricted by indexes, especially the search by primary key will be efficient.

But, however, searching via LIKE, with wildcard on beginning, would be a performance issue.

answered Jan 17, 2012 at 20:16

Cjxcz Odjcayrwl

23k43 gold badges149 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Since 9.1, PostgreSQL is capable of using an index for LIKE operation, see: depesz.com/index.php/2011/02/19/…

aneez Over a year ago

The GIN index sounds interesting... I guess ill just have to run a few tests and see for myself.

Collectives™ on Stack Overflow

PostgreSQL large table SELECT/INSERT performance?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related