30 ms·
In Praise of PostgreSQL
- beermonster 5y agoHow does it compare to MariaDB?
- rubenv 5y agoIt's better.
- beermonster 5y agoThis [1] seems to be a more discerning answer [1] https://hevodata.com/learn/mariadb-vs-postgresql/ https://hevodata.com/learn/mariadb-vs-postgresql/
- Tostino 5y agoFYI, the section on partitioning is wrong there. Postgres supports table partitioning perfectly fine[1]. 1. https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
- 0xdeadbeefbabe 5y agoLess West Side Story more Jungle Book.
- nelgaard 5y agoThey are both very impressive systems. I have done many projects in both PostgreSQL and MariaDB/MySQL I do tend to get attached to the DB of my last project because I discover new cool things. And there are some differences. But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL. I have re-implemented systems that used non open-opensource DBMS's on Windows and the convenience and performance (on the same hardware) were just amazing when using PostgreSQL or MariaDB.
- SahAssar 5y ago> But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL. Can MySQL do GIS related features like PostGIS? Also IIRC to index JSON in MySQL you need to add generated columns for all the properties/paths you want to index, which can get pretty hairy.
- dagw 5y agoCan MySQL do GIS related features like PostGIS? It can[1] for most of the common cases, but it lacks many of the more advanced features PostGIS has. So if you have a MySQL database up and running, you probably don't have to switch to PostGIS just because you want to add some geospatial data. However if starting a new geospatial project then you should almost certainly choose PostGIS. [1] https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
- majewsky 5y agoIs there a MySQL equivalent for "FOR UPDATE SKIP LOCKED"? That's a feature that I've fallen in love with because it has completely eradicated the need to have a message queue like RabbitMQ or whatever. For example, for distributing jobs to background workers, I just insert them into a jobs table and have the workers poll for jobs with: DELETE FROM jobs WHERE id = ( SELECT id FROM jobs FOR UPDATE SKIP LOCKED ) RETURNING *; Then just commit the respective transaction on success or rollback on failure. Would this look as simple as that in MySQL?
- evanelias 5y agoMySQL 8 supports FOR UPDATE SKIP LOCKED [1], but does not have a RETURNING clause. MariaDB 10.5+ supports a RETURNING clause, and 10.6 now has FOR UPDATE SKIP LOCKED. This is quite new though (10.6 went GA a month ago) so there might be some wrinkles. [1] https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/ https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-an...
- ploxiln 5y agoPostgres has always prioritized correctness, and more sophisticated datatype and query features ... mysql/mariadb have had more consistent performance and easier/more-flexible replication. I'm thinking about auto-vacuum and logical replication to upgraded versions... I've had much less trouble with mysql/mariadb around these topics many years ago, than I've had with Postgres relatively recently. But also, I don't care so much about fancy data types and query features, I stick to the basics.
- codesections 5y ago> PostgreSQL has taken a complex problem and solved it to such an effective degree that all of its competitors are essentially obsolete, perhaps with the exception of SQLite. I realize that this isn't the main point, but that aside strikes me as high praise for SQLite — which has also always struck me as exceptionally well built software.
- beermonster 5y agoSQLite is literally on billions of devices and most people (even tech folk) are none the wiser.
- pedrocr 5y agoI'm always amazed at the amazing reviews SQLite gets, particularly in a thread about PostgreSQL. I get the angle of SQLite competing with fopen(). If what you need is a file format you can do a lot worse than SQLite. But I've worked on multiple desktop apps where the only reason SQLite gets used for the internal database is that PostgreSQL doesn't have a mode where you can ship it as part of the app easily and asking the user to setup a database is a non-starter. If not for that the performance benefits of the swap would be very significant. I suspect if PostgreSQL shipped such a mode a very large percentage of SQLite uses would switch over.
- donarb 5y agoOn the Mac at least, there is PostgresApp. A double-clickable application that wraps a PostgreSQL server. Installation takes a minute or two without having to use the command line for compilation. https://postgresapp.com https://postgresapp.com
- deleted 5y ago[deleted]
- hitekker 5y agoThe "mode" you wish for is a rearchitecture of PostgreSQL. Embedded and client-server DBs are two distinct systems.
- fabian2k 5y agoI'll second the praise for the Postgres documentation. I learned a lot about Postgres simply by looking through the documentation and reading the parts that looked interesting.
- djk447 5y agoSame here. I feel like I built several years of my career on reading that documentation and knowing where to find things when other folks didn't, so they'd come and ask me and I'd just send them to the right place.
- mjw1007 5y agoParts of the documentation are very good. For example https://www.postgresql.org/docs/13/wal-configuration.html https://www.postgresql.org/docs/13/wal-configuration.html is done properly: it describes what that part of the program actually does, with the settings that affect it introduced at the appropriate point, separately from the reference documentation which lists the same settings in alphabetical order. But not all aspects of Postgres get this quality of documentation. It would be good to see a similar treatment of the checks the planner or executor performs to decide whether to permit access to a particular table and column and row, rather than having to deduce it from the separate descriptions of the security features. (For example, the basic fact that a VIEW accesses a table using the privileges of the view's owner, not those of the user executing the query, is very hard to find in the documentation.)
- p_l 5y agoNot just about postgres internals, but generally about SQL in my case. Interestingly enough the competition for "biggest impact on my SQL Knowledge"... Is a book about MS Access 97 which taught me enough about how SQL actually works that I could understand Postgres docs.
- funcDropShadow 5y agoIt is great that you can switch on any page in the documentation to the documentation of another release of PostgreSQL. That is extremely helpful if you are working with different versions.
- zz865 5y agoI used Sybase and SQL Server for a long time. PostgresSQL I found much tougher than expected to figure out. Just seems built for functionality over ease of use. I guess I got to get used to it.
- openthc 5y agoVersus Sybase!? Not my experience (but its so long ago now). I feel this may be a just a comfort-zone switch for you. Like, Sybase is known and therefore already figured out and "better". All big systems take some ramp-up / comfort-building time. As others have said, the documentation, the community (still on IRC and email!) are excellent. And heck, digging in the code for information is not so terrible either (and it's huge, and C (and I don't do either of those things) and I still found what I needed). I'm sure you'll be used to it in short order. I've already poured you some Kool-Aid :)
- mixmastamyk 5y agoI found the o'Reilly book, Postgres: Up and Running approachable if you have some db experience.
- frankwiles 5y agoBeen a big fan of PG since the late 90s. I always joke that the only time I’ve lost data with PG is when it was my own damn fault!
- doctor_eval 5y agoAgreed. The last and only time I saw a database corruption with PG was in 1999 and the culprit was a memory fault in a raid array. Cross fingers now that my hubris does not come and bite me.
- deleted 5y ago[deleted]
- flowerlad 5y ago> For a start, Postgres is simply an incredibly powerful, robust, and reliable piece of software, providing the best implementation of SQL. Why is it better than MySQL? This article wasted my time. Is there a better article which explains why Postgres is better than other free and opensource SQL implementations?
- CabSauce 5y agoBetter yet, there's a website that allows you to find other websites that answer your questions.
- flowerlad 5y agoSadly the first page of results is all similar to this site, in terms of quality. HN readers are more knowledgeable than websites that can find other websites.
- indymike 5y agoMySQL and MariaDB are REALLY great software. The real difference is not one of quality (both are proven performers, and both have survived the test of time, both are used at scale by some pretty giant customers), it is one of quantity. Postgres simply has more features by a pretty wide margin than does MySQL. For many DBAs and developers, Postgres is a more flexible choice due to the additional capability.
- bogomipz 5y agoCould anyone recommend a good and current book on Postgres?
- VWWHFSfQ 5y agoThis one's great: https://theartofpostgresql.com/ https://theartofpostgresql.com/
- claytonjy 5y agoAs a counterpoint, I did not like this book and learned much more efficiently from the postgres docs
- bogomipz 5y agoCan you say what you didn't like about it? It's a little pricey so I would be curious to hear your feedback.
- claytonjy 5y agoIt's been a couple years but I found it very verbose and not very well edited. It may have improved since then. I did appreciate their push towards doing more in the DB and less in the app layer, though, and I wish more devs would hear that message!
- mixmastamyk 5y agoI liked the o'Reilly book, Postgres: Up and Running approachable if you have some db experience. It is short and to the point.
- natrys 5y ago1. Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13 - Luca Ferrari, Enrico Pirozzi is current, and covers a lot of Postgres specific grounds in a structured manner. The examples are sometimes too succinct and hard to follow though, but I find it good for quickly looking things up, specifically the management side of things. 2. PostgreSQL Query Optimization: The Ultimate Guide to Building Efficient Queries - Henrietta Dombrovskaya, Boris Novikov, Anna Bailliekova is quite new, and exclusively focuses on query side of things, but goes deep into this topic. I haven't read it though, only flipped through the pages but it seems good.
- smnscu 5y agoIf anyone is looking for the Alpine article linked in the first paragraph, it's only available via Gemini, or via a proxy like here https://portal.mozz.us/gemini/drewdevault.com/2021/05/06/Praise-for-Alpine-Linux.gmi https://portal.mozz.us/gemini/drewdevault.com/2021/05/06/Pra...
- __app_dev__ 5y ago> all of its competitors are essentially obsolete For most companies I've worked at I've used either SQL Server or Oracle and from what I've seen when looking at job posts recently the demand is only increasing. Although I live in Los Angeles and not Silicon Valley. Additionally Facebook and other large tech companies still use MySQL and outside of silicon valley many large companies still run IBM databases. I don't see the other major databases being obsolete at all. I've used PostgresSQL before for learning (~3 years ago) but didn't really like the web based UI compared to SQL Server or Oracle query tools. Does anyone have recommended PostgresSQL query interfaces?
- brightball 5y agoRegarding SQL Server at least (I can't speak to Oracle), they've created something of a network effect. SQL Server is really expensive. But, the more Microsoft stack you have in house the more discounts you get. The more Microsoft certified staff you have, the more discounts you get. This biases companies to hiring more developers with Microsoft certs, which leads to more developers getting Microsoft certs, which leads to more discounts and a feeling of investment at higher levels of the company that lead them to be unwilling to switch. I spent 5 years at a company that was using SQL Server as the backbone of it's flagship product. They pushed it as far as they could, but ultimately the biggest issues with the product at the company revolved around either limitations of SQL Server, lack of features with SQL Server or licensing being prohibitive of different types of beneficial scaling strategies. I pushed for them to make the switch to PostgreSQL for probably my last 3 years there as part of the architecture group, including migration and training plans, etc but it was like talking to a wall. Nobody could acknowledge the SQL Server issues. Everyone who had a part in considering solutions was too invested as far as I could tell. Eventually they tried moving to Couchbase (NoSQL) before finally making the switch to PostgreSQL from what I heard from people I know who still work there. As far as query interfaces go, you can use just about any tool you like. I prefer Navicat myself but I know a lot of people like DBeaver and DataGrip.
- __app_dev__ 5y agoThanks for the info! I've been using Navicat for SQLite and like it. If I ever get a PostgresSQL job I'll try it there since it works well for SQLite. At my current job we are on IBM AS/400 for ERP (legacy and replacing it would be very high risk as it would change the business drastically so that won't happy any time soon). For all other stuff we are using SQL Server and several of our key 3rd party vendors only use SQL Server. I think the fact too that some many companies run Microsoft Network and Servers that it naturally lends toward the network effect as you mention. Plus while it's expensive it's 10x cheaper than Oracle in many cases.
- deleted 5y ago[deleted]
- massysett 5y agoMySQL is also licensed under the GNU GPL.
- da_chicken 5y agoAs I recall, at one point MySQL drew significant criticism (or at least skepticism) for being dual licensed and requiring contributors to relinquish copyright to their code. To many, it felt like the goal was to lure you in and then change the license model once you'd adopted it fully. This was many years ago, before Oracle even entered the picture, but my guess is that that is what GP is remembering.
- anarazel 5y agoPostgreSQL however is not: https://www.postgresql.org/about/licence/ https://www.postgresql.org/about/licence/
- smashed 5y ago> PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses. https://www.postgresql.org/about/licence/ https://www.postgresql.org/about/licence/
- andrewmcwatters 5y agoI want to use PostgreSQL, because there seems to be a lot of technical discussion around why it's so great, but the tooling I have available to me seems to be immature compared to MySQL's. And so, I find it really difficult to justify investing time in building up infrastructure to support it for my clients' work, because I need to be effective from day one. Realistically that has absolutely nothing to do with PostgreSQL but whether or not some little third-party company or one or two individual developers have built something solid like Sequel Ace. That's really troubling for me to think about, but these CLI tools are insufficient for being productive. This a great example to me as to why a business can't use a potentially superior product because some ecosystem feature is non-existent, or is hard to find. That being said, I'm not too upset because modern MySQL seems perfectly fine, and still more widely used than PostgreSQL.
- merb 5y agoif you use psql you will be way more productive than using any gui tool. also Sequel Ace is so barbones that using a cli is way way faster.
- andrewmcwatters 5y agoI understand that, but I'm not interested in writing everything by hand. I'd rather have tooling work for me instead, and verify correctness by reading the manual for this-or-that. It also helps my staff when I have a recommended tool for those less SQL inclined.
- ekzy 5y agoIntelliJ is decent and has drivers for pretty much all database systems. It can be downloaded has a stand-alone too (DataGrip)
- andrewmcwatters 5y agoThank you for the insight!
- doctor_eval 5y agoNailed it - except one massive feature missed IMO is pl/pgsql. As a language it’s a bit clunky* but once you learn it, and you start moving database logic into the database, suddenly everything is 25% LOC, often literally 100x faster, and easier to understand. As an added bonus, when I’ve moved very complex data transforms into pl/pgsql, the non-programmer technical people can now read the logic and have more than once pointed out problems (with solutions). * it helps a lot if you write your SELECT and other statements in lowercase. Feels barbaric I know, but reading shoutycode all day gives me a headache.
- __app_dev__ 5y agoYeah I haven't used pl/pgsql but it reminds me of Oracle's pl/sql which is very powerful. At first the Oracle version felt clunky to me too but then after taking time to learn it I also found moving complex app logic to SQL allowed me to make complex app changes much quicker. I have use that method of development now for years in a variety of apps and databases and it works great.
- simonebrunozzi 5y agoI am still surprised that no large company exists around PostgreSQL, similarly to Oracle, the (defunct/acquired) MySQL AB, or Redis Labs. Wondering whether this is a missed opportunity. This is not to bash the free software part of PostgreSQL, but rather to focus on something that could drive more enterprise adoption, I guess to the detriment of primarily Oracle and Microsoft SQL Server.
- gregjor 5y agoWhat a bizarre love letter to a piece of software. Some of this looks cribbed from the postgres.org site. "After 25 years of persistence, and a better logo design, Postgres stands today as one of the most significant pillars of profound achievement in free software, alongside the likes of Linux and Firefox." The better logo really does it. All three of the "pillars" the author names are clones or derivatives of commercial products (Ingres, Unix, Netscape Navigator). Postgres development was partly funded by DARPA at Berkeley, and then through licensing to IBM, before it was open-sourced. Only Linux can be called a "profound achievement" of free software if the author means the cathedral and bazaar model, and then just barely. "PostgreSQL has taken a complex problem and solved it to such an effective degree that all of its competitors are essentially obsolete, perhaps with the exception of SQLite." Uh, no. The whole notion of "competitors" in the world of open source software is flawed. PostgreSQL has not made Oracle, SQL Server, and DB/2 obsolete, nor has it made MySQL obsolete, or any of the other database managers we have to choose from. PostgreSQL is about half as "popular" as MySQL (https://db-engines.com/en/ranking https://db-engines.com/en/ranking), and both are below Oracle. "... providing the best implementation of SQL" and in the footnote "No qualifiers. It’s straight-up the best implementation of SQL." I can't tell what the author means by "best," it's like writing that Oreos are the "best" cookies. In terms of standards compliance DB/2 is better. In terms of performance and scalability Oracle, SQL Server, and DB/2 are all objectively "better." In terms of compared to the main open source alternative, MySQL, the two are nearly neck-and-neck with features, compliance, and performance. "SQL is usually the #1 bottleneck in web applications, and Postgres does an excellent job of providing you with the tools necessary to manage that bottleneck." Not in my experience. Sometimes excessive database traffic causes performance issues in web apps, but many other problems show up, like Apache/nginx contending for resources with MySQL or PostgreSQL (hint: don't run those on the same server/VPS). If the code makes too many database queries, or badly-written queries (WordPress and pretty much every ORM) that's a fault in the application code, not the database, and PostgreSQL doesn't magically fix it.