18 ms·
PostgreSQL Rising
- juiceandjuice 14y agoI'd love to move away from Oracle to Postgres, I really would. I'm trying to. But for massive amounts of data the partitioning and some other features of Oracle just work better. The partitioning is a huge thing, especially for our data which is partitioned by week then organized according to a hierarchical triangular mesh with bitmapped indexes. This works so well for us (at 8 billion rows) it's silly. MySQL couldn't do this and hacking this into Postgres was sloppy. Stored procedures in Oracle work pretty good as well (especially because of shared memory), but I'm trying to eliminate them as much as possible to try to accommodate other people. I'm quick to complain about Oracle (especially the fucking cost based optimizer and how it falls back to retarded hash joins for me frequently, leading me to add hints, rewrite SQL or run the tuning advisor), but for it's faults, it does some things pretty well. That being said, I'm wholeheartedly rooting for Postgres.
- robconery 14y ago> our data which is partitioned by week then organized according to a hierarchical triangular mesh with bitmapped indexes Sounds like you're getting your money's worth out of Oracle - which is a good thing. For many others (especially those in the .NET world) they use very few of the high end SQL Server stuff.
- juiceandjuice 14y agoYeah, there's a lot of other things we use it for. We have a lot of hierarchical data that uses the CONNECT BY statement, and I hate it because it has issues with scaling and bad execution plans, so I'm working on migrating all of that to a closure table instead.
- va_coder 14y agoDoes Postgres have an equivalent Connect By feature? Last I checked it didn't. If you don't need to scale your hierarchical data much, its pretty handy. I'd prefer to go to Postgres too.
- rsutphin 14y agoPostgreSQL 8.4+ has recursive common table expressions. I don't know if they do everything that CONNECT BY does, but I know that they do everything that I've ever used CONNECT BY for.
- einhverfr 14y agoSince way back when the tablefunc contrib module had a connectby() function, but it has some limitations being a C function. The better way is to use WITH RECURSIVE CTE's. See http://ledgersmbdev.blogspot.com/2012/07/ctes-and-ledgersmb.html http://ledgersmbdev.blogspot.com/2012/07/ctes-and-ledgersmb.... for how we use them in LedgerSMB for hierarchical data.
- zzzeek 14y agoWITH RECURSIVE is the SQL standard version of what CONNECT BY provides with oracle. Postgresql and SQL Server both have it.
- benrhughes 14y agoThere's a reason Oracle can charge an arse-load of money, and it's not because they're "evil": it's because their DB does some frigging incredible things when you know how to use it. I've worked with a 4 billion row star schema, partitioned by day then sub-partitioned for query optimization. It was OK to be "stale", so we inserted each day's data into an indexless table then swapped it in as a new partition over night. Billions of rows in performance critical apps is pretty edge case. I think a lot of people paying for Oracle could pretty easily migrate away.
- SCdF 14y agoAs much as I hate Oracle you're right, they are very much in the Cisco "makes the easy things hard and the hard things possible" zone. Nearly every situation I've used Oracle under would have worked fine in any free DB (including SQLite3 in some silly instances) but I don't doubt there are situations out there where it excels.
- smilliken 14y agoIf you'd like to give Postgres a serious shot, I'd recommend taking a look at Citus: http://citusdata.com/ http://citusdata.com/. It's a distributed database forked from Postgres, and can easily handle billions of rows in realtime for certain use-cases. We use it at MixRank to store and run analytics on for ads we crawl in the internet (a respectable amount of data), and it handles extremely well. (Full disclosure: I know the Citus team well). I've also pushed Postgres to its limits and suspect you'd be able to make it work with that much data, though I wouldn't consider it a trivial exercise executing that transition on a production system. If you're seriously considering it though, feel free to reach out via the email in my profile.
- jeltz 14y agoWhile your partitioning scheme should be implementable in PostgreSQL using table inheritance I do not doubt it is simpler to manage in Oracle. The PostgreSQL partitioning is lacking in usability and also a bit in features.
- chris_wot 14y agoThere could be a good reason for Oracle to choose a hash join. Do you have a large table joining to a small table? Multiple processors? Setting the PGA might also be influencing optimiser... The biggest issue with Oracle is that even experts don't really know how the optimiser does something. Tuning queries in Oracle s a bit of a black art. It really shouldn't be!
- jeltz 14y agoIn PostgreSQL tuning queries is generally quite easy once you have learned to read the EXPLAIN output. The EXPLAIN output makes it obvious most of the time why the planner chose the plan it did. This is also an area which has received improvements in every recent release. Both the explain output and the cost based planner.
- spudlyo 14y agoThe complaint that MySQL is by default loosey-goosey with your data is valid, but it's an easy default to change. Here is what happens when you run some of the commands shown in that 'Why Not MySQL?' video on a sanely configured MySQL system by setting SQL_MODE to TRADITIONAL. This mode also allows you to not have dates with zeroes, etc. mysql> alter table test change column my_money my_money decimal(2,0); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into test values (4,'bar', 100); ERROR 1264 (22003): Out of range value adjusted for column 'my_money' at row 1
- robconery 14y agoYep - easy to alter the defaults. My point was that these are the defaults which I consider silly.
- spudlyo 14y agoYes, it's a silly default, but I don't think it's the fatal flaw that you make it out to be. That's ok though, this is clearly an advocacy piece, so I don't expect nuance. If none of that freaks you out, then I have one word for you: Oracle. This to me smacks of FUD. Oracle, evil and terrible as they are, would have a very hard time killing or otherwise harming MySQL due to the code being GPL'd and the vibrant MySQL community (Percona, Facebook, Twitter, Drizzle, MariaDB, SkySQL, etc) that fixes bugs, adds features, and directly competes with the work Oracle does.
- robconery 14y agoI've been the victim of that flaw - I biffed a Rails migration and sent (what should have been) $200 transactions into the DB as $99. My local tests used SQLite, so I didn't catch it until we went live - true deal there. Very dumb on my part. At the same time - changing data values not stupid? We may differ on this point but I believe it's insanity. I'd love to see NULLs honored as well as constraints. That said - 1/0 = NULL is on par with Javascript silliness. Finally, it's easy to characterize my comment as FUD but Oracle has a habit of using Open Source systems to its advantage. Why else would they buy MySQL? And the licensing is not as strong as you may think. Either way - it's very argumentative and I'm happy to agree it's unsure at best.
- spitfire 14y agoWhat tool is he using to do the postgres part of that video? I've been looking for a decent postgres gui and can't find one. EDIT: Navicat it looks like. One comment, way back int he mists of times (the 90's) MySQL was known as the database that would eat your data. It had a number of data gobbling bugs, as well as no transactions. I'm genuinely surprised how far good marketing and ease of use will go to make a product successful. The postgres guys might learn a bit there. Ease of use will drive a lot of adoption.
- robconery 14y agoNavicat - it's a great GUI
- crazyphoton 14y agopgadmin
- sudont 14y agoUnrelated, but hopefully a good place to stick this: What was that Mac 10.7-only, command-line but standalone database tool, designed for high-level analysis of data via queries?
- radq 14y agoAre you thinking of this? http://inductionapp.com/ http://inductionapp.com/
- sudont 14y agoYes, __Thank You!__ I've been spending hours at a time looking for it, spamming here was a last resort. (Throwing "Postgres" in my searches was what was fouling them—turns out it was just spawned by Heroku's Postgres team...)
- floobar 14y agoare you paid for talking about postgres via tekpub or otherwise? sometimes your postgres chatter seems like you are.
- mekoka 14y agoOn my way to build a multi-tenant application I went through a great deal of articles recommending various architecture strategies. I was looking for an approach to organize the data for the app's various customers (multi-tenant). Most recommendations revolved around 2 solutions: 1 db per tenant, or 1 db for all tenants with a tenant_id in each table. Lucky me, I eventually stumbled upon a thread where someone mentioned Postgresql's schemas, which kinda give you the best of both worlds. It's unfortunate that the web development world has been so very much MySQL (and PHP) centric, because it often constrains us to see solutions to problems with the capabilities of only these technologies. I had heard much good about Postgresql in the past, unfortunately it was also introduced with the spectre of a costly adaptation period, which resulted in making me postpone the move. This time though, I was tempted enough to give it a shot and in that same evening I jumped in. It's been about a month now and things have been surprisingly smooth. The only tool I use to interact directly with the db is the psql client. Other than that the symbiosis is nearly perfect with Python (I use SQLAlchemy with psycopg2). If I have a recommendation to current MySQL users who would like to try Postgresql, just give in. It's really not as hostile a transition as some seem to make it. Not everything will work as you'd expect from your MySQL background, but most issues are known, the tools are great, the community is great and the documentation is simply top-notch.
- deleted 14y ago[deleted]
- neeleshs 14y agoif u r using django, shameless plug here, http://tidbids.posterous.com/saas-with-django-and-postgresql-schemas http://tidbids.posterous.com/saas-with-django-and-postgresql... may help a bit
- rosser 14y agoI mentioned something about this the last time a discussion involving Django and Postgres came up, but it bears repeating. If your environment is set up such that database connections are long-lived, please double-check that you're not using SQLAlchemy's default behavior to open an explicit transaction (e.g., "BEGIN TRANSACTION") upon connection. (It may no longer be the default, but it was last I worked with a Django shop, ~two years ago, now.) That behavior interferes with VACUUM's ability to do its job, your tables can bloat linearly with how "write-hot" they are, and performance of queries using those tables will reflect that. Briefly, this is a consequence of Postgres' MVCC (multi-version concurrency control) architecture. Physically, an UPDATE statement is an atomic INSERT/DELETE operation; each version of every row is stored on disk, and Postgres keeps track of which versions of which rows are "visible" in the context of which transactions. Obviously, that's not a sustainable approach, both in terms of performance, and resource consumption. You'll fill up your storage with "dead" tuples, and you'll have to trawl through disk page after disk page of them to find a single "live" one. Enter VACUUM and later, the autovacuum daemon. They know which transactions are open, and which versions of rows were modified by which transactions. "Dead" tuples older than any open transaction are truly dead, and their disk footprint can be re-used by subsequent INSERT — or UPDATE — operations. SQLAlchemy's (former?) default behavior to open an explicit transaction upon connection isn't ideally suited for use with MVCC-based databases (so if you're using InnoDB, you'll probably have some flavor of this same problem) — particularly if your environment has connections open for days at a time, which was the case at that former gig. When I found this problem in their stack, tables with hundreds, or perhaps a few thousand rows had disk footprints in the gibibytes. They were among the hottest tables in the system and performance, consequently, sucked. The only thing that kept them alive was that the people they had working on this stuff before me had a weekly maintenance window (site outage) where they ran a VACUUM FULL and REINDEX. How no-one twigged to the fact that app performance followed a perfect sawtooth pattern with a period of exactly one week, lock-step in phase with the maintenance cycle, I'll never understand... You can check whether your environment is configured this way with: SELECT * FROM pg_stat_activity WHERE current_query = '<IDLE> in transaction' ORDER BY xact_start; If any of those guys are older than ... not very old at all (seconds, perhaps minutes at the outside, but that's a function of how your stack works, and only you can really say what's abnormal) then, Houston, you have a problem. A caveat: this test is fairly specific, but somewhat less sensitive, with false negatives being possible. What you're really looking for is Postgres backends with an xact_start — current transaction start time — that is (potentially significantly) out of line with how long your normal database operations should take. Those usually show up as "<IDLE> in transaction" but you could very well happen to run the above query exactly when one of these offending connections is busy servicing a query, itself... EDIT: clarifications
- sxcurry 14y agoInterestingly, Ingres was the first database I ever used - running on Unix (v6 or v7?) on a PDP 11/34. Since then, I've ambled through Oracle, SQL Server, etc, but I'm currently using PostgreSQL on a project, and loving it. The native spatial types are really handy for what I'm implementing, and the speed and stability are great.
- cageface 14y agoBack when I was still doing web development, I had a big stack of things I had to deal with to actually get html on somebody's screen. Most of them were slightly annoying to deal with and I would often push them back to the one day a week I reserved to futz with annoying little details. But working with Postgres was always a joy. Everything seemed to be designed in a logical, straightforward way and worked the first time without any hacks or workarounds. It always felt like there was so much untapped potential humming there behind the prompt. Eventually my company switched to Oracle and DB hacking quickly became another one of those annoying things.
- gfodor 14y agoI'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's usually not a good idea to use too many esoteric database features when building an app, since it couples your system with a particular database. That said, even if you don't use PostgreSQL's whiz bang features, its stability, performance, and outright sanity with regards to handling data make it the right database to reach for in many cases. And, for smaller projects where you are willing to couple yourself with it, its additional data types, query features, and so on are awesome.
- pvh 14y agoI absolutely disagree. Drop-in portability between databases is an operational myth for any production application anyway. Whether you're using Postgres, Riak, Mongo or Oracle, you're going to have to do a lot of work to change your database infrastructure. Further, every database, noSQL or otherwise, offers a different set of features and functionality. Why the heck wouldn't you take advantage of k-nearest-neighbors indices? Why would you want to roll your own full text search functionality? The alternative is to introduce whole other services to your infrastructure! In closing: if you've got 'em, smoke 'em.
- monstrado 14y agoDepending on your application and how you're taking advantage of your database, you could drop-in replace a database. For example, people using some sort of abstraction on top of their database (an ORM) often switch between databases. With that being said, if you're actually designing a complex database back-end for an application you will likely want to spend time becoming very familiar with the database of choice, learn the advantages and then exploit them. This could prove to be pretty difficult to just replace on a whim later on. edit: Switching between an RDBMS and a "NoSQL" database would be much different though. Switching between one SQL server to another is one thing, but switching from a SQL server to a "NoSQL" server is going to be depend on what kind of model you're changing to. For example, MongoDB & Riak are both "NoSQL" databases but they are both quite different fundamentally.
- 14y ago
- mmaunder 14y agoI think the reason that MySQL became more popular than PostgreSQL is because it did one thing really good: It was a very fast file access daemon. PostgreSQL tried to do too much, ended up not doing any of it very well especially in the area of performance and didn't become as popular. Maybe useful to remember this going forward.
- deleted 14y ago[deleted]
- jeffdavis 14y agoI see it as a major success story. Postgres was developed over a long period of time with careful attention to architecture, robustness, and extensibility. It also focused on both external and internal documentation and cleanliness. And, it followed the traditional database system model with a cost-based optimizer and everything else. As a result, we see a very robust developer community. It's a real machine -- churning out high quality releases every year with a great mix of newsworthy features. Enterprises and startups alike are flocking to postgres for a huge variety of use cases. Slow and steady wins the race (and it doesn't hurt to be 5 tons and have tusks) and isn't tired afterward. And in databases, it's good to play for the long term because that's how long the data will last. I wouldn't trade that for some first-to-market popularity.
- taligent 14y agoYour entire argument falls apart when you look at all of the companies that chose MySQL over PostgreSQL and are still using them today. Basically every major internet site today relies on MySQL.
- jeffdavis 14y agoFirst of all, the success of postgresql and mysql are not mutually exclusive. Postgres operates in a lot of markets that MySQL doesn't (as far as I know) like telecom and finance. Postgres gets entirely new users with a variety of use cases, as well as people from Oracle, SQL Server, MySQL, and even some converts from people who tried NoSQL and found it lacking in some way. An undeniably, there is a shift happening, even in parts of the market that were a MySQL stronghold. Heroku and their customers use postgres for multi-tenancy. Instagram uses postgres for photo-sharing. I think MySQL has been very complacent because they are the default for a lot of simple web apps, and that keeps their numbers high. They aren't really breaking into new markets -- postgres is winning the geospatial market big time, and is always coming out with new features to break into new markets (personally, I am trying to advance postgres into the temporal database space).
- marquis 14y agoWhat are the scaling differences between MySQL and PostgreSQL? That's the main reason we haven't shifted and we have a new project coming up that I've been interested to use PostgreSQL with as one our developers prefers it, but are we opening a whole new can of worms on that front?
- chimi 14y agoPostgreSQL is massively more scalable than MySQL.
- spudlyo 14y agoDefinitely. When your application needs hundreds of read slaves in order to scale the load, PostgreSQL has always been everyone's first choice due to it's mature and historically awesome replication system. That's why companies who need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc) all have hundreds (if not thousands) of PostgreSQL machines in their infrastructure.
- joshhart 14y agoIm going to assume this is sarcasm since Facebook is built entirely on sharded MySQL. Don't want anyone else to get confused in case it doesn't come through for them.
- chimi 14y agoFacebook, et al, use memcache and a plethora of other products to improve the scalability of MySQL. Here's one scalability comparison between the two: http://tweakers.net/reviews/649/7 http://tweakers.net/reviews/649/7
- AaronBBrown 14y agoThat is a comparison from 2006 using MySQL 5.0.20 (ancient!) and as such is completely irrelevant to any discussion on performance in 2012. Dramatic scalability improvements went into late 5.1.x releases, and then even more in 5.5.
- rufugee 14y agoAs a long time PostgrSQL fan and user, it's nice to see it get well-deserved attention. It's kept our company of 3000 employees going strong for nearly a decade and has NEVER failed us or lost any of our data. (Knock on wood ;-) )
- saturn 14y agoFor those Rails users amongst us, many of these cool data types (I am particularly hanging out for Arrays) are available in the fast-improving postgres_ext gemⁱ, and (even more exciting) there's an effort to get it merged into Rails 4. ⁱ https://github.com/dockyard/postgres_ext https://github.com/dockyard/postgres_ext
- cmaxwell 14y agoI would probably consider PostgreSQL if I didn't need MySQL on my VPS for wordpress.
- pwpwp 14y agoThe article summary is #ccc on white.
- pvh 14y agoPeople often ask us at Heroku -- why Postgres? The short answer is: we needed to do something, and it's the best.
- einhverfr 14y agoI like that. It sums up our experience with PostgreSQL on the LedgerSMB project too.
- pjmlp 14y ago> we needed to do something, and it's the best. For such a statement I would rather take one of DB2, Oracle or SQL Server.
- reidrac 14y agoIn my experience you don't want to provide X as a service based on a third party closed product (meaning: anything not open source & open community).
- pjmlp 14y agoI imagine you don't do business in the enterprise world.
- reidrac 14y agoYes, I do. I said "in my experience" and I totally understand why Heruku is offering PostgreSQL and not DB2, Oracle or SQL Server as a service. Amazon RDS started with MySQL, and only two years later Oracle support was added. SQL Server three years later.
- pjmlp 14y agoSorry about that, should have read better your comment. On my area of work we also do a lot of open source, but most customers won't think twice about using proprietary database solutions, specially taking into consideration the types of deployments we do. In my career in the enterprise world, I've mostly used Oracle, Informix, DB2, Microsoft SQL Server, Sybase SQL Server. The only two project I used MySQL so far, one was for a prototype application, to be shown in computer fairs. The other was an online survey.
- AaronBBrown 14y agoI just watched a 10 minute video whereby nearly all the issues pointed out can be solved with one line in my.cnf or dynamically by setting SET GLOBAL server_sql_mode=TRADITIONAL. Yeah the default is no good. Learn your RDBMS and the problem goes away. Watching him point and click and move windows around also made it very difficult to follow. PostgreSQL is an awesome RDBMS, but adoption will never eclipse MySQL until they have a scalable replication model that allows tiered replication, multi-master replication, writable slaves (very useful for reporting boxes), and the ability to purge data on a master without purging it on the slave. That, and the ability to reliably upgrade your binary without performing a massively time-consuming dump and reloading. Replication and upgradability is everything when you are in operations, which are things many (not all) developers do not consider.
- jeffdavis 14y agoYour concerns are constructive and well-placed. It happens that they are all either done or actively being improved, but that's not obvious unless you follow very closely, so keep 'em coming. "a scalable replication model that allows tiered replication" They do in 9.2 (currently beta), it's called cascading replication. The other replication features you mention are under active development by a team of reputed hackers. "That, and the ability to reliably upgrade your binary without performing a massively time-consuming dump and reloading." pg_upgrade has been available and you can upgrade from 8.3 to the latest without the dump/reload cycle. It has been a little rocky (by postgres standards) and some people have been hesitant, but it gets the job done and it's been improving a lot. A talk at a recent conference spoke quite highly of pg_upgrade despite running into some challenges (like a library versioning issue related to some perl functions they had and some multibyte characters I think). Not for the faint of heart, but if you really need this and don't mind reading a little (or hiring a consultant), you can make it work. Every issue or every missing feature seems like a showstopper if you don't step back for a minute. No DBMS is perfect. Everyone has their list of "postgres is missing X,Y, and Z". The funny thing to me is that X, Y, and Z change with each release because the previous X and Y were added (often with greater flexibility than originally imagined), and there's a new alternative to Z. Postgresql puts out a very high quality release every year with major features. Usually, it's a good balance of features requested by (potential?) users like you and new innovative features that move the database world ahead.
- mixmastamyk 14y agoAnyone interested in a pgadmin-like console client? I started one, and if a few people with more in-depth experience helped out I think it could be handy. https://bitbucket.org/mixmastamyk/dumbo/ https://bitbucket.org/mixmastamyk/dumbo/
- davyjones 14y agoI would really appreciate it folks can try out my free and open source GUI client for postgres: http://pgXplorer.com http://pgXplorer.com. It is available for Mac, Ubuntu (64) and Windows (64).
- yorhel 14y agoJust tried to compile from git, but the qt-psql requirement wasn't very easy to satisfy on Arch. Your Ubuntu binary worked fine. Looks quite nice, and there certainly is a need for more PostgreSQL GUIs. Some feedback from the minute I used it: - You don't handle bytea columns very nicely. I'd expect those to be displayed in hex or so. - Browsing a table is very slow on tables with large columns. I was testing on a table with a ~10KiB text column. - I personally prefer a more compact interface, having an option for smaller buttons would be a good start. :-) (Edit: formatting)
- davyjones 14y agoThanks a lot for your response! > qt-psql requirement wasn't very easy to satisfy on Arch Whoops! My primary dev machine is Arch. Let me fire up a clean VM and try and reproduce the problem. Are you rolled to the latest? > - You don't handle bytea columns very nicely. I'd expect those to be displayed in hex or so. Unfortunately, the database types to Qt types get mapped in a pretty undesirable way (for this use case atleast). For example, I do custom handling of timestamp types within reason. Let me take a look at bytea as well. > - Browsing a table is very slow on tables with large columns. I was testing on a table with a ~10KiB text column. I strongly suspect the Delegate class of Qt. This class handles the painting of cell data and it can be customized to a high degree. And I think even the default Delegate is pretty heavy leading to extreme stress when painting cells that carry a lot of data. Unfortunately, I don't see this as something that can be fixed quickly unless I start from very low level classes on Qt to display table data. > - I personally prefer a more compact interface, having an option for smaller buttons would be a good start. :-) Actually, I was thinking about having the scroll wheel adjust the icon size on toolbars. Let me look into that as well. Thanks a lot for your input; I really appreciate it.
- rickmb 14y agoThe primary target audience for hacker-to-hacker Postgres evangelism is MySQL users. Because let's face it, the choice for DBs like Oracle is usually made upstairs, and for very different reasons. So why do Postgres advocates insist on dissing MySQL with false and misleading arguments? The usual target is some default settings, when obviously there are three kinds of MySQL users: the ones that actually have a reason to want less strictness, the ones that know how and when to change the settings, and the ones that just don't give a fuck. You're not going to convince the latter until you are #1, and you're insulting the intelligence of the first two, whilst also making them question whether the arguments in favor of Postgres are actually true, since you're not being particularly honest in your criticism of MySQL. And you're wondering why Postgres doesn't get any more love? Most MySQL users are well aware that Postgres is technically a better DB. They mostly (right or wrong) feel MySQL serves their needs sufficiently, and are not particularly attracted to an open source community with such a douchy attitude. If even Oracle doesn't scare people of sufficiently to jump ship and switch to a better, free and open database, you've got a serious image problem. Stop blaming it on other people's "stupidity".
- yread 14y ago> usual target is some default settings for me it would be missing common table expressions, weak implementation of functions and SPs (recursion) and horrible .NET connector (connections dying, int can get returned as byte[]). And I'm not even mentioning the gazillions of advanced features that pgsql (and Oracle or SQL Server) has that mysql doesn't.
- adrianhoward 14y agoMy personal all time mysql fave - values that are both null and not-null http://use.perl.org/use.perl.org/_Adrian/journal/32627.html http://use.perl.org/use.perl.org/_Adrian/journal/32627.html
- deleted 14y ago[deleted]
- 14y ago
- stef25 14y agoI had to use Postgres at a job or about 6 months and hated it due to lack of a decent GUI. The "official" one has many bugs and quirks and takes quite a bit of getting used to especially after coming from phpMyAdmin. So it's nice to see there is now an .app for it.
- arjn 14y agoMy opinion is contrary to most of the comments here. About a year ago we switched a multi-tiered app from MySQL/MSSqlServer to Postgres/Postgres. While everything works fine, after months of using it I find writing programs for and with MySql or MSSqlServer seems easier. One specific complaint I have is that there is no transaction control within pgsql stored procedures (functions). I had to kludge the db link module to get transactions to work. Another related complaint is the DB Link module has poor functionality when compared to MSSqlServer's DB Link. I brought this up yesterday at the PostGres booth at OSCON and they said they're aware of it. No news on when they'll get around to fixing it. A feature we miss is MySql's ability to use different DB engines such as Archive or Memory. AFAIK there is no equivalent to these in Postgres. We put mysql's Archive engine to good use before we migrated our app. Now we have to export data to gz files via script. In general I don't think there is a strong reason for the majority of apps to switch to Postgres when MySql works for most needs. (I should mention that pgsql has some nice locking functions that we found useful)
- jeffdavis 14y ago"One specific complaint I have is that there is no transaction control within pgsql stored procedures (functions). I had to kludge the db link module to get transactions to work." Yes. Functions work within a transaction, which is usually a blessing. But there are cases where you want to do the transaction control in the procedure itself. This is a known request that is discussed as two different features: "stored procedures" (which is probably what you want) and "autonomous transactions".
- lazyjones 14y agoOne of the best features of Postgres is Tom Lane. We've been using Postgres since 2000 and following the (user-oriented) mailing lists closely and he's always been responsive, helpful, extremely competent and tactful - a rare mix indeed (unfortunately). He should be the primary role model for FOSS developers, where quirky characters dominate the field. Thanks, Tom!
- deleted 14y ago[deleted]
- mgkimsal 14y agoSomething that tends to get overlooked in pg/mysql discussions is administration - more specifically account management. all of this is done in the 'mysql' database via SQL directly in mysql ('insert into user', 'grant permission', etc). Most tutorials on PG I've seen over the years show these things being done from the commandline, and assume a unix environment, and an understanding of connecting from various account shells and such. i've been made aware that you that there are other ways of dealing with user/perm management in pg, but from the perspective of people running shared hosting, the default tutorials feel extremely burdensome compared to managing everything in one database via SQL (no need for multiple user accounts on the system - just multiple user accounts on the database).
- robe2 14y agoCREATE USER is a standard in many relational databases e.g MySQL, PostgreSQL etc. You really shouldn't be inserting directly into mysql system tables. It's not portable. PostgreSQL has phppgadmin and pgAdmin which provide a nice GUI experience for role management.
- imalolz 14y agoI've been neck deep in MySQL for the past 5-6 years, simply b/c that's what my company uses. I've been wanting to pick up postgres but my only motivation has been curiosity. Any good resources/tutorials people here can recommend?
- robe2 14y agoOur site is dedicated to that: http://www.postgresonline.com/ http://www.postgresonline.com/ Also check out our new book which came out this month. http://shop.oreilly.com/product/0636920025061.do http://shop.oreilly.com/product/0636920025061.do Its primary target audience are people coming from other relational databases.
- sams99 14y agoCan the lack of mixed DML/DDL transactions be worked around with a change to my.conf? non transactional migrations always seem to trip me up in mysql, cleaning up the mess is a nightmare
- jasonkostempski 14y agoOne thing I've always loved about SQL Server is the Adventure Works OLTP and data warehouse DB samples, they are absolutely priceless when it comes to experimenting and learning. Is there something like that for Postgres? Is Postgres even intended to be used as a data warehouse and, if so, are there any decent business intelligence tools that can work with a Postgres warehouse? I'm not looking for an open source Analysis Services/Business Intelligence Studio equivalent, just something that I can build some cubes with and run MDX queries against.
- dotborg2 14y ago- functional index - index partitioning some great features of postgres, which were not mentioned so far
- snambi 14y agoi believe one of the main reasons for mysql's popularity is that it is available by default with many hosting providers who provide PHP based hosting for a very cheap price. These are low-medium volume sites developed by a small teams. They just don't need or care about the benefits of using a better database like 'postgres'. IMHO, postgres developers and support organizations need to work with hosting companies to make postgres available along with myql.
- ondrae 14y agoThe recent release of PostGIS 2, the spatial extension of Postgres, has been a huge benefit to geo software. It has increased the ease and speed at which we can now work with large data sets and do spatial computation on them. My favorite part of PostGIS 2, besides much of the cleaned up syntax, is the new Geography datatype. It has the spatial projection built in to it, so it automatically does great arc computations. Before we had to write more clunky SQL to achieve this. Was a significant barrier for my learning postgis, which has now been removed.
- robe2 14y agoGeography data type came out in 1.5 NOT 2.0. Raster and better 3D support were the big story in 2.0 But stay tuned for 2.1 --> Geography will become MUCH faster: http://blog.opengeo.org/2012/07/12/making-geography-faster/ http://blog.opengeo.org/2012/07/12/making-geography-faster/
- smsm42 14y agoI was reading this article with great interest, exactly up to the part where it claims MySQL is stupid. Maybe it's just me but I've had enough of articles that are written with the idea that the best way to promote your product is to bash your perceived competition and call it stupid, worthless, idiotic and broken. People, here's the news for you: people don't use your competition (or your beloved tool's competition) because they are stupid. They use it because of variety of reasons, but it almost never involves them being stupid, and calling their choice stupid is almost never a good way to get them to consider changing their preferences. Even more disappointing, when the author proceeds from showing how stupid MySQL is (random collection of choices which can be plausibly decided in any way) to showing how much better Postgres it, he doesn't show anything useful. He just shows random collection of features that may be useful in some use case he probably encountered in his practice, but no compelling picture of a superior product. Nobody changes DB server because one has "tomorrow" keyword and another does not. And when performance comparison is done - no numbers at all are quoted except for initial data size. Just bare claims "it performed well" - how well? Are these results comparable to what I can expect on my data with my setup? How much manual work (mentioned in passing as 2 experienced DBAs were working on it - but how complex was what they did?) would it require to make it perform? How much better would it perform if I hired a very good DBA? These are very important questions to consider when you choose the DB for your project, but completely omitted in the article.
- systems 14y agobetter names would have been PostSQL or PostDB not Postgres
- kami8844 14y agoAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDD EEEEEEEEEEEEEEEEEEEE FFFFFFFFFFFFFFFFFFFF GGGGGGGGGGGGGGGGGGGG HHHHHHHHHHHHHHHHHHHH IIIIIIIIIIIIIIIIIIII JJJJJJJJJJJJJJJJJJJJ KKKKKKKKKKKKKKKKKKKK LLLLLLLLLLLLLLLLLLLL MMMMMMMMMMMMMMMMMMMM NNNNNNNNNNNNNNNNNNNN OOOOOOOOOOOOOOOOOOOO PPPPPPPPPPPPPPPPPPPP QQQQQQQQQQQQQQQQQQQQ RRRRRRRRRRRRRRRRRRRR SSSSSSSSSSSSSSSSSSSS TTTTTTTTTTTTTTTTTTTT UUUUUUUUUUUUUUUUUUUU VVVVVVVVVVVVVVVVVVVV WWWWWWWWWWWWWWWWWWWW XXXXXXXXXXXXXXXXXXXX YYYYYYYYYYYYYYYYYYYY ZZZZZZZZZZZZZZZZZZZZ