9 ms·
Postgres has been my DB of choice for nearly a decade. The only times I wind up working with another db are because: (1 it is a better technical fit for a ver
by gjolund 10y ago
Postgres has been my DB of choice for nearly a decade. The
only times I wind up working with another db are because:
(1 it is a better technical fit for a very specific problem
(2 there is already a legacy db in place
I have been voted down at a couple of startups that wanted to run a "MEAN" stack, invariably all of those startups moved from MongoDB or shutdown.
The only time I will advocate for anything other than Postgres is when Wordpress is involved. If the data model is simple enough then MySQL is more than up for the task, and it avoids an additional database dependency.
Thankfully all the ORM's that are worth using support MySQL and Postgres, so using both is very doable.
### Useful Postgres (or SQL in general) tools/libraries :
Bookshelf ORM http://bookshelfjs.org/ http://bookshelfjs.org/
PostgREST automated REST API https://github.com/begriffs/postgrest https://github.com/begriffs/postgrest
Sqitch git style migration management http://sqitch.org/ http://sqitch.org/
- __float 10y agoWhat do you mean by MySQL avoiding an additional database dependency?
- deleted 10y ago[deleted]
- gleenn 10y agoIIRC PHP has built-in support for MySQL out of the box.
- charliedevolve 10y agoI think he meant that MySQL is the only supported DB for Wordpress, and while you might be able to get it running on another DB after jumping through some hoops, it's probably not worth the effort.
- m_mueller 10y agoHow would you handle replicating a DB to mobile devices? This is the reason why I've been using CouchDB, but if Postgres or a plugin offered something comparable I'd have gone for it for sure.
- virmundi 10y agoOut of curiosity, how often do your detached DBs update old records? The reason I ask is nostalgia. A company I worked with used MySQL as an embedded DB (laptops under their control). Yesterday, I thought about how absurd this was. The DB only inserted new records and read static records. An embedded SQL would have worked great here. Would that be true for you too?
- m_mueller 10y agocompletely depends on the app. It's a PaaS, not just a specific app.
- gjolund 10y agoHonestly that's not something I would handle at the db layer. I would build a service that is responsible for keeping client db's in sync and abstract that away from my database entirely.
- m_mueller 10y agoI'm not a fan of rolling our own sync code if I don't have to.
- nileshtrivedi 10y agoRethinkDB's new project Horizon could be a solution. But of course, that's not PostgreSQL.
- m_mueller 10y agoInteresting. Tbh. I'm not looking for solutions at the moment, I just know that back when we decided on the technology, CouchDB was pretty much the only good player in town. I was just curious what's out there today. RethinkDB is being mentioned a lot, I'll have to check it out some time.
- manigandham 10y ago> invariably all of those startups moved from MongoDB Why? Especially after point #1 and assuming the document-store was a good fit for the data model.
- phs2501 10y agoCan't speak for the original poster, but at this point Postgres is a very capable and performant document-store database as well with the addition of the JSONB type and associated indexing and modification (in 9.5) functions. It's also more mature, has generally better durability than MongoDB, and it is easy to fall back/extend into relational queries on your document-store data if need be. IMHO, unless you have a situation such that Mongo's horizontal scaling is actually required, it's better to use Postgres even if you're doing document-store stuff.
- SEJeff 10y agohttp://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha... it certainly can be done with postgres, but isn't as simple.
- gjolund 10y agoPostgres is a better document store than Mongo. The only time I would recommend Mongo is for storing geo-spatial data, as it has several built ins that make it much easier to work with. Even then I find it a lot more convenient to keep that data in Postgres and replicate it to Mongo.
- ap22213 10y agoThe only issue that I have with this top-most comment is that it presents PG as a silver bullet. But, there are a lot of different types of databases for a reason. For instance, at my current startup, we employ at least 7 different databases (including PG). And, I don't say that to brag - each has a specific use for the problem at hand. You have to consider the needs and trade offs of your specific project. And, if you can, try to isolate your storage behind some interface. Because often your needs will change. (I say this with 20+ years of experience and over a dozen commercially-successful products in my belt)
- gjolund 10y agoI agree with you 100%. An abstracted interface for your datalayer is a must have. Many startups and projects begin with a single db and grow into new dbs as the business requirements change. Obviously if you know your data model well enough you can foresee a lot of these requirements and pick the right tool for the job. However, you will usually need to pick a db to start with and hope that it will accommodate as many of those unknowns as possible. I believe that Postgres is the best choice in that scenario, as it is extremely mature and has an incredible amount of flexibility.
- ap22213 10y agoTotally agree. In 95% of the cases, you won't go wrong starting with PG.
- frostymarvelous 10y agoFunny. I just said the same thing before reading your comment. Somehow that makes me feel better that at least, I'm on the right path.
- jeffdavis 10y agoNot disagreeing necessarily, but having many different databases has a cost. Data has more value when combined, so a wise database choice also depends on what you already have. You can combine data from different systems (e.g. FDWs, which postgres has great support for), but something is usually lost along the way. I think postgres is a good default, because it is good for a lot of things. But sure, if you make an informed decision otherwise, there's nothing wrong with that.
- bulkan 10y agoThere is also Sequelize [1] which has more activity 1 - http://docs.sequelizejs.com/en/latest/ http://docs.sequelizejs.com/en/latest/
- Keepo 10y agoI would also like to mention Objection[1] it is very minimalistic and doesn't get into your way. Most of the time you are writing queries with Knex[2] or you can write raw sql if you feel like it. I tried a lot of ORMs for node and this is the one I liked the most. The guy working on it is also very responsive and have superb documentation. 1 - http://github.com/Vincit/objection.js http://github.com/Vincit/objection.js 2 - http://knexjs.org http://knexjs.org
- hibearpanda 10y agoWe've been using Objection over bookshelf recently and we really like it. One thing that caused us to choose Objection over bookshelf is because bookshelf doesn't support composite keys: https://github.com/tgriesser/bookshelf/issues/720 https://github.com/tgriesser/bookshelf/issues/720
- te_chris 10y agoI haven't worked with Sequelize personally, but a friend has been recently and curses the day it was born - he wishes he'd used bookshelf.
- iyn 10y agoCan you ask your friend about the exact details? From what I've seen it seemed OK.
- EGreg 10y agoJust out of curiosity, why when wordpress is involved? Doesn't wordpress support PG? What about drupal and other frameworks, would you say the same? Our framework only has a mysql adapter for now, but it should be pretty easy to add a postgres one.
- creshal 10y agoWordpress does not support anything other than MySQL, which is somewhat unique as far as CMSes go. There seem to be a few Drupal addons that don't mix with Postgres, but other than that it should be fine. Same with all others. Generally, if you can, you should consider supporting Postgres in your framework. It's a much saner and robuster database from an Ops point of view (replication doesn't fail as often) and more flexible from a Dev view (performance is generally more predictable, much wider feature set with document storage / PostGIS / etc., …).
- scurvy 10y agoI like Postgresql, but it's far from superior to MySQL from an ops monitoring point of view. MySQL exposes a lot more information and run-time stats than Postgresql does. Simple things in MySQL are also impossible in Postgresql. How do you simply and effectively guarantee that your Postgresql replicas are not lagged and are indeed connected to the master and successfully writing down new logs in a streaming hot standby replica? You can, but it requires that all of your replication accounts also have superuser access on the master. Fun stuff, that.
- creshal 10y agoWhich is IMO preferable to MySQL servers randomly losing sync for no reason every few weeks. I don't care how many tuning knobs MySQL has if none of them makes it work as smoothly as Postgres.
- eventemitter 10y agoRelared ORM is another orm supporting postgres and mysql. It builds the models by itself by loading the required definitions from the database. https://www.npmjs.com/package/related https://www.npmjs.com/package/related
- gjolund 10y agoI'll have to give that a shot, seems like a nice compromise between fully automated solutions like Postgrest and a conventional ORM.
- pjmlp 10y agoI have been using it a lot lately, and do like it. It isn't a popular opinion on HN, but I will still advise for Oracle or SQL Server in terms of tooling, cluster scaling, server side programming and DB drivers. Then again, we work with customers whose Oracle and SQL Server licenses costs aren't an issue.
- Intermernet 10y agoHave an upvote, I'm not sure why you're being down-voted. If you take the issues of open-source and licensing out of the equation (both important issues in their own right, but not related to the point at hand) then Oracle and SQL Server are both ridiculously good. I personally try to avoid them (due to the cost, and the lock-in) but they are astoundingly performant and featureful RDBMSs with a huge amount of support and documentation behind them.
- pjmlp 10y agoThanks for the upvote. I guess some took it personally, although I mentioned I do like Postgres. I just happen to like the other ones even more, since I was lucky to be able to use them in a few projects.
- developer2 10y agoDownvotes originate from open source fanatics. Odd it results in downvotes though; my first thought is also a fairly retaliatory "commercial offerings, why?!", but I'd never downvote for it. tips hat
- antb123 10y ago1) Coming to a discussion on Postgres and saying Oracle is better is clearing trolling 2) "server side programming" - as you have the source code with Postgresql and their a plugins for most major programming languages I don't buy this. 3) If you of spent the same on Enterprisedb or CitusDB I can guarantee you would get similar polish and support. People don't.
- nileshtrivedi 10y agoAlso check Postgraphql: https://github.com/calebmer/postgraphql https://github.com/calebmer/postgraphql Inspired from Postgrest linked above, it automatically builds a GraphQL API by reflecting on postgres schema.
- gjolund 10y agoI was looking at that the other day, really exciting project.
- gshulegaard 10y agoAs others have mentioned, with Citus Data open sourcing CitusDB, Postgres is now capable of fairly easily handling Mongo sized records. Something else you might be interested in: https://www.linkedin.com/pulse/mongodb-32-now-powered-postgresql-john-de-goes https://www.linkedin.com/pulse/mongodb-32-now-powered-postgr... I have always been a Postgres fan, but now I find it very difficult to imagine a problem MongoDB would be better suited for.