Slashdot Mirror

If you are using hand-rolled SQL, most MySQL queries will execute on Postgres without much modification. However, MSSQL will be vastly different.

For example, look at these ugly MSSQL queries with explicit locking, which you will probably have to use as developers and DBAs can't seem to agree on a standard isolation mechanism:

SELECT COUNT(UserID) FROM Users WITH (NOLOCK) WHERE Username LIKE 'foobar'

and

UPDATE Users WITH (ROWLOCK) SET Username = 'fred' WHERE Username = 'foobar'

Also, there is no LIMIT / OFFSET keywords in MSSQL, you have to do crazy shit like:

WITH results AS (
        SELECT
                rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
                , *
        FROM tableName
)
SELECT *
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

Source: http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

You will soon realize that the Express version is super-limited (4GB max size, 1 GB ram, 1 core, no replication, etc.)
Source: http://www.microsoft.com/sqlserver/2005/en/us/compare-features.aspx

Postgres is highly tunable, but the defaults (that ship with many OSes) are for small footprints. This is an older document, but still relevant with explanations and the annotated config guide (bottom of page). Throw 8 cores and 16GB ram at Postgres, tweak the conf a tiny bit, and the feature set and performance will surprise you.

Tune Postgres: http://www.varlena.com/GeneralBits/Tidbits/perf.html

There's no reason to use MSSQL unless all of your development and applications are on Windows, and your development team can't use anything other than their IDEs in a limited way. Once you start using Postgres, and realize the power behind it, you'll never want to use anything else.

If, for some strange reason, your company wants to spend money and buy DB support, go for a commercial vendor of postgres. Enterprise DB has some nice management features: http://www.enterprisedb.com/products/index.do

It is a bit of lore. It is a difference between the two products that you might not have considered before migrating that you better take into account. I didn't even think about it, and I almost got into a huge jam as a result.

SELECT count(uid) FROM users WHERE username=BINARY('coryking');

Except that breaks unicode. Characters aren't byte[]'s, they are characters.

The proper answer is to use the correct collation. The only thing is PostgreSQL doesn't really do that kind of thing yet.

My solution was to create a new datatype based on this example that uses case-insensitive operators. Instead of using the "varchar", I my spiffy new, case-insensitive "ltext" version. Fully indexable too!

Google is your friend...

http://www.varlena.com/GeneralBits/Tidbits/perf.ht ml

http://www.revsys.com/writings/postgresql-performa nce.html

There is also good material in chapter 4 (Performance) of "PostgreSQL" by Douglas and Douglas (Sams Publishing Developer's Library)

I don't think it's so much a case of elitism -- it's just that not every application needs to be overburdened by a complete GUI frontend. The documentation for PostgreSQL is quite rich actually: see http://www.postgresql.org/docs/8.1/interactive/ind ex.html for the latest sets. The amount of work that goes into detailing nearly every aspect of configuring, running, and working with this database system would seem to indicate that the developers are far from lazy. Not bothering to read said documentation on the other hand ... There is also an extremely helpful set of mailing lists, wikis, and related sites (such as the General Bits site: http://www.varlena.com/varlena/GeneralBits/ ) such that keys to the "mysteries" of various parameters and how to tweak one's server for best performance are quite readily available.

Actually, it takes all of 10 minutes to get replication working reliably.

Try this: link for more information, howtos and scripts to assist you in setting up replication.

- Having to run vacuum all the time to help the query optimizer figure things out. Why this doesn't happen automagically in the background without me having to worry about it is beyond me.

The MOST IMPORTANT THING you need to know about running postgresql is how to set the shared_buffers setting correctly.

Look at this page and follow how to tune it for your particular memory size. This will save you a LOT of heartache. You'll see speed increases from that one setting alone of ten or twenty times. Good luck -- this is critical:

http://www.varlena.com/varlena/GeneralBits/Tidbits /perf.html

-Dom

As somebody who uses postgres a lot, if you haven't done so already you should take a look at the configuration parameters (debian sticks them in /etc/postgres(ql?)/postgresql.conf or something like that). By default it usually ships with fairly conservative parameters, and you can tune it for much higher performance (albeit at the expense of using more ram). I seem to recall using this article to get started on that, though as with any database performancing tuning is a black art involving query optimization, schema optimization (table design, indexing, etc.), parameter optimization, and hardware tuning.

PgSQL had a very steep lerning curve even for people with experience in other databases. Various unusual stuff just popped up in places which could notbe expected from previous experience elsewhere.

IMO, it has a "very steep" learning curve because so many people have to unlearn all the bad habbits from MySQL. If you're coming to PostgreSQL, and can be bothered to read and follow directions for 30-minutes to an hour, IMO, you'll be on the same footing. In my opinion, the there is a curve, but it's not steep at all, assuming you're not coming from MySQL, where you've been taught to do things counter to just about every other product out there.

How about COUNT(*) and other aggregates?

That's a great question! Sadly, select count(*) is still slow and don't imagine that changing any time soon, at all. Many other aggregates are now faster. These are incremental changes that have been creaping in over the last four of fives releases. Aside from select count(*), PostgreSQL offers a very speedy solution. The general run is, if you really need a select count(*), keep a derived count somewhere, which is probably going to be faster anyways. Ya, no ideal, but a perfectly valid solution for most.

MySQL is nearly maintanence-free. It just runs and runs.

Perfectly valid comment. However, for the last several releases, there has been an autovacuum daemon available, which takes care of the vast majority of these situations. More below.

PgSQL needs VACUUM and friends.

This is true, however, with the autovacuum daemon, it pretty much takes care of it self. One of the things you'll see in 8.0 is that the daemon is now integrated into the postmaster. This will make it even easier to use and setup.

Once we tried to move a database (from MSSQL) with market data to PgSQL (I think it was 7.4). A lot of stuff was inserted, and a lot of selects performed. Running vaccum/vacuum analyze once in 24 hours was not enough!

That's not all that surprising. It's also possible that you needed some additional tuning on your database, to properly reuse all of your pages, when they were vacuumed.

By the end of the day performance of selects was abysmal. Every time we ran VACUUM ANALYZE, it took longer, and longer, and longer.

This is one of those situations where it pays to be proactive. Assuming you had it configured properly, to correctly reuse all of your pages, you may still needed to run vacuum more than once per day. But, that's not nearly as bad as it sounds. Had you running it, say 20 (wild number here) times a day, via cron, chances are, the whole system would of stayed fast and happy. Once you fall behind the curve, then you'll always be playing catchup. Worse, if you're not reusing all of the pages (tuning issue), performance will continue to get worse and worse, proportionaly to the amount that your not reusing, compounded by not vacuuming often enough.

Here is a great PostgreSQL tuning referencea

What about the problem with memory management and cache? I see it's being addressed in 8.0. I have to test 8.0, but with 7.4 it was impossible to run PgSQL efficiently as a dedicated server - grab all available memory and cache everything aggressively. It was very frugal about memory even when it was not necessary.

I assume you mean as a non-dedicated server. Also, assuming you're running on Linux, and using a current kernel (2.6.x), you can tuning the "swappiness" of the kernel, which greatly helps both dedicated and non-dedicated installations.

And, of course, the query optimizer. But that's understandably difficult. I have to see if improvements in 8.0 are significant

Well, you are right that the optimizer is a tough nut. This is why so many give up and allow hints and junk. Just the same, the optimizer generally works pretty well. It's really just a matter of

The real problem with Postgresql, however, is that if you are doing lots of updates where the keys increase forever, the index files grow forever. You can, of course, drop and recreate them (which we do in a cron job), but in a real 24/7 environment you've got a real problem when your queries all turn into table-scans because the indexes aren't built yet.

Here is some more information (seeIndex Maintenance? )

The only option I know if is to have two sets of tables and swap between them.

-- ac at work