Gary Sieling » Database Performance Tuning

Imagine a system that uses large-ish hex string identifiers, e.g. similar to UUIDs. To make the math easy, lets say we make a table with a million have 20 character IDs: create table ids1 (id cha...

https://www.garysieling.com/blog/efficient-storage-of-large-ids-in-postgres/?utm_source=rss&utm_medium=rss&utm_campaign=efficient-storage-of-large-ids-in-postgres

The rise of “platform” sites (e.g. Heroku) enables developers to build and deploy web applications cheaply, without understanding operational problems. Typically these products let you purcha...

https://www.garysieling.com/blog/validating-application-performance-in-a-cloud-environment-using-c-hangfire-and-rethinkdb/?utm_source=rss&utm_medium=rss&utm_campaign=validating-application-performance-in-a-cloud-environment-using-c-hangfire-and-rethinkdb

The following query finds all tables in a schema, ordered by row count: SELECT sysobjects.* , sysindexes.Rows FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id WHERE type = '...

https://www.garysieling.com/blog/finding-the-number-of-rows-of-all-tables-in-a-sql-server-database/?utm_source=rss&utm_medium=rss&utm_campaign=finding-the-number-of-rows-of-all-tables-in-a-sql-server-database

Liquibase is a tool for managing database schemas (e.g. diffing schemas and writing migration scripts), and supports most major commercial and open-source relational databases. I’ll show how to...

https://www.garysieling.com/blog/comparing-two-sql-server-databases-with-liquibase/?utm_source=rss&utm_medium=rss&utm_campaign=comparing-two-sql-server-databases-with-liquibase

Implementing Auditing Storing every change to an application’s database allows for sophisticated forensic analysis- usage trends over time, as a long-range debugger or for implementing data cor...

https://www.garysieling.com/blog/auditing-data-changes-postgres/?utm_source=rss&utm_medium=rss&utm_campaign=auditing-data-changes-postgres

I saw a talk by some operations engineers who work for ARIN at PGConf 2014. They presented their architecture for high availability, which uses CMAN, Corosync, and Pacemaker, which handle message...

https://www.garysieling.com/blog/postgres-high-availability-talk-notes-pgconf-2014/?utm_source=rss&utm_medium=rss&utm_campaign=postgres-high-availability-talk-notes-pgconf-2014

Tuning SQL queries is a useful skill, and while many people struggle to manage complex SQL, the work is actually a series of simple tricks. For instance, refactoring a query often brings about al...

https://www.garysieling.com/blog/testing-output-postgres-queries-tuning/?utm_source=rss&utm_medium=rss&utm_campaign=testing-output-postgres-queries-tuning

Setup I’ve set up several scenarios to see how Postgres handles querying multiple full-text indexes. To demonstrate these scenarios I set up the following table and indexes, which has 1.1 milli...

https://www.garysieling.com/blog/querying-multiple-postgres-full-text-indexes/?utm_source=rss&utm_medium=rss&utm_campaign=querying-multiple-postgres-full-text-indexes

For this test, I set up a 1.1 million row table1 using data from github2. If you’re not familiar with full-text in Postgres, they use the @@ operator to apply a query to a document3, which look...

https://www.garysieling.com/blog/gin-vs-gist-for-faceted-search-with-postgres-full-text-indexes/?utm_source=rss&utm_medium=rss&utm_campaign=gin-vs-gist-for-faceted-search-with-postgres-full-text-indexes

When I attempted to implement faceted search against a Postgres full-text index, I found I had issues getting Postgres (9.2) to pick up the index: CREATE INDEX search_idx2 ON data2 USING gin(to_t...

https://www.garysieling.com/blog/fixing-issues-where-postgres-optimizer-ignores-full-text-indexes/?utm_source=rss&utm_medium=rss&utm_campaign=fixing-issues-where-postgres-optimizer-ignores-full-text-indexes