3 ms·
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 c
by kolar 4y ago
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 :)
- fuy 4y agothere's also things like pg_repack which don't lock the tables (but will incur high IO while freeing up the space).
- kolar 4y agoAlso Postgres is too slow for large analytical databases. You need columnar database to make fast queries on >1Tb of data.
- bfelbo 4y agoYou could use TimescaleDB which is a Postgres extension that adds support for columnar tables and time-based chunking. Works brilliantly IMO.
- ithkuil 4y agoDoes anyone have experience with some postgres columnar store extension like https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw ?
- ttfkam 4y agoAWS Redshift works wonderfully in that capacity.
- whoopdeepoo 4y agoMy experience was not enough support for common postgres features
- martintietz 4y agoAgree. Here is a list of the limitations: https://github.com/citusdata/citus/tree/main/src/backend/columnar#limitations https://github.com/citusdata/citus/tree/main/src/backend/col...
- martintietz 4y agoAs 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 https://github.com/citusdata/citus
- ttfkam 4y ago1TB 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.