cabecada

This recipe is a work in progress and has never been run as-is.

• timeouts are in ms
• lock timeout: in postgres, when a statement that wants a restrictive lock waits on another lock, other statements that want locks can't jump the queue. so even though the statement that is waiting might only take a very short amount of time, when it starts running, while it is waiting no other statements can begin. So we set the lock timeout pretty low and retry if we don't get it.
• statement timeout: we set a short statement timeout before statements which do lock and which we expect to take a short amount of time, just in case something about our assumptions/understanding is wrong and the statement ends up taking a long time. if this happens the statement will bail early without causing harm, and we can investigate what is wrong with

cabecada

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:region:account-id:dbuser:dbi-resource-id/database-user-name"

cabecada

-- 
-- Befor using it you should enable dblink extension in database and allow user to run dblink_connect_u
-- You may need to change 'dbname=osm' to your db connection options in line 34.
-- CREATE EXTENSION dblink;
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO user;
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO user;
--

DROP FUNCTION IF EXISTS public.g_parsel(query text, table_to_chunk text, num_chunks integer);
CREATE OR REPLACE FUNCTION public.g_parsel(query text, table_to_chunk text, num_chunks integer default 2)

cabecada

t

pg_extension_name: citus pg_extension_version: 11.1-1 pg_readme_generated_at: 2023-01-30 17:35:52.80893+03 pg_readme_version: 0.5.5

Citus distributed database

cabecada

🗺🐘 1000+ PostgreSQL EXTENSIONs

This is a list of URLs to PostgreSQL EXTENSION repos, listed in alphabetical order of parent repo, with active forks listed under each parent.

⭐️ >= 10 stars
⭐️⭐️ >= 100 stars
⭐️⭐️⭐️ >= 1000 stars
Numbers of stars might not be up-to-date.

cabecada

1.1 Linux process management

• process scheduling
• interrupt handling
• signaling
• process prioritization
• process switching
• process state
• process memory

cabecada

CREATE ROLE owner LOGIN ENCRYPTED PASSWORD 'secret' CONNECTION LIMIT 3;
ALTER ROLE owner SET statement_timeout = 20000;
ALTER ROLE owner SET lock_timeout = 3000;
ALTER ROLE owner SET idle_in_transaction_session_timeout = 3000; -- v9.6+

CREATE ROLE readwrite_users NOLOGIN;
CREATE ROLE readonly_users NOLOGIN;

CREATE DATABASE exampledb WITH OWNER owner ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

cabecada

CREATE OR REPLACE FUNCTION public.clone_schema(
	source_schema text,
	dest_schema text)
RETURNS void AS
$BODY$
DECLARE
object text;
buffer text;
default_ text;
column_ text;

cabecada

# Sharding within Foreign Data Wrappers and Inheritance [WIP]

Previous sources:
https://www.depesz.com/2015/04/02/waiting-for-9-5-allow-foreign-tables-to-participate-in-inheritance/
http://snowman.net/slides/pgfdw_sharding.pdf

Postgres 10 next things:
https://wiki.postgresql.org/wiki/Built-in_Sharding
<logical replication>

cabecada

-- So as an example of using a log orientated approach in PostgreSQL,
-- let's write a simple blog application. We will want to be able to:

-- * Write and edit blog posts
-- * Publish revisions of posts for public viewing
-- * Delete posts
-- * Add or remove tags to posts

-- Let's start by creating a schema.