Slashdot Mirror
Why To Choose PostgreSQL Over MySQL, MariaDB (dice.com)
Nerval's Lobster writes: PostgreSQL, MySQL, and MariaDB are the three "main" open-source relational databases available today (there are four if you count FireBird, but for brevity we're excluding it). For years, MySQL had a reputation of being faster than PostgreSQL, but much of that was due to the MyISAM database engine, which didn't support transactions. On the flip side of things, PostgreSQL had a reputation for being slower but more reliable. But with the recent versions of both platforms, things have started to change; for example, speed has been less of a problem for PostgreSQL, while MySQL now defaults to the InnoDB engine, which does handle transactions. According to developer David Bolton, here's why PostgreSQL is worth a second look for your database-management needs (Dice link).
Try my product... I guarantee you will be satisfied with it or your money back... here's how to order...
My eyes reflect the stars and a smile lights up my face.
Postgres is an excavator, while the other two are bobcats. For heavy lifting, the choice is obvious.
Well, not so fast. For single server, PostgreSQL is superior (although less user friendly from the shell) But Mysql / MariaDB still beat PostgreSQL when it comes to replication.
At least for me, the killer feature of PostgreSQL is its procedure language PL/pgSQL. By a fortunate accident, I had the opportunity to write some complicated features (read "calculation heavy") for a web app using PL/pgSQL. Once coupled with triggers, you can just leave everything to the DB to the point, controller has to do nothing except query and return JSON objects to the front. It is so expressive, powerful, efficient and reliable.
I have worked with MySQL, Oracle, MSSQL and as of late MongoDB.
Given a choice, I will always settle for PostgreSQL... it is just so natural to work with.
post-gres-cue-ell.
Now give it a try! :-)
I use it for everything except the stuff where sqlite is more appropriate.
pr0n - keeping monitor glass spotless since 1981.
Postgres also has a lot of nice features. For example, I love table inheritance and don't understand why it's not more common of a feature - I probably use it in about 80% of the databases I make these days. It's just so logical and useful for real-world data (which often has at least some degree of heirarchial structure), and avoids having to hack together triggers or query logic to emulate it.
I hate to bring up our imminent arrest during your crazy time, but we gotta move.
And not because MySQL is a better product, but because everyone thinks of MySQL as the database that isn't a very good choice for large projects. You should be comparing Postgres to SQL Server, Oracle, etc. -- the big guys.
DBA's are known for being rational and objective when discussing competing RDBMSs, I'm looking forward to this discussion. Maybe next we could discuss which is better Islam, Christianity, or atheism.
I came to the datacenter drunk with a fake ID, don't you want to be just like me?
Table Inheritance is purely awesome.
A good structure is having a commonality across all tables.
My personal set that I like are the following
UID,
CreateDateTime,
ModifiedDateTime,
Enabled,
Archive,
ArchiveFromUID
Without it such feature, it is too easy to get into a habit of adding non-standard naming conventions, and actions. Especially if it was originally suppose to be a quick lookup table which happened over time become the core table for the data entry in the system.
If something is so important that you feel the need to post it on the internet... It probably isn't that important.
and PostgreSQL has pgFouine http://pgfoundry.org/projects/...
Like I'm going to trust a Dice "insights" page to tell me what DBMS to use...
MySQL is easy until you want to do something interesting with it (which you usually do, at some point). Then it becomes a huge pain in the ass and you wish you'd done things right in the first place (i.e. you wish you'd gone with PostgreSQL).
SQLite is fine for multiuser-read / singleuser-write. Also for built-in per-instance DBs in applications. Which covers a heck of a lot of use cases, online and not. Something else that's pretty awesome is it is trivial to compile SQLite right into an application. This serves both to make the application less complicated to install, and to ensure that the DB format, behavior and performance won't change when other parts of the host system change. Less opportunity for Apple / Linus / Microsoft / etc. to Break Your Shit(TM)
Within the Python2 environment, where I do a lot of my work, I use a convenient wrapper for SQLite (and another for PostgreSQL.)
Both DBs are very useful to me. I looked at MySQL and wasn't convinced there was any benefit to adding it to my toolbox, so... none of that. :)
I've fallen off your lawn, and I can't get up.
I would expect the issue to be MVCC, not FKs. Both Postgres and Firebird do MVCC, which incurs overhead when writing data (never overwrite, always add delta records, then fix pointers so readers can follow the chain, and also cleanup deltas no longer needed by any active transactions) and when reading data (follow pointer chains, verify a given record should be visible to the current transaction despite it being listed in the slightly-larger index), etc.
The switch from myISAM to InnoDB brings MVCC with it (in addition to, as you point out, actual constraints) so the cause/effect may be unclear.
I happen to use and love FB, it's been rock-solid for me for over a decade now, but I've never pushed it for *performance* reasons. It's always been about the features: MVCC that always works (unlike Oracle's, and on-by-default unlike SQLServer's), transactional triggers (came in really handy), triggers that do what you intend (unlike SQLServer's), better temporary-table mechanisms than SQLServer, better stored procedures (selectable like a table-valued-function, but can read & write like a stored procedure). There's no equivalent to PostGIS, though, and there's no built-in replication method beyond shadow databases. And other stuff that a Wikipedia page would be better at explaining.
If you're serious about speed, I'd love to see benchmarks to back you up. If you're trolling, I hope readers will consider Firebird anyway, it really is a good DBMS.
My software stores spacecraft testing data. Each test is good for a couple of gigabytes, and we run dozens each year. We use PostgreSQL because:
- Rock-solid stability. Zero data loss after a decade and a half of operations. Zero problems of _any kind_, over that same period.
- Great features and excellent standard conformance.
- Documentation is absolutely excellent, best of any open source project I know of.
- pgAdmin 3 allows trivial on-the-fly inspection of databases.
- No licensing issues. No payment "per core", "per connection", or whatever other bullshit they've come up with now. Install where we like, as much as we like.
We didn't choose MySQL because it lacked ACID compliance (data loss would be problematic), and because it has entirely too many weird gotchas. Sure, you can work around all of those... But why would you if you can also choose PostgreSQL?
As for Oracle, that's what we used before PostgreSQL. Back in the days, you couldn't store more than 2000 characters in a string, their C interface was the stuff of nightmares, support tools were non-existent, and installing it yourself, or on anything that wasn't the Blessed HPUX Cluster, was impossible. We had two (minor) data loss events in three years, but that might have been a hardware issue. But the killer reason is of course licensing - with Oracle, we had one server on which all work had to take place. Now we have dozens, and setting up an ad-hoc server for some quick testing is trivial - both technically, and in terms of licensing.
I can take a laptop to a customer site and do a demo or some work, without worrying about licensing. With Oracle you never know whether you are compliant or not, and being found to be non-compliant is extremely, extremely painful.
Everyone I know just calls it "Postgres".
This was its original name. It was the successor (Get it? "Post"?) to the RDBMS known as Ingres. There was some IP issue associated with the Postgres name when they went to open source it, so they gave it a new name: PostgreSQL. Yes, I agree it was a very poor choice, something only a true nerd would come up with.
The issue I quickly realized with mysql when I tried to replace an aging IBM DB2 data warehouse (with a total of 10 billion rows) was that Mysql only supported a small subset of the SQL standard. This quickly lead to almost all of our normal queries for BI not even being able to be ported. After trying to make it work anyway I eventually gave up. I had recently started using postgresql in my home server set up since I didn't know when direction Debian was going to take with the whole mysql/maridb debacle. So I convinced my boss to scrap the mysql idea and try again with Postgres. I was amazed at how well the SQL standard was supported by postgres. It also had a far superior query planner that mopped the floor compared to mysql performance for any query with more than a couple of joins. I also like how postgres isn't owned by any one entity like mysql. It is all I use now.
I'm a Sql Server guy myself (I spent a brief period as #1 user by rep within the sql-server tag on Stack Overflow back in 2009), but Postgresql does offer some nice language features missing in Sql Server. It also has table inheritance and for larger servers can save you a LOT of licensing costs. It performs pretty well these days, too. I agree that MySql is toy, though. Still no windowing functions after 10+ years as part of the ansi standard, awful handling of NULLs, and no FULL JOINs are just three of the many reasons that MySql is and has been for some time only the 2nd best open source DB in most categories. The only reason it's popular today is because of the self-perpetuating nature of popularity. People like it because it's what they've known, and it's what's been available.
It is not true that there are no indices. What this statement means is that Indices do not span tables. If you put an index on the parent, that index ONLY applies to the parent. You can put the same index on the child.
We use inheritance like this: ...
components
\ interfaces - constraint = Interfaces only
\ ports - constraint = Ports only
\ memory - constraint = Memory only
I can select from 'components' and find everything, but I can select from interfaces and not see ports. .. Smaller tables, faster access, and yet a report can select from components to find all types of things.
MariaDB and MySQL are basically the same thing. It comes down to licensing and vendor preference. But Postgresql vs MySQL vs Sqlite is just a question of what your use case is.
Sqlite is for the prototyping, small projects, and small foot print. Its an amazing piece of software and solution for its niche. It is probably the most widely used DB out there. Extremely easy to setup, program against, and test. And very forgiving.
MySQL is for the small to large size operations. Easiest to setup and manage for the feature set you obtain. It is fast and reliable and has a lot of 3rd party support. Most devs work in this area and I think this is why it is used so much. It is also many folks first "personal" testing DB and thus has a lot of momentum. You can use it at the enterprise level, but not really where it shines. Its like taking a Camry and putting a HEMI in it. It works, but that's all we can really say about it. Use when migrating an existing solution is too costly.
Postgresql is large to enterprise level projects. I place it between MSSQL and Oracle. Its a wonderful software minus the "Dedicated Vendor Support" toilet paper that PHBs love. Extremely feature rich. But it needs enterprise level care and maintenance processes just like the others. You can use it on small projects, but its really over kill.
This is the same discussion we been having since 2005. Each system has improved a lot, and their use cases overlap more, but the general logic on which is best to use is still the same.
Not the same. JetProfiler shows you, in realtime and with pretty pictures, what fucked up stuff you're doing to your database. pgFouine is:
"pgFouine is a PostgreSQL log analyzer written in PHP. It is based on PQA, the Practical Query Analyzer written in Ruby. pgFouine aims to be able to parse huge logs and to have a nice and useful HTML output."
Yeah, welcome to 1995.
Depends on what kind of replication you need.
It does pretty decent asynchronous master->slave replication.
You can also have a mirror with synchronous writes.
Multi-master replication- there are some 3rd party tools to do that, still pretty young and immature AFAIK.
Clustering- there are some 3rd party projects to do that, some commercial.
More info here: https://wiki.postgresql.org/wi...
--Coder
I recommend either PostgreSQL or SQLite. PostgreSQL is so easy to install and set up, though, that I would recommend SQLite only if you don't control the server.
It's pronounced Postgres Q L, if you want to say it all the way. But it's okay if you just want to say Postgres. Even the database's default superuser is still called postgres.