At 22 years old, Postgres might just be the most advanced database yet
arcentry.com
arcentry.com
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.
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.
Like, imagine you have a few hundred terabytes of data you need to keep in-sync globally, between multiple datacenters, with many servers in each DC.
By cross-datacenter I mean datacenters far away from each other, in different geo regions.
It still doesn't have proper upsert or JSON columns, so PostgreSQL wins on small usability features, but SQL Server is a serious workhorse when it comes to large scale operational and analytical systems with unmatched performance. PG barely has parallel queries or decent connection management and is a decade away from catching up to SQL server in these features.
Upsert (MERGE) was added in SQL Server 2008 (although it's chock full of known bugs): https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
JSON was added in SQL Server 2016, although indexing it is still tricky: https://docs.microsoft.com/en-us/sql/relational-databases/js...
Is the integrated encryption comparable to pgcrypto?
Stretch and Polybase are much more limited to Azure storage and Hadoop systems with deep integration into T-SQL, and scale-out using clustering.
I am a little torn about this - on the one hand it strikes me as laziness that all these Windows developers will just pick SQL Server because it is so convenient (good integration with Visual Studio, drivers are present by default in .Net Framework). On the other hand, in terms of performance and reliability, I cannot say a single bad word about SQL Server. Another thing I like is that one can use accounts from an Active Directory domain as database accounts, so you do not have to duplicate group structures and such, if you use elaborate database permissions.
I have come to like T-SQL, which integrates some procedural features into SQL, so SQL Server does not need a separate language for writing stored procedures, triggers, and so forth. Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature.
Postgres offers many features SQL Server lacks, e.g. the array of data types available in Postgres is amazing, or the fact that it has enums. SQL Server has some features Postgres lacks, for example the Service Broker, which is very interesting, but apparently non-trivial to use correctly.
Embedded R in SQL Server as well.
Postgres is my default database choice, or SQLite if it's small, but SQL Server is a solid, high performance product and I'll gladly run it for the most demanding workloads.
https://en.wikipedia.org/wiki/Oracle_Database#Releases_and_v...
The problem is, SQLite does have a few limitations that it doesn't make obvious: some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably. (Don't believe me? Try this: https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894)
I know that SQLite supports concurrent writes and reads technically, but it predictably leads to 'database is locked' issues. In practice you have to use it in a fashion that it only ever does one thing, read, or write. No multiple writes. No single write and multiple readers, no single write and single reader. Just read — or write. (Edit: WAL doesn't help with this either.)
Or you're in a world of hurt. I'd love to be able to move to Postgres just to get away from that.
If you fix this (which is easy), then WAL works just fine. Note that transactions, as is standard, span from BEGIN/COMMIT to COMMIT/ROLLBACK.
See e.g. https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#t... and https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p...
[1] Really it's more like "encoded": https://docs.python.org/3/library/sqlite3.html#controlling-t... -- you already need to know the issue to understand that this section specifies the root cause.
How many drivers have you actually tested? There are 2 comments from 2016 on that thread saying other drivers do work fine.
I don't know...it's recently added upsert compatible with Postgres syntax as well as support for window functions.
Additionally, sqlite has a sophisticated json extension.
> it can only ever do one write or read to the database predictably
WAL mode addresses this, allowing an arbitrary number of readers with a writer.
For concurrent applications just use a dedicated write thread and enqueue writes...your example is contrived. Connection management, transaction scope management, etc, can all work in your favor. Writes occur in a few milliseconds, meaning you only need to hold the exclusive lock very briefly in most situations.
More information here: http://charlesleifer.com/blog/going-fast-with-sqlite-and-pyt...
Not all writes are couple milliseconds. I regularly have transactions that take 15< seconds, and it's not because the data size that's being input is large, it's just that the SQL is recursive and complex enough to warrant recursion (storing graph data).
https://www.sqlite.org/src/tree?ci=754ad35cd26da361
https://www.sqlite.org/src/artifact/b98409c486d6f028
Haven't yet played around with it myself, and I tend to expect it'd be more experimental than something to use in production.
Do you mean your application actually throws the error "database is locked," and dies without simply waiting for the lock to release? This behavior definitely goes against the grain of the documentation. From the SQLite website:
"SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds." --- https://www.sqlite.org/whentouse.html
I have to wonder, with the others here, whether the bug is in the driver, or just anywhere but SQLite. With such fragility it would have blown up by now. SQLite is ubiquitous: the iPhone, iTunes, Android, Chrome, Windows 10, McAfee, the Redhat Package Manager (RPM), commercial flight software, and many other pieces of software --- https://www.sqlite.org/famous.html
Also just want to add an oracle developers post that I find hilarious...
This was a gem. Really makes you appreciate not having to work on such a codebase.
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.
MSSQL also runs on Linux: https://hub.docker.com/r/microsoft/mssql-server/
Yes, SQL server is complex software, and can be installed via apt-get as well.
We had a requirement for paginating through records, and the group of MSSQL admins were... flummoxed at the request. "Why would you need to do that? Just use top(X)!" Umm... when there's 48000 records, and you're trying to see records 47000-47100.... wth?
They spent days back and forth trying to come up with reusable sprocs with odd combinations of top/reversing/top/sorting - SQL Server 2000 at the time. I'm not sure the rownumber() thing was available at the time. And... there was much grumbling about this 'stupid' request. I was honestly a bit shocked (I was still a bit naive about 'enterprise' folks) that this was such a chore, and not there. We also needed in-place encryption/decryption, which one of the prototypes had with mysql aes_encrypt(), but we had to use MSSQL, so... thousand of dollars later for some db tool, this was available.
I know MSSQL has many advantages, and is very powerful in many situations, but MS-first people sometimes are oblivious to 'standard' functionality other products have.
That's a choice ala ODBC. A simple TDS client library works just fine. In fact, it's not a complex protocol for basic uses and is fairly easy to write a library for.
As another commenter pointed out, you can run MSSQL in Docker. We do this in CI.
You can also install it pretty easily on a standard Linux machine, without much more complexity than Postgresql. For example, you can `apt install mssql-server`
https://docs.microsoft.com/en-us/sql/linux/quickstart-instal...
https://docs.microsoft.com/en-us/sql/connect/sql-connection-...
It can also run in Docker containers on Windows, Mac or Linux and spins up in seconds, along with the usual package manager installs on Redhat/Ubuntu:
https://docs.microsoft.com/en-us/sql/linux/quickstart-instal...
1) Weird gaps in ANSI compatibility (no concatenation with ||, no current_date, top n instead of limit/offset, etc.)
2) Just yesterday a client's SQL Server started selecting deadlock victims to be killed, so the DBA's workaround was to throw (nolock) hints on every table and read dirty data. It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks.
3) The query planner seems amazingly naive for such a mature product. Hard to understand how a query with three where-clause constraints can execute in 20 ms, but adding a fourth constraint makes it run 240000 ms, but I see this kind of insanity every day.
SQL Server has like 4 different isolation levels + MVCC, you just pick the one you need. Postgres just does MVCC so it sounds like you should just switch SQL Server to that mode. A poor workman blames his tools. Unless that tool is MongoDB.
In the real world of mediocre DBAs and databases managed by application vendors, it's never that easy, is it?
Postgres just works with default settings while SQL Server chokes on the same transactions with the same volumes.
SQL Server is legendary [0] for deadlocks even when the workload is mostly reads and rarely writes. You just don't see this with Oracle, Postgres or MySQL with default settings.
Not sure why I'd pay for the privilege of extra headaches.
[0] It was a problem in 2008, still not resolved https://blog.codinghorror.com/deadlocked/
SQL Server has one of the most advanced query optimizers. Are you running on an old version or using out of date statistics?
I'm still confused how people prefer oracle [1] other postgres.
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)));}}
Of course, that's a Scheme interpreter, so it might be at least a little expected.
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.
This only happens if the column is indexed, heap-only-tuples will allow in-place updates otherwise. This doesn't dismiss it as a potential problem entirely, but depending on your needs you may never run into this.
That's what the zHeap storage engine for Postgres fixes - in-place updates for fixed width data.
And I have seen Oracle 12c servers completely lock because of that way.
That said, one can often capture much of the performance benefit with Postgres indexes - basically, instead of trying to tightly-pack the base tables, copy the data into indexes which are tightly packed exactly as you need. Put another way, Postgres indexes are synchronously updated materialized views - and yes, the Postgres planner/optimizer will automatically answer queries from an index if it's faster and all the columns are in there.
I esp recommend reading about function and expression indexes, as well as the (new) covering indexes.
http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-in... https://www.endpoint.com/blog/2013/06/10/postgresql-function... https://paquier.xyz/postgresql-2/postgres-11-covering-indexe...
Better space usage and performance for that single index since the table is the index, slightly worse performance for non clustered indexes due to a double index traversal.
FWIW I much prefer Postgres to Oracle, but not because of the technology as much as the price.
https://github.com/postgres/postgres/blob/master/src/backend...
Mind you, I'm biased because I've written a fair amount of actual "Lisp style C", e.g.:
http://www.kylheku.com/cgit/txr/tree/regex.c?id=2938a0d7e64e...
My detector for "this C is Lispy" has a rather high threshold / low gain.
You can still see the Lisp heritage in a few places, but overall I wouldn't say Postgres is "literally lisp in C".
In code that truly embraces "Lispy" lists, you cannot switch to an encapsulated list representation without making substantial changes to the code. For instance if you have any cdr recursion going on, that will have to be restructured, because a bag-style list doesn't have a cdr that is itself a bag-style list. (Perhaps the function has to be split into an external one that takes the List and then a local recursive part that works with the ListCell.)
I would say that to a fair extent, it looks to me as if the code anticipated a future retargeting to a different list representation, whereas the "Lispy" approach is to embrace a particular list representation.
Lisp programs sometimes need to improve the performance of adding to the tail of a list; it's done with some wrapping, like a structure that keeps a pointer to the tail. Usually that is only locally used; it doesn't "travel" as part of the representation of the list. It is not an encapsulation device, but only a process device. Of course there is also the common pattern of building up a list in reverse followed by a destructive nreverse. And the meta-approach of designing things to avoid doing work at the tail end of the list.
I just remembered the existence of some C code that has nothing to do with any Lisp implementation internals, which pushes and nreverses: http://www.kylheku.com/cgit/c-snippets/tree/autotab.c#n228
A double-ended queue (deque) can be formed in Lisp by using a pair of lists. So both ends of the queue are list heads, from which we cdr into the interior. I developed such a thing which is hosted here:
http://www.kylheku.com/cgit/lisp-snippets/tree/deque.lisp
With this deque, we simply use the push macro to add items to either end of the queue. pop-deque will remove an item from either end. Underflows are handled by rebalancing the dequeue: shuffling items from one list to the other. The cost of that is amortized. (BTW I see that pop-deque has a multiple-evaluation-of-arguments problem; it really should be written using get-setf-expansion.)
I think changing the list representation made sense in part because the rest of the codebase didn't use lists in a deeply Lispy way, for the most part.
I used to call it CrashGreSlow back when Mysql was maintained. Postgres had a manifesto which trash-talked Oracle but it was not a reliable product at that time. Maybe the people who said it was fast were running it on ram disks with fsync turned off or something.
After mysql got bought by Sun and put on ice, postgres caught up with the hype and now it is pretty reliable. That wasn't always the case.
It is never just PostgreSQL is a great database. It’s always that MySQL, Oracle, MongoDB and the hundreds of NoSQL databases are all unusable junk with no unique benefits.
MongoDB is crap.
I think the people who use Arangodb don't talk about it because they see it as a competitive advantage.
Right now I am working on an abstraction layer for document databases and using couchdb side by side with Arangodb. I guess I'll have to write a N1QL parser to go with my AQL parser.
The database I have the most fun with is SQLite. It is single-user, which means you can't log in with the SQL monitor when your program is running, but if you can accept that it's just great.
However the fact that it's owned by Oracle does make it a little bit scary, even though we have no exposure to their code or anything linked at all. I sort of wish we'd chosen Postgres instead.
Oracle has been a reasonable steward of mysql. They started making releases again. They haven't really moved it forward but I am sure they are taking care of their customers.
So far I haven't been impressed with mariadb and the other forks. For instance I would download it because I heard some hype but the installer didn't work.
This simply is not true. I personally know a bunch of engineers who worked for MySQL AB and stayed on through the Sun and then Oracle acquisitions, and worked on MySQL that entire time.
Additional source: Sun acquired MySQL AB in early 2008. MySQL 5.1 was released in late 2008, and major releases continued on a cadence of every 2 to 2.5 years to this day. You can verify this via release notes, or via Wikipedia, among other sources.
> They haven't really moved it forward
This is purely a matter of opinion. From my own perspective, as someone who has been using MySQL for 15 years, I see substantial ongoing improvement in each major release.
If MySQL was as stagnant as you claim, why would the majority of the largest internet properties continue to rely on it as their primary data store?
Recent major versions have provided substantial enhancements to performance, replication, JSON/document support, and observability, to name just a few.
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.
You must have been using a different mongodb than I did.
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-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.
I've been meaning to try it out for the HA postgres use case, but haven't gotten around to it.
I bet Citus is like this, but I haven't used it yet.
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.
We use the same migrations across hsqldb, postgresql and sql server and it just works
Oh wow, I've wanted exactly this for Postgres. Never knew how to search for it though.
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.
https://www.c-sharpcorner.com/article/create-sql-server-data...
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.
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-dec...
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.
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.
A 30-second outage to switch to a replica is perfectly fine for production.
You can get servers with 2T of memory and 96 cores off the shelf for a few years now. Real world workloads that you can't run on these boxes are few and far between. But everyone worries about "scalability" like they'll ever run into those limits.
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)
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.
I guess that's coming in version 12 after a few google searches.
https://www.percona.com/blog/2014/12/10/reset-mysql-root-pas...
But the article doesn’t really delve into anything advanced at all. Triggers? Please.
"PostgreSQL's fsync() surprise"
- 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.
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.
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
Maybe having clustered indexes, good partitioning, etc.
Still, I’m hard pressed to ever recommend anything other than PostgreSQL or MySQL.
Regarding support, there's plenty of fantastic companies providing support and consulting services for PostgreSQL and having used plenty of commercial databases I know of know no other database that has as vibrant a community providing amazing free support as well.
However from my experience with Oracle and from what I've heard about their source code, its fundamentals are just disgusting. Decades of haphazardly piled-up features and fixes means that if you're the lucky customer to find a new bug, good luck understanding what the heck is going on by yourself.
I'd like to think MSSQL is in a bit better shape, but they have lots of the same incentives so who knows.
> It takes 6 months to a year (sometimes two years!) to develop a single small feature (say something like adding a new mode of authentication like support for AD authentication).
There's no way in the world I would have imagined that adding AD authentication to any database would be a "single small feature".
An ass to drag onto the carpet. In enterprise speak, "support" means "legal liability". You have to be able to sue someone if it fails and loses your company money. This was taught to me by an old IT hand back when I was a young naïve Linux kiddie "you have to be able todrag somebody's ass onto the carpet when the shit breaks".
Oh yes, and there's also brand recognition and a large pool of certified Oracle or SQL Server DBAs to draw from when making hiring decisions. Yes, technical people will know that Postgres is solid and will know how to spot good people to maintain a Postgres installation -- but the person signing off on the IT budget is a total normie. He will smile when he sees familiar names and make a lolwut face when he sees postgres.
Relatedly, Postgres doesn't have a yacht. Oracle's yacht won the America's Cup.
It means that anyone in the company can ring a vendor 24/7 and get the best quality help on the product. It means your outages are smaller and less frequent. You usually can’t get that with a generic open source product or from smaller companies and it’s especially an issue issue in non-US countries where it’s often impossible to get decent support.
I’ve never once seen a company sue a vendor or vice versa.
The first three results I got from a search engine:
1. https://palisadecompliance.com/oracle-lawsuit-pushing-cloud/
2. https://www.theregister.co.uk/2018/03/22/oracle_shoddy_servi...
3. https://www.businessinsider.in/I-felt-like-we-were-being-ext...
And if you can’t handle this I think you have bigger problems to worry about.
What a retarded position to hold.
You have forgotten what the purpose of these providers was in the first place.
"Company X provides service/software Y so you can do business"
But that's not what they do.
"Company X optimizes to extract as much rent as possible from its client in order to show ever increasing amounts of profitability to the stock market"
And I’m guessing you’ve never dealt with a vendor before because often companies negotiate better terms than list price.
And CIO/CTOs rarely unilaterally make decisions on technology choices. You have Enterprise Architects for that.
In contrast, I recently heard about a company with a market cap in the tens of billions where the execs said, "By date X, we won't own hardware or run datacenters anymore." I'm told it forced a lot of teams to reevaluate fundamental technology decisions made decades ago.
So sure, the decisions are proximately about compatibility or specific features. But I think it's reasonable to ask why those specific features are necessary. Because places like Google and Facebook make it clear that it's not scale alone that makes it necessary to give Oracle a truck of cash every year.