0 mention of language or datatype extensibility, which is huge.
Showdown: MySQL 8 vs. PostgreSQL 10
41–50 of 99 posts
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#42I like that PostgreSQL can have both relational table and JSONB document collections (NoSQL) in the same database. Use NoSQL where it makes sense and relational tables for data that is inherently relational and query and join both (or batch-process from one to the other). I find this very cool. Of course I wonder if it's too much cool and in trying to do everything it's falling short in some significant and fundament…
Wow, I had no idea it could store jsonb. Any obvious advantages to using mongodb for nosql that I should consider? I’m way more comfortable with Postgres and would rather stick to that
Storage and thus speed at the expense of memory utilisation. RAM vs HDD.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#43The article contains a footnote about UUIDs as primary keys. > UUID as a primary key is a terrible idea, by the way — cryptographic randomness is utterly designed to kill locality of reference, hence the performance penalty Is there anyone who can go a little bit more in detail? We planned to migrate our database to use UUIDs as primary keys. This will allow creating new rows on clients knowing the new primary key be…
The one time I used UUIDs as primary keys I quickly regretted it. They're huge, they're not possible to manually enter ("which row ID was causing that bug?") and they make foreign key relationships ugly and large too. If you want to generate IDs independently of the database you can do so using a "ID generator" mechanism. Set up three redis instances (or MySQL or anything else that can increment a counter). Have each…
Is "Shaffer" a typo of "sharded"? If not I'm curious what
the term means (all that google turns up are a bunch of papers by a guy with that name)?
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#44I like that PostgreSQL can have both relational table and JSONB document collections (NoSQL) in the same database. Use NoSQL where it makes sense and relational tables for data that is inherently relational and query and join both (or batch-process from one to the other). I find this very cool. Of course I wonder if it's too much cool and in trying to do everything it's falling short in some significant and fundament…
Wow, I had no idea it could store jsonb. Any obvious advantages to using mongodb for nosql that I should consider? I’m way more comfortable with Postgres and would rather stick to that
I have not found a situation where mongodb has been a better choice than postgresql.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#45Earlier quoted context omitted.
(The author is assuming that the primary key controls disk layout, which is usually true.) One advantage of using an incrementing integer is that rows will be ordered on disk based on when they were created. This often helps performance. If a query asks for 25 consecutive rows, there is a good chance they will all be on the same page. If you use UUIDs, then they could be on 25 different pages and you will have to do…
> One advantage of using an incrementing integer is that rows will be ordered on disk based on when they were created If each identifier starts with a logical time, say lamport timestamp, you can still get the same ordering effect without incrementing integers in a centralized place somewhere.
Have used this to great results in postgres, mysql, redis, etc.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#46> With a clustered index, when you look up a record by the primary key
To nit-pic - it might be the case in mySQL but some DBs (SQL Server for instance) allow the clustering key to be something other than the primary key, and for some analytical workloads this can be much more efficient.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#47I am pretty sure that PG has had clustered indexes for a decade or more... ? e.g. https://www.postgresql.org/docs/9.1/static/sql-cluster.html Or is this term referring to a different feature/method than this? One thing not mentioned: PL/SQL vs. whatever the MySQL equivalent is.
You're correct that PostgreSQL has had clustered indexes for quite a long time. The only difference here is that PostgreSQL allows a HEAP table. That said, you do have to re-cluster a PostgreSQL and that requires an exclusive lock on the table, which is obviously not idea for a massive table. MySQL will always cluster on the PRIMARY KEY or (if there isn't one) the first UNIQUE key[0], but as far as I can tell it always clusters on write. I don't see any way to configure the padding of the clustered index like you can on SQL Server, so I'm not sure how this is accomplished.
MySQL only supports SQL in procedures[1]. There is no PL/pgSQL[2] equivalent, and except for custom UDFs written in C/C++, there's no support for external procedures, either. PostgreSQL[3] supports PL/pgSQL, PL/Python, PL/Tcl, and PL/Perl in base, plus there's external modules for PL/Java, PL/Lua, PL/R, PL/sh, and PL/v8.
[0]: https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.h...
[1]: https://dev.mysql.com/doc/refman/8.0/en/adding-functions.htm...
[2]: https://www.postgresql.org/docs/current/static/plpgsql-overv...
[3]: https://www.postgresql.org/docs/current/static/xplang.html
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#48I am pretty sure that PG has had clustered indexes for a decade or more... ? e.g. https://www.postgresql.org/docs/9.1/static/sql-cluster.html Or is this term referring to a different feature/method than this? One thing not mentioned: PL/SQL vs. whatever the MySQL equivalent is.
This is not the same thing - it is rearranging an existing index for efficiency as a one-off process. It needs to be repeated when the data is substantially changed.
With a true clustered index the clustering property is as far as possible (it can get somewhat fragmented in the presence of random data) maintained during normal operation without the need for a full rebuild every now and then to keep the benefits for new data.
> When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.
This makes that operation very nasty. For a large amount of data you are looking at locking your applications out of the database for some time, and the delay is relative to the total data size in the table being acted upon, not the amount of data that has recently arrived or changed.
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#49Earlier quoted context omitted.
It's pretty unlikely to get a collision[1], and the client should be able to handle the gracefully (regenerate the UUID and retry). [1] https://www.quora.com/Has-there-ever-been-a-UUID-collision
> It's pretty unlikely to get a collision unlikely is not zero, which was why I commented. I'd hate to rely on uniqueness of something that has a chance of not being unique.
GP pointed out the special version of the algorithm that handles collisions gracefully. Note that this algorithm ("open addressing") is so frequently used that you will probably find a variation of it in pretty much any piece of software you have ever used. It's a well-understood method, not only from a practical perspective but also in terms of theory; check out e.g.: https://en.wikipedia.org/wiki/Linear_probing
Re: Showdown: MySQL 8 vs. PostgreSQL 10
#50> UUID as a primary key is a terrible idea, by the way
Unless you use a v1 UUID, i.e. via NEWSEQUENTIALID() in SQL Server. IIRC postgres has an equivelant available as a standard module.