11 ms·
At 22 years old, Postgres might just be the most advanced database yet
- atonse 8y agoThanks to these engines and extensions, Postgres has become that rare tool for me where I have to ask "why use ___ instead of Postgres?" If they got their clustering story to be as easy as MongoDB's was 5 years ago (From what I read, Citus does this well), it's yet another excuse to stick with it.
- chmod775 8y ago> If they got their clustering story to be as easy as MongoDB's was 5 years ago You must have been using a different mongodb than I did.
- sroussey 8y agoOn paper. Add that to the end of that sentence, and it will be true again.
- Townley 8y agoOne of the things I liked about Mongo (compared to Postgres) was the ability to configure a list of connected servers, an election pattern for deciding the master, and... that's it. With that, servers on multiple hosts were load-balancing read-only queries, propagating writes, and failing over in a way that worked for us (a few seconds of write failures of the master ever died was fine for the application we were making). Meanwhile, postgres has this replication scheme available ("Trigger-Based Standby Master Replication") but the steps to implement it were much harder, and required an evaluation of possible replication/failover needs that we didn't know enough to do. It also requires a dive through pages of the documentation at https://www.postgresql.org/docs/10/different-replication-solutions.html https://www.postgresql.org/docs/10/different-replication-sol... to understand how to implement any of the above strategies. Postgresql's availability of different replication methods, and the resources available for each, are impressive for sure. But there's something to be said for having Mongo's easily-understandable list of steps to get multiple servers talking to each other right away.
- SahAssar 8y agoOut of interest, have you tried https://github.com/zalando/patroni https://github.com/zalando/patroni ? It seems to fill this niche. I've been meaning to try it out for the HA postgres use case, but haven't gotten around to it.
- atonse 8y agoI won't speak to data integrity, but as the other poster said, you just had to set up the replication, election mechanisms and you were done. I bet Citus is like this, but I haven't used it yet.
- craigkerstiens 8y agoCouldn't agree more on the extensions part. We wrote a post recently highlighting how extensions have been key into what Postgres has become and is becoming. It got some pickup here on HN, in case you missed it in the initial post you can find it at https://www.citusdata.com/blog/2018/11/27/postgres-more-than-a-relational-database/ https://www.citusdata.com/blog/2018/11/27/postgres-more-than....
- zamalek 8y ago> why use ___ instead of Postgres? MSSQL has DataDude. You write your database as CREATE statements. This is then parsed into an AST and semantic model, diffed against a live database (or another script) and you get an ALTER script. You also get working intellisense, build errors, and everything you'd expect from something like C++. It completely changes how you develop databases into something a whole lot more modern. Especially in source control: migrations are storing history on top of the history which source control already provides, which is nonsensical. I still want to develop something for Postgres someday that replicates this.
- garblegarble 8y agoLiquibase is quite nice for doing this in a cross-database way - you describe the changes you want to make to the schema and it applies them as needed to the db (and because you're properly describing the changes it can also do rollback for a change). We use the same migrations across hsqldb, postgresql and sql server and it just works
- weberc2 8y ago> MSSQL has DataDude. You write your database as CREATE statements. This is then parsed into an AST and semantic model, diffed against a live database (or another script) and you get an ALTER script. Oh wow, I've wanted exactly this for Postgres. Never knew how to search for it though.
- atonse 8y agoI used to use RedGate's SQL Compare (and SQL Delta) for this for years. Compare two databases and spit out an alter script to get you from one state to another (for schemas and data). The tooling is definitely lacking in the Postgres world. I like using Postico on the mac for basic data tasks, since it's rather polished. But doing anything else is a slog using an ugly app.
- jakobegger 8y agoIs the product name really DataDude, or is that a typo? Do you have a link to that product?
- outworlder 8y agoAnd even if you have to use some other tool (Redis, Cassandra, MySQL!), you can access it from Postgres – https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers PG has some warts, and the config defaults are really more suitable for a RaspberryPI rather than production use(so people sometimes get the wrong impression out of the box), but it is rock solid and can support so many different use-cases. It's one of the few pieces of software I could feel confident that, due to the way it's designed, my data will still be there even if someone yanks a server power cord.
- nickjj 8y agoAre you running at a scale where a single master DB isn't sufficient? Because nowadays you can get servers with about 192GB of RAM and 32 CPU cores. Clustering seems like one of those "what if"[0] scenarios where maybe if you were operating at roflcopter scale you might need it but for 99.9999999999999% of cases, a single master DB is more than enough -- at least with a well designed SQL db like Postgres. [0]: https://nickjanetakis.com/blog/optimize-your-programming-decisions-for-the-95-percent-not-the-5-percent https://nickjanetakis.com/blog/optimize-your-programming-dec...
- chrisfosterelli 8y agoOr maybe you just want to have a hiccup on a single machine not take down your entire application?
- vvillena 8y agoHot standbys work well in Postgres. As OP said, the issue is clustering, not replication.
- threeseed 8y agoIt’s not about scale it’s reliability. Servers especially in the cloud can go down pretty quickly. Not just for hardware reasons either. I can’t imagine any company crazy enough to run one server like you’re describing. Definitely wouldn’t pass any enterprise service continuity testing/review that’s for sure.
- marcosdumay 8y agoClusters for reliability are very different from clusters for performance, and very well supported by Postgres.
- atonse 8y agoAs others said, it isn't about scale, but reliability, and even things like patching. With a load balancer, you can easily patch your web servers, but very often, DB servers go unpatched and un-upgraded for years since they aren't in a self-healing cluster. To add some detail, I worked on an e-commerce system that was like this. We would've lost something like $60k an hour if we rebooted the database server (yes, one server, nobody expected this sort of growth). So we didn't touch it for 3 years until it was about to run out of disk space. Then we had no choice, but then we set it up as a fully replicated system with a ton of disk space, making future patching easier.
- wenc 8y agoSSMS is a really good free database management IDE/GUI/tool for MS SQL Server. It can do graphical live query profiling, index management, management of views/stored procedures/triggers/constraints/functions etc. Postgres currently has nothing comparable. DataGrip, though proprietary, is the closest in terms of functionality, but even it doesn't have the tight integration with the database that SSMS has. If you've used SSMS, other database GUIs feel underpowered. (Oracle SQL Developer is ugh)
- atonse 8y agoAgreed, I actually really like SQL server especially because of SSMS.
- wenc 8y agoOne of SSMS's strengths, as opposed to a generic database GUI IDE, is that it only works with one database: SQL Server (and variants like Azure SQL). This is important for accessing the specific features that distinguish that database from others, e.g. indexed views, clustered columnar indices, user-defined types, functions, linked servers, replication, HA, etc. There are so many cool and interesting features in MS SQL Server. I don't go for Microsoft products in general, Microsoft really got SQL Server right.
- AlfeG 8y agoI use Dbeaver. Its not a ssms, but very handy tool
- wenc 8y agoI've used it too. It's one of the best free tools of its ilk. But yes, it's not SSMS.
- gt565k 8y agoI'd like to see support for computed/derived columns with options to be materialized or non-materialized. I guess that's coming in version 12 after a few google searches.
- CyanLite2 8y agoPostgres doesn't come close to the enterprise-level features AND support as that of Oracle or SQL Server.
- jon-wood 8y agoI’m curious, what features are you missing? Support is always going to be a bit different with open source products, but I’m sure there’s companies that’ll give you a support contract worth more than the equivalent from MS or Oracle.
- daxorid 8y agoA few key missing features: - You can't write off or expense three martini lunches and golf excursions with a PostgreSQL sales rep. - When you deal with unavailability, you can't point to the PostgreSQL support SLA's four hour response and tell your boss "not our problem". - It's harder to justify larger budgets for your IT fiefdom and thus your sense of self-importance when you don't pay for unnecessary license fees.
- dragonwriter 8y ago> You can't write off or expense three martini lunches and golf excursions with a PostgreSQL sales rep. That's one of the Oracle features supported by EnterpriseDB’s proprietary downstream version. > When you deal with unavailability, you can't point to the PostgreSQL support SLA's four hour response and tell your boss "not our problem". Also, this, but it is a 24 hour resolution window for Severity 1 issues. > It's harder to justify larger budgets for your IT fiefdom and thus your sense of self-importance when you don't pay for unnecessary license fees. EnterpriseDB addresses this, but certainly doesn't usually match Oracle in the budget impact department.
- dkhenry 8y agoSo I know your comment is a bit tongue in cheek, but there are real concerns about the last two points that people may not realize. At many large orginizations the outside vendor may be the _only_ real technical capabilities that team has so there is no solution other then "outsourcing" to the vendor's support team. Budgets have similar incentives that make teams want to keep a large budget around even if they could get the job done with less resources. That slush money comes in handy to have the vendor do all sorts of work that isn't really about getting their system running ( i.e. doing all the integration with other parts of your stack ) I think lots of open source products or smaller teams think winning those "Enterprise" deals is about having the better product and checking some boxes on a requirements sheet. When you get into enterprise its all about the relationships
- rubyn00bie 8y agoAnyone else getting a bad certificate when visiting in Firefox?
- igneo676 8y agoWorks fine for me - Latest Firefox Developer Edition on Linux
- joecool1029 8y agoCheck your clock and date. If it's off by a lot you'll get weird cert errors.
- paulryanrogers 8y agoWhile indeed advanced I find it's focus on reliably and ACID are more useful than triggers or extensions. Triggers increase write load and extensions aren't available on all hosting services. Replication, interchangable storage, and standard SQL/PL support are getting better, but still lag competitors like MySQL.
- segmondy 8y agoI've a love and hate relationship with triggers, but I just need to say triggers don't have that much overhead if done correctly. 99% of businesses don't have a scaling concern, and triggers will do them well if they use it to capture mandatory rules instead of trying to implement it at the application layer. It's a pain when misused. The key thing is to reason about your user permission/roles correctly so that you don't end up with cascading failures when adding new triggers.
- ernst_klim 8y agoI've dived into the postgres code recently and it was incredible. Lisp legacy is all over the place, it's literally lisp in C, though unlike many "Langname-styled C" codebases it looks really clean and organic. I'm still confused how people prefer oracle [1] other postgres. [1] https://news.ycombinator.com/item?id=18442941 https://news.ycombinator.com/item?id=18442941
- jstimpfle 8y agoWhat is "Lisp style C"? Can you link an example?
- nicklaf 8y agoI can't find it, but I remember seeing a linked list library for C, which supported a functional style like Lisp. I don't know how closely it was related to S-expressions, but I'd be interested if somebody could help me remember what I'm thinking of here.
- mschaef 8y agoI tend to think of George Carette's SIOD Scheme interpreter when I think of "Lisp style C": LISP difference(LISP x,LISP y) {if NFLONUMP(x) err("wta(1st) to difference",x); if NULLP(y) return(flocons(-FLONM(x))); else {if NFLONUMP(y) err("wta(2nd) to difference",y); return(flocons(FLONM(x) - FLONM(y)));}} http://people.delphiforums.com/gjc/siod.html http://people.delphiforums.com/gjc/siod.html Of course, that's a Scheme interpreter, so it might be at least a little expected.
- hildaman 8y agoI'll tell you why - try to update one column in 100GB worth of row data. Postgress makes a copy of _every_ row and you need 100GB of extra space on the hard-drive until you commit the transaction. Now extrapolate to a 1TB table that needs updating. Oracle has a way of doing this w/o copying the entire row.
- snuxoll 8y ago
- sroussey 8y agoThe title should include “free” before database, and it’s generally true. But the article doesn’t really delve into anything advanced at all. Triggers? Please.
- blattimwind 8y agoI've recently used T-SQL / MSSQL and was surprised how starkly it differs from your typical "foss sql" (be it postgres, sqlite or mysql). One really obvious example is how [] are used for qualifying names, or how there is no LIMIT clause (instead you use SELECT TOP(n), but you still use an OFFSET n ROWS clause after the ORDER BY clause for an OFFSET; there is also OFFSET n ROWS FETCH NEXT m ROWS ONLY). Another example are curious limits to programmability, e.g. TEXT can't be used for procedure parameters. There also seem to be small limits on BLOBs. No NATURAL JOIN (which I mostly use for ad-hoc queries). It is also very different deployment wise (as are all Microsoft products). You don't have a client library or anything like that, but a system-wide database driver instead. Applications use a driver interface and could (most don't) support other database versions or even databases. You can't "just" throw a MS SQL install on a machine, it needs to be properly installed system-wide and register all its components or it won't work properly etc. — so spinning an instance up for testing really isn't nearly as easy as with postgres.
- TomMarius 8y agoWindows (for test/dev): https://hub.docker.com/r/microsoft/mssql-server-windows-developer/ https://hub.docker.com/r/microsoft/mssql-server-windows-deve... MSSQL also runs on Linux: https://hub.docker.com/r/microsoft/mssql-server/ https://hub.docker.com/r/microsoft/mssql-server/
- Lifesnoozer 8y agoT-SQL might have some oddities, but Microsoft distributes SQL Server as a Docker image, very easy to setup. https://hub.docker.com/r/microsoft/mssql-server/ https://hub.docker.com/r/microsoft/mssql-server/
- Nullabillity 8y agoThat the setup is complex enough that they need a container should tell you enough.
- manigandham 8y ago
- kev009 8y agoI'm a big fan of Postgres and it is basically the only DB I use but the title is naive and the post is low content. SQL Server, Oracle, and DB2 are crown jewels products and each have substantial and difficult to implement niche or extreme scale features or other sweet spots that no open source databases come close to after two decades, and they weren't sitting still during that time either.
- EGreg 8y agoCan you be more specific? I always wondered why people use them
- kev009 8y agoI'm not advocating their use and the default should be right and smallest fit for the job, which means sqlite or Postgres nearly always. But too often people get in this mindset that old commercial products are meritless or whatever. An example for Oracle would be RAC. This is a mature shared data architecture to provide high availability and horizontal server scaling to a DB. Deploying this is a fairly standard Oracle DBA activity. Getting something similar with open source tools is a big lift and permanent support commitment and will be for a while. Meanwhile these three commercial DBs have provided continuity for decades for features like this.
- devy 8y ago> But too often people get in this mindset that old commercial products are meritless or whatever. There is a lot of that assumption. And then, there is the vendor lock-in licensing terms that commercial DB vendors utilize, in that database switching cost is just prohibitively high and impractical.
- manigandham 8y agoThe lock-in is not from licensing, it's from the features and performance. If you aren't using those capabilities then you didn't need that product anyway, and can use any number of migration tools to switch to a different database.
- thedangler 8y agoI started using Postgresql a long time ago when I tried to do a sub query in MySQL and it didn't support sub queries. Haven't looked back since.
- andscoop 8y agoI do not know if this is still the case but the Json support was non existent in mssql 4 years ago and in postgresql is absolutely phenomenal.
- siquick 8y agoStarted using PG recently at a new job but can't really see any benefit over MySQL. What am I missing?
- gaius 8y agoIf you lose the root password, you can fix it in Postgres without taking the database down.
- captainperl 8y agoThere are several ways to update the root password in MySQL with zero down-time, either by manipulating the user table, using replication, or using other accounts (ie. most people grant excessive privileges to role accounts.) https://www.percona.com/blog/2014/12/10/reset-mysql-root-password-without-restarting-mysql-no-downtime/ https://www.percona.com/blog/2014/12/10/reset-mysql-root-pas...
- mooreds 8y agoThe big one I am aware of is transactional ddl, which I believe got integrated into MySQL 8.
- guiriduro 8y agoData :)
- madeuptempacct 8y agoI generally use SQL or MongoDB. Used Postgres in a few places - it was used just like SQL is. Am I missing something? The only arguments I heard for Postgres were "free" and "support for JSON" (which I never tried).
- NedIsakoff 8y agoOne word: Exadata..
- luord 8y agoThe article doesn't have anything I didn't know about or new but yet another reminder of how postgres is, IMO, the best piece of sw ever created is nice.
- tonysdg 8y agoA fun article about PostgreSQL and fsync(): "PostgreSQL's fsync() surprise" https://lwn.net/Articles/752063/ https://lwn.net/Articles/752063/