Postgres 11 – A First Look
craigkerstiens.com
craigkerstiens.com
This section was really surprising to read! I use the strong_migrations [1] gem to catch "NOT NULL column with a default value", because it's such a common mistake.
I guess I had assumed that this was just something we had to accept, based on decades of architecture decisions, and the way that Postgres was written. It never occurred to me that it was actually possible to change the default behavior of Postgres and fix the underlying issue.
[1] https://github.com/ankane/strong_migrations#adding-a-column-...
While this is good to help out beginners a bit, people should really learn Ctrl + D. It works practically everywhere and saves tons of time guessing/remembering and even typing the correct incantation for the program you want to exit.
Yes, it's great to learn Ctrl + D.
But losing out on beginners that aren't familiar in favor of things that are naturally intuitive is a great way to make those who are new to programming and especially databases feel like they're dumb or not capable of it. I still recall the first time I was in vi and I couldn't figure out how to get out of the damn thing, and don't get me started on arrow keys. There are a lot of ways to help others learn things like Ctrl + D, an even better experience might be a 1 second delay with a message informing them of other ways.
We could bikeshed all day on which is the more pristine way to exit a program, paint the shed whatever color you want, being intuitive for beginners is never a bad thing.
Let's look at a typical novice's session with the mighty ed:
golem$ ed
?
help
?
?
?
quit
?
exit
?
bye
?
hello?
?
eat flaming death
?
^C
?
^C
?
^D
?Note the consistent user interface and error reportage. Ed is generous enough to flag errors, yet prudent enough not to overwhelm the novice with verbosity.
Yet for me, ctrl-d is my usual first way of trying to exit any shell or cli utility.
Whether it's natural or not, I couldn't say. Arguably the nipple is the first and only intuitive interface, everything after that is learned. After many years on my/maria I find the backslash shortcuts very counterintuitive to 'show databases | tables' etc.
While I think it's great to have both options present, I don't think the demographic of people who work out they want an rdbms, evaluate and select postgresql, work out how to install server and client components, su to the postgres user, and are then likely to abandon in frustration as they can't determine how to exit the cli, is negligible.
Today though many more are coming to development without an academic or linux background. If you install Postgres via Postgres.app there is a chance you're not at all familiar with a commandline. What is intuitive... yes it's relative to your background. I very much hope that the number quit or exit is intuitive for, those that don't come from a linux or academic background, grows of time. This does become a more valuable tool the more beginners there are, and personally from working with a lot of beginners already suspect it's more valuable than many realize.
That's pretty much the summary of my post.
> If you install Postgres via Postgres.app ...
I had to go lookup what postgres.app actually is -- evidently it's an easy way for Mac users to install postgres.
I don't have a Mac -- which is also true for most people -- so I can't speak to the average mac user's experience or expectations.
As I have (had to repeatedly) say a few times now, I think it's great that multiple options to end the program are available.
I'd imagine for Mac users the 'most intuitive' thing would be to find some magic symbol on their keyboard and combine it with the letter Q.
Not complaining about its presence, as per my comment :
"While I think it's great to have both options present,..."
Just observing that claims of 'more intuitive' interfaces are very subjective, and that it was unlikely to be a deal breaker for a new user.
I can repeat (again) that I think it's great both options are there.
Given pretty much all the other 'standard' postgres commands are \[a-z] and sometimes \[a-z][a-z] it sounds like more a complaint about \q
Surely it is when you detect the angry mashing of keys?
There should really be a function for that in the standard library. Distinguishing such inputs from the actions of cats would be the difficult bit.
I disagree with you here, Craig. In my experience I reach for Ctrl + D, Ctrl + C in quick succession to see what succeeds. I've learnt those and it works for most programs that don't trap exits (again, why would you trap exit?). It has saved me from learning how to exit interactive terminals for python, ruby, node, PostgreSQL (psql) without learning their specific instruction. I like that and I'd prefer to have one way of exiting programs that works on all programs on the system than learn what's any programs preferred command.
flush buffers, gracefully close sockets, print the final \LF to the stdout.
I bet you love Vim ;-P
> things that are naturally intuitive
Pick one
[1] The 0.01% of the time was when I was testing out using Windows for software dev. I don't remember but I don't think Ctrl + D works there.
I think it worked in powershell however, can't test right now though
Also: chromeos is Linux. It's a safe bet they'll support standard signals
Not sure about for PostgreSQL in cmd off the top of my head (posting from my phone). I suspect because I got down voted that it may not work there.
It seems like a reasonable set of conclusions to draw, which makes ctrl-d indeed the most obvious thing to try.
Also ‘exit’ works in bash.
1. Print "You can also quit using Ctrl + D"
2. Quit.
But I also believe that only doing step 2 is better than neither. psql should prioritize psql's usability over educating users on Unix standards.
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> quit
Use quit() or Ctrl-Z plus Return to exit
>>> exit
Use exit() or Ctrl-Z plus Return to exit >>> type(quit)
<class '_sitebuiltins.Quitter'>
>>> repr(quit)
'Use quit() or Ctrl-Z plus Return to exit'
class Quitter(object):
def __init__(self, name, eof):
self.name = name
self.eof = eof
def __repr__(self):
return 'Use %s() or %s to exit' % (self.name, self.eof)
def __call__(self, code=None):
# Shells like IDLE catch the SystemExit, but listen when their
# stdin wrapper is closed.
try:
sys.stdin.close()
except:
pass
raise SystemExit(code)
So there's no magic. There's only Python.
;)
Just like ctrl-c generates INT and ctrl-z generates STP. This is worth learning.
It's very different. Ctrl-D does not generate signals. When written into the tty master, it forces the kernel to answer any pending read() syscalls on the tty slave, even if it means returning 0 bytes.
Programs then apply the convention of treating a read() that returns 0 bytes as meaning EOF (because that's how it works with all other file types that are not tty slaves).
This isn’t bike shedding at all as there’s a specific character code to indicate you’ve finished with a program and it’s standardized on all sane pieces of software. Heck it even works with things that aren’t specifically programmed to “exit” as they’ll simply end the stream on receipt.
That's the point about why it's worth it to learn: It's universal to all terminal programs, even if the author of the program didn't know about it.
This includes nodejs's repl, irb, ed, gdb, tftp, virsh, gpg --card-edit, and others.
It also works for programs that read stdin as if it's a file if you're using a modern readline shell to run them (e.g. cat, tee, etc)
I used to be able to Ctrl+C knowing that it would interrupt any currently running query but never lose my session.
If I want to quit the PostgreSQL shell, I'd Ctrl+D like any other shell.
What will I do now?
Spanner and AWS Aurora base off of more mysql than postregsql from what I can tell. Why?
Notice that parallel to MySQL's rise a particular loosely-typed, never-except, often-wrong programming language also became popular. To this day I feed my family with that language.
The typical coder (not that I do not say "developer") who codes in PHP does not care about correctness. He does not understand why monetary values cannot be stored in floats, he does not know what bitwise manipulation is, he does not know the difference between character encodings. What does he care what MySQL does, he knows that he can put strings in if he calls them VARCHAR, and he can pull the right one out with a WHERE. He cares not enough to check that user bios fit in his VARCHAR, and when he learns to JOIN he does not understand which constraints to put in the ON clause.
I do have nicer words for the L and A in the stack.
mSQL was a free/low cost SQL database in the early 90s. Originally it was an SQL translator built on top of Postgres (that used POSTQUEL). That was too slow (because Postgres had higher system requirements), so a new lightweight engine was developed for it.. and that's what later became MySQL. The point was a lightweight SQL database that ran well on cheap early-90s computers.
The compromises to make mSQL fast were inherited by MySQL, and they can't be easily changed without breaking the ecosystem that was developed around mSQL/MySQL.
Postgres on the other hand, was a university project in the 80s and 90s being developed on mainframes. It was always a slow moving, cautious project. Yes, it didn't make the compromises mSQL made, because it didn't have to. It used more memory, more CPU, but was more correct/safe than MySQL.
That was the same reason it lost in the 90s. mSQL was out being used by websites and other projects on cheap computers. Postgres was mostly waiting for computer tech to get fast enough so they wouldn't need to compromise their code too much to take it off the mainframe an on to cheaper computers. Postgres only adopted SQL in response to mSQL's popularity. Early Postgres was also harder to setup, another point that wasn't addressed until after mSQL took off.
MySQL was popular because of the compromises they made. If mSQL just kept Postgres as the engine, MySQL never would have existed. And if they instead waited (even a couple of years), and did things correctly, they wouldn't have beat Postgres -- which had a decade lead in development and was more advanced than mSQL/MySQL.
This doesn't sound accurate to me. Which compromises are you referring to?
Historically, MySQL's major source of criticism related to leniency of type safety / automatic data conversion -- which is unrelated to performance. It's also essentially a solved problem with the advent of strict sql_mode. MySQL made this the default in 2015, but it has been available (and recommended as a best practice) since 2004.
Performance in MySQL, as a general topic, greatly depends on the storage engine. Relative to Postgres, MySQL's pluggable storage engine API is both a blessing and a curse -- it permits use of alternative engines that perform significantly better for specific workloads, at the cost of substantial administrative complexity.
Performance-wise, Postgres generally has the lead for things like number of supported index types, join strategies, query planning for very large queries (and OLAP workloads in general). These may or may not matter for you, depending on your workload.
MyISAM does offer amazing write performance, at the cost of not being crash-safe. But given its reliance on the fs cache, it would be unusual to describe it as "in-memory storage". Anyway, the GP was talking about compromises that "can't be easily changed without breaking the ecosystem", which doesn't describe MyISAM either -- MyISAM is basically deprecated in modern MySQL deployments.
What, then what did it use before?
This was something that was popular back when you used tape to store data. A record contained navigational references that told you where the next record was, allowing you to fast forward the tape to that position without reading everything in between.
The 2nd was POSTQUEL, which was a QUEL language: https://en.wikipedia.org/wiki/QUEL_query_languages POSTQUEL was the preferred and recommended way of querying postgres.
These were both on their way out even in the early years of Postgres (mid-80s). Navigational databases are from the 60s. SQL was invented at IBM in the early 70s, and adopted by Oracle and DB2 in late-70s, and by the mid-80s they had gained significant market share and most databases that used QUEL had moved to SQL around that time.
Specifically, POSTQUEL was not really obsolete, it's just that the market had picked SQL by then. Compare the fates of git versus Mercurial. Mercurial is not obsolete, but git is the clear popularity winner.
Second, I don't recall at all a navigational interface to postgres and the idea that this was a primary access method in postgres is quite surprising. Do you have a reference for this? I'd be very curious to read it.
POSTQUEL was definitely the primary interface to postgres. The navigational interface was an early Postgres feature. See page 3 and page 10 ("fast path") of Stonebraker's 1990 paper on the implementation of postgres: http://db.cs.berkeley.edu/papers/ERL-M90-34.pdf
Thanks for the link. Fast path was mostly about the function manager and only incidentally about data access. As in, "if you call all these functions that are part of the infrastructure, you can get at data". But that's not really a navigational interface (get first of set), more a side effect of exposing the internals via the function manager.
I started at Sybase in 1988, there were a lot Britton Lee alums, who had also worked on the ingres project as well. I ran in the the rest at Illustra in the 90s.
One also has to remember that long ago there was no serverless, no VPS/cloud/ec2/linode instances, no heroku/beanstalk/appengine, no docker/vagrant - to get going fast you had $x/mo shared hosting and [W|X]AMP for development, and this horrible thing called FTP. The only reasonable way to host a website cheaply was on some terrible shared hosting server which only [reasonably] supported php and mysql (okay I lie there was a cgi-bins and mod_perl) otherwise you'd be looking at dedicated servers.
I think it's safe to say we've all seen good code a crap code -- regardless of language.
It's not the wand, it's the magician.
(nb: I've been building software for 20+ years, I use PHP (among others) and knew JOIN and types in PG before I ever saw PHP, I cannot be the only one)
PHP and JavaScript both democratized programming, as BASIC did years before, but that also meant that more tutorials and howtos for PHP were written by people relatively new to programming compared to say howtos on Haskell or C.
When I work with Python, Java, C++, bash, SQL, or Javascript in a team I feel mediocre at best. I can get around, but I know that I'm easily outclassed. Contrast with PHP, where I'm almost always the best dev in the room. True that I have much more experience with PHP than in the other technologies that I've mentioned, but PHP has the dual curse of having a very low bar to entry, and seems-to-work enough that most "PHP coders" never feel the need to progress beyond the most basic of understanding.
When I meet new devs I deliberately try to postpone mention of PHP as long as I can, to avoid attaching myself to the tainted stigma that PHP has acquired.
It's not just the data type that it's stored in, if you convert it to that at any point, you lose the exact precision and introduce those errors. It can add up to some serious differences over a lot of values.
I did some analysis on my database to see if I had stored any of those numbers is floating points, what would the amount difference be in total, and what would be the absolute difference for any single account. it was awhile ago, but I believe that the absolute difference per client was around $20 or $30, but the absolute difference per account within that client was as high as $100. Obviously that's pretty damn unacceptable.
Because of those two advantages (that really only lasted a couple of years), a small ecosystem developed around it. This was the database you used to develop interactive websites in the early/mid-90s.
Then MySQL came. MySQL extended mSQL, and kept compatibility with it's ecosystem... and people switched from mSQL to MySQL.
Postgres also existed in the 80s and 90s, but it used POSTQUEL, was harder to setup, and had higher system requirements. Not a lot, but enough to discourage use on low end computers. (Remember, Postgres at the time was being developed on university servers, where they had plenty of system resources.)
They did address these points in postgre95 (in fact, Postgres adopted SQL as a response to mSQL popularity), but by then it was too late. mSQL and MySQL already had an ecosystem.. and in that brief moment, they lost the race.
The idea that it was slower stuck around for many years, even after the improvements in computers, and (less so) optimizations in Postgres made it no longer true.
Postgres didn't start being considered again until Go, RoR, et al started to become popular.. displacing the PHP ecosystem. And the biggest reason you see MySQL around so much today is that there's about 25 years of legacy code built around it.
Edit: If you're curious what POSTQUEL was like: https://en.wikipedia.org/wiki/QUEL_query_languages
Both for beginners where it was packaged with PHP and available with basically every webhost, and for advanced users that took advantage of the simple and reliable replication. There were a lot of problems with it, but the novice users didnt run into them and the advanced users knew how to deal with it. These decades of mainstream use carry a lot of momentum and many large companies continue to use it because they've always used it (like Youtube for example).
PostgreSQL only recently became much more usable after v9.5 and is now being recognized and growing in popularity.
I think I first started using it at v6 or 7. I liked it could I could run it from a .bat file and spin it up for tests without installing it.
It always worked well, but there are many newer developments around operations and scaling that finally made it much easier to run. Even now there are some limitations with logical replication and horizontal scaling but the main system can easily be installed and running in a few commands to serve most users just fine.
It should be noted that the rise of Docker containers and K8S has also helped greatly in letting users run all kinds of software with minimal work.
Some of the MySQL backup/restore stuff is a lot more straightforward (innobackup vs WAL shipping).
MySQL replication with binlog_pos was a total crapshoot. The reason innobackupex exists (and anything from Percona, to be honest) is because MySQL's default options were so unworkable. The Postgres equivalent is probably something like barman.
MySQL replication was workable before GTID. GTID simply made things a lot easier. GTID was hard to work into existing DB's at places like Facebook, but it we managed to get things going without a DBA in several multi-TB databases (sans downtime).
GTID is over 4 years old at this point. That's not relatively new in the tech world. Especially with the pace of things like Kubernetes and containers.
There are 3rd party additions to Postgres, too. pg_rewind was written by eBay (?) to address the obvious shortcomings of repointing primaries and replicas.
To be fair, PostgreSQL was noticed way before, Estonia's e-nation website was built on it (even the logic!) and I can't honestly remember when that thing has been down. The UI would need a bit of refreshing but I have the feeling if they're going to update it they're going to replace it all with something shittier.
Early on in my career, the biggest selling point for Mysql was the excellent web admin tool "phpMyAdmin" - it really helped get applications off the ground, since the core of most modern systems is the data model. Users could modify data, without your needing to create a UI for that use case.
It conveniently used the same stack as the rest of the software, but I remember spinning up a phpMyAdmin instance even after moving away from PHP as an application language.
Postgres still doesn't really have anything like that! The closest that comes to mind is Django's Admin tool.
Basically a port of phpMyAdmin to postgres. I used it for several years around ~2003-5.. it's nearly identical.
That said, and with a disclaimer that my own bias leans the other way (as I have a lot more MySQL experience than Postgres experience) although I try to be impartial about these things:
* Spanner: I've heard this second-hand and might be incorrect, but as I understand it, Google's original internal MySQL team was shuttered in ~2009 and some of those folks may have transferred over to the Spanner team. I wouldn't say that Spanner is particularly based off of MySQL anyway though, but I don't have enough familiarity to say that conclusively.
* Aurora: AWS now offers a Postgres-based Aurora as well. As for why the built the MySQL one first, I'd assume that was likely a business decision based on mysql-vs-postgres RDS usage at the time.
* Those two aside, there are definitely major products based around Postgres. AWS Redshift is one example. Or look at CockroachDB, which chose wire-protocol compatibility with Postgres. (There are some examples the other way too; e.g. TiDB, which chose wire-protocol compatibility with MySQL).
* Regarding why MySQL has historically been popular, there are a lot of factors. I'd say the biggest one for large users has been replication; some of my older HN comments delve into that more, https://news.ycombinator.com/item?id=16880663 for one example. For smaller users, ease-of-use has been important. The other discussions in this thread hint at this, especially around silly simple things like the "exit" command.
It also used to be more scalable, because PG's replication story used to be poor. This is why Google and (I assume) Amazon used MySQL internally. This is all a long time ago, but I suspect this is the reason more people are familiar with MySQL than Postgres at these companies. Keep in mind that Spanner was developed and used internally at Google long before it was publicly announced.
Amazon actually uses a lot of Oracle with some scattering of MySQL.
But maybe at their scale even Oracle can be made tolerable.
That, and they have some serious leverage in negotiations.
(I used to deal with Oracle DBs a lot a few years ago but thankfully not anymore.)
Because MySQL for some reasons is more accessible[1] to beginners, juniors, average developers, and other people who may prefer a lower barrier of entry over correctness/safety (e.g. full-stack devs, managers, people who don't have much time); who form the bulk of the community combined.
It's the same for languages, frameworks, and other tech; as several people noted already.
[1] Or maybe "was"; in which case the answer becomes "historical superior accessibility".
Once the herds picked MySQL (and php) the economies of scale started, and advantages grew. Meanwhile, the proper and safe competitors were generally disgusted (drama word but not entirely an exaggeration) but who cares about them, maybe even a weird positive to the new breed of web devs who were inventing the future.
My preference is MySQL purely because the syntax is easier, or at least consistent with what I learned at uni in the late ‘90s.
I also like MySQL workbench, although I’m sure there is an equivalent for PG I haven’t needed to look for one in recent years.
If PG allowed me to use the same syntax as MySQL I’d probably switch, purely because experts and those with more experience than I generally prefer PG for a whole lot of reasons.
EDIT: I should have googled first before replying. Looks like the syntax is near enough to identical for my simple needs and pgadmin will do what I need. I’m not sure why I had to learn some other weird syntax in 2012 for postgres, perhaps it was just shortcuts. Either way I’m going to seriously consider migrating now. :)
Also, Postgres' documentation is really fantastic. I bought a book on Postgres when I switched, but really I could have just stuck to the online manual and saved the thirty bucks.
I like many things about MySql [as you imply, syntax a bit more verbose but easier to remember - e.g. "SHOW TABLES" vs "\d" ; I also like the nonstandard behavior of MySql 'GROUP BY' as it makes good sense to me ]-- but overall I'm really enjoying the flexibility of Postgres. PGadmin4, on the other hand, leaves much to be desired -- I generally stick to psql.
It's a shame, psql is awesome once you know enough to use it, but GUIs is how people start using RDMSes, and it's about the only thing that MySQL is clearly superior to PostgreSQL.
PostgreSQL's approach isn't the most intuitive but it's well documented and very powerful. A few aliases could probably help, it's good they've added `quit`/,`exit` this time around.
postgresql still seems to have 2 ways, but the majority of tutorials will demonstrate the "become the postgres user and add unix-level accounts". Things like having a unix-level "adduser" program that manages pg stuff is... odd to me, because it's not 'self-contained' in the app. It's more system-level admin stuff I need to do or be aware of.
Whenever I bring this up, I get push back that I'm doing it wrong, and that of course you can just create user accounts from within pg itself via psql, and ... all other sort of attendant feedback. This approach had not seemed to be the norm/default approach, nor the one that was in multiple postgres books I had 10 years ago.
I use pg on some projects, but am no expert in it.
I have a lot of respect for the PostgreSQL team. But I think they don't care about the second part, or the constraints of the first part make it impossible. A simple example, the performance of a simple un-indexed DISTINCT on a single string column on a single table, is still quite bad for millions of rows.
Also to note, PostgreSQL has too many features for my taste. I get it's the selling point. But that usually correlates with poor performance and code complexity, too.
Of course, there's a problem of all modern fast database engines being proprietary. But by not addressing the performance problem, perhaps with an smaller engine lighter in features, open source is left biting the dust. And that's not good for the industry.
Why is this surprising?
[1]: https://www.phoronix.com/scan.php?page=news_item&px=PostgreS...
[1] https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items
So after initialising a new partition, I also create a unique index attached to that partition directly instead of the parent table.
I found the migration from 9.x to 10.x deeply painful, I think the one-time cost of the dump-restore cycle hurt me far more than I expected. I very much hope the transition to 11 can be done more expeditiously.
The version convention has changed with v10.0 so it should be trivial to change from 10.x to 11.x . In your position I'd be updating 9.4.x -> 9.5.x -> 9.6.x -> 10.x
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.
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.
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.
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`.
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.
create table conversations (
message_id integer primary key,
conversation_id integer,
timestamp timestamp,
user_id integer,
message varchar
);
select distinct on (conversation_id) *
from conversations
order by conversation_id, timestamp desc;
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)?
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
Depends on how often that last message query is run. If it's infrequent, the trigger is just write amplification.
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).
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.
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.
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.
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.
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.
Natively without a plugin, it's not possible though.
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?
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.
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.
There's some good discussion of it here https://news.ycombinator.com/item?id=16539317
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.
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)
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.
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.
There is an easy level "parallel safe" that would allow scans of different cstore partitions in a parallel query, and a harder-to-code "parallel aware" level that would allow parallel scans on one individual cstore_fdw relation. AFAIK no FDW has attempted parallel scans yet, but cstore looks like it may be an ideal case to be first?