database

S3SQLite – A Serverless Relational Database

It’s a thin wrapper around the normal Django SQLite database engine, but it automatically syncs the DB file with S3.

Obviously, this will cause problems for high-write applications, but for high-read applications without concurrent writes, it scales very well, it’s trivial to set up, and it’s orders of magnitudes cheaper that AWS RDS.

PostgreSQL Domain Integrity In Depth

<span class="kw">CREATE</span> EXTENSION uri;

<span class="kw">CREATE</span> <span class="kw">DOMAIN</span> http_uri <span class="kw">AS</span> uri <span class="kw">CHECK</span> (
      uri_scheme(<span class="fu">VALUE</span>) <span class="kw">IS</span> <span class="kw">NOT</span> <span class="kw">NULL</span>
  <span class="kw">AND</span> uri_scheme(<span class="fu">VALUE</span>) <span class="kw">IN</span> (<span class="st">'http'</span>, <span class="st">'https'</span>)
  <span class="kw">AND</span> uri_host(<span class="fu">VALUE</span>) <span class="kw">IS</span> <span class="kw">NOT</span> <span class="kw">NULL</span>
  <span class="kw">AND</span> uri_host(<span class="fu">VALUE</span>) <> <span class="st">''</span>
);

<span class="co">-- works great</span>
<span class="kw">SELECT</span> <span class="st">'https://www.foo.com/bar/baz'</span>:<span class="ch">:http_uri</span>;

<span class="co">-- forbidden</span>
<span class="kw">SELECT</span> <span class="st">'ftp://www.foo.com/bar/baz'</span>:<span class="ch">:http_uri</span>;

CREATE FUNCTION is_valid_cc(smallint[]) RETURNS boolean AS $$
SELECT SUM(
CASE WHEN (pos % 2 = 0) THEN
2*digit - (CASE WHEN digit < 5 THEN 0 ELSE 9 END)
ELSE
digit
END
) % 10 = 0
FROM
unnest(ARRAY( -- loop over digit/position
SELECT $1[i] -- ... which we read backward
FROM generate_subscripts($1,1) AS s(i)
ORDER BY i DESC
)
) WITH ordinality AS t (digit, pos)
$$
LANGUAGE SQL

Google Cloud Spanner Database

The first horizontally scalable, strongly consistent, relational database service

Cloud Spanner is the world’s first fully managed relational database service to offer both strong consistency and horizontal scalability for mission-critical online transaction processing (OLTP) applications. With Cloud Spanner you enjoy all the traditional benefits of a relational database; but unlike any other relational database service, Cloud Spanner scales horizontally to hundreds or thousands of servers to handle the biggest transactional workloads.

Customers across industries use Cloud Spanner for mission-critical use cases including:

Powering customer authentication and provisioning for multi-national businesses

Building consistent systems for transactions and inventory management in the financial services and retail industries

Supporting high-volume systems that require low latency and high throughput in the advertising and media industries

Cool Stuff in PostgreSQL 10: Auto-logging

We started off by creating a logging infrastructure, then arranging for a single table to use it.

Rather than repeat that work for each table, let’s use a relatively although not completely new feature: EVENT TRIGGER. The idea here is that we fire a trigger on CREATE TABLE and see to it that the table is logged. We’ll write the trigger first, even though in reality, we’d need to load the function it calls first.

CREATE EVENT TRIGGER add_logger

 ON ddl_command_end

 WHEN tag IN ('create table')

 EXECUTE PROCEDURE add_logger();

COMMENT ON EVENT TRIGGER add_logger IS 'Ensure that each table which is not a log gets logged';

The magic happens inside add_logger(), but it’s magic we’ve already seen. First, we’ll get the table’s name and schema using pg_event_trigger_ddl_commands(), filtering out tables which are already log tables. The test here is crude and string-based, but we could easily go to schema-based ones.