[kolar]

Be careful when using Postgres as queue with priorites, especially if your messages are large. Removing rows doesn't free up the memory in Postgres and vacuum could remove rows only from the end of a 'page'. As a result of that queue will require enormous amount of space to work. The only way to free up space is to use VACUUM FULL which rewrites whole database and will lock queue for long time. I've had a lot of headaches when we've tried 'use Postgres for everything' on production :)

[kolar]

Also Postgres is too slow for large analytical databases. You need columnar database to make fast queries on >1Tb of data.

[martintietz]

As always: it depends. For some workloads something like Citus [1] might allow you stay within the PostgreSQL ecosystem even when you are trying to do OLAP.

[1] https://github.com/citusdata/citus

[ttfkam]

1TB is peanuts. You can usually get by even with a lot more. Once that's expired though, you can just switch relatively easily to a different flavor of Postgres.

It's why AWS Redshift exists: Postgres with column-oriented storage.

[ithkuil]

Does anyone have experience with some postgres columnar store extension like https://github.com/citusdata/cstore_fdw ?

[whoopdeepoo]

My experience was not enough support for common postgres features

[martintietz]

Agree. Here is a list of the limitations: https://github.com/citusdata/citus/tree/main/src/backend/col...

[ttfkam]

AWS Redshift works wonderfully in that capacity.

[bfelbo]

You could use TimescaleDB which is a Postgres extension that adds support for columnar tables and time-based chunking. Works brilliantly IMO.

[fuy]

there's also things like pg_repack which don't lock the tables (but will incur high IO while freeing up the space).