Skip to content

Instantly share code, notes, and snippets.

@steve-chavez
steve-chavez / accounting.sql
Created July 7, 2024 18:12 — forked from NYKevin/accounting.sql
Basic double-entry bookkeeping system, for PostgreSQL.
CREATE TABLE accounts(
id serial PRIMARY KEY,
name VARCHAR(256) NOT NULL
);
CREATE TABLE entries(
id serial PRIMARY KEY,
description VARCHAR(1024) NOT NULL,
amount NUMERIC(20, 2) NOT NULL CHECK (amount > 0.0),
-- Every entry is a credit to one account...
@steve-chavez
steve-chavez / psql_commands_history.md
Created September 5, 2022 22:06 — forked from lovubuntu/psql_commands_history.md
History for psql commands

There's no history in the database itself, if you're using psql you can use "\s" to see your command history there.

You can get future queries or other types of operations into the log files by setting log_statement in the postgresql.conf file. What you probably want instead is log_min_duration_statement, which if you set it to 0 will log all queries and their durations in the logs. That can be helpful once your apps goes live, if you set that to a higher value you'll only see the long running queries which can be helpful for optimization (you can run EXPLAIN ANALYZE on the queries you find there to figure out why they're slow).

Another handy thing to know in this area is that if you run psql and tell it "\timing", it will show how long every statement after that takes. So if you have a sql file that loo

@steve-chavez
steve-chavez / makefile
Created June 22, 2022 17:40 — forked from ArtemGr/makefile
SPI helper for a PostgreSQL CHECK when using one-to-many with a jsonb.
all: spi.so
spi.so: spi.o makefile
g++ -shared -o spi.so spi.o
cp --remove-destination spi.so /var/lib/postgresql/spi.so
spi.o: spi.cc makefile
g++ -g -O2 -Wall -std=c++11 -fpic -c -o spi.o -I/usr/include/postgresql -I/usr/include/postgresql/9.4/server spi.cc
clean:
@steve-chavez
steve-chavez / Arcan.nix
Created April 29, 2021 14:45 — forked from egasimus/Arcan.nix
Building Arcan on NixOS, 2020 version
({ lib, newScope, stdenv, pkgs }: let
# nicer aliases
derive = stdenv.mkDerivation;
concat = builtins.concatStringsSep " ";
# vendored libuvc: don't build, just make sources available
libuvc-src = derive {
name = "libuvc-src";
# using fetchgit instead fetchFromGitHub because
-- This script was generated by the Schema Diff utility in pgAdmin 4
-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated
-- and may require manual changes to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps.
BEGIN;
-- Cast: money -> bigint
{
"success": 1,
"errormsg": "",
"info": "",
"result": null,
"data": [
{
"id": 326,
"type": "cast",
"label": "Casts",
@steve-chavez
steve-chavez / Dockerfile
Last active September 27, 2022 17:29
Metarest: create database objects from Supabase client libs
FROM postgres
RUN metaDependencies="git \
ca-certificates \
build-essential" \
&& apt-get update \
&& apt-get install -y --no-install-recommends ${metaDependencies} \
&& apt-get install -y build-essential \
&& apt-get install make \
&& cd /tmp \
@steve-chavez
steve-chavez / map.sql
Last active January 31, 2022 02:25
Create a OSM map from a pg function with PostgREST
-- From
-- + http://duspviz.mit.edu/tutorials/intro-postgis.php
-- + http://duspviz.mit.edu/web-map-workshop/leaflet_nodejs_postgis/
CREATE TABLE coffee_shops
(
id serial NOT NULL,
name character varying(50),
address character varying(50),
city character varying(50),
state character varying(50),
# From: https://stevechavez.xyz/nixops-blog.html
let
region = "us-east-2";
accessKeyId = "dev";
in {
network.description = "stevechavez.xyz";
resources = {
ec2KeyPairs.xyzKeyPair = { inherit region accessKeyId; };
elasticIPs.xyzIP = { inherit region accessKeyId; };
@steve-chavez
steve-chavez / nginx-tuning.md
Created April 25, 2020 23:07 — forked from denji/nginx-tuning.md
NGINX tuning for best performance

Moved to git repository: https://github.com/denji/nginx-tuning

NGINX Tuning For Best Performance

For this configuration you can use web server you like, i decided, because i work mostly with it to use nginx.

Generally, properly configured nginx can handle up to 400K to 500K requests per second (clustered), most what i saw is 50K to 80K (non-clustered) requests per second and 30% CPU load, course, this was 2 x Intel Xeon with HyperThreading enabled, but it can work without problem on slower machines.

You must understand that this config is used in testing environment and not in production so you will need to find a way to implement most of those features best possible for your servers.