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

Ask HN: PostgreSQL or MySQL?

news.ycombinator.com

Post reply on HN
Print view View original

61–70 of 181 posts

Re: Ask HN: PostgreSQL or MySQL?

#61

Post by Zelphyr »

nbouscal wrote: post #6

The following isn’t the top reason I recommend Postgres, but is the reason I think least likely to be echoed in a dozen other comments: Postgres has some of the best documentation of any software product I’ve ever used. If someone wants to learn about SQL or databases, I always have to restrain myself from recommending that they just read the Postgres manual front to back. It’s comprehensive, it’s well written, it’s…

Too many Open Source developers discount the value of documentation. I all too often hear, "It's Open Source so the community should step up and write the documentation." To which I counter that the best person/people to at least start the documentation are the ones who build the product as they're the most knowledgeable about it. The community will gladly contribute. I personally believe that great documentation was a big reason for PHP's success in the early days.

Re: Ask HN: PostgreSQL or MySQL?

#62

Post by no_gravity »

I really like the direct DB to filesystem mappings of the MyISAM engine in MySql. A database is simply a directory. A table is simply 3 files in that directory:

    - One for the structure
    - One for the data 
    - One for the indexes
You can simply copy a MyISAM database by copying the directory (while the MySql server is stopped).

MariaDB has forked the MyISAM engine and created the Aria engine. It is a crash safe version of MyISAM and is now their default engine for all system tables.

So in contrast to MySql, you can completely disable InnoDB in MariaDB. MySql does not support that anymore as their system tables are InnoDB.

Especially for non-concurrency workloads, MyISAM and Aria are super fast.

For these reasons, I really like MariaDB.

Re: Ask HN: PostgreSQL or MySQL?

#63

Post by madhadron »

evanelias wrote: post #55
dezzeus wrote: post #27

I'm going to write a couple of things about the first point. Several years ago, a knowledgable guy told me that the most compelling reason for choosing between PostgreSQL and MySQL was the expected I/O: "for read-intensive workloads (e.g. blogs), choose MySQL; for mixed workloads (e.g. forums), choose PostgreSQL". But I honestly don't know if that may still be valid as of today. Nowadays, I think that for basic thing…

> Several years ago, a knowledgable guy told me that the most compelling reason for choosing between PostgreSQL and MySQL was the expected I/O: "for read-intensive workloads (e.g. blogs), choose MySQL; for mixed workloads (e.g. forums), choose PostgreSQL". This seems a bit backwards. MySQL's main strength is OLTP workloads, including mixed read-write workloads / high write volumes. A majority of the giant social netw…

The biggest social network uses MySQL because they already dumped so much engineering into it that changing is basically impractical. But it's mostly used as a key-value store backing a custom graph database.

Re: Ask HN: PostgreSQL or MySQL?

#64

Post by Thaxll »

evanelias wrote: post #50
rayrrr wrote: post #37

Earlier quoted context omitted.

Funnily enough, I have not been using MySQL for the last 10 years either, because I switched to Postgres and never went back.

Fair, but why comment on MySQL's supposed deficiencies if your knowledge of it is so out-of-date?

But vanilla PG didn't get good master / slave feature until "recently" so you were running without replicas in the last 10 years?

https://www.postgresql.org/about/news/1235/

Re: Ask HN: PostgreSQL or MySQL?

#65

Post by turk73 »

I would say that it all depends on your environment and the politics there. Is ACID necessary? If not, try an NoSQL solution first. If you want large scale replication, setup a Cassandra cluster because that is way easier than any RDBMS scaling situation with the downside of you lose traditional SQL join and group by syntax, which isn't really a big deal once you learn the ins and outs of that. If you do use a NoSQL solution, you can still get SQL behavior using embedded rdbms like Hypersonic SQL (HSQL) or maybe Derby for niche use cases. If you just need a reliable place to store data, Cassandra has a lot of advantages. There is no free lunch. Look at what Azure offers to get an idea of what companies are doing.

For small stuff, it barely matters what technology you select it will all perform well. It's at scale where solutions tend to fall apart.

I don't know if I answered any of your questions at all. Just be aware that I work with lots of JSON and we don't bother converting any of it. If you need to query through JSON, then setup ElasticSearch. Don't monkey around with converting data in and out of SQL databases just to harness some query capabilities because you can also do all that in code with the upshot of having it be unit and integration testable there. I still love SQL, but my recent job has forced me to accept certain realities.

Re: Ask HN: PostgreSQL or MySQL?

#66

Post by cagmz »

steffann wrote: post #9

I have used PostgreSQL+JSONB and it works great. Doing indexes on JSON fields adds a lot of value.

What do you use the JSONB column for?

We've loved the flexibility of JSONB while our schema wasn't nailed down, but eventually migrated many fields to be columns for ease of reading and writing nested objects.

Re: Ask HN: PostgreSQL or MySQL?

#67

Post by madhadron »

1. Do you have experience running PostgreSQL or MySQL in production? Use what you know. Do you have friends or colleagues with that experience? Use what they know. Is the stack you end up using predominantly used with one or the other, so you have lots more people in the community who will have hit issues you might have? Use that one. Failing any of those? Default to PostgreSQL.

2. I think you mean SQL+JSON, and the answer is PostgreSQL's JSONB columns, or dumping blobs into rows in MySQL (just keep your row sizes below 4kb).

3. Possible? Yes. A good idea? Almost never. Trees and relations are quite mathematically different, and you will need to specify quite a bit of the mapping to make it come out in a way you would actually want to work with.

4. Not really...generally you would have a SQLite database. If you need stuff that doesn't go in well as a blob, you would put it in a file and include a reference in the database.

Re: Ask HN: PostgreSQL or MySQL?

#68

Post by perlgeek »

johannes1234321 wrote: post #20
nmpennypacker wrote: post #10

Personally,PostgreSQL. Both have their advantages and disadvantages, but my standard arguments in favor of Postgres are that it: 1. Enforces data types natively. I've shot myself in the foot before with MySQL and 'polluting' data. 2. JSONB support makes it easy to use a 'hybrid' schema if you want NoSQL-like behavior or a junk drawer to shove JSON into. Regarding your conversion questions, it's common to have an app…

> 1. Enforces data types natively. Quite a while ago MySQL changed the default to be strict on types and not doing truncations anymore. Recently it also (finally) got check constraints. > 2. JSONB support makes it easy to use a 'hybrid' schema MySQL has a JSON data type with validation, binary storage, efficient updates and efficient replication.

My experiences are with a pretty old mysql version (5.5, iirc), but that version allowed inserts without supplying a value to a column that is NOT NULL and has no default value.

If that's the strict mode, I shudder to think what the lax mode must be... :(

And upgrade to MariaDB 10.3 caught those cases, so at least there's some progress in the mysql/mariadb ecosystem.

> MySQL has a JSON data type with validation, binary storage, efficient updates and efficient replication.

Postgres has, in addition, indexes over expression, which allows you to build indexes for specific queries into JSONB.

(I'd argue that if you need indexes, it's typically better to extract that data into the regular table and not keep it in the json, but it can still save your butt if you need to react quickly to a requirement change and no time for a data migration).

Re: Ask HN: PostgreSQL or MySQL?

#69

Post by yen223 »

majewsky wrote: post #48
yen223 wrote: post #30

Earlier quoted context omitted.

If the requirements are still up in the air, then I would aim to use the simplest, most evolvable solution. This to me means no database.

If the requirements are still up in the air, but you know that you're going to be storing some data, definitely go with a database, even if just a single SQLite file. Using plain files means figuring out the file format, which turns into a huge mess when requirements get tacked on later. The other option would be to store a JSON file or a bunch of JSON files in a directory, at which point you're building a shitty ver…

The grandparent comment is right about one thing: you need to figure out what your use case is, in order to make a good judgment call. “Some data” can mean a lot of things. Transactional data? Sure, get a sql database in. Images, video, or plain json documents? SQL databases are a poor fit there.

One should also consider if they even need to manage data persistence. I’ve had success using Google Sheets as the backing data storage for a prototype. Below a certain scale, it is a lot easier to visualise and manipulate data in a spreadsheet than through a sql client.

Re: Ask HN: PostgreSQL or MySQL?

#70

Post by h2odragon »

barrkel wrote: post #45
glax wrote: post #16

For me postgresql's schema, EXPLAIN, Column modification and UUID. But most of the time I stick to sqlite, it satisfies most of the project with low footprint and require for installation restricted servers.

Every time I've used sqlite for any kind of throwaway web app, I've regretted it due to the almost complete lack of support for concurrent operations. Sqlite is for file formats, not anything that might have concurrent writes.

> Sqlite is for file formats, not anything that might have concurrent writes

Thats very well put and excellent advice. I still bust out in giggles that we're in a world where "SQL for file formats" is a thing.

Post reply on HN