micktwomey + postgresql   49

yandex/odyssey: Scalable PostgreSQL connection pooler
Advanced multi-threaded PostgreSQL connection pooler and request router.

postgresql  postgres  database 
22 days ago by micktwomey
avoid OR for better PostgreSQL query performance - Cybertec
PostgreSQL query tuning is our daily bread at Cybertec, and once you have done some of that, you’ll start bristling whenever you see an OR in a query, because they are usually the cause for bad query performance.

Of course there is a reason why there is an OR in SQL, and if you cannot avoid it, you have to use it. But you should be aware of the performance implications.

In this article I’ll explore “good” and “bad” ORs and what you can do to avoid the latter.
postgresql  optimization  performance 
4 weeks ago by micktwomey
Top Backup Tools for PostgreSQL | Severalnines
Examples of different backup tools for PostgreSQL (pgBackRest, Barman, Amanda).
postgresql  postgres  tools 
9 weeks ago by micktwomey
jberkus/annotated.conf: Presentation materials for the Annotated.Conf Tutorial
This repo contains materials for the GUCS tutorial from SCALE 16x.

More importantly, it contains annotations of all 269 postgresql.conf settings for PostgreSQL 10.
11 weeks ago by micktwomey
migra is a schema comparison tool for PostgreSQL.
python  postgresql 
march 2018 by micktwomey
PostgreSQL rocks, except when it blocks: Understanding locks
Handy, covers PG locking discussions I normally have with developers :)
One problem I often see users struggle with when it comes to Postgres is locks. While Postgres is amazing at running multiple operations at the same time, there are a few cases in which Postgres needs to block an operation using a lock. You therefore have to be careful about which locks your transactions take, but with the high-level abstractions that PostgreSQL provides, it can be difficult to know exactly what will happen. This post aims to demystify the locking behaviors in Postgres, and to give advice on how to avoid common problems.
postgres  postgresql 
february 2018 by micktwomey
Principles and Applications of the Index Types Supported by PostgreSQL
PostgreSQL has different index interfaces for different data types and business scenarios. Let’s introduce the principles and application scenarios for each index.
postgresql  postgres 
february 2018 by micktwomey
Cybertec Postgres Configurator
Enter in some specs and details on the workloads and you get a sample PostgreSQL config.
postgresql  postgres  tool  configuration 
january 2018 by micktwomey
Experiences with running PostgreSQL on Kubernetes
Below is a transcript of an interview I had with our CTO, Sasha Klizhentas, about his experience running PostgreSQL on Kubernetes. In this interview, we discuss the challenges involved, open source and commercial tools that can help and other alternatives to managing stateful applications on Kubernetes.

For some background, Gravitational specializes in running applications across a variety of infrastructure footprints with the help of Kubernetes. The applications our customers deploy need a persistent data store to go along with their stateless microservices. Making things more complicated is the fact that the majority of our deployments are on-premise private SaaS, so we can not rely on cloud services like AWS RDS.
postgresql  kubernetes  docker  postgres 
january 2018 by micktwomey
Understanding caching in Postgres - An in-depth guide | Madusudanan
Caching can be considered an important aspect in tuning database system performance.

While this post is mainly focused on postgres, it can be easily compared and understood with other database systems.
database  postgresql  postgres 
october 2017 by micktwomey
Using Atomic Transactions to Power an Idempotent API — Brandur Leach
Part one of a series on getting easy data correctness by building APIs on the transactional machinery of Postgres.
postgresql  api 
september 2017 by micktwomey
Autovacuum Tuning Basics
A few weeks ago I covered the basics of tuning checkpoints, and in that post I also mentioned that the second common source of performance issues is autovacuum (based on what we see on the mailing list and at our customers under support). So let me follow-up on that with this post about the basics of autovacuum tuning. I’ll very briefly explain the necessary theory (dead tuples, bloat and how autovacuum deals with it), but the main focus of this blog post is tuning – what configuration options are there, rules of thumb, etc.
postgresql  performance 
september 2017 by micktwomey
PostgreSQL, Aggregates and Histograms
Generating a histogram in the psql console using width_bucket.
postgresql  sql  statistics 
august 2017 by micktwomey
What is Barman? What problem resolves?
Barman allows your company to implement disaster recovery solutions for PostgreSQL databases with high requirements of business continuity. Taking an online hot backup of PostgreSQL is now as easy as ordering a good espresso coffee.
backup  postgres  postgresql  tool 
august 2017 by micktwomey
Getting Started with Event Sourcing in Postgres – BackerKit Blog
In a previous post we explored how our application tracks sent emails. We set things up to update or insert a record for each email with the current state (delivered, dropped, etc). In this post we’ll look at an alternative data model where we’ll store each event as its own record and then roll up the events to get the current state of a particular email.

With this different modeling we’ll be able to understand the full lifecycle of an email allowing us to debug issues better as well as have a better understanding of how our users interact with our emails. In our original setup we could only know about the most recent email event. We’ll use the concept of event sourcing to maintain this history.
database  postgres  postgresql 
august 2017 by micktwomey
Sensible Defaults
When I first set out to create pgcli, my goal was to design a Postgresql client that shipped with sensible defaults. It shouldn't require fiddling with config files to enable features.

How did we do on that goal? This is one of those subjective goals that is not so easy to measure. But fortunately, Craig posted a useful blogpost that shows how to configure your psql shell to make it powerful.

I figured I'll use that as a scoring card to see how many of those features are shipped by default in pgcli.
cli  postgresql  database 
july 2017 by micktwomey
Postgres EXPLAIN Visualizer (pev)
Pev is designed to make Postgres query plans easier to grok. It displays a plan as a tree, with each node representing a step that takes in a row set and produces another.
database  postgres  visualization  postgresql  psql 
july 2017 by micktwomey
The big old reliable elephant: talking about Postgres with Craig Kerstiens

In this opportunity I interviewed Craig Kerstiens. Craig works for citusdata, a company that helps customers scale databases beyond a single node.
postgres  postgresql  citusdata  sql  dbs 
april 2017 by micktwomey
RethinkDB versus PostgreSQL: my personal experience › SageMathCloud Blog
Nice anecdotal story about SageMath's move from RethinkDB to PostgreSQL. I wonder how many projects moved due to the licensing issues and might never go back.
postgresql  rethinkdb  database 
february 2017 by micktwomey
A tour of Postgres' Foreign Data Wrappers Craig Kerstiens
Foreign Data Wrappers (FDW) in psql, wonder can you use materialised views over these? (Yes you can, in fact materialised views are the recommended way to cache for performance, neat.)
september 2016 by micktwomey
PGCon2016: Index Internals
PostgreSQL includes several index types: GiST, SP-GiST, GIN, and of course, the regular B-tree. DBAs are familiar with using each of these for specific use cases, GIN for full-text search, GiST for geometrical data, and so on, but how do they work internally? What makes them suitable for the cases they're typically used for?

In this presentation, I will walk through the internal structure of each of these index types, explaining what strengths and weaknesses each one of them have.
database  postgres  postgresql  sql 
august 2016 by micktwomey
"BedquiltDB is an open-source PostgreSQL extension and set of code libraries, which wraps the PostgreSQL jsonb data type in a slick programmatic API inspired by NoSQL document stores."
database  postgresql 
july 2016 by micktwomey
Introduction to PostgreSQL physical storage
Nice post detailing the storage implementation of PostgreSQL.
january 2016 by micktwomey
UUID vs BIGSERIAL for Primary Keys
At a glance at this article it seems UUIDs work just fine as primary keys in psql :)
postgresql  database  sql 
october 2015 by micktwomey
"Pgcli is a command line interface for Postgres with auto-completion and syntax highlighting."
cli  database  postgresql  sql 
september 2015 by micktwomey
REST API for any Postgres database
api  database  postgres  postgresql  rest  haskell 
january 2015 by micktwomey
MySQL to PostgreSQL translation tool which is fast? Sign me up :)
postgresql  postgres  mysql  tools 
january 2015 by micktwomey
Postgres: The Bits You Haven't Found
Collection of nice tips and features in PostgresSQL. Useful for folks coming from other databases.
via:hackernews  postgresql  tips  database  sql 
march 2013 by micktwomey
One Change Is Not Enough - Aeracode
On lanyrd's move to new host, new OS version and a new DB backend.
via:hackernews  postgresql  ubuntu  aws 
november 2012 by micktwomey
Running PostgreSQL on AWS
Good tips for running PostgreSQL on AWS.
postgresql  aws 
june 2012 by micktwomey
PostgreSQL When It’s Not Your Job at DjangoCon Europe
Good talk on how to tune a PostgreSQL db, for folks who aren't dbas :)
june 2012 by micktwomey
Building a MongoDB Clone in Postgres: Part 1 - LegitimateSounding.com
Experiment to create a MongoDB compatible implementation using PLV8 and PostgreSQL.
postgresql  mongodb 
june 2012 by micktwomey
An asynchronous postgres adapter for twisted.
twisted  postgresql  database  python 
february 2006 by micktwomey

Copy this bookmark: