13 ms·
Why Use Postgres?
- Minikloon 9y agoNo disclaimer in the article that the author works for Citus.
- craigkerstiens 9y agoApologies. Now updated for that.
- asah 9y agosince you're reading HN... Thx!! suggestion: under 'Much More' it would be useful to link to articles or docs about each of the features listed.
- craigkerstiens 9y agoWill make sure to add more links when back at my machine
- ainar-g 9y agoSuggestion: range types link should lead directly to the docs. Some people really dislike PDFs and Postgres docs are awesome.
- StreamBright 9y agoNot sure if there should be any. The article is mostly about Postgres.
- eropple 9y ago...except for the "About" page right up there on his personal blog.
- billions 9y agoPostgres seems to have become the go-to relational database ever since MySQL fell in the hands of Oracle. Can anyone speak to how its json tree compares to MongoDB's document store in practice?
- diakritikal 9y agoI'm not seeing it, just an uptick in usage. I find most places that previously used MySQL just use MariaDB instead.
- ma2rten 9y agoIn my experience actually most people still use MySQL and not MariaDB.
- cwyers 9y agoIf you go to MySQL's website, they will list page after page of users, most of which you've heard of. If you go to MariaDB's website, they list users. Most of them you've never heard of.
- sverhagen 9y agoAgreed, it seems many people look at Maria as a way out if MySQL itself or its licensing ever turns on them, without wanting to support them in the current tense.
- brianwawok 9y agoI don't think postgres is the default over mysql. I think WordPress and php guys default to MySQL. I think python and Django guys end up defaulting to Postgres. Rails and node.js I am less sure what the default is, maybe mongodb? I don't think either camp has really changed since Oracle came along. A lot of momentum in different stacks.
- bshimmin 9y agoThe actual default for Rails is SQLite [1], though a lot of people (perhaps a majority) use PostgreSQL. You certainly can use MongoDB with Rails via Mongoid, though I'm not sure vast numbers of people do, comparatively. For Node, well, it varies, but at least once upon a time, people used to talk about the "MEAN" stack (MongoDB, Express, Angular, Node) - MongoDB and Node are certainly often used together. [1] Not MySQL - edited as per the below correction.
- ainar-g 9y agohttps://www.postgresql.org/docs/9.6/static/rangetypes.html https://www.postgresql.org/docs/9.6/static/rangetypes.html https://www.postgresql.org/docs/9.6/static/functions-range.html https://www.postgresql.org/docs/9.6/static/functions-range.h... Holy shit. Why hasn't anyone talked about it sooner? I've seen literally dozens of tables with begin TIMESTAMP, end TIMESTAMP, and with handmade validation against intersection. And there is even a union operation! Seriously, my mind is blown. Rails even supports it! http://edgeguides.rubyonrails.org/active_record_postgresql.html#range-types http://edgeguides.rubyonrails.org/active_record_postgresql.h...
- hartator 9y agoEven with the inclusion of JSONB, I think Postgres is still lagging behind MongoDB by enforcing schema. After so many years doing web apps, I am seeing very little interest to have to enforce 2 times the schema: one time in the DB via migtations and one time in the app itself via ORMs. Maybe I am missing something really obvious. ps: I don't mind the downvoting. I am truly looking for answers.
- cygned 9y agoAlways wondering about constraints, relationships, entity/data reusablity and things like calculations, aggregations, ...; typical "SQL tasks" - how does one handle these in a document-oriented schemaless database like mongodb?
- throwanem 9y agoVia corollary with Greenspun's tenth law, or not at all.
- cygned 9y agoThanks, didn't knew about that.
- hartator 9y agoShouldn't the web app itself enforce constraints? I don't think it's reasonable to enforce password strenght for example on the DB level.
- wedowhatwedo 9y agoIf your database knows a user's password, you are doing it wrong no matter what persistence layer you are using.
- hartator 9y agoWhy the DB coudln't do the hashing itself? Storing is the actual dangerous from a security perspective, plain passwords themselves just hitting your db or your web apps is fine as long you don't keep a log anywhere in plain.
- retox 9y agoI rented some time on a vultr server recently and chose a prepackaged build which included a MySql install. Coming from a MS SQL background it felt positively medieval. I haven't migrated yet but from my research Postgres seems the closest competitor in the relational db space. I considered MS SQL for Linux but the server alone required 3GB RAM...
- ktRolster 9y agoI think you'll like Postgres. At least, that's been my experience working with those three, that it is the most comfortable.
- mamcx 9y agoPG is far better than Mysql, something to note if you come from a traditional but robust RDBMS/environment.
- wst_ 9y agoDo you have anything to back up this opinion?
- mamcx 9y agoThe article of this HN already cover that (plus the others that the original post also point)... But because this is about someone coming from a more traditional RDBMS (like oracle, sql server, sybase) it will note that some or most of the features of PG are not different from something like them. Also, PG was from the start more focused in be robust, instead of MySql that was focused in be fast (at the cost of being robust). PG is a better fit for more traditional workloads from years now. And the careful, well-thought, solid development, feature implementation and release discipline is clearly very professional, to match the ones from commercial vendors. PG is not just good for startups and enthusiasts, but solid enough to be recommended to most companies with total confidence.
- wst_ 9y ago
- unixhero 9y agoFar too brief. I would appreciate a really long text that would in a convincing manner explain why Postgres Is so awesome. I work in the industry, and all I see are Oracle and Sybase everywhere. The experts are zealots also, not even having heard of Postgres. Not willing to believe a word I'm saying about Postgres. I am already convinced of course, but the industry is not. Not finance, not trading, not telecom.
- brightball 9y agohttp://www.brightball.com/articles/why-should-you-learn-postgresql http://www.brightball.com/articles/why-should-you-learn-post...
- ktRolster 9y agoNo one ever chooses Oracle or Sybase on technical merits. That is, although those two products may indeed have some merit, that is not why they are chosen.
- hughperkins 9y agoI've used oracle. Its awesome. Expensive. But awesome. If I have to pay, postgres is OK. But oracle is really really good. If they were laptop operating systems, postgres would be Debian, oracle would be Mac os x.
- malux85 9y agoCan you give a bullet point list aimed at someone who is very experienced at Postgres but has never used oracle? I'm curious of the corner cases and killer features that make you love oracle so much
- forgotpw1123 9y agoI don't like Oracle DB really, but there's one feature that does stand apart from Postgres. Oracle has an internal scheduler that works much like an OS scheduler, allowing you to set priority and resource usage for each user or connection. This made Oracle the go-to database for anyone that needs multi tenant support but wants to allow users to access their own database. If you allow database access its trivial to create a really slow query and the resource limiters prevent one tenant from ruining performance for everybody. The best example of this is Salesforce, which has their own proprietary SQL-like query language that's clearly just a crappy front end to generate raw SQL to feed their Oracle DB's. Without Oracle's per-tenant limit this would be far too risky because of idiots making bad queries. An better solution these days is to put each tenant in a Postgres container and let the OS control resource limits for them, but this wasn't an option until recently.
- fabian2k 9y agoHyperLogLog sounds interesting, but looking at the Github page of that extension it mentions that it has been tested with the versions 9.0, 9.1, 9.2, 9.3 and the last commit is 2014. Is it just finished and doesn't need any updates to keep up with newer Postgres versions? Or is it more of an abandonded project?
- searchfaster 9y agoHyperloglog is a pretty straightforward, simple and awesome algorithm.. probably that is the reason.. Take a look at redis implementation.. it is very easily readable.
- jeltz 9y agoMost parts of the PostgreSQL extension API are really stable so if it is a simple extension one should not be too worried about the project being inactive. The extensions I have written have not required any changes at all when upgrading PostgreSQL.
- jerrysievert 9y agogreat read, but may I also suggest Array? being able to have a field like: ingredients VARCHAR[] and index like: USING GIN (ingredients) and using an operator like @> SELECT * FROM table WHERE ingredients @> ARRAY['mushrooms', 'sour cream'] gives you such amazing flexibility and speed, it's not even funny. also, while I was sad to not see PLV8 up there with PostGIS (an amazing extension, btw), I was still happy to see it mentioned with such gusto.
- pvg 9y agoThat's mentioned in the write-up from five years ago of which this is an update.
- rkv 9y agoCan you give me an example where an array would be more beneficial than having another table with {id, name}? I personally have never found a use for them.
- cygned 9y agoI could think of denormalization; e.g. you have an entitiy that can have 0..n tags and you want to speed up the retrieval, I could think of arrays being a faster way.
- jerrysievert 9y agoQuite a bit faster, actually, depending on the dataset.
- jerrysievert 9y agosimplicity, for one - write a quick query to retrieve all recipes that contain all of any number of ingredients. SELECT * FROM recipes r INNER JOIN ingredients i ON (r.id = i.recipe_id) WHERE i.ingredient IN ('mushrooms', 'sour cream') load up some data and run explain analyze on both schemas/queries.
- scurvy 9y agoIs there a new way around the requirement to rebuild your entire replication topology after upgrading versions? (say 9.4 to 9.5) You get a new master ID when running the initdb step, and doing this throws everything else in the topology off. TIA
- avenoir 9y agoHas anyone done a serious implementation on top of full-text search functionality in Postgres? I have a pretty large dataset that's currently in Postgres and I'm deciding between it and Elasticsearch.
- rpedela 9y agoDepends on your needs, but ES is a superior search engine in just about every way. PG may be good enough for your needs though. In my general view, if you want a more powerful and easy-to-use alternative to LIKE then PG search is great. If you need something more like Google then you should use ES.
- jeltz 9y agoNot just that. PostgreSQL also offers a clear advantage if you need to access both full text data and relational or geographical data in the same query. On the other hand what you say is true, ES is much more flexible in what it can offer in full text search.
- rpedela 9y agoES is very good at storing and querying non-text data including geo data although there are exceptions such as numeric. Both PG and ES have their strengths and weaknesses. ES is great at search. PG is great at joins and constraints. Everything else depends a lot on the specific use case.
- jeltz 9y agoYes, both databases can handle all kinds of data and have support for a wide range of index types, but ES does not come anywhere close to PostGIS when it comes to geodata. PG is superior at search for geodata, while ES is superior at text search.
- elchief 9y agoPG 9.6 has phrase search now which is nice. And multi-word synonyms beats most search servers. Lack of BM25 or TFIDF (see the `smlar` extension) is the main issue
- jgord 9y agoId love postgres even more if they had native support for writing stored procs [ functions ] in javascript.
- anarazel 9y agoI guess you know about https://github.com/plv8/plv8 https://github.com/plv8/plv8 , but would like to see it builtin? Unfortunately, having something like v8 as a postgres build dependency would be pretty drastic increase in build requirements. A number of distributions of postres (debian/ubuntu packages (all versions http://apt.postgresql.org/ http://apt.postgresql.org/), rhel based (https://yum.postgresql.org/ https://yum.postgresql.org/) provide it in an easy manner. I'm not sure there's something as convenient for OSX and windows however :(
- jgord 9y agomy bad.. just found about plv8, and will experiment with it and jsonb on future projects. [ pg installs I work with are invariably running on linux, so all good. ]
- myth17 9y agoMost important reason to use PostgreSQL : Constant Time Recovery (Recovery from long running transaction that rollback is instantaneous)
- mack73 9y agoFrom reading the documentation [0] I can't help but feel a bit underwhelmed by the feature set of a GIST index. Maybe I'm not looking in the right place, but what index provide near mathes (fuzzy, prefix) as well as exact term matches?
- elmigranto 9y agoLook up Postgres FTS.
- frostymarvelous 9y agoInterestingly, I just yesterday published a post about pglogical. http://thedumbtechguy.blogspot.com/2017/04/demystifying-pglogical-tutorial.html http://thedumbtechguy.blogspot.com/2017/04/demystifying-pglo... Postgres is an amazing piece of software.