EXPLAIN is almost useless on mysql, but very helpful in postgres.
EXPLAIN is a very useful command once you have some degree of success (users).
Ask HN: PostgreSQL or MySQL?
11–20 of 181 posts
Re: Ask HN: PostgreSQL or MySQL?
#12On the first point, default to using Postgres unless you have a particular reason not to. In general, it offers more and better-executed features. There are some scenarios where MySQL offers a benefit and this advice doesn’t apply, but it’s unlikely you will need to worry about that if you’re in a “what database do I use” position. Your other questions are a bit too vague to be answerable. It’s possible to build anyt…
As someone who has transitioned from MySQL->pg, this makes total sense to me. Partitioning is still a bit better in MySQL IMO (many types / options) - but pg is nearly there.
Re: Ask HN: PostgreSQL or MySQL?
#13Regarding 1): choosing a software stack (or any part thereof) without a clear use case is a recipe for failure. What's the use case?
To be fair, often you don't have your requirements completely nailed beforehand, or they change midway through the project.
That's why I choose Postgres: aside from extreme scale or latency, you can throw pretty much anything at it. Also, given expert advice, its performance degrades gracefully i.e. lots of easy ways to optimize and workaround issues.
Example: pg's type system is insanely flexible incl native support for high speed JSON, latlng, geometry, and more - and types are "batteries included" incl matching, inspection/extraction (e.g. JSON navigation), conversion, concurrency and concurrency control, ACID recovery, replication, etc. For example, it's one way line of code to create an index on the results of a JSON navigation expression whose result is then converted to another datatype e.g. timestamptz. Performance is then 100x+ on queries that use that expression.
Pg datetime handling is also world class incl timezones and conversion, query and manipulation functions. Want the records from last Tuesday across timezones, given data in seconds-since-the-epoch? The SQL is shorter than in English.
Re: Ask HN: PostgreSQL or MySQL?
#14#1: depends on your scenario. Postgres is more like Oracle--there are types of query it can do natively that would have to be externally assisted with MySQL. MySQL is simpler, has all kinds of warts, but scales decently. Here's why uber moved to MySQL: https://eng.uber.com/mysql-migration/ #2-4: My use of SQLite has always been very basic, so no idea. Per SQLite's author: " SQLite does not compete with client/server…
Re: Uber it's not like they didn't cause some of their own issues by keeping very long running transactions. Also, they don't use MySQL directly, my understanding is thqt they wrote their own database and use MySQL as a kv store.
Re: Ask HN: PostgreSQL or MySQL?
#15[deleted]
Re: Ask HN: PostgreSQL or MySQL?
#16For 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.
Re: Ask HN: PostgreSQL or MySQL?
#17You don't really benefit from MySQL speed if you are not Facebook, so I would always start with PostgreSQL.
Re: Ask HN: PostgreSQL or MySQL?
#18Given the progress both Mariadb and postgresql has made I think you will not go wrong with either. In both you have support for json now which one you chose depends on your use case.
Mariadb - If you want easy horizontal scalability with master-master replication go with mariadb.
Postgresql - If you want reliable and powerful sql-92 compliance and willing to work with master-slave go for postgresql. You can do master to master with postgre-Xl [1]. But still given the ease of use with which you can work with galera cluster I would not recommend postgresql for master-master.
Anyways for your other questions:
2. SQLite already supports json type [2] so its not SQLite+JSON its just SQLite.
3. Yes you can write a simple python script to do it.
4. Why to use a custom file format when SQLite has its own format with support for json.
Re: Ask HN: PostgreSQL or MySQL?
#19Are you confusing SQLite with SQL?
Re: Ask HN: PostgreSQL or MySQL?
#20Personally,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.