Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL vs. MS SQL

pg-versus-ms.com

Post reply on HN
Print view View original

31–40 of 106 posts

Re: PostgreSQL vs. MS SQL

#31

Post by UnoriginalGuy »

DrJokepu wrote: post #18
masklinn wrote: post #7

> PostgreSQL: the docs actively encourage you to simply use the TEXT type. This is a high-performance, UTF-8 validated text storage type which has no length limit . The emphasised part is incorrect, Postgres's text storage has a 1GB limit (that stands for CHAR, VARCHAR and TEXT which all use the same underlying mechanism): http://www.postgresql.org/docs/9.4/static/datatype-character... > In any case, the longest poss…

That being said, if your relational database has 1GB records, you really need to reevaluate whether you are using the right tool for the job.

While I kind of agree, what exactly is the "right tool" for storing large blobs?

You can use the filesystem but then you have to manage data migration/sync by "hand" which is kind of a bitch for distributed systems.

Honestly storing large blobs with meta-data telling you about the blobs is extremely common. I'm yet to hear of how you're meant to do it correctly.

Re: PostgreSQL vs. MS SQL

#32

Post by rbanffy »

masklinn wrote: post #7

> PostgreSQL: the docs actively encourage you to simply use the TEXT type. This is a high-performance, UTF-8 validated text storage type which has no length limit . The emphasised part is incorrect, Postgres's text storage has a 1GB limit (that stands for CHAR, VARCHAR and TEXT which all use the same underlying mechanism): http://www.postgresql.org/docs/9.4/static/datatype-character... > In any case, the longest poss…

Let's agree he should have said "no practical length limit".

If you are using gigabyte-long text strings in a relational database, you are doing it very wrong.

Re: PostgreSQL vs. MS SQL

#33

Post by bergeoisie »

general_failure wrote: post #27

I scrolled to a random page. It was totally absurd, stopped right there. "Crucially, because open-source software tends to be written by people who care deeply about its quality (often because they have a direct personal stake in ensuring that the software works as well as possible), it is often of the very highest standard (PostgreSQL, Linux, MySQL, XBMC, Hadoop, Android, VLC, Neo4JS, Redis, 7Zip, FreeBSD, golang, P…

Weird that OpenSSL isn't on the list of OSS projects.

Re: PostgreSQL vs. MS SQL

#34

Post by fluffygrenade »

general_failure wrote: post #27

I scrolled to a random page. It was totally absurd, stopped right there. "Crucially, because open-source software tends to be written by people who care deeply about its quality (often because they have a direct personal stake in ensuring that the software works as well as possible), it is often of the very highest standard (PostgreSQL, Linux, MySQL, XBMC, Hadoop, Android, VLC, Neo4JS, Redis, 7Zip, FreeBSD, golang, P…

I didn't even get that far.

Clearly SQL Server definitely wasn't suitable for our 4.5TiB of LoB financial data in 500 tables for the last 15 years and was such poor quality that we have absolutely no problems whatsoever running two 48 core Xeons with 64GiB of RAM at 50% load 24/7...

Edit: I live down the road from the guy:

Matthew Byrne (address removed, but lives in Shepperton, UK)

Please have enough balls to put your name on the site and the article or enough skills to hide your whois entry.

Re: PostgreSQL vs. MS SQL

#35

Post by x0x0 »

UnoriginalGuy wrote: post #31
DrJokepu wrote: post #18

Earlier quoted context omitted.

That being said, if your relational database has 1GB records, you really need to reevaluate whether you are using the right tool for the job.

While I kind of agree, what exactly is the "right tool" for storing large blobs? You can use the filesystem but then you have to manage data migration/sync by "hand" which is kind of a bitch for distributed systems. Honestly storing large blobs with meta-data telling you about the blobs is extremely common. I'm yet to hear of how you're meant to do it correctly.

commonly, s3 or zfs nas for storage and pg for metadata

Re: PostgreSQL vs. MS SQL

#36

Post by DrJokepu »

x0x0 wrote: post #35
UnoriginalGuy wrote: post #31

Earlier quoted context omitted.

While I kind of agree, what exactly is the "right tool" for storing large blobs? You can use the filesystem but then you have to manage data migration/sync by "hand" which is kind of a bitch for distributed systems. Honestly storing large blobs with meta-data telling you about the blobs is extremely common. I'm yet to hear of how you're meant to do it correctly.

commonly, s3 or zfs nas for storage and pg for metadata

That's basically what a client of mine does.

Re: PostgreSQL vs. MS SQL

#37

Post by JohnBooty »

This article is a fantastic tour of some of Postgres' programmer-friendly features.

If you're interested in databases but aren't familiar with Postgres, it's a good read if you skip the criticisms of MSSQL. Some of those criticisms are spot-on, some are iffy, and some are rah-rah-yay-yay-open-source exhortations that seem to be lifted from Slashdot threads fifteen years ago.

(For whatever it's worth, I love MSSQL. Been using it for well over a decade. That said, I certainly don't mind reading criticisms of it.)

Re: PostgreSQL vs. MS SQL

#38

Post by roller »

taspeotis wrote: post #4

Three things I really enjoy about MSSQL are: 1) SQL Server Data Tools for complex schema management. 2) Graphical execution plans. 3) It's the database best supported by Entity Framework. If you know what IEnumerable is, and you know what IQueryable is, and you know what a leaky abstraction is, you can use EF to quickly start getting data in and out of SQL Server with very reasonable performance.

Just on your #2: I'm not entirely sure how the graphical executions plans help in the end. I've seen too many plans that wouldn't fit on 30" display. The postgresql explain text format is reasonable, and can even be automatically logged when queries are slow [1].

http://www.postgresql.org/docs/9.3/interactive/auto-explain....

Re: PostgreSQL vs. MS SQL

#39

Post by threeseed »

UnoriginalGuy wrote: post #31
DrJokepu wrote: post #18

Earlier quoted context omitted.

That being said, if your relational database has 1GB records, you really need to reevaluate whether you are using the right tool for the job.

While I kind of agree, what exactly is the "right tool" for storing large blobs? You can use the filesystem but then you have to manage data migration/sync by "hand" which is kind of a bitch for distributed systems. Honestly storing large blobs with meta-data telling you about the blobs is extremely common. I'm yet to hear of how you're meant to do it correctly.

I had to solve this problem at my last job and we had vendors let us know how companies like Apple, Sony, Disney, EA etc have solved it. Basically there are two ways to do it. (1) Store it on some "filesystem". In quotes because how you do it can vary wildly e.g. S3, GlusterFS, Standard directories using DRBD for HA. (2) Take the blob, slice it into pieces, hash it and spread it across a sharded database.

Generally it seems to be that if you have lots of unique large files then use filesystem. But if you have files which are likely to have duplicates then use a database. So a file storage locker may use (1) but a service like iTunes Match would use (2). And IIRC Apple in fact does store at least uploaded music files in Cassandra.

Re: PostgreSQL vs. MS SQL

#40

Post by rzimmerman »

general_failure wrote: post #27

I scrolled to a random page. It was totally absurd, stopped right there. "Crucially, because open-source software tends to be written by people who care deeply about its quality (often because they have a direct personal stake in ensuring that the software works as well as possible), it is often of the very highest standard (PostgreSQL, Linux, MySQL, XBMC, Hadoop, Android, VLC, Neo4JS, Redis, 7Zip, FreeBSD, golang, P…

I hit the one about how MS SQL Server doesn't run on Linux and had a similar reaction.
Post reply on HN