PostgreSQL Icon

PostgreSQL

PostgreSQL is an open source database system.
24 Stories
All Topics

PostgreSQL pg-structure.com

Extract the structure of a Postgres database into JavaScript

When your database is the source of truth, it’s often useful to inspect that truth and reuse it elsewhere in your application. import pgStructure from "pg-structure"; async function demo() { const db = await pgStructure({ database: "db", user: "u", password: "pass" }, { includeSchemas: ["public"] }); const table = db.get("contact"); const columnNames = table.columns.map(c => c.name); const columnTypeName = table.columns.get("options").type.name; const indexColumnNames = table.indexes.get("ix_mail").columns; const relatedTables = table.hasManyTables; }

read more

PostgreSQL layerci.com

Postgres is a great pub/sub & job server

We all know Postgres is a great relational database (you do know that, don’t you?). When it comes time for a pub/sub solution, however, we often reach for Kafka, Redis, or RabbitMQ. But did you know that Postgres is pretty well suited as a persistent pub/sub server as well? There are very few use cases where you’d need a dedicated pub/sub server like Kafka. Postgres can easily handle 10,000 insertions per second, and it can be tuned to even higher numbers. It’s rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes. Check the linked article for how they use Postgres in this fashion and a nice list of other benefits. For my money, the fact that I’m not adding another moving part to my infrastructure is reason enough to start with Postgres and go from there.

read more

Derek Sivers sivers.org

PostgreSQL self-contained stored procedures example

Based on Derek’s now page he has ended his 7 year sabbatical and he’s taking Seth Godin’s advice to publish something every day. What Derek shared here is part of that commitment… This week, I wrote a shopping cart to sell my books directly from my own site. So I took a couple extra hours today to put my code into public view, so anyone can play around with it. It’s a working self-contained shopping cart / store. It’s a very concrete example of using stored procedures to keep all the data logic together in one place. You can use it from JavaScript, Python, Ruby, or any language you want, since all the functionality is in the database itself. It works.

read more

PostgreSQL github.com

A webdev platform built entirely in PostgreSQL

Aquameta is a web-based IDE for full-stack web development. Developers can manage HTML, CSS, Javascript, database schema, views, templates, routes, tests and documentation, and do version control, branching, pushing, pulling, user management and permissions, all from a single web-based IDE. In theory. And mostly in practice. Under the hood, Aquameta is a “datafied” web stack, built entirely in PostgreSQL. The structure of a typical web framework is represented in Aquameta as big database schema with 6 postgreSQL schemas containing ~60 tables, ~50 views and ~90 stored procedures. Apps developed in Aquameta are represented entirely as relational data, and all development, at an atomic level, is some form of data manipulation. Also in theory. And mostly in practice. This is super experimental, but what a cool idea. Eric Hanson’s been at it it off-and-on for 20 years now…

read more

PostgreSQL blog.couchbase.com

Comparing Postgres JSONB with NoSQL

The Couchbase team did a great job putting together this fairly-reasoned analysis. It gives side-by-side comparisons of Postgres’ JSON query syntax and SQL++/N1QL, which is the query language used in Couchbase. It touches on indexes, performance, ergonomics, and finally where each is a good fit. I’ve personally found that Postgres’ JSON data types provide just enough document-orientation that I can sprinkle in where it makes sense in our data models. But, as with all things in developer-land, YMMV!

read more

Michael Malis YouTube

Writing an interpreter in SQL for fun and no profit!

Michael Malis at !!Con 2019: Writing SQL can be hard. SQL code is a bizarre combination of yelling and relational algebra. How can we make writing SQL easier? By embedding our own programming language in our SQL queries of course! In this talk, we’ll take a look at how you use a combination of various Postgres features to build a programming language out of SQL.

read more

Parker Selbert github.com

Reliable and observable job processing in Elixir via Postgres

Oban’s primary goals are reliability, consistency and observability. It is fundamentally different from other background job processing tools because it retains job data for historic metrics and inspection. You can leave your application running indefinitely without worrying about jobs being lost or orphaned due to crashes.

read more

PostgreSQL rob.conery.io

PostgreSQL tools for the visually inclined

jumping from SQL Server to PostgreSQL is much more than changing a tool. PostgreSQL was built on Unix, with Unix in mind as the platform of choice, and typically runs best when it’s sitting on some type of Unix box. The Unix world has a pretty specific idiom for how to go about things and it certainly isn’t visual! Rob Conery with a deep dive into psql and what makes it awesome.

read more

Amazon Web Services enterprisedb.com

Is Amazon’s new MongoDB-compatible DBMS really PostgreSQL under the covers?

This is a nice rundown of the technical clues indicating that DocumentDB might be powered by Postgres. PostgreSQL isn’t the only DBMS that scales writes vertically and reads horizontally via replication, but when you add this all up, especially some of the specific limitations, I think it makes a pretty compelling argument that PostgreSQL is the engine powering AWS DocumentDB.

read more

Craig Kerstiens craigkerstiens.com

The best Postgres feature you're not using

Craig Kerstiens on why common table expressions (CTEs) are so cool: For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. … [CTEs] actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be. Click through for examples of these in use.

read more

PostgreSQL github.com

Odyssey – a scalable PostgreSQL connection pooler

How does Odyssey scale? Odyssey can significantly scale processing performance by specifying a number of additional worker threads. Each worker thread is responsible for authentication and proxying client-to-server and server-to-client requests. All worker threads are sharing global server connection pools. Multi-threaded design plays important role in SSL/TLS performance. Currently in beta, but the authors are using it in production so it’s likely usable at this stage.

read more

Dimitri Fontaine tapoueh.org

PostgreSQL data types – series recap

Dimitri Fontaine, a PostgreSQL major contributor, has written an awesome blog series on PostgreSQL data types to show how to benefit from the PostgreSQL concept of a data type. The PostgreSQL concept of a data type is more than input validation, a PostgreSQL data type also implements expected behaviors and processing functions. This allows an application developer to rely on PostgreSQL for more complex queries, having the processing happen where the data is — for instance when implementing advanced JOIN operations, then retrieving only the data set that is interesting for the application. The posts in this series were extracted from his book Mastering PostgreSQL in Application Development — which teaches SQL to developers so they can replace thousands of lines of code with very simple queries.

read more

PostgreSQL phoronix.com

Postgres looks to LLVM's JIT for up to 20% speed up

This was posted back in March, but it’s news to me: A long-running project has been JIT-compiling SQL queries in PostgreSQL by making use of LLVM’s just-in-time compilation support, rather than passing SQL queries through Postgres’ interpreter. With the LLVM JIT’ed queries, more efficient code is generated by being able to make more use of run-time information and can especially help in increasing the performance of complex SQL queries. JIT-compiling expressions for PostgreSQL has been found to be up to ~20%+ faster in database tests like TPC-H. Creating indexes was found to be even 5~19% faster with this JIT mode Hopefully this feature will progress quick enough to land in Postgres 11. 🙏

read more

Benjie Gillam graphile.org

Graphile –  tools for building performant, pluggable GraphQL APIs

PostGraphile is the new incarnation of PostGraphQL (project history), which introspects your Postgres database schema and creates a fully functional GraphQL API for it. I’ve been poking around with these tools as I get acquainted with the provider side of GraphQL. I don’t think we’ll end up using PostGraphile if/when we ship our public Changelog API (news + podcasts), because I’m a control freak. But it’s been great for getting started quickly and seeing what’s possible. Highly recommended 👌

read more

0:00 / 0:00