Strain on a table

ollmorris

I am going to have loads of records stored in 1 table. The records will be big in isze (maybe 1000 words). The reason for this is so that they can be searched easily by a user.

How much strain can a table take under this pressure and is it worth it?

Thanks
Oll

stolzyboy

what is loads of records, most databases will laugh at anything up to a million records

Sxooter

It's not about whether the database can take the strain, it's about whether or not the user can handle the wait.

Look into full text indexing for searches. It's a godsend for stuff like this.

And like Stolzyboy says, anything less than a million records is probably not really all that big.

ollmorris

Well I was thinking of about 2000. How much will the user have to wait to access 2 of 2000? Will there be a noticable difference?

Thanks

drawmack

there are a couple of ways you can accomodate this

1) full text indexing
2) use a words table which handles searching and ranking all in one shot.

I'd say 2000 records with a properly indexed database you'll be fine. But try it, put 100 records in it, use a menchmarker to time how long the search takes and then extrapolate. It's not perfect but it's pretty close.

csn

Does PostgreSQL support full-text indexing? If so, how does it compare to MySQL's?

I remember reading Jeremy Zadowsky's blog where he raved and raved about it (and even took some pot shots at the PG crowd).

Sxooter

Yes, postgresql does support full text indexing / searching.

There are now three packages in the /contrib directory to allow for this, the older fulltextindex, tsearch, and tsearch2.

On top of this there's another system to abstract out the full text searching called fts.

You can see fts in action at fts.postgresql.org, where it provides very speedy searches of all the mailing lists for postgresql for the past several years.

Jeremy, by the way, is a bit of a cheerleader, but he does do some nice work. I don't think he's all that knowledgable about real database issues though as evidenced by much of his writing about why MySQL is all you'll ever need.