< ^ txt
Thu May 18 06:00:01 EDT 2023
========================================
Slept from eleven-thirty to seven without waking.
Mostly sunny.
Highs in the mid 60s.
Southeast winds 5 to 10 mph.
# Work
* 11:00 AM - 11:30 AM CTO standup
* 12:00 PM - 01:00 PM Cynerge all-hands
* 12:00 PM - 01:00 PM Solution architecture review
* 02:00 PM - 02:30 PM Cloud assessment regroup
* 03:00 PM - 04:00 PM Xentity bi-weekly eMNEPA call
* 04:00 PM - 04:30 PM Vision for GIS with Eric Aiello and a ton of CTO people
Steve make cocktails for everyone in the afternoon.
# Home
* [x] AWS study
* [ ] schedule AWS cert exam
* [ ] play with text-based (Gemini?) D&D hex thing
* [ ] work on CC 0e D&D reference rules
* [ ] spell list (old to new)
* [ ] monster list (old to new)
* [ ] magic item list (old to new)
* [ ] schedule dentist appointment
* [ ] PenFed
* [ ] transfer funds from old savings account (http://www.citizensbank.com/HSBC)
* [ ] get backup credit card
* [ ] money market?
* [ ] schedule optometrist appointment
* [ ] play with Go web base
Rode the exercise bike for ten minutes in the morning.
https://blog.mastermind.dev/indexes-in-postgresql
> Imagine you have a list of contacts on your iPhone, but this list is not sorted alphabetically. You want to call John. What would you do? You would go through every contact starting from the top and stop on the contact named John. Let’s say your list contains 100 contacts, and if you are lucky enough, you would need to go through 20 - 30 contacts before finding the right one. What if John is the last contact? It would take a lot of time to find the contact.
>
> That’s the problem that is solved by indexes. Without the index on the column, when you do the filtering, the database is doing the full scan, which simply means that it goes through every record unless the matching record is found. It can take a lot of time, depending on the database size.
>
> When you put the index on a column, in our case, the name of the contact, the database creates a special structure that sorts the contacts. So if you are looking for John, you would look for contact names starting with the letter J; it speeds up the searching process a few times.
>
> There are a few types of indexes; I will discuss them next. Let’s focus on the default one, which is B-tree […]
>
> B-tree stands for balanced search tree. It simplifies the binary search tree by allowing nodes with more than two children. Each node contains keys in ascending order.
>
> The truth is that in most of the cases, you should be fine with the b-tree index that is created by the default unless you cope with a very specific information in your database.
[Note that geospatial data may be one of the use cases where there are better index types than b-tree; see GiST and SP-GiST.]
> You won’t benefit from putting indexes on the wrong columns; your database would only take more disk space. In general, the following practices are considered as good: […] Index columns that you search on […] Index columns for database-level validation […] Index columns used for join operations […] Index column that you often use for sorting […].
```
CREATE UNIQUE INDEX slug_idx ON users (slug);
```
> The EXPLAIN command prints the execution plan of a query without executing it. The plan contains the order of the operations, join methods, index usage, and estimated costs:
```
EXPLAIN SELECT title FROM articles WHERE published = true;
```
> The ANALYZE command collects statistics about the data in a table or index. In opposite to the EXPLAIN command, it provides actual runtime performance metrics:
```
ANALYZE SELECT title FROM articles WHERE published = true;
```
> If you plan to add an index on multiple columns, just modify the last part of the command:
```
CREATE INDEX title_category_idx ON articles (title, category);
```
> When you build a query, you use WHERE keyword to narrow search results, and the same is for the indexes creation:
```
CREATE INDEX title_idx ON articles (title) WHERE published = true;
```
> To detect unused indexes in our database, we can use the pg_stat_user_indexes table which contains usage statistics.
> Frequent update and delete operations can lead to a situation where there is a lot of unused space in a table or index relation files on the disk; it is called bloat. Such a situation can cause the performance degradation.
> The bloat is created when auto VACUUM is not enabled or when it’s enabled but is not running frequently enough to keep up with the workload on the database. You can check if this feature is enabled by running the following query:
```
SELECT name, setting, unit FROM pg_settings WHERE name = 'autovacuum';
```
> To remove the bloat from the given index, you can rebuild the index:
```
REINDEX INDEX index_name;
```
> Constraints are related to indexing because each time you create the constraint, you also create an index.
Servings: grains 2/6, fruit 1/4, vegetables 3/4, dairy 2/2, meat 0/3, nuts 2/0.5
Breakfast: bean burrito, carrots, banana, coffee
Lunch: bean burrito
Afternoon snack: a cocktail
Dinner:
< ^ txt