News for hackers. Stuff that matters.
Re:Postgres 11 – A First Look by deytempo on Saturday September 22, 2018 @12:31AM (#18044455)
What are some reasons I might consider Postgres over MySQL when choosing a DB platform?
Re:Postgres 11 – A First Look by petre on Saturday September 22, 2018 @01:14AM (#18044579)
JSON support, ability to query it, JSONB support, real spatial extensions, timescale extensions, consistent behaviour, online backups. We now have to migrate from MySQL to Postgres because of these features. Instead of MySQL 8 we will be probably usng Pg 11. Now we are using MariaDB.
Re:Postgres 11 – A First Look by evanelias on Saturday September 22, 2018 @12:05PM (#18046521)
> JSON support, ability to query it

MySQL 5.7 has this, and MySQL 8 improves on it further. Note that MariaDB's implementation is substantially different than MySQL's here.

> consistent behaviour

This one's a bit vague :) But generally using strict sql_mode and innodb_strict_mode go a long way towards alleviating this common complaint.

> online backups

Online physical backups can be created with Percona's xtrabackup, which is open source. Or for online logical backups, there's always mysqldump, or third-party concurrent options like mydumper.

> real spatial extensions, timescale extensions

Yeah these ones I definitely see Postgres having a substantial edge, if they're important to your application or workload.

Re:Postgres 11 – A First Look by jpalomaki on Saturday September 22, 2018 @02:50AM (#18044858)
Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).
Re:Postgres 11 – A First Look by pmontra on Saturday September 22, 2018 @03:49AM (#18044993)
Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message.

I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function.

A MySQL DBA solved that problem in that project I worked on years ago without queries. He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. My friend told me he wants to code that query, no triggers, not even an INSERT OR UPDATE from his PHP code. I replied to go on, he'll learn a lot of things and I hoped this will make him pick PostgreSQL next time.

Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.

Re:Postgres 11 – A First Look by masklinn on Saturday September 22, 2018 @10:58AM (#18046219)
> Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.

You can write PG stored procedures in close to a dozen languages between the builtins (pgSQL, Tcl, Perl and Python) and third-party (Java, Ruby, PHP, R, Scheme, JS, …).

The language itself is not why developers don't like stored procedures, it's because stored procedure are an even worse version of image-based languages (like Smalltalk or Self, except they had much better UI, tooling and interaction model): hard to version, hard to update, hard to test, hard to debug, hard to cache, hard to scale, …, and your wide-spread well-supported ancillary tooling goes away pretty much entirely.

Re:Postgres 11 – A First Look by tormeh on Saturday September 22, 2018 @07:49AM (#18045630)
Stored procedures are plaintext code running on production servers and is, in general, never cleanly redeployed. So by default they discourage source control. This is my primary problem with them. Also that (at least MySQL) has shitty error messages when you're trying to build/debug the things.
Re:Postgres 11 – A First Look by ht85 on Saturday September 22, 2018 @05:28PM (#18048105)
Maintaining and migrating stored procedures can be a pain, especially when you're running a single tenant infrastructure, and even more if you don't migrate everyone at the same time.

I've had a lot of success by creating a small build system for the database logic, that can be as simple as running a bunch of SQL files in order.

The most important aspect is for the build to be "pure", rebuilding the whole thing every time instead of incrementally changing the schema.

A practical way to do this is to have all your stored procedures in a separate schema from your data, and starting your build process with `DROP SCHEMA procs CASCADE`.

Re:Postgres 11 – A First Look by Avamander on Saturday September 22, 2018 @06:38AM (#18045423)
I've had to debug a stored procedure, they're horrible when you have to do that. Just a big headache. I literally rewrote every single one of those procedures in the software's primary language, the code can now be easily debugged, logged, replaced and read.
Re:Postgres 11 – A First Look by pmontra on Saturday September 22, 2018 @07:55AM (#18045643)
I'm not a big fan of PL/SQL. Almost nobody is, so your approach is standard.

It's ok if all accesses to the database go through the primary application. Triggers and stored procedures make more sense when a database serves multiple applications possibly in multiple languages. Think of the db as the microservice in front of the data store. Simple stored procedures are no that bad.

Re:Postgres 11 – A First Look by twic on Saturday September 22, 2018 @09:06AM (#18045821)
Given:

    create table conversations (
      message_id integer primary key,
      conversation_id integer,
      timestamp timestamp,
      user_id integer,
      message varchar
    );
Then:

    select distinct on (conversation_id) *
    from conversations
    order by conversation_id, timestamp desc;
Should do it. Although i believe "select distinct on" is a PostgreSQL extension rather than standard SQL.

Now, what i don't know is how this is planned for a large table, or what indices would let you get a sensible plan. Maybe just an index on (conversation_id, timestamp)?

Re:Postgres 11 – A First Look by tfehring on Sunday September 23, 2018 @12:47AM (#18049453)
Extension or not, that's a really useful shortcut. Coming from SQL Server, the best option I know of requires a CTE or subquery:

    WITH CTE_messages AS
    (
        SELECT
             *
            ,row_nbr = ROW_NUMBER() OVER(PARTITION BY conversation_id
                                         ORDER BY timestamp DESC)
        FROM
            conversations
    )
    SELECT
        *
    FROM
        CTE_messages
    WHERE    row_nbr = 1
    ORDER BY
        conversation_id
This does give a performant plan in SQL Server with an index on (conversation_id, timestamp), for what it's worth. I imagine the same is true for this syntax in PostgreSQL. But SELECT DISTINCT sometimes does strange things to execution plans in general, and I wouldn't be surprised if that extends to SELECT DISTINCT ON.
Re:Postgres 11 – A First Look by blattimwind on Saturday September 22, 2018 @07:10AM (#18045512)
> He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database.

Depends on how often that last message query is run. If it's infrequent, the trigger is just write amplification.

Re:Postgres 11 – A First Look by gaius on Saturday September 22, 2018 @03:50AM (#18044994)
With Postgres you don't need MongoDB, InfluxDB, or any other trendy thing, Postgres does it all, and better than all the wannabes.
Re:Postgres 11 – A First Look by sz4kerto on Saturday September 22, 2018 @05:31AM (#18045256)
Is that really true? Postgres is great, but it doesn't cater for _all_ use cases. For example, horizontal scalability is -- understandably for an ACID RDBMS -- not as trivial as it is for some document stores.
Re:Postgres 11 – A First Look by gaius on Saturday September 22, 2018 @06:06AM (#18045342)
Check out what Citus are doing with PG https://www.citusdata.com
Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @07:39AM (#18045605)
That's a fork of PostgreSQL.

So it doesn't help you when it comes to using cloud hosted services e.g. AWS or Azure. It's also a commercial product that isn't exactly cheap ($890/node).

Re:Postgres 11 – A First Look by stareatgoats on Saturday September 22, 2018 @09:35AM (#18045904)
It's not a fork (according to their promotion), it's an 'extension', but not sure I can explain the difference. Their solution looks interesting but pricing wasn't very visible on their website, what you quote is prohibitive for me (where does it say?). But I at least assumed the 'open source' would be free.
Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @10:10AM (#18046021)
Pricing is available here: https://www.citusdata.com/pricing

And the point still stands that this isn't core PostgreSQL. So you can't use managed services like AWS RDS with this. And you're reliant entirely on the vendor for support.

So from the user perspective it is basically a fork.

Re:Postgres 11 – A First Look by manigandham on Saturday September 22, 2018 @08:39PM (#18048753)
PostgreSQL is completely open-source with no backing company so everyone is reliant on some vendor if they want support. It makes no difference that Citus is one of them, with some core PG devs onboard.

Why would you talk about AWS RDS and then skip over Citus Cloud which is a managed service? If you consider this a fork then any plugin for PG is also a fork, which then makes it hard to have a productive discussion.

Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @09:40PM (#18048955)
The reason I am talking about AWS RDS is because most companies aren't multi-cloud. Data needs to be housed and secured within that private VPC. And so Citus Cloud is irrelevant because it is an externally housed product.

If Citus allowed their proprietary product to be run within existing clouds that could be something but it isn't. And again since it is a significant add-on i.e. beyond just a simple plugin that almost demands vendor support it is effectively a fork.

Re:Postgres 11 – A First Look by manigandham on Sunday September 23, 2018 @12:25AM (#18049399)
What does RDS have to do with being multi-cloud? It's a hosted offering which only works in AWS. And Citus Cloud does offer VPC connectivity so you can access it privately. As stated before, the extension is also open-source and you can run it in any cloud or datacenter yourself, for free. Why would you claim that's not allowed?

You seem to be stuck on a strange intrepretation of vendor support when PG has no backing vendor, so you can pick whichever one you want. If you aren't comfortable with Citus and prefer AWS then that's fine, but you make the trade-off in the features as well. Not every managed postgres service chooses to support every PG extension so it's nothing unique in that regard, and is ultimately no different than the choices involved in picking any other database with vendor backing.

You've posted several comments now that seem to show that you do not know much about Citus or are willingly misleading people about it. I'm not sure why but perhaps it would be better if you read up on the details or disclosed your angle more clearly.

Re:Postgres 11 – A First Look by stareatgoats on Saturday September 22, 2018 @10:50AM (#18046183)
OK thanks for this. The product seems promising even if it is not core PG. I'd have to dive into the deep end and test drive to check if the trade-offs would be worthwhile. The 'open source version' seems free, so I guess I could at some point.
Re:Postgres 11 – A First Look by manigandham on Sunday September 23, 2018 @12:19AM (#18049381)
An extension means you're just running normal Postgres and load this on top. All of the normal features of Postgres are available and you can upgrade that as new versions are released without waiting for some other vendor to update their own specialized version of it. And yes, Citus is open-source and free for you to run yourself.
Re:Postgres 11 – A First Look by mslot on Saturday September 22, 2018 @06:17PM (#18048303)
Citus is an open source plug-in that you load into vanilla PostgreSQL, similar to PostGIS and other extensions.
Re:Postgres 11 – A First Look by Avamander on Saturday September 22, 2018 @07:09AM (#18045506)
Could you please elaborate how to use Postgres as a time series database (like InfluxDB)?
Re:Postgres 11 – A First Look by manigandham on Saturday September 22, 2018 @08:46PM (#18048783)
There is absolutely nothing special about "time-series" to be an actual type of database. It's all hype.

Time-series data is data that has a time component which is usually the primary property to query by. Almost every database can handle this, like MongoDB/Redis for lightweight use, Cassandra for write-heavy/global replication, ElasticSearch for raw search-style quering, or an OLAP columnstore (Redshift, MSSQL, Snowflake, Clickhouse) for serious ingest and querying. Monitoring systems like Prometheus and Netdata even have time-series storage built-in because it just isn't that hard.

InfluxDB is only really useful in the context of the integrations that it provides with the common monitoring software stacks. You can easily just create a regular relational table with "time" as a column and get great performance with an index, and then use partitioning to break up the table to get great performance over lots of data.

pg_partman is an extension that makes partitioning automatic. Timescale is an extension that makes time-focused partitioning automatic. Citus is an extension that makes partitioning across multiple nodes automatic. Or use one of the distributed OLAP systems mentioned above.

Re:Postgres 11 – A First Look by scrollaway on Saturday September 22, 2018 @07:48AM (#18045628)
The TimescaleDB plugin is extremely promising: https://www.timescale.com/

Natively without a plugin, it's not possible though.

Re:Postgres 11 – A First Look by StreamBright on Saturday September 22, 2018 @09:19AM (#18045862)
Create a table and have a timestamp for your data? Not sure what you are asking.

On hand we have: 15+ years old mature product that is widely supported and it can be used as a time series database

Other hand: n+1 young database engine with all of the problems that any young data storage engine has (only exception is FoundationDB because those guys actually understood the challenges of writing a reliable data store)

The question is, what can InfluxDB offer over Postgres for storing time series data?

Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @10:01AM (#18045982)
Well InfluxDB actually is a time series database. That's all it does.

The query language is optimised for time based queries. The storage is optimised for time series. It is part of the metrics/monitoring ecosystem so it has integrations with tools like Grafana.

PostgreSQL doesn't have any time series capabilities OOTB. It's just people modelling the concept in a relational fashion. No different to how Excel can be used as a time series database.

And seriously everyone should just get over FoundationDB. There are plenty of people who have invented new databases that got it right e.g. Cassandra, HBase, Redis etc.

Re:Postgres 11 – A First Look by mslot on Saturday September 22, 2018 @06:51PM (#18048413)
PostgreSQL has excellent time series capabilities. It can load millions of rows per second, efficiently scan by time range, build rollup tables, has expressive SQL with excellent support for time (timezones, ranges, timestamps, intervals, conversion, etc.), it can combine multiple indexes to query large volumes of time series data quickly, it can do sampling, and it can expire data efficiently through partitioning. Ok, it's not entirely ootb. To make it work nicely you may need extensions like pg_partman to automate partitioning and Citus to scale out, but once you do you have a time series database that's faster and more powerful than anything else on the market.
Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @09:34PM (#18048937)
Congratulations. You just described the capabilities of ANY SQL database.

But again there are capabilities that exist only in InfluxDB because all it does is time series data. It's not multi-purpose. Which again is why you see it all over the place in the metrics/monitoring ecosystem but you never see PostgreSQL, MySQL etc.

Re:Postgres 11 – A First Look by xdanger on Sunday September 23, 2018 @01:04PM (#18051981)
Actually you can use PostgreSQL in those with timescaledb plugin. ;)
Re:Postgres 11 – A First Look by gaius on Saturday September 22, 2018 @08:24AM (#18045719)
Could you please elaborate how to use Postgres as a time series database (like InfluxDB)?

There's some good discussion of it here https://news.ycombinator.com/item?id=16539317

Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @07:37AM (#18045598)
This is simply untrue.

MongoDB is a much better JSON store. It's ridiculously faster, has much richer update semantics and it's simply not comparable when it comes to the ease of clustering.

As for time series unless I am confused but PostgreSQL doesn't have any OOTB capabilities.

Re:Postgres 11 – A First Look by gaius on Saturday September 22, 2018 @08:28AM (#18045727)
MongoDB is a much better JSON store

That hasn't been true for a long time, if it even ever really was when you consider that the key role of a database is to reliably persist your data https://www.enterprisedb.com/node/3441 (2014)

Re:Postgres 11 – A First Look by threeseed on Saturday September 22, 2018 @08:50AM (#18045788)
Think about this for a second. You just posted a link that is over 4 years old.

It's completely irrelevant. Especially since I am referring to version 3.0+ where they completely replaced their entire engine with WiredTiger. And from that point on the performance for tuple level updates for example is faster than I've seen from ANY database.

And this nonsense about reliable persistence of data was a case of bad defaults many, many years ago. It was also fixed many, many years ago and so these tired memes just make you look bad.

Re:Postgres 11 – A First Look by gaius on Saturday September 22, 2018 @09:16AM (#18045853)
You just posted a link that is over 4 years old.

Yes, to make my point that it hasn't been true "for a long time". Yes Mongo had JSON support before PG did, but PG soon surpassed it.

these tired memes just make you look bad

Nah, they make me look like what I am, an extremely experienced data guy. Maybe Mongo has some use cases in webdev, I wouldn't know.

Re:Postgres 11 – A First Look by sv12l on Saturday September 22, 2018 @01:33AM (#18044644)
If data is central to your business, in all likelihood it will be, and you care.
Re:Postgres 11 – A First Look by calcifer on Saturday September 22, 2018 @02:13AM (#18044760)
This is not a useful or constructive comment with its unstated argument of "only idiots use mysql". It's sarcastic flamewar bait.
Re:Postgres 11 – A First Look by guiriduro on Saturday September 22, 2018 @02:46AM (#18044848)
No, its just the TL;DR version of a longer argument demonstrating MySQL's ability to lose, truncate and misinterpret data and ignore commands and thereby create false expectations (like, your data is validated and safe). PostgreSQL takes far greater care over data.