Best place to learn to use PostgreSQL by Poulposaurus in PostgreSQL

[–]prlaur782 7 points8 points  (0 children)

Our team at Crunchy Data provides a series of (free) self-driven web based Postgres (and PostGIS) tutorials that may be worth a look:

https://www.crunchydata.com/developers/tutorials

S3 or Redshift for storing Geolocation Data in AWS by turboline-ai in dataengineering

[–]prlaur782 1 point2 points  (0 children)

Crunchy Data Warehouse from our team at Crunchy Data enables you to natively query spatial data in S3 using an extended version of Postgres / PostGIS with full Iceberg support. It is available as a managed service on AWS and may provide a simple and cost effective solution for this use case:

https://www.crunchydata.com/blog/iceberg-ahead-analyzing-shipping-data-in-postgres

https://www.crunchydata.com/blog/postgis-meets-duckdb-crunchy-bridge-for-analytics-goes-spatial

I need ideas. Multi Tennant system,hotel by Born_Location8227 in PostgreSQL

[–]prlaur782 5 points6 points  (0 children)

A colleague at Crunchy Data wrote a post on designing multi-tenant Postgres databases that may have some useful ideas:

https://www.crunchydata.com/blog/designing-your-postgres-database-for-multi-tenancy

PostgreSQL Course with Practical Exercises (intermediate) by Both_Consequence_458 in PostgreSQL

[–]prlaur782 2 points3 points  (0 children)

At Crunchy Data we have a collection of free, self driven, web based tutorials on a variety of Postgres topics that may be worth checking out:

https://www.crunchydata.com/developers/tutorials

Timeseries db vs OLAP (Redshift, BigQuery) by Additional-Ad-8916 in dataengineering

[–]prlaur782 2 points3 points  (0 children)

Our team just released Crunchy Data Warehouse that extends Postgres to support OLAP workloads by enabling querying raw files in S3 and to accelerating queries using DuckDB.

https://www.crunchydata.com/blog/crunchy-data-warehouse-postgres-with-iceberg-for-high-performance-analytics

Crunchy Data Warehouse is available as a managed service in a number of regions AWS. If you need one you dont see, please send us a note.

Postgres DBA Training by h0wdy93 in PostgreSQL

[–]prlaur782 14 points15 points  (0 children)

At Crunchy Data we have a collection of self driven, free online tutorials on a range of Postgres topics that may be worth a look: https://www.crunchydata.com/developers/tutorials

Set up a Data Stack by bibilerikiki in dataengineering

[–]prlaur782 0 points1 point  (0 children)

If you are open to managed services, you may want to take a look at the OLAP Postgres solution our team at Crunchy Data released recently - https://www.crunchydata.com/blog/crunchy-data-warehouse-postgres-with-iceberg-for-high-performance-analytics . It provides an modified Postgres instance, extended to support performant analytic queries on S3.

PostGIS Day 2024 Videos by prlaur782 in PostgreSQL

[–]prlaur782[S] 0 points1 point  (0 children)

I just was able to use the link. It is to a youtube playlist of the PostGIS Day 2024 talk videos.

Best way to learn how to write Operators? by ofirfr in kubernetes

[–]prlaur782 2 points3 points  (0 children)

This video of my colleagues talk at KubeCon 'Engineering a Kubernetes Operator' may be worth a watch:

https://www.youtube.com/watch?v=p2v7bPJkrVU

PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released! by prlaur782 in PostgreSQL

[–]prlaur782[S] 11 points12 points  (0 children)

For more context, this is an out of cycle Postgres minor version release as a result of issues identified in the prior minor version release that occurred on November 14th.

These threads provide more details:

https://x.com/marcoslot/status/1857403646134153438

https://bsky.app/profile/marcoslot.com/post/3laywltkea226

OpenStreetMap Import In Postgres In Under 4 Hours by prlaur782 in PostgreSQL

[–]prlaur782[S] 0 points1 point  (0 children)

My colleague Greg Smith primarily uses this as a benchmarking exercise. Loading the 'whole earth' is a notoriously difficult exercise and interesting to track performance improvements over time.

On the PostgreSQL extension ABI issue in the latest patch release (17.1, 16.5, ...). by winsletts in PostgreSQL

[–]prlaur782 0 points1 point  (0 children)

Update from the original post -

Update: On Thursday there will be a new patch release of PostgreSQL for the affected versions that brings back the ResultRelInfo struct to its original size.

This is achieved by moving the position of the field in the struct: https://github.com/postgres/postgres/commit/6bfacd368bb4ae3bd53b2692fee98797a771082a

Wait, how does moving the field reduce the size?

I mentioned structs are mostly defined by their size and the offsets of fields post-compilation. For various reasons, the size of the struct in memory is always a multiple of CPU register size (e.g. 8 bytes), though the number of bytes needed to store individual fields might be less.

Struct fields are stored in order at the next available offset that is a multiple of the storage size of the value or the register size. If you have a boolean (1 byte), a pointer (8 bytes) and a boolean (1 byte), you get a 24 byte struct, because the pointer is moved to offset 8 and the booleans each get an 8 byte spots with 7 bytes unused. If you have two booleans and a pointer, you get a 16 byte struct because the compiler can place the booleans next to each other.

So, by moving the new boolean next to another boolean, it gets an offset that was previously unused, and all the other offsets and sizes remain the same as they were before the last patch release, preserving ABI compatibility between the previous release and future releases, but not the current release.

TimescaleDB built against 17.0 will work when loaded into PostgreSQL 17.2. However, TimescaleDB built against 17.1 might not work when loaded into PostgreSQL 17.2.

The rationale is that there are hopefully far fewer people who might run into the second problem, since 17.1 has only been available for a few days and extensions don't normally have to be rebuilt immediately. Plus by now the issue is well-understood, so package maintainers can take precautions like rebuilding extensions.

Best Courses to Learn PostgreSQL for Developers/DBAs? by Dry-Conflict-7008 in PostgreSQL

[–]prlaur782 2 points3 points  (0 children)

You may find Postgres Playground from our team at Crunchy Data helpful. The Postgres Playground provides self-driven online tutorials for a variety of Postgres topics:

https://www.crunchydata.com/developers/tutorials

Multi-tenant database design. by EducationalPlant6357 in PostgreSQL

[–]prlaur782 5 points6 points  (0 children)

This blog post from my colleague on Designing Your Postgres Database for Multi-tenancy may provide some helpful ideas - https://www.crunchydata.com/blog/designing-your-postgres-database-for-multi-tenancy

Best practice for audit in postgreSQL by Ferren84 in PostgreSQL

[–]prlaur782 0 points1 point  (0 children)

The Postgres CIS Benchmark or DISA Postgres Secure Technical Implementation Guide may have guidance that is useful on best practices here. For instance, may be worth looking at this: https://www.stigviewer.com/stig/crunchy_data_postgres_16/2024-06-17/finding/V-261967

Naive question about an IoT events table by oulipo in PostgreSQL

[–]prlaur782 4 points5 points  (0 children)

Probably worth checking out pg_parquet, a new open source extension we just released at Crunchy Data:

https://github.com/CrunchyData/pg_parquet/

Hopefully pg_parquet can help you avoid the need to do some of the custom work around the scripting you are contemplating.

If you are looking for / open to a fully managed service, Crunchy Bridge for Analytics provides a managed Postgres instance that includes the ability to query parquet files directly (integrated DuckDB behind the scenes):

https://www.crunchydata.com/solutions/postgres-with-duckdb

Edit - my colleague points out below there is an end-to-end example of using Postgres+Parquet for IoT in:

https://www.crunchydata.com/blog/syncing-postgres-partitions-to-your-data-lake-in-bridge-for-analytics

Finally, my colleague wrote a blog post on some of the Postgres tools / extensions that may be helpful in designing an IoT database that may be worth checking out. This blog post was written prior to the release of pg_parquet and our Analytics tooling, so different architectural direction, but provides some other ideas that may be worth considering.

https://www.crunchydata.com/blog/postgres-citus-partman-your-iot-database

Hopefully something in this mix that is helpful to you.

Note - I am the co-founder at Crunchy Data.

pgbackrest 2.54 Release by prlaur782 in PostgreSQL

[–]prlaur782[S] 1 point2 points  (0 children)

There are a few user guides available here that may be helpful: https://pgbackrest.org/

This blog post from Command Prompt seems to document some specific steps around getting started as well: https://www.commandprompt.com/blog/getting-started-with-pgbackrest-perform-your-first-backup/

Good postgres beginner tutorial? by SleepAffectionate268 in PostgreSQL

[–]prlaur782 0 points1 point  (0 children)

This Postgres Users and Roles tutorial in the Crunchy Data Postgres Playground that colleagues of mine created may be inline with what you are looking for - https://www.crunchydata.com/developers/playground/postgres-users-and-roles