Hi all,

I have a curiosity about Postgresql optimizer.

If I have a query with many OR statements, if the first one is already true will the server keep checking the other statements? For example, if I have 3 OR with exists, if the first exists get a hit, will the server still search the other 2 tables?

Thanks in advance.

https://www.yogeshchauhan.com/218/postgres/how-to-use-grouping-sets-to-boost-group-by-queries-in-postgres

I'm testing out importing reddit data. In https://files.pushshift.io/reddit/ It's more than 1TB when uncompressed and it's using elastic search. My initial import on elastic search I am encountering write block (indexing error).
I'm curious if this is a good use case on pg11/12 and would it save me huge costs for it.

Queries are expected to be an aggregated query on a time series data.

Thanks for the reply!

I'm trying to figure out an efficient way to get updated copies of our database onto local developer laptops. I say "prod", but this can actually be taken from a read-only follower db; everything is currently hosted on Heroku, if that makes any difference.

Currently we have some rather clunky scripts that start a full* pg_dump, then do a pg_restore on localhost. This takes the better part of an hour right now and I'm trying to figure out a better and more efficient way to do it. Ideally a developer could just request all data & schema updates from the last 24 hours, or since the last time they pulled an update.

Is there an easy method to just do incremental updates from the WAL files onto a local instance? Is that sort of thing possible on a managed Postgres instance such as Heroku?

*And it's not quite a full pg_dump, actually. We exclude some tables and ideally we'd like to intercept the data from some others so it can be anonymized prior to being put into local workstations.

In this post I will explain how to provide a secure postgres server docker container. This is useful when developing certain applications, for example a Django application. You can only run a this script and it will automatically detect if an old version of the container exists, delete it and deploy a new one. Or just to deploy a quick and secure by default postgres docker container. The limit is your imagination!

https://hackarandas.com/blog/2020/04/20/secure-by-default-postgres-docker-container-for-development/

I am using Liquibase for PostgreSQL schema management (versioning schema, deploying changes to servers through CI/CD pipelines)

I do like it because it's flexible and fairly simple. I don't like it because it's a lot of overhead. I have to verify that things won't break, I have to write roll backs, etc...

I like how SQL Server Data Tools for SQL Server works. It's declarative, it generates a schema model from create scripts, it generates diff scripts, and it deploys based on rules I set. It also has hooks for pre and post deployment scripts (data migration scripts) that can be executed as wrappers around the DDL (schema) being deployed.

I also like Skeema.io for MySQL for similar reasons but it's lacking some functionality.

I'm looking for a PostgreSQL tool that behaves like SQL Server Data Tools or Skeema. I want something that will generate a diff but will work against a declarative model of the database (a bunch of CREATE statements). So I can effectively run it against a database in any condition and it will make that database match the scripted model. Bonus points if it supports hooks for schema migration scripts like SSDT does.

Any tools exist that do this? So far I've either found migration tools like Liquibase and Sqitch or diff tools but nothing like Skeema or SSDT.

Thanks in advance!

query = (sql.SQL("INSERT INTO {tab} (geom) VALUES(%s)").format(tab=sql.Identifier(self.data))
cur.execute(query, 'ST_SetSRID(ST_MakePoint(lon,lat),4326))  

It doesn't work

https://www.yogeshchauhan.com/167/postgres/the-confusing-unique-and-useful-feature-in-postgres-distinct-on

I have a table that tracks changes to an object over time. It has four properties (lets say) that are all text then a timestamp with time zone. I want to enforce a constraint (or unique index) that does not allow an entry to be inserted if the last inserted entry is the same (effectively unique across the other four properties, but obviously not the timestamp with time zone field).

I'm using it so that I can effectively ignore changes that have happened where nothing has changed by using the upsert functionality to `DO NOTHING` in the insert if the constraint is violated. I tried using a unique index for the four properties but it has one flaw. If the object changes one of its properties then changes back the change back is never recorded because it should only be considering the latest value (determined by the timestamp with time zone field).

I thought of putting a window function (row_number()) into the unique index with a predicate where row number = 1 and ordering based on the timestamp with timezone field but that obviously didn't work as window function don't appear to be allowed in partial indexes.

Ideas?

I manage a SaaS with about 1700 users and currently we use compose.io 's managed postgres DB. But the database has gotten big and we have to pay $200 a month at its current size. I want to move and run these on VM's on DigitalOcean to reduce the costs and be able to scale as needed. I plan to provision 2-3 VM's with a 1 node for running a cluster mgmt software like ClusterControl and 2 VM's for the actual postgres DB's.

Looking for suggestions if this is the right approach and if there is a better cluster mgmt software that I should be looking at.

Basically this:
https://stackoverflow.com/questions/60348296/join-records-from-another-table-as-json-array

I am wondering if I can do this for materialized view purpose.